RECYCLEBIN WITH RETENTION

Following is a SQL*Plus script which can purge recycle bin with retention date

Syntax: sqlplus system/password@TNS @purge_recyclebin <Days to keep>

REM Script: purge_recyclebin.sql
REM User must has satisfy any of the following priviledge
REM 1. Has DROP ANY priviledge, like SYSTEM, or DBA
REM 2. Or SYSDBA, like SYS

set pagesize 0 feedback off
set verify off

REM Initialize Date format
alter session set nls_date_format = 'DD-MON-YY hh24:mi:ss';

select sysdate || ' Recycle Bin Purging Start' from dual;

col param1 new_value param1

REM prompt Getting parameter 1 from command line
select &1 Param1 from dual;

REM Printing parameter 1 captured
select 'Retention Day: &param1' Param_In from dual;

col type format a8
col ORIGINAL_NAME format a15
col owner format a9
col ts_name format a10

set pagesize 100
prompt
prompt Recyclebin Summary - Before
select substr(droptime, 1, 7) month, owner, count(*) num_obj,
   sum(space) total_space
from dba_recyclebin group by owner, substr(droptime, 1, 7);

prompt
prompt Recyclebin To Be Purge
select substr(droptime, 1, 7) month, owner, type, count(*) num_obj,
   sum(space) total_space
from dba_recyclebin
where droptime < to_char(sysdate - &&param1 , 'yyyy-mm-dd')
group by owner, substr(droptime, 1, 7), type;


REM List of tables in recyclebin
REM select owner, original_name, droptime, TYPE, ts_name, space
REM from dba_recyclebin order by droptime;

set termout off pagesize 0

spool purge_this
select 'purge table ' || owner || '.' || original_name || ';' Purge_command
from dba_recyclebin
where droptime < to_char(sysdate - &&param1 , 'yyyy-mm-dd')
and type = 'TABLE';
spool off

REM Executing the script
set echo on time on
spool purge_this.out
@purge_this.lst
spool off
set echo off

set pagesize 100 termout on

prompt
prompt Recyclebin Summary - After
select substr(droptime, 1, 7) month, owner, count(*) num_obj,
   sum(space) total_space
from dba_recyclebin group by owner, substr(droptime, 1, 7);

prompt

set pagesize 0
select sysdate || ' Recycle Bin Purging End' from dual;
set pagesize 100 termout on verify on feedback on

Question(1) : what’s the recyclebin Retention Period of time and in which parameter they have to work on it  ( undo_retention , db_flashback_retention_target ).?

Ans. 

Question(2): Location of allocate the Recyclebin space. ?

Ans. On tablespace level. When a tablespace is completely filled up with recycle bin data such that the datafiles have to extend to make room for more data, the tablespace is said to be under "space pressure." In that scenario, objects are automatically purged from the recycle bin in a first-in-first-out (FIFO) manner. The dependent objects (such as indexes) are removed before a table is removed.

Similarly, space pressure can occur with user quotas as defined for a particular tablespace. The tablespace may have enough free space, but the user may be running out of his or her allotted portion of it. In such situations, Oracle automatically purges objects belonging to that user in that tablespace.



Reference :

Enable / Disable / Purge Recyclebin