query regarding DROP TABLE [message #376075] |
Mon, 15 December 2008 23:34  |
 |
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 #376079 is a reply to message #376075] |
Mon, 15 December 2008 23:46   |
flyboy
Messages: 1903 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 #376150 is a reply to message #376148] |
Tue, 16 December 2008 05:30   |
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 #376233 is a reply to message #376229] |
Tue, 16 December 2008 09:38   |
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
|
|
|
|
|
|