Recyclebin
Oracle has
provided two views user_recyclebin and dba_recyclebin for this purpose
but for ease we also have recyclebin synonym which points to
user_recyclebin.
By
default recyclebin parameter is "ON" in Oracle 10g and later, but you
can disable it if you don't want to use this feature. We will discuss
this later on in this article.
Let's understand this feature with an example.
Create
a table as shown, we have used serial_no to maintain the different
versions of the same table. This point will become clear later on.
Insert a row in the table with serial_no as "1".
SQL> create table test (serial_no number,c_date date);
Table created.
SQL> insert into test values (1,sysdate);
1 row created.
If we
query user_tables and tab, we find that our new object has been
created.Our recyclebin view is still empty as we have only created the
table and nothing is dropped yet.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST USERS
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
TEST TABLE
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected
Now we will drop the table and see what happens. There
is no row in user_tables and it was expected as we have dropped the
table. There is a row in recyclebin view which was also expected as the
table that we have dropped has moved to recyclebin. The object_name
represents the new name of the dropped object, original_name represents
the name of the original object which was dropped, type tells us the
type of object dropped, can_undrop tells us whether the object can be
undropped or not, can_purge tells us that whether object can be deleted
from the recylebin or not.
SQL> drop table test;
Table dropped.
SQL> select table_name,tablespace_name from user_tables;
no rows selected
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TABLE
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CU CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST TABLE YES YES
Now
we will again create the table test and insert a row into it. This time
serial_no is 2. If we closely look at the view "TAB" we find that it
shows two entries one for newly created object and another for dropped
table object hence we can conclude that TAB view is made from two views
"user_tables" and "user_recyclebin".
SQL> create table test (serial_no number,c_date date);
Table created.
SQL> insert into test values (2,sysdate);
1 row created.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TABLE
TEST TABLE
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CU CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST TABLE YES YES
We
will drop this table again and query the recyclebin view. Now there are
two entries one for serial_no "1" and another for serial_no "2" which
is shown in the query below. Make sure to use ' " ' (double quotes)
while querying tables in recyclebin.
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CU CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7o6RjgQKjAMgRUvw==$0 TEST TABLE YES YES
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST TABLE YES YES
SQL> select * from "BIN$z3VTkO7n6RjgQKjAMgRUvw==$0";
SERIAL_NO C_DATE
---------- ---------
1 27-NOV-12
SQL> select * from "BIN$z3VTkO7o6RjgQKjAMgRUvw==$0";
SERIAL_NO C_DATE
---------- ---------
2 27-NOV-12
Flashback
Flashback
is used to recover dropped objects from recyclebin. Flashback recovers
the most recent dropped object from recyclebin. In our case also test
table with serial_no "2" was the most recent one and hence it was
recovered. The first copy of test table is still in the recyclebin. If
you want to use that copy you need to rename test to test_tmp and then
execute flashback query again. Or you can also use "flashback table
"BIN...." to before drop" command directly.
SQL> flashback table test to before drop;
Flashback complete.
SQL> select * from test;
SERIAL_NO C_DATE
---------- ---------
2 27-NOV-12
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CU CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7n6RjgQKjAMgRUvw==$0 TEST TABLE YES YES
Purging
Purging
is used to delete objects from recyclebin. You can delete individual
objects by using command "purge table object_name". To delete all the
objects in recyclebin at once you can use command "purge recyclebin".
Make sure you purge objects from the recyclebin periodically as these
objects are still occupying the same segments and eating up your space.
You can also disable the recyclebin option as shown in next section.
SQL> purge table "BIN$z3VTkO7n6RjgQKjAMgRUvw==$0";
Table purged.
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected
SQL> purge recyclebin;
Recyclebin purged.
If
the purge clause is used with drop command then object does not move to
recyclebin, it is deleted permanently and can't be recovered from
flashback.
SQL> drop table test purge;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected
No data in recyclebin as table is purged with drop. It is similar to windows command (Shift + Delete).
Recyclebin Parameter
There
might be a situation wherein you don't want to use this feature. You
can do this simply by setting recyclebin parameter "OFF". Now it becomes
equivalent to "drop table purge" command. You can set this parameter at
both session and system level.
SQL> create table test (serial_no number,c_date date);
Table created.
SQL> insert into test values (3,sysdate);
1 row created.
SQL> alter session set recyclebin=off;
Session altered.
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
no rows selected
Impact on Dependent Objects
Here we
will see what happens to the dependent objects of dropped objects. We
will create index along with the table and then drop the table. On
dropping, index will also be dropped.
SQL> create table test (serial_no number,c_date date);
Table created.
SQL> create index test_idx on test (serial_no);
Index created.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST USERS
SQL> select index_name, table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
TEST_IDX TEST
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CU CP
------------------------------ ------------------ ---------- --- ---
BIN$z3VTkO7p6RjgQKjAMgRUvw==$0 TEST_IDX INDEX NO YES
BIN$z3VTkO7q6RjgQKjAMgRUvw==$0 TEST TABLE YES YES
As
you can see clearly in recylebin that there are two entries, one for
the table and another for the index. Once you issue a flashback query
both the objects are restored.
SQL> flashback table test to before drop;
Flashback complete.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST USERS
SQL> select index_name, table_name from user_indexes;
INDEX_NAME TABLE_NAME
------------------------------ ------------------------------
BIN$z3VTkO7p6RjgQKjAMgRUvw==$0 TEST
One
thing to notice here is that name of the original index is lost as can
be clearly seen from user_indexes view. So keep this thing in mind while
restoring tables with index and make sure to rename your index so as to
give them meaningful name.
Note: If an index is dropped without the table then it doesn't move to recyclebin, it is purged directly.
Can_Undrop and Can_Purge
Now we will see the use "can_undrop" and "can_purge"
columns.Can_undrop column is "NO" for row corresponding to index, which
means that this object can not be undropped individually as this is a
dependent object. But you can purge this object from recyclebin.
SQL> drop table test;
Table dropped.
SQL> Select object_name, original_name, type, can_undrop cu, can_purge cp from recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE CU CP
------------------------------ ------------------------------ ---------- --- ---
BIN$z3VTkO7s6RjgQKjAMgRUvw==$0 TEST TABLE YES YES
BIN$z3VTkO7r6RjgQKjAMgRUvw==$1 BIN$z3VTkO7p6RjgQKjAMgRUvw==$0 INDEX NO YES
SQL> purge index "BIN$z3VTkO7r6RjgQKjAMgRUvw==$1";
Index purged.
If we
flashback now only the table is restored as we have already deleted the
index from recyclebin. We will have to create the index manually.
SQL> flashback table test to before drop;
Flashback complete.
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
TEST USERS
SQL> select index_name, table_name from user_indexes;
no rows selected
No comments:
Post a Comment