Database Reference
In-Depth Information
You may want to wait to see if anybody screams that an application is broken because it can't write to a table or
index in the tablespace to be dropped. When you're sure the tablespace isn't required, drop it, and delete its data files:
SQL> drop tablespace inv_data including contents and datafiles;
■
you can drop a tablespace whether it's online or offline. the exception to this is the
SYSTEM
tablespace, which
can't be dropped. it's always a good idea to take a tablespace offline before you drop it. By doing so, you can better
determine if an application is using any objects in the tablespace. if you attempt to query a table in an offline tablespace,
you receive this error: ora-00376: file can't be read at this time.
Tip
Dropping a tablespace using
INCLUDING CONTENTS AND DATAFILES
permanently removes the tablespace and any
of its data files. Make certain the tablespace doesn't contain any data you want to keep before you drop it.
If you attempt to drop a tablespace that contains a primary key that is referenced by a foreign key associated with
a table in a tablespace different from the one you're trying to drop, you receive this error:
ORA-02449: unique/primary keys in table referenced by foreign keys
Run this query first to determine whether any foreign key constraints will be affected:
select p.owner,
p.table_name,
p.constraint_name,
f.table_name referencing_table,
f.constraint_name foreign_key_name,
f.status fk_status
from dba_constraints p,
dba_constraints f,
dba_tables t
where p.constraint_name = f.r_constraint_name
and f.constraint_type = 'R'
and p.table_name = t.table_name
and t.tablespace_name = UPPER('&tablespace_name')
order by 1,2,3,4,5;
If there are referenced constraints, you need to first drop the constraints or use the
CASCADE CONSTRAINTS
clause
of the
DROP TABLESPACE
statement. This statement uses
CASCADE CONSTRAINTS
to drop any affected constraints
automatically:
SQL> drop tablespace inv_data including contents and data files cascade constraints;
This statement drops any referential integrity constraints from tables outside the tablespace being dropped that
reference tables within the dropped tablespace.
