Home » RDBMS Server » Server Administration » Purge Recyclebin(What is dba_recyclebin.can_purge?) (linux,Oracle11gr2)
Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #533628] Wed, 30 November 2011 23:28 Go to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Hi
Can I Purge all the items from dba_recyclebin without checking any other details ? Also what is meant for the column - dba_recyclebin.can_purge . I got the below result for the below query. Can I purge those(purge dba_recyclebin)?

SQL> select count(*) from dba_recyclebin where can_purge<>'YES';

  COUNT(*)
----------
        70

Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #533630 is a reply to message #533628] Wed, 30 November 2011 23:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I give up.
Can you?
Purging [message #533636 is a reply to message #533628] Wed, 30 November 2011 23:57 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

You can purge *everything* from dba_recyclebin as far as you have enough privileges

Kamran Agayev A.
Oracle ACE, OCP

[Updated on: Thu, 01 December 2011 01:21] by Moderator

Report message to a moderator

Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #533646 is a reply to message #533636] Thu, 01 December 2011 00:23 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
But why the query is giving the below result and what is the purpose of the field can_prge?

SQL> select count(*) from dba_recyclebin where can_purge<>'YES';

  COUNT(*)
----------
        70

Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #533648 is a reply to message #533646] Thu, 01 December 2011 01:01 Go to previous messageGo to next message
Kamran Agayev
Messages: 145
Registered: February 2009
Location: Azerbaijan, Baku
Senior Member

It's written in the documentation that:
"Indicates whether the object can be purged (YES) or not (NO)"
http://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4046.htm

Can you try to purge on of those objects and see what happens?

Kamran Agayev A.
Oracle ACE, OCP

[Updated on: Thu, 01 December 2011 01:21] by Moderator

Report message to a moderator

Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #533649 is a reply to message #533646] Thu, 01 December 2011 01:01 Go to previous messageGo to next message
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
DBA_RECYCLEBIN description; CAN_PURGE indicates whether the object can be purged (YES) or not (NO).
Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #533956 is a reply to message #533648] Thu, 01 December 2011 23:45 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
The objects coming under the category 'can_purge='NO'' are either LOB/LOB INDEX. If try to purge those through toad , the indexes are getting purged. When I issue the DDL in prompt its not working as given below .
SQL> select object_name,
       original_name,
       type,
       can_purge
  from user_recyclebin
 where can_purge = 'NO' and object_name in ('SYS_IL0000077384C00003$$', 'SYS_LOB0000077384C00003$$');

OBJECT_NAME                    ORIGINAL_NAME                    TYPE                      CAN
------------------------------ -------------------------------- ------------------------- ---
SYS_IL0000077384C00003$$       SYS_IL0000077384C00003$$         LOB INDEX                 NO
SYS_LOB0000077384C00003$$      SYS_LOB0000077384C00003$$        LOB                       NO

SQL> purge     LOB index  'SYS_IL0000077384C00003$$';
purge     LOB index  'SYS_IL0000077384C00003$$'
          *
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL> purge    lob 'SYS_LOB0000077384C00003$$';
purge    lob 'SYS_LOB0000077384C00003$$'
         *
ERROR at line 1:
ORA-38302: invalid PURGE option


SQL>


Please help

[Updated on: Fri, 02 December 2011 01:20] by Moderator

Report message to a moderator

Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #533994 is a reply to message #533956] Fri, 02 December 2011 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't purge LOB, you purge TABLE or INDEX.
Just read the statement syntax in the documentation, do not try to invent it.

Regards
Michel
Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #534032 is a reply to message #533994] Fri, 02 December 2011 05:41 Go to previous messageGo to next message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
But Toad is allowing to purge the LOB . why?
Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #534042 is a reply to message #534032] Fri, 02 December 2011 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ask TOAD not Oracle.
For me, TOAD is just b...s... and your question is the proof.
TOAD is ONLY for experts NOT for newbies.

Regards
Michel

[Updated on: Fri, 02 December 2011 06:22]

Report message to a moderator

Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #534109 is a reply to message #533628] Fri, 02 December 2011 11:52 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
Are you trying to clean out the contents of the recycle bin?
If so you can do this:


purge dba_recyclebin;

Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #534112 is a reply to message #534109] Fri, 02 December 2011 12:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not ONLY a LOB!
Is this not clear from the posts?

Regards
Michel
Re: Purge Recyclebin(What is dba_recyclebin.can_purge?) [message #535107 is a reply to message #534112] Mon, 12 December 2011 04:06 Go to previous message
prejib
Messages: 126
Registered: March 2009
Location: India
Senior Member
Hi,

I could clear the recyclebin both LOB and other objects also
SQL> SQL> conn / as sysdba
Connected.
SQL> purge dba_recyclebin;

DBA Recyclebin purged.


SQL> select *  from dba_recyclebin;

no rows selected

SQL>


Previous Topic: How to reset the parameter value?
Next Topic: UNDO in parameter file
Goto Forum:
  


Current Time: Fri Apr 26 03:37:59 CDT 2024