Home » SQL & PL/SQL » SQL & PL/SQL » query regarding DROP TABLE (Oracle 10g, XP)
query regarding DROP TABLE [message #376075] Mon, 15 December 2008 23:34 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,

I have query regarding DROP TABLE statement.

First I create table as
SQL> create table tmp_tbl
  2  (col1 number);

Table created.


then I drop that table as
SQL> drop table tmp_tbl;

Table dropped.


Having done this, I fire SELECT query like this
SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
ABC                            TABLE
DEMO_USERS                     TABLE
DEMO_CUSTOMERS                 TABLE
DEMO_ORDERS                    TABLE
DEMO_ORDER_ITEMS               TABLE
DEMO_STATES                    TABLE
DEMO_PAGE_HIERARCHY            TABLE
DEPT                           TABLE
EMP                            TABLE
TBL_FOR_COLLECTION             TABLE
DEMO_PRODUCT_INFO              TABLE

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
check_for_&                    TABLE
TABLE_FOR_REG_EXP              TABLE
TBL_WITH_LONG                  TABLE
BIN$Wnd33Is6Q0Cogd7EwfUEUw==$0 TABLE
BIN$6SobE2CnRtuDoXftQHlCJA==$0 TABLE
BIN$CqEWC0f8Sj+tNv5UPpHMEA==$0 TABLE

17 rows selected.


as shown in last row, some value is still shown as a table.

if I try to drop that table with that garbage value, it says that "Cant perform ddl operation on recycle bin"

so please explain,
How it works?
when that table is dropped from recycle bin also?
how to drop table permanently?
Re: query regarding DROP TABLE [message #376076 is a reply to message #376075] Mon, 15 December 2008 23:39 Go to previous messageGo to next message
rap.fernandes
Messages: 4
Registered: June 2008
Junior Member
Hi,

Kindly use the following to delete from the recycle bin,

PURGE RECYCLEBIN;

Regards,

Raphael
Re: query regarding DROP TABLE [message #376079 is a reply to message #376075] Mon, 15 December 2008 23:46 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
if I try to drop that table with that garbage value

And the exact command was? As the table name contains special characters, it is good to put it between double quotes (").
Quote:
when that table is dropped from recycle bin also?

Similarly as the Windows recycle bin works - after Oracle decides that the involved tablespace does not contain space enough for newly created objects/rows.
Quote:
how to drop table permanently?

Issue the PURGE option of DROP statement, as described in the documentation.

[Edit: of course, DROP is the right statement]

[Updated on: Tue, 16 December 2008 00:15]

Report message to a moderator

Re: query regarding DROP TABLE [message #376088 is a reply to message #376079] Tue, 16 December 2008 00:31 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks for quick reply.

I was using drop statement as shown below

SQL> drop table "BIN$CqEWC0f8Sj+tNv5UPpHMEA==$0";
drop table "BIN$CqEWC0f8Sj+tNv5UPpHMEA==$0"
           *
ERROR at line 1:
ORA-38301: can not perform DDL/DML over objects in Recycle Bin


regards,
Delna
Re: query regarding DROP TABLE [message #376097 is a reply to message #376088] Tue, 16 December 2008 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't you understand to "can not perform DDL/DML over objects in Recycle Bin"?

Regards
Michel
Re: query regarding DROP TABLE [message #376148 is a reply to message #376097] Tue, 16 December 2008 05:27 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Is there any option so that we are not required to specify PURGE option explicitly?
Re: query regarding DROP TABLE [message #376150 is a reply to message #376148] Tue, 16 December 2008 05:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As @ThomasG said - you can use the DROP TABLE command with the PURGE option, :
DROP TABLE <table_name> PURGE;
, or you can disable the recycle bin for your session with
ALTER SESSION SET recyclebin=OFF;
, or you can disable the recyclebin for your system.
Re: query regarding DROP TABLE [message #376225 is a reply to message #376148] Tue, 16 December 2008 09:01 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
to my knowledge purge recyclebin is the only best option rather than trying multiple command sets;
yours
dr.s.raghunathan
Re: query regarding DROP TABLE [message #376229 is a reply to message #376225] Tue, 16 December 2008 09:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
to my knowledge purge recyclebin is the only best option rather than trying multiple command sets;

Can you explain this remark?

Regards
Michel
Re: query regarding DROP TABLE [message #376233 is a reply to message #376229] Tue, 16 December 2008 09:38 Go to previous messageGo to next message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
For quite sometime i was facing this problem of special characters even after drop. I hope i have raised this question in this forum too. (i did not check just my rememberance). Various solutions were too offered. In oracle apex object browser i used this purge recyclebin. Then there were no issues. whereas when i try multiple set of command, i landed with various errors. May be it is my poor understanding about other means. Hence I opined. thats all.
yours
dr.s.raghunathan
Re: query regarding DROP TABLE [message #376235 is a reply to message #376233] Tue, 16 December 2008 09:51 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Did DROP TABLE <table_name> PURGE; not work?
Re: query regarding DROP TABLE [message #376312 is a reply to message #376235] Tue, 16 December 2008 22:24 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks everybody

Problem solved with
DROP TABLE <table> PURGE

regards,
Delna
Re: query regarding DROP TABLE [message #376368 is a reply to message #376235] Wed, 17 December 2008 02:28 Go to previous message
dr.s.raghunathan
Messages: 540
Registered: February 2008
Senior Member
it works but multiple drops and after some normal delete sequences in various tables and with other activities single purge recyclebin works fine.
Previous Topic: creating file on server
Next Topic: Dynamic queryin based on number of inputs provided
Goto Forum:
  


Current Time: Sun Dec 11 06:00:31 CST 2016

Total time taken to generate the page: 0.09152 seconds