Recyclebin at User & Database level in Oracle database 11gR2
Posted by Mir Sayeed Hassan on February 28th, 2018
Recycle bin concept in Oracle database 11gR2
Recycle bin is similar to windows recyclebin in Oracle database & here the entire drop object is stored in FLASHBACK Area
As per my recycle bin its a virtual where all the dropped objects reside in it, It occupy the exact space as when they are created in database, In case if the table “test” created in the ‘XYZ’ tablespace & the dropped table “test” will remain in “XYZ” tablespace itself.
This dropped table will be renamed as “BIN$$”, you can also view the dropped table and also you can retrieve the dropped table by using the flashback query, the dropped table remain in the database until you perform the PURGE Recycle bin
In case if you drop the user or tablespace, there will be no recycle bin of the object and also the recycle bin will not work for the SYS Objects
Below is some testing scenario in recycle bin:
How to enable the recycle bin in Oracle database 11gR2
Check the recycle bin is enable or not
[oracle@ogg-test1 ~]$ !sq
sys@testdb> show parameter recycle NAME TYPE VALUE ------------------------------------ ----------- -------- buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin sring OFF
If you need to enable the recycle bin, it can be in scope=spfile only
sys@testdb> select INSTANCE_NAME,VERSION,STATUS from v$instance; INSTANCE_NAME VERSION STATUS ---------------- ----------------- ---------------------------- testdb 11.2.0.4.0 OPEN
sys@testdb> show parameter pfile NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ spfile string /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfiletestdb.ora
sys@testdb> alter system set recyclebin=on scope=both; alter system set recyclebin=on scope=both * ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option
##The above error occur in scope=both
Execute with scope=spfile works fine & restart the database
sys@testdb> alter system set recyclebin=on scope=spfile; System altered.
Shutdown the database: sys@testdb> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Startup the database:
sys@testdb> startup ORACLE instance started. Total System Global Area 2455228416 bytes Fixed Size 2255712 bytes Variable Size 771753120 bytes Database Buffers 1660944384 bytes Redo Buffers 20275200 bytes Database mounted. Database opened.
Verify
sys@testdb> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ----------- recyclebin string ON
sys@testdb> show parameter recycle NAME TYPE VALUE ------------------------------------ ----------- -------------------- buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string ON
How to disable the recycle bin in Oracle database 11gR2
sys@testdb> show parameter recycle NAME TYPE VALUE ------------------------------------ ----------- --------------------- buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string ON
sys@testdb> alter system set recyclebin=off scope=spfile;
Shutdown the database:
sys@testdb> shu immediate Database closed. Database dismounted. ORACLE instance shut down.
Startup the database:
sys@testdb> startup ORACLE instance started. Total System Global Area 2455228416 bytes Fixed Size 2255712 bytes Variable Size 771753120 bytes Database Buffers 1660944384 bytes Redo Buffers 20275200 bytes Database mounted. Database opened.
Verify:
sys@testdb> show parameter recycle NAME TYPE VALUE ------------------------------------ ----------- -------------------- buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string OFF
Purge Recycle bin
Two types of purge recycle bin are
User level — Purge only tables related to specific user
Database level — Purge all the tables in database
Purging at User level:
Verify the recycle bin is enabling or not, if not enable by using the above procedure:
In my case the recycle bin is enabled as shown below:
sys@testdb> show parameter recycle NAME TYPE VALUE ------------------------------------ ----------- ------------------ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string ON
Login to the user:
sys@testdb> conn mir Enter password: Connected.
mir@testdb> show user USER is "MIR"
mir@testdb>create table test1(eno numbser(10)); Table created.
mir@testdb> commit; Commit complete.
mir@testdb> select * from test1; ENO ---------- 1 1
mir@testdb> drop table test1; Table dropped.
mir@testdb> select object_name,original_name,operation,type,dropscn,droptime from user_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPSCN DROPTIME ------------------------------ -------------------------------- --------- ------------------------- ---------- ---- BIN$ZkZlpwYeaf/gU3t4qMCdow==$0 TEST1 DROP TABLE 2.2859E+11 2018-02-28:16:35:27
mir@testdb> select count(*) from user_recyclebin; COUNT(*) -------- 1
You can recover the above drop table or you can purge from the recycle bin
Recover table from recycle bin;
mir@testdb> show recyclebin; ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST1 BIN$ZkZlpwYeaf/gU3t4qMCdow==$0 TABLE 2018-02-28:16:35:27
Perform the flashback to above table as shown below:
mir@testdb> flashback table test1 to before drop; Flashback complete.
mir@testdb> show recyclebin; mir@testdb> sho user USER is "MIR"
Verify
mir@testdb> select table_name from user_tables; TABLE_NAME ------------ TEST1
Hence the drop table has been flashback from the recycle bin
Purge the specific table or all the tables at user level
mir@TESTDB> sho user USER is "MIR"
mir@TESTDB> create table t1(eno number(10)); Table created.
mir@TESTDB> create table t2(eno number(10)); Table created.
mir@TESTDB> create table t3(eno number(10)); ---- Table created.
mir@TESTDB> commit; Commit complete.
mir@TESTDB>select table_name from user_tables; TABLE_NAME ------------ TEST1 T1 T2 T3
mir@TESTDB> drop table t1; Table dropped.
mir@TESTDB> select object_name,original_name,operation,type,dropscn,droptime from user_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPSCN DROPTIME ------------------------------ -------------------------------- --------- ------------------------- ---------- ---- BIN$ZkZlpwYfaf/gU3t4qMCdow==$0 T1 DROP TABLE 2.2859E+11 2018-02-28:16:46:23
Now purge the specific table
mir@TESTDB> purge table t1; Table purged.
mir@TESTDB> show recyclebin; (empty)
Now purge the entire table from recycle bin
mir@testdb> select * from tab; TNAME TABTYPE CLUSTERID ------------------------------ ------- ---------- BIN$ZkZlpwYgaf/gU3t4qMCdow==$0 TABLE BIN$ZkZlpwYhaf/gU3t4qMCdow==$0 TABLE TEST1 TABLE
mir@testdb> select count(1) from user_recyclebin; COUNT(1) ---------- 2
mir@TESTDB> purge recyclebin; Recyclebin purged.
Verify:
mir@testdb> select object_name,original_name,operation,type,dropscn,droptime from user_recyclebin; no rows selected
mir@testdb> select count(1) from user_recyclebin; COUNT(1) -------- 0
Purging at Database level:
Connect to different user & drop some tables from it
Connect to User “mir” & drop some table
sys@testdb> conn mir Enter password: Connected.
mir@testdb> create table testing1(eno number(10)); Table created.
mir@testdb> drop table testing1; Table dropped.
mir@testdb> sho recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ -------------------------------------------- TESTING1 BIN$ZkarskX0a8jgU3t4qMBPIQ==$0 TABLE 2018-02-28:16:55:02
Connect to User “sayeed” & drop some table
sys@testdb> conn sayeed Enter password: Connected.
sayeed@testdb> create table test1(eno number(10)); Table created.
sayeed@testdb> drop table test1; Table dropped.
Goto the sys user & verify the drop tables
sys@testdb> conn sys/sys as sysdba Connected.
sys@TESTDB> select OWNER,object_name,original_name,operation,type,dropscn,droptime from dba_recyclebin; OWNER OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPSCN DROPTIME ------------------------------ ------------------------------ -------------------------------- --------- MIR BIN$ZkarskX0a8jgU3t4qMBPIQ==$0 TESTING1 DROP TABLE 2.2859E+11 2018-02-28:16:55:02 SAYEED BIN$Zka0FPjRa/jgU3t4qMB6oQ==$0 TEST1 DROP TABLE 2.2859E+11 2018-02-28:16:57:23
sys@TESTDB> select count(*) from dba_recyclebin; COUNT(*) -------- 2
Now purge the dba_recyclebin, In this case it will clear all the tables from the different users has drop & placed in recycle bin
sys@testdb> purge dba_recyclebin; DBA Recyclebin purged.
sys@testdb> select count(*) from dba_recyclebin; COUNT(*) -------- 0
sys@testdb> select OWNER,object_name,original_name,operation,type,dropscn,droptime from dba_recyclebin; no rows selected
Others ways to Purge the objects from database
Purge table — only table are purge
Purge index only index are purge
Purge recyclebin; — All the objects are purge
Purge dba_recyclebin — All the objects are purge
Purge tablespace — Purge all the object from specified tablespace
Purge tablespace USER <user_name> — Purge all the objects from tbs which belongs to specified user