Drop Table in Oracle

If you think you don't need a table then you can drop it with the DROP TABLE table_name clause. It is easy thing to drop but before drop you should know around the consequence of dropping a table.

If you drop a table then before that think about following consequences.

•After dropping table you no longer access the data in it.

•All views and PL/SQL program units dependent on a dropped table remain in tact but they become unusable or invalid.

•All indexes and triggers associated with a table are dropped.

•All synonyms for a dropped table remain, but return an error when used.

•All extents allocated for a table that is dropped are returned to the free space of the tablespace and can be used by any other object requiring new extents or new objects.

Example of This Scenario:
----------------------------------------
1)Look at free space of tablespace user_tbs.

SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072

2)Now create a table in that tablespace.

SQL> create table test2 tablespace user_tbs as select level a1, level a2 , level a3 from dual connect by level<=10000;
Table created.

3)Let's check the free space of the tablespace now.

SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98172928

4)Let's drop the table and check again free space.

SQL> drop table test2;
Table dropped.

SQL> select sum(bytes) from dba_free_space where tablespace_name='USER_TBS';
SUM(BYTES)
----------
98435072

A)To drop a table simply use DROP TABLE .. keyword. To drop test table use,

DROP TABLE TEST;

B)If the table to be dropped contains any primary or unique keys referenced by foreign keys of other tables and you intend to drop the FOREIGN KEY constraints of the child tables, then include the CASCADE clause in the DROP TABLE statement, as shown below:

Example of This Scenario:
-----------------------------
1)Create both parent and child table.


SQL> create table parent(a number primary key);
Table created.

SQL> create table child ( b number references parent);
Table created.

2)Check the Constraints and their type.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';

CONSTRAINT_NAME C
------------------------------ -
SYS_C006345 R

SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='PARENT';

CONSTRAINT_NAME C
------------------------------ -
SYS_C006344 P

3)Now drop parent table produce oracle error.
Now try to drop parent table.
SQL> drop table parent;
drop table parent
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys

It raises ORA-02449 because the primary keys in table referenced by foreign keys. So to drop the table you have to drop foreign key constraints of the child table. This is done by

4)To drop parent table you have to include cascade constraints which will drop foreign key constraints.
SQL> drop table parent cascade constraints;
Table dropped.

5)Check the child table and see no constraints there.
SQL> select CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where OWNER='ARJU' and TABLE_NAME='CHILD';
no rows selected

If you check the metadata of child table before dropping the parent table and after dropping parent table you will see table definition is changed.

Before drop I got ,
SQL> select dbms_metadata.get_ddl('TABLE','CHILD') from dual;
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER,
FOREIGN KEY ("B")
REFERENCES "ARJU"."PARENT" ("A") ENABLE
)

After drop parent table I get,
CREATE TABLE "ARJU"."CHILD"
( "B" NUMBER
)

C)When you drop a table, normally the database does not immediately release the space associated with the table. Rather, the database renames the table and places it in a recycle bin. If you should want to immediately release the space associated with the table at the time you issue the DROP TABLE statement, include the PURGE clause as shown in the following statement:

SQL>DROP TABLE child PURGE;
Table dropped.
To drop object from recylcebin use PURGE statement.

SQL> purge table parent;
Table purged.
Related Documents
http://arjudba.blogspot.com/2008/09/how-to-disable-and-enable-all.html
http://arjudba.blogspot.com/2008/05/create-user-in-oracle.html
http://arjudba.blogspot.com/2008/06/create-temporary-table-in-oracle.html
Create, Alter , Rename, Modify Table SQL
http://arjudba.blogspot.com/2008/06/drop-table-in-oracle.html