Home » SQL & PL/SQL » SQL & PL/SQL » purge recyclebin
icon4.gif  purge recyclebin [message #230484] Thu, 12 April 2007 02:06 Go to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
Hi all, need your kind assistance urgently.

Currently my tablespace are full. I've check my objects in my tablespace and most of them are object from the recyclebin (table partition). So i run the following command

purge table "table patition"

It return me a message saying that the object is not in Recyclebin.

When i run

show recyclebin;

No output was returned. Its driving me crazy. I've tried to restart the database and still not working. Please help, Thank you.
Re: purge recyclebin [message #230487 is a reply to message #230484] Thu, 12 April 2007 02:10 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You're not using double quotes in your statement, are you? That might be the problem. And how about PURGE USER_RECYCLEBIN? That will empty your recyclebin.

MHE
Re: purge recyclebin [message #230507 is a reply to message #230484] Thu, 12 April 2007 02:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the query you did on the recyclebin. (don't forget to format it.)

Regards
Michel
Re: purge recyclebin [message #230516 is a reply to message #230484] Thu, 12 April 2007 02:57 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
hi all thanks for the reply.

i did the following

purge table "ci_prepaid.BIN$V3zj9aOWRImOT89aKyyVSg==$0";

when i run the above it says "No item found in recyclebin"

Show recycle bin;

No data was returned.


Hi Maaher,

when you said run the following,

PURGE USER_RECYCLEBIN;

It will remove all those objects from the recyclebin no matter what object type?

Re: purge recyclebin [message #230540 is a reply to message #230516] Thu, 12 April 2007 03:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So there is no problem.
You can't purge something from your recycle bin because your recycle bin is empty.

Regards
Michel
Re: purge recyclebin [message #230542 is a reply to message #230484] Thu, 12 April 2007 03:30 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
thanks for the reply. i understand that i cant purge anything from the recyclebin when there's nothing inside there.

My problem is my table space is show there's objects in my recycle bin. But i cant seems to see them when i run

show recyclebin;

I'm using TOAD by the way. Need help
Re: purge recyclebin [message #230548 is a reply to message #230542] Thu, 12 April 2007 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post the query that allows you to say there is objects in your recycle bin that fill your tablespace.

Regards
Michel
Re: purge recyclebin [message #230564 is a reply to message #230484] Thu, 12 April 2007 04:00 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
hi here's the print screen.
Re: purge recyclebin [message #230565 is a reply to message #230484] Thu, 12 April 2007 04:01 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
here's the view for tablespace
Re: purge recyclebin [message #230568 is a reply to message #230484] Thu, 12 April 2007 04:02 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
the above 2 are the screen shots for my DB. Please help Thank you.
Re: purge recyclebin [message #230576 is a reply to message #230565] Thu, 12 April 2007 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, firewall does not allow me to download images, copy and paste them in text files.

Regards
Michel
Re: purge recyclebin [message #230802 is a reply to message #230484] Thu, 12 April 2007 21:39 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
User Object Name Object Type
CI_PREPAID BIN$Lax+Rj3AD63gRAASeZ5J1Q==$0:W30207 TABLE PARTITION


hi all, when i did the below

select * from recyclebin;

output:
no rows selected.




purge table user_recyclebin;

output:
ORA-38307: object not in RECYCLE BIN

Please help. Really appreciate it.

Re: purge recyclebin [message #230809 is a reply to message #230802] Thu, 12 April 2007 23:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
execute:
show user
select * from DBA_RECYCLEBIN where object_name='BIN$Lax+Rj3AD63gRAASeZ5J1Q==$0:W30207';
select * from DBA_OBJECTS where object_name='BIN$Lax+Rj3AD63gRAASeZ5J1Q==$0:W30207';


"purge table user_recyclebin" means you want to purge a table named "user_recyclebin" which is not what you want to do.

With "select * from recyclebin" you query your recycle bin. Object may be in another recycle bin's.

Regards
Michel
Re: purge recyclebin [message #230864 is a reply to message #230484] Fri, 13 April 2007 02:25 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
thanks michel. what i did was i

select * from DBA_RECYCLEBIN;
some results was returned.

then
purge DBA_RECYCLEBIN;
objects was deleted.

but when i went to TOAD to check it still show that objects was not deleted. Making crazy. Please advice. Is there any other way i can deleted the stuff from recyclebin?

  • Attachment: schema.JPG
    (Size: 255.72KB, Downloaded 192 times)
Re: purge recyclebin [message #230880 is a reply to message #230864] Fri, 13 April 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still can't download images nor doc, just txt or dat files so can't help you.

Regards
Michel
Re: purge recyclebin [message #230886 is a reply to message #230484] Fri, 13 April 2007 03:52 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
is there anyone else who can help? Thank you
Re: purge recyclebin [message #230894 is a reply to message #230886] Fri, 13 April 2007 04:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I noticed that you are connected as SYS. What happens if you connect as the regular user (the owner of the original object) and purge his recyclebin?

It might sound silly, but don't forget to refresh in TOAD. You wouldn't be the first. Wink

MHE
Re: purge recyclebin [message #230897 is a reply to message #230484] Fri, 13 April 2007 04:28 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
hi maaher thanks for the reply. yes, i did login to ci_prepaid. The owner of the object in recyclebin. i did do a refresh.

it was still the same.

when i query the DB, nothing was found. When i refresh the view on TOAD, it still shows the recyclebin objects. Its taking up a lot of tablespace. I need to remove them as soon as possible.

Please advice. Thank you.
Re: purge recyclebin [message #230923 is a reply to message #230897] Fri, 13 April 2007 06:23 Go to previous messageGo to next message
aboodlardy
Messages: 80
Registered: November 2006
Location: India
Member
Hi

To drop the table from the recyclebin of a particular user for a tablespace, u can try as this

SQL>purge tablespace <tablespace-name> user <user-name>;


hope this helps

regards
Abood
Re: purge recyclebin [message #230939 is a reply to message #230484] Fri, 13 April 2007 08:22 Go to previous messageGo to next message
dannywcw
Messages: 24
Registered: March 2007
Junior Member
i've just tried the command. its still not working. i seems that i can't remove the object from the recyclebin. i've tried restarting it and still not working.
Re: purge recyclebin [message #230942 is a reply to message #230484] Fri, 13 April 2007 08:48 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
simply LOGIN as the user then issue the command

purge recyclebin;


This will purge ALL deleted objects from the current schema.

[Updated on: Fri, 13 April 2007 08:48]

Report message to a moderator

Re: purge recyclebin [message #230945 is a reply to message #230939] Fri, 13 April 2007 09:00 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Firstly - don't panic. You aren't going to run out of space, or get 'Unable to extend' errors because of the recycly bin.

From the Documentation:
Dropped objects are kept in the recycle bin until such time as no new extents can be allocated in the tablespace to which the objects belong without growing the tablespace.


Now, can you run the following query, to show which users have objects in the recycle bin.
Run it in SQL*Plus and copy and paste the results back here - don't post images if you please, as my firewall won't let them in.
SELECT owner,type,count(*) from dba_recyclebin group by owner,type


This will show you which users actually own the objects in the recycle bin.

As a general rule of thumb - don't do things as SYS. To paraphrase Tom Kyte (may his databases never need recovery) "SYS is special, SYS is magical, don't do things as SYS"
Previous Topic: More information about the views
Next Topic: Sql doubt
Goto Forum:
  


Current Time: Sun Dec 11 02:33:44 CST 2016

Total time taken to generate the page: 0.09557 seconds