Purge is useful in 10g when you want to "really truly drop a table". In 10g, tables can be restored after being dropped.
When you drop a table in 10g like this:
Oracle renames it (moves it to the recyclebin) and the table is not (truly) dropped.
You can get it back:
Note: You can't flashback objects in SYSTEM tablespace. So be careful.
I f you want truly drop a table use purge:
Now the table is gone and you cannot fashback:
When you drop a table in 10g like this:
drop table table_name;
Oracle renames it (moves it to the recyclebin) and the table is not (truly) dropped.
You can get it back:
flashback table table_name to before drop;
Note: You can't flashback objects in SYSTEM tablespace. So be careful.
I f you want truly drop a table use purge:
drop table table_name purge;
Now the table is gone and you cannot fashback:
flashback table table_name to before drop;
*
ERROR AT LINE 1:
ORA-38305: OBJECT NOT IN RECYCLE BIN
30 Mar 2007 07:32:15
good information
28 May 2007 16:56:36
I got a following error,
ALTER TABLE RPE_FAULT_C DROP CONSTRAINT SYS_C00381906
RPW_DROP_CONST ERR> *
RPW_DROP_CONST ERR> ERROR at line 1:
RPW_DROP_CONST ERR> ORA-38301: can not perform DDL/DML over objects in Recycle Bin
but it got resolved while issuing following command "drop table table_name purge;"
as mentioned above.
my doubt is drop is also a ddl command but how come it allowed a ddl (drop command) to perform.
contradicting the error message & solution.
09 Jun 2007 15:20:28
Thank mustapha.
06 Sep 2007 12:30:00
any one guide what is the purpose of sysaux tablespac... what is the advantage.
Regards
S.Nandakumar
08 Nov 2007 10:44:05
Thanks Mr. mustapha, very useful information.
19 Dec 2007 14:26:25
thanks
this is too usefull information..
09 Sep 2008 14:29:04
Very useful information
28 Apr 2009 12:09:38
I am getting followin error:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin ...
My query is I wanted it to drop in my schema, table is not there as it was droped from another process.
I was expecting following error:
Table or view does not exist.
How come it went ahead and checked my recycle bin and tried to delete table from there.
Is this normal process in Oracle 10g? Is there some way to stop this checking from recycle bin.
Thanks in Advance