Showing posts with label recyclebin. Show all posts
Showing posts with label recyclebin. Show all posts

Wednesday, May 10, 2023

Can the recyclebin be turned off for a container database but remain on in a pluggable database?

Yes, the multitenant architecture allows this.
SQL>select name, value , ISPDB_MODIFIABLE from V$parameter where name = 'recyclebin';

NAME                           VALUE                ISPDB
------------------------------ -------------------- -----
recyclebin                     OFF                  TRUE
Switch container to a PDB:
SQL>alter session set container=veg7;

Session altered.

SQL>show con_name

CON_NAME
------------------------------
VEG7

SQL>select name, value , ISPDB_MODIFIABLE from V$parameter where name = 'recyclebin';

NAME                           VALUE                ISPDB
------------------------------ -------------------- -----
recyclebin                     ON                   TRUE
Remember that the pluggable database must be closed and reopened if you toggle the recyclebin on/off:
SQL>alter session set container=veg7;

Session altered.

SQL>alter system set recyclebin=on container=current scope=spfile;

System altered.

SQL>alter pluggable database close;

Pluggable database altered.

cdb>SQL>alter pluggable database open;

Pluggable database altered.
If you want to toggle the recyclebin on/off for the container database, the procedure is the same except that the entire instance will have to be bounced.

Wednesday, November 15, 2017

How to purge objects from the recycle bin


sqlplus / as sysdba
purge dba_recyclebin; --> purges all recyclebin objects, globally
purge tablespace user_data user scott; --> purge only scott's Objects from the user_data tablespace
purge tablespace user_data; --> purge all recyclebin Objects from the user_data tablespace

conn scott/tiger
purge recyclebin; --> purges scott's recycle bin only

The next statement purges the specified object from the recycle bin. The first example refers directly to the system-generated name of the object, the second is refering to the original name of the object.
purge table "BIN$W/n28BqBgRbgU64JAQoz3A==$0";
purge table emp;