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
VSQL varchar2(500);
VSQL1 varchar2(500);
Vcnt number(5);
begin
select count(*) into Vcnt from dba_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 dba_recyclebin';
execute immediate VSQL1;
dbms_output.put_line('DBA RECYCLEBIN has been purged.');
end if;
end;
/
Prerequisites
The database object must reside in your own schema or you must have the
DROP ANY ... system privilege for the type of object to be purged, or you must have the SYSDBA system privilege. To perform the PURGE DBA_RECYCLEBIN operation, you must have the SYSDBA or PURGE DBA_RECYCLEBINsystem privilege.