When dropping tablespaces, I like to use the “including datafiles” clause so I don’t have to find and remove the underlying datafiles. That presents a problem because that clause is only accepted if you add the “contents” clause (including contents and datafiles), meaning it should drop any objects with segments in the tablespace. Not a problem since you checked the dba_segments first, right?
WRONG!!! if you tell Oracle to drop the contents, it will drop objects that have no segments, but would create a segment there if a row was inserted. By default, 11g will defer segment creation for a table or index if there are no rows when the object is created (very common case). So you’d better check for this before you drop the tablespace. If only Oracle would allow us to leave off the contents clause (check and fail if there are any dependent objects, but drop the datafiles if the TS is dropped), but alas it does not, so we must be diligent to check this if we want to drop datafiles as part of the tablespace drop.
Here is the contents of a SQL script I use to check that a tablespace has no dependencies. Try this on a tablespace you know has objects and also a non-existent tablespace, to see the output.
set ver off pages 0 feed off
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_segments!’) FROM dba_segments WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_tables!’) FROM dba_tables WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_tab_partitions!’) FROM dba_tab_partitions WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_tab_subpartitions!’) FROM dba_tab_subpartitions WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_indexes!’) FROM dba_indexes WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_ind_partitions!’) FROM dba_ind_partitions WHERE tablespace_name = ‘&&1’;
SELECT decode(COUNT(*), 0, ‘OK’, ‘Dependency found in dba_ind_subpartitions!’) FROM dba_ind_subpartitions WHERE tablespace_name = ‘&&1’;
exit