Learn Database Concepts

Automate recyclebin purge in oracle Setup this simple scheduler job as sysdba to purge the objects in the  recycbin . This is one of the most space cosuming location that often dba's forget to cleanup and the objects get piled up occupying lot of space. Based on how long you want to save these dropped object setup a job under scheduler to run below plsql block either daily, weekly or monthly.   I suggest to run weekly. -- For user_recyclebin purge -- -- plsql -- declare VSQL varchar2(500); VSQL1 varchar2(500); Vcnt number(5); begin select count(*) into Vcnt from user_recyclebin; /***   Optional if you would like to keep record count of objects purged -- Uncomment if you would like to keep this insert into SYS.PURGE_STATS (obj_count) values (Vcnt); commit; **/ if Vcnt>0 then VSQL1:='purge  user_recyclebin '; execute immediate VSQL1; dbms_output.put_line('DBA RECYCLEBIN has been purged.'); end if; end; / -- For dba_recyclebin purge -- -- plsql -- declare

oracle@Linux01:[/u01/oracle/DPUMP] $ exp atoorpu file=abcd.dmp logfile=test.log table=sys.aud$ About to export specified tables via Conventional Path ... . . exporting table                           AUD$     494321 rows exported Export terminated successfully without warnings. oracle@qpdbuat211:[/d01/oracle/DPUMP] $ echo $? 0 oracle@Linux01:[/u01/oracle/DPUMP] $ imp atoorpu file=abcd.dmp logifle=test.log LRM-00101: unknown parameter name 'logifle' IMP-00022: failed to process parameters, type 'IMP HELP=Y' for help IMP-00000: Import terminated unsuccessfully oracle@Linux01:[/u01/oracle/DPUMP] $ echo $? 1 Can be used in export shell scripts for status verification: if test $status -eq 0 then echo "export was successfull ." else echo " export was not successfull . " fi Also check below page fore reference : https://docs.oracle.com/database/121/SUTIL/GUID-34D0DEE7-3530-42DC-BE01-C2588CC73CE5.htm#SUTIL38