There are two recyclebin views present in ORACLE :
1. USER_RECYCLEBIN and
2. DBA_RECYCLEBIN.
For convenience, when we are firing queries at RECYCLEBIN which is a public synonym that points to USER_RECYCLEBIN. The RECYCLEBIN is enabled by default in 10g, but one can turn it on or off with the RECYCLEBIN init parameter, at the session level or system level.
Let’s say RECYCLEBIN is enabled, any tables that we drop are not actually getting deleted. Instead, Oracle just renames that particular table which has been dropped and all its associated objects (indexes, triggers, LOB segments, etc) to a system-generated name that begins with BIN$.
For example, consider this simple table:
SQL> Create table test (num number);
Table created.
SQL> Insert into test values (1);
1 row created.
SQL> Insert into test values (2);
1 row created.
SQL> Insert into test values (3);
1 row created.
SQL> Select * from test;
NUM
----------
1
2
3
If the RECYCLEBIN init parameter is set to ON (the default in 10g), then the dropped table will placed in the RECYCLEBIN:
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_DROP CAN_PURGE DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- ------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:50:33
What has happened to the table is that when we dropped it that has got renamed. The table data is still there and can be queried just like a normal table:
Note: the table name must be in quotes (“ “)
SQL> select * from "BIN$brwK19W3F/7gRAADuhyf0w==$0";
NUM
----------
1
2
3
Since the table data is still there, we can "undrop" the table. This operation is known as a "flashback drop". This operation will simply rename the BIN$... table to its original name:
SQL> Flashback table "BIN$brwK19W3F/7gRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
NUM
----------
1
2
3
It's important to know that after you've dropped a table, it has only been renamed; the table segments still exist there in your tablespace, unchanged, occupying space. This space still counts against your user tablespace quotas, as well as filling up the tablespace. It will not be reclaimed until you get the table out of the recyclebin. You can remove an object from the recyclebin by restoring it, or by purging it from the recyclebin.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime from recyclebin;
Object_name original_name type can_undrop can_purge droptime
--------------------------------------------------------------------------------
BIN$brwK19W3F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-09:12:26:22
SQL> purge table “BIN$brwK19W3F/7gRAADuhyf0w==$0” ;
Table purged.
SQL> select * from recyclebin;
No rows returned.
You have several purge options. You can also purge everything from the USER_RECYCLEBIN using PURGE RECYCLEBIN; a user with DBA privileges can purge everything from all recyclebins using DBA_RECYCLEBIN; and finally, you can purge recyclebin objects by schema and user with PURGE TABLESPACE USER.
Unless you purge them, Oracle will leave objects in the recyclebin until the tablespace runs out of space, or until you hit your user quota on the tablespace. At that point, Oracle purges the objects one at a time, starting with the ones dropped the longest time ago, until there is enough space for the current operation. If the tablespace data files are AUTOEXTEND ON, Oracle will purge recyclebin objects before it autoextends a datafile.
Dealing with dropped table versions
Just as you can wind up with several versions of a file with the same name in the Windows recycle bin, you can wind up with several versions of a table in the Oracle recyclebin also. For example, if we create and drop the TEST table twice, we'll have two versions in the recyclebin:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> create table test (test_col varchar2(10));
Table created.
SQL> insert into test values('Version 2');
1 row created.
SQL> commit;
Commit complete.
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop as "UND", can_purge as "PUR", droptime fro
m recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE UND PUR DROPTIME
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brrGkpUUF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:05
BIN$brrGkpUVF7vgRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:08:48:31
Now query the two dropped tables to verify that whether they are different:
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we are going to issue a FLASHBACK DROP command for TEST, which version will Oracle restore?
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 2
Oracle will always restore the most recent version of the dropped object. If we want the previous version then we can select the new BIN$... name of the TEST from the recyclebin and we can perform flashback query operation on the BIN$... table as follows. This will restore the required version of the TEST table.
SQL> select * from "BIN$brrGkpUUF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 1
SQL> select * from "BIN$brrGkpUVF7vgRAADuhyf0w==$0";
TEST_COL
----------
Version 2
Now we want to restore the Version 1 table then we need to capture the BIN$... name of the TEST table i.e.,
BIN$brrGkpUUF7vgRAADuhyf0w==$0
Now issue the flash back query as follows it will restore the required version of the TEST table.
SQL> flashback table "BIN$brrGkpUUF7vgRAADuhyf0w==$0" to before drop;
Flashback complete.
SQL> select * from test;
TEST_COL
----------
Version 1
Going with DEPENDENT OBJECTS
In a modern RDBMS, Most will have indexes, constraints, and/or triggers some stand alone. When a table is dropped ORALCE drops the dependent objects associated with it. When you drop a table with the recyclebin enabled, the table and its dependent objects get renamed, but still have the same structure as before. The triggers and indexes get modified to point to the new BIN$ table name. (Any stored procedures that referenced the original object, will be invalidated.)
For example:
SQL> create table test (test_col varchar2(10));
Table created.
SQL> create index ind_test_col on test(test_col);
Index created.
SQL> insert into test values ('Version 1');
1 row created.
SQL> commit;
Commit complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WxF/7gRAADuhyf0w==$0 IND_TEST_COL INDEX NO YES 2009-07-15:10:18:46 53649 53650
BIN$brwK19WyF/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:18:46 53649 53649
SQL> flashback table test to before drop;
Flashback complete.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
no rows selected
The RECYCLEBIN views have a few other columns that make the relationship between TEST and IND_TEST_COL clear:
The PURGE_OBJECT column is the object number of the object itself; eg. the object number of IND_TEST_COL is 53650. Note the value of the BASE_OBJECT column for IND_TEST_COL: 53649, the object number of the associated version of the TEST table.
If we FLASHBACK DROP the TEST table, its index will be restored - but Oracle will not rename it to its original name. It will retain its BIN$.. name:
SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME
------------------------------
BIN$brwK19WxF/7gRAADuhyf0w==$0
SQL> drop table test;
Table dropped.
SQL> select object_name, original_name, type, can_undrop, can_purge, droptime, base_object, purge_object from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CAN_UNDROP CAN_PURGE DROPTIME BASE_OBJECT PURGE_OBJECT
------------------------------ -------------------------------- ------------------------- --- --- --
BIN$brwK19WzF/7gRAADuhyf0w==$1 BIN$brwK19WxF/7gRAADuhyf0w==$0 INDEX NO YES 2009-07-15:10:43:33
BIN$brwK19W0F/7gRAADuhyf0w==$0 TEST TABLE YES YES 2009-07-15:10:43:33 53649 53649
Note the values in the CAN_UNDROP and CAN_PURGE columns for the index. An index cannot be undropped without the table - so CAN_UNDROP is set to NO. It can, however, be purged without purging the table:
SQL> purge index "BIN$brwK19WzF/7gRAADuhyf0w==$1";
Index purged.
Now, if we restore the table, it will be restored without the index:
SQL> select index_name from user_indexes where table_name='TEST';
no rows selected
LIMITATIONS
A few types of dependent objects are not handled like the simple index above.
o Bitmap join indexes are not put in the recyclebin when their base table is dropped, and not retrieved when the table is restored with FLASHBACK DROP.
o The same applies for materialized view logs; when you drop a table, all mview logs defined on that table are permanently dropped, not put in the recyclebin.
o Referential integrity constraints that reference another table are lost when the table is put in the recyclebin and then restored.
If space limitations force Oracle to start purging objects from the recyclebin, it purges indexes first. If you FLASHBACK DROP a table whose associated indexes have already been purged, it will be restored without the indexes.
No comments:
Post a Comment