This forum is currently locked. You can't register or post questions at this time. (read more)

Procedure to purge recyclebin

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

sandeepssalve
Member
Posts: 2
Joined: Mon Jun 24, 2013 2:27 pm

Procedure to purge recyclebin

Postby sandeepssalve » Mon Jun 24, 2013 2:39 pm

Hi,

We want to purge recycle bin retaining last 30 days objects.
For that I created a procedure in SYSTEM schema as follows:
===
create or replace procedure purge_recyclebin
as
sql_stmt VARCHAR2(200);
cursor ctabname is
select owner, object_name from dba_recyclebin
where
type='TABLE'
and
droptime < TO_CHAR(SYSDATE - 30, 'YYYY-MM-DD:HH24:MI:SS');
begin
for i in ctabname loop
sql_stmt := 'PURGE TABLE i.owner."i.object_name"';
execute immediate sql_stmt;
--dbms_output.put_line ( "owner");
--dbms_output.put_line ( "object_name" ||'dropped');
end loop;
end;
==

This procedure compiles successfully, but it fails giving the below error:
SYSTEM-IPGDEV.IRVING.COM>exec purge_recyclebin;
BEGIN purge_recyclebin; END;

*
ERROR at line 1:
ORA-00933: SQL command not properly ended
ORA-06512: at "SYSTEM.PURGE_RECYCLEBIN", line 13
ORA-06512: at line 1


SYSTEM-IPGDEV.IRVING.COM>
===

I have given required grant of sys.dba_recylcebin to SYSTEM.

Also if i executed the script manually I am able to purge the objects in recyle bin:
SYSTEM-IPGDEV.IRVING.COM>select 'purge table '||owner||'."'||object_name||'";' from dba_recyclebin
2 where
3 type='TABLE'
4 and
5 droptime < TO_CHAR(SYSDATE - 30, 'YYYY-MM-DD:HH24:MI:SS')
6 and
7 rownum < 10;

'PURGETABLE'||OWNER||'."'||OBJECT_NAME||'";'
----------------------------------------------------------------------------
purge table APPLSYS."BIN$yZrd63ffcZvgQAUKPAVhLQ==$0";
purge table MSC."BIN$yxK2g8F+6Q3gQAUKPAUnwg==$0";
purge table MSC."BIN$yxNY0gth6pfgQAUKPAVf4w==$0";
purge table MSC."BIN$yxNY0gtj6pfgQAUKPAVf4w==$0";
purge table MSC."BIN$yxNY0gtk6pfgQAUKPAVf4w==$0";
purge table MSC."BIN$yxNY0gtl6pfgQAUKPAVf4w==$0";
purge table MSC."BIN$yxNZPQuh2zngQAUKPAVf/g==$0";
purge table MSC."BIN$yxNZPQui2zngQAUKPAVf/g==$0";
purge table MSC."BIN$yxNZPQuj2zngQAUKPAVf/g==$0";

9 rows selected.
SYSTEM-IPGDEV.IRVING.COM>purge table APPLSYS."BIN$yZrd63ffcZvgQAUKPAVhLQ==$0";

Table purged.

SYSTEM-IPGDEV.IRVING.COM>
===

Please help me to get this executed from procedure so that I can schedule it in dbms_scheduler.

Thanks and regards,
Sandeep S. Salve.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Procedure to purge recyclebin

Postby Tim... » Mon Jun 24, 2013 2:53 pm

Hi.

You need to swap this:

Code: Select all

sql_stmt := 'PURGE TABLE i.owner."i.object_name"';


for this:

Code: Select all

sql_stmt := 'PURGE TABLE ' || i.owner || '."' || i.object_name ||'"';


You are building a string to execute. The contents of your variables are not subsitituted at runtime. They are used as leterals. Since you don;t have a schema called "i.owner" and an table called "i.object_name", which is what you asked to purge, it fail. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

sandeepssalve
Member
Posts: 2
Joined: Mon Jun 24, 2013 2:27 pm

Re: Procedure to purge recyclebin

Postby sandeepssalve » Tue Jun 25, 2013 10:12 am

Thanks Tim.

This has worked.
Thanks a lot.

Regards,
Sandeep S. Salve.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Procedure to purge recyclebin

Postby Tim... » Tue Jun 25, 2013 12:47 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 5 guests