how do i permanently drop table from table space?

[kahangire]

i have dropped tables from the database and committed, but when i invoke "select * from tab" the number of tables is still the same only that the dropped tables change name to something as seen below:
BIN$+Rsr88VHSxSpUvP2w6f2ig==$0 TABLE

what may this mean?

thanx guys!

Ivan
kahangire.ivan@gmail.com

 

[Dagon]

Doing "select * from sys.dba_tables" will give you more information. Look at the temporary column - it will probably be set to 'Y'. These are temporary tables created by Oracle for queries and you shouldn't need to do anything with them.

For Oracle-related work, contact me through Linked-In.

 

[karluk]

Your dropped tables are being put in the recycle bin, which allows you to recover them if you decide you shouldn't have dropped them after all.

It is possible to disable this feature via the command

alter system set recyclebin=off scope=both;

but then you might be faced with doing a database recovery if you accidentally drop the wrong tables.

 

[karluk]

By the way, you can get rid of objects in the recycle bin using the "purge" command. To purge just a single table or index you would do something similar to

purge table my_table;

To purge all objects from the recycle bin

purge recyclebin;

It would be good to get into the habit of dropping tables first and then purging the recycle bin a few days later.

 

[SantaMufasa]

To purge all objects from the recycle bin

purge recyclebin;

...And to clarify, this purges all objects from your recyclebin only (not The recyclebin for everyone [i.e, it does not purge the recyclebin for the database]).


To purge the recyclebin for the entire database, you must:

SQL> conn / as sysdba
SQL> purge dba_recyclebin;

Notice that you must connect as SYS to purge the entire database's recyclebin.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Thargy]

To answer the posted question, if you want to permanently drop a table then you should use the following:-

DROP TABLE <table name> CASCADE CONSTRAINTS PURGE;

Regards

T