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