Home » SQL & PL/SQL » SQL & PL/SQL » oracle 10g purge all entries of a single table
oracle 10g purge all entries of a single table [message #199274] Mon, 23 October 2006 02:10 Go to next message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
in oracle 10g, i know that all dropped tables will be entered
in user_recyclebin;

and if i drop a table twice, two entries are made in user_recyclebin


create table test (col_a varchar(4));
SQL> drop table test;

SQL> select object_name, original_name, type, createtime, droptime
     from   user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME      TYPE    CREATETIME          DROPTIME
------------------------------ ----------------   ------  ------------------- --------
----------- 
BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST               TABLE   2004-02-21:19:04:03 2004-02-
21:19:04:41





now i create it again, and drop it, there will be 2 entries
in user_recyclebin;


SQL> create table test (col_b varchar(4));
Table created.

SQL> select object_name, original_name, type, createtime, droptime
     from   user_recyclebin;

OBJECT_NAME                    ORIGINAL_NAME      TYPE    CREATETIME          DROPTIME
------------------------------ ----------------   ------  ------------------- --------
----------- 
BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TEST               TABLE   2004-02-21:19:07:33 2004-02-
21:19:08:17


SQL> drop table test;
Table dropped.

SQL> select object_name, original_name, type, createtime, droptime
     from   user_recyclebin;
OBJECT_NAME                    ORIGINAL_NAME      TYPE    CREATETIME          DROPTIME
------------------------------ ----------------   ------  ------------------- --------
----------- 
BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST               TABLE   2004-02-21:19:04:03 2004-02-
21:19:04:41
BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TEST               TABLE   2004-02-21:19:07:33 2004-02-
21:19:08:17

SQL> show recyclebin
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TEST             BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TABLE        2004-02-21:19:08:17
TEST             BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TABLE        2004-02-21:19:04:41





how to give a query in such a way that all entries of test
in user_recyclebin are purged

i dont want to give a single purge table "bin.."

everytime...or a single command which will delete all
entries in user_recyclebin

Re: oracle 10g purge all entries of a single table [message #199276 is a reply to message #199274] Mon, 23 October 2006 02:17 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
PURGE TABLE TEST

Ross Leishman
Re: oracle 10g purge all entries of a single table [message #199279 is a reply to message #199274] Mon, 23 October 2006 03:10 Go to previous message
gautamvv
Messages: 254
Registered: June 2005
Senior Member
forget about data integrity for a while

i asked my java team to give

purge recyclebin;

and it worked, and also while dropping the table if i give

drop table test purge;

it wont go to recyclebin as to what they say
Previous Topic: how to get newly inserted row (or rowid) in oracle?
Next Topic: connect by - too many rows?
Goto Forum:
  


Current Time: Sat Dec 10 09:22:49 CST 2016

Total time taken to generate the page: 0.10460 seconds