Tuesday, September 12, 2006

Recyclebin 10g

I have looked into the recyclebin feature in 10g.
I have tested it by dropping a table form the user scott, comiited the drop and recovered the table.


SQL> conn scott/tiger
Connected.

SQL> descr recyclebin
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJECT_NAME NOT NULL VARCHAR2(30)
ORIGINAL_NAME VARCHAR2(32)
OPERATION VARCHAR2(9)
TYPE VARCHAR2(25)
TS_NAME VARCHAR2(30)
CREATETIME VARCHAR2(19)
DROPTIME VARCHAR2(19)
DROPSCN NUMBER
PARTITION_NAME VARCHAR2(32)
CAN_UNDROP VARCHAR2(3)
CAN_PURGE VARCHAR2(3)
RELATED NOT NULL NUMBER
BASE_OBJECT NOT NULL NUMBER
PURGE_OBJECT NOT NULL NUMBER
SPACE NUMBER


SQL> select object_name from user_objects;


OBJECT_NAME

--------------------------------------------------------------------------------
PK_DEPT
DEPT
EMP
PK_EMP
BONUS
SALGRADE

6 rows selected.

SQL> drop table salgrade;

Table dropped.

SQL> commit;

Commit complete.

SQL> select droptime, original_name from recyclebin;

DROPTIME ORIGINAL_NAME
------------------- --------------------------------
2006-08-29:07:10:28 SALGRADE


SQL> select object_name, original_name, operation from recyclebin;

OBJECT_NAME ORIGINAL_NAME OPERATION
------------------------------ -------------------------------- ---------
BIN$HCIPm0RKljjgQKjAAV0coQ==$0 SALGRADE DROP


SQL> select object_name from user_objects;

OBJECT_NAME
--------------------------------------------------------------------------------
PK_DEPT
DEPT
EMP
PK_EMP
BONUS
BIN$HCIPm0RKljjgQKjAAV0coQ==$0

6 rows selected.

SQL> flashback table salgrade to before drop;

Flashback complete.

SQL> select object_name from recyclebin;

no rows selectedSQL> select object_name from user_objects;

OBJECT_NAME
--------------------------------------------------------------------------------
PK_DEPT
DEPT
EMP
PK_EMP
BONUS
SALGRADE

6 rows selected.

SQL> select * from salgrade;

GRADE LOSAL HISAL
---------- ---------- ----------
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999


The recyclebin feature does not work for sys objects
If you realy want to delete the table and release the space, use the purge command.

No comments: