Recyclebin : A new feature of RECYCLEBIN was introduced in Oracle 10g.
Enabled : By Default RECYCBLEBIN is enabled. It can be disabled.
There are two SYS owned views USER_RECYCLEBIN and DBA_RECYCLEBIN.
RECYCLEBIN$ is the table owned by SYS. Query DBA_OBJECTS for RECYCLEBIN and you will find these and others.
Basically, in simple terms it works similar to recyclebin in Windows. When you delete an object it goes to recyclebin, either you can recover it from there or PURGE recyclebin and get rid of it.
PURGE :
1. DROP TABLE PURGE;
· This will drop the table and purge at the same time and will not move it to recyclebin. It is similar to dropping a table in earlier versions (9i and below)
2. PURGE TABLESPACE tablespace_name USER username;
Substitute tablespace_name and username.
· All objects in a particular tablespace for a user that have been dropped and are still in recyclebin can be purged.
3. PURGE TABLESPACE tablespace_name
Substitute tablespace_name.
· All objects in a particular tablespace that have been dropped and are still in recyclebin can be purged.
4. PURGE RECYCLEBIN;
· This can be used to purge user recyclebin. · Note : USER_RECYCBLEBIN synonym refers to RECYCLEBIN
5. PURGE DBA_RECYCLEBIN;
· As a dba you can clear entire recyclebin and clean (purge) all the dropped objects.
You can also purge a particular object in the recyclebin after it is dropped.
Objects in recycle bin are purged, if there are space constraints (running out of space in the tablesapce)
Some of the constraints and limitations
It is not recommended to disable recyblebin.
· Disable recyblebin globally (for the database)
o alter system set recyclebin = off;· Disable recyclebin for a session
o alter session set recycblebin = off;
Enabled : By Default RECYCBLEBIN is enabled. It can be disabled.
There are two SYS owned views USER_RECYCLEBIN and DBA_RECYCLEBIN.
RECYCLEBIN$ is the table owned by SYS. Query DBA_OBJECTS for RECYCLEBIN and you will find these and others.
Basically, in simple terms it works similar to recyclebin in Windows. When you delete an object it goes to recyclebin, either you can recover it from there or PURGE recyclebin and get rid of it.
PURGE :
1. DROP TABLE PURGE;
· This will drop the table and purge at the same time and will not move it to recyclebin. It is similar to dropping a table in earlier versions (9i and below)
2. PURGE TABLESPACE tablespace_name USER username;
Substitute tablespace_name and username.
· All objects in a particular tablespace for a user that have been dropped and are still in recyclebin can be purged.
3. PURGE TABLESPACE tablespace_name
Substitute tablespace_name.
· All objects in a particular tablespace that have been dropped and are still in recyclebin can be purged.
4. PURGE RECYCLEBIN;
· This can be used to purge user recyclebin. · Note : USER_RECYCBLEBIN synonym refers to RECYCLEBIN
5. PURGE DBA_RECYCLEBIN;
· As a dba you can clear entire recyclebin and clean (purge) all the dropped objects.
You can also purge a particular object in the recyclebin after it is dropped.
Objects in recycle bin are purged, if there are space constraints (running out of space in the tablesapce)
Some of the constraints and limitations
- Would not preserve table's integrity constraints in recycble bin.
- Would not restore the indexes or triggers automatically when table from recycble bin is restored.
It is not recommended to disable recyblebin.
· Disable recyblebin globally (for the database)
o alter system set recyclebin = off;· Disable recyclebin for a session
o alter session set recycblebin = off;
Posts
