OraFAQ Forum: SQL & PL/SQL » purge recyclebin

Home » SQL & PL/SQL » SQL & PL/SQL » purge recyclebin

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

purge recyclebin [message #230484]

Thu, 12 April 2007 02:06

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.

Report message to a moderator

Re: purge recyclebin [message #230487 is a reply to message #230484]

Thu, 12 April 2007 02:10

Maaher
Messages: 7065
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

Report message to a moderator

Re: purge recyclebin [message #230507 is a reply to message #230484]

Thu, 12 April 2007 02:49

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Post the query you did on the recyclebin. (don't forget to format it.)

Regards
Michel

Report message to a moderator

Re: purge recyclebin [message #230516 is a reply to message #230484]

Thu, 12 April 2007 02:57

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?

Report message to a moderator

Re: purge recyclebin [message #230540 is a reply to message #230516]

Thu, 12 April 2007 03:27

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: purge recyclebin [message #230542 is a reply to message #230484]

Thu, 12 April 2007 03:30

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

Report message to a moderator

Re: purge recyclebin [message #230548 is a reply to message #230542]

Thu, 12 April 2007 03:33

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: purge recyclebin [message #230564 is a reply to message #230484]

Thu, 12 April 2007 04:00

dannywcw
Messages: 24
Registered: March 2007

Junior Member

hi here's the print screen.

Attachment: sql editor.jpg
(Size: 110.07KB, Downloaded 737 times)

Report message to a moderator

Re: purge recyclebin [message #230565 is a reply to message #230484]

Thu, 12 April 2007 04:01

dannywcw
Messages: 24
Registered: March 2007

Junior Member

here's the view for tablespace

Attachment: schema browser.jpg
(Size: 215.83KB, Downloaded 694 times)

Report message to a moderator

Re: purge recyclebin [message #230568 is a reply to message #230484]

Thu, 12 April 2007 04:02

dannywcw
Messages: 24
Registered: March 2007

Junior Member

the above 2 are the screen shots for my DB. Please help Thank you.

Report message to a moderator

Re: purge recyclebin [message #230576 is a reply to message #230565]

Thu, 12 April 2007 04:09

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sorry, firewall does not allow me to download images, copy and paste them in text files.

Regards
Michel

Report message to a moderator

Re: purge recyclebin [message #230802 is a reply to message #230484]

Thu, 12 April 2007 21:39

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.

Report message to a moderator

Re: purge recyclebin [message #230809 is a reply to message #230802]

Thu, 12 April 2007 23:23

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: purge recyclebin [message #230864 is a reply to message #230484]

Fri, 13 April 2007 02:25

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 534 times)

Report message to a moderator

Re: purge recyclebin [message #230880 is a reply to message #230864]

Fri, 13 April 2007 03:44

Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Still can't download images nor doc, just txt or dat files so can't help you.

Regards
Michel

Report message to a moderator

Re: purge recyclebin [message #230886 is a reply to message #230484]

Fri, 13 April 2007 03:52

dannywcw
Messages: 24
Registered: March 2007

Junior Member

is there anyone else who can help? Thank you

Report message to a moderator

Re: purge recyclebin [message #230894 is a reply to message #230886]

Fri, 13 April 2007 04:20

Maaher
Messages: 7065
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

Wink

MHE

Report message to a moderator

Re: purge recyclebin [message #230897 is a reply to message #230484]

Fri, 13 April 2007 04:28

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.

Report message to a moderator

Re: purge recyclebin [message #230923 is a reply to message #230897]

Fri, 13 April 2007 06:23

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

Report message to a moderator

Re: purge recyclebin [message #230939 is a reply to message #230484]

Fri, 13 April 2007 08:22

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.

Report message to a moderator

Re: purge recyclebin [message #230942 is a reply to message #230484]

Fri, 13 April 2007 08:48

Bill B
Messages: 1971
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

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"

Report message to a moderator

Previous Topic:	More information about the views
Next Topic:	Sql doubt

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Aug 25 16:33:42 CDT 2025