Drop tablespace - check for non-segment objects

Suppose we create a tablespace, with an empty table on it, and then try to drop it.

SQL> create tablespace PAULTS datafile '/data/DEMO/PAULTS.dbf' size 10M;

Tablespace created.

SQL> create table TLEEG (a char(10)) tablespace PAULTS;

Table created.

SQL> select * from dba_segments where tablespace_name='PAULTS';

no rows selected

SQL> drop tablespace PAULTS;

drop tablespace PAULTS

*

ERROR at line 1:

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

Ok, so there are no segments, but the table is in the tablespace… tricky. Don’t drop the tablespace, because the table will be gone:

SQL> drop tablespace PAULTS including contents;

Tablespace dropped.

SQL> desc TLEEG

ERROR:

ORA-04043: object TLEEG does not exist

So I recreate the tablespace and table as in the sample above. But how to make sure the tablespace is really-really empty?

Let me, in addition to the table, add another table:

create table TWITHLOB (a int,b clob) tablespace PAULTS;

Now I’ve written the following script:

define MYTS=&Possible_empty_tablespace

set verify off

col CFnd heading "Contents Found"

select 'Table: '||owner||'.'||table_name as CFnd

from dba_tables where tablespace_name='&&MYTS'

  union

select 'Index: '||owner||'.'||index_name as CFnd

from dba_indexes where tablespace_name='&&MYTS'

  union

select 'Lob segment: '||owner||'.'||table_name||'('||column_name||')' as CFnd

from dba_lobs where tablespace_name='&&MYTS'

;

undefine MYTS

This will give:

Enter value for possible_empty_tablespace: PAULTS

Contents Found

--------------------------------------------

Index: PAUL.SYS_IL0000222132C00002$$

Lob segment: PAUL.TWITHLOB(B)

Table: PAUL.TLEEG

Table: PAUL.TWITHLOB

So this gives me sort of a checklist, of objects that are in the tablespace.

There are things I forgot, like table_partitions.

CREATE TABLE invoices

(invoice_no    NUMBER NOT NULL,

 invoice_date  DATE   NOT NULL,

 comments      VARCHAR2(500))

PARTITION BY HASH (invoice_no)

PARTITIONS 2

STORE IN (PAULTS,PAULTS);

If I change my script above, adding the phrase

union

select 'Table Partitions: '||table_owner||'.'||table_name||'.'||partition_name as CFnd

from dba_tab_partitions where tablespace_name='&&MYTS'

this will give me:

Contents Found

--------------------------------------------

Index: PAUL.SYS_IL0000222132C00002$$

Lob segment: PAUL.TWITHLOB(B)

Table Partitions: PAUL.INVOICES.SYS_P61

Table Partitions: PAUL.INVOICES.SYS_P62

Table: PAUL.TLEEG

Table: PAUL.TWITHLOB

6 rows selected.

My discussion point is the following. Which objects should I consider to make my check script complete? Ok, approach might be:

select table_name from dba_tab_columns

where column_name='TABLESPACE_NAME' and table_name like 'DBA%';

This gives us about 34 objects that might need to be investigated. Some are obviously not relevant for our script, like DBA_EXTENTS and DBA_DATA_FILES. But we found more that we should include.

TABLE_NAME

------------------------------

DBA_ALL_TABLES

DBA_AUTO_SEGADV_CTL

DBA_CLUSTERS

DBA_DATA_FILES

DBA_EXTENTS

DBA_FILE_GROUP_TABLES

DBA_FILE_GROUP_TABLESPACES

DBA_FLASHBACK_ARCHIVE_TS

DBA_FREE_SPACE

DBA_FREE_SPACE_COALESCED

DBA_HIST_SEG_STAT_OBJ

DBA_INDEXES

DBA_IND_PARTITIONS

DBA_IND_SUBPARTITIONS

DBA_LOBS

DBA_LOB_PARTITIONS

DBA_LOB_SUBPARTITIONS

DBA_LOB_TEMPLATES

DBA_OBJECT_TABLES

DBA_ROLLBACK_SEGS

DBA_SEGMENTS

DBA_SEGMENTS_OLD

DBA_SUBPARTITION_TEMPLATES

DBA_TABLES

DBA_TABLESPACES

DBA_TABLESPACE_GROUPS

DBA_TABLESPACE_THRESHOLDS

DBA_TABLESPACE_USAGE_METRICS

DBA_TAB_PARTITIONS

DBA_TAB_SUBPARTITIONS

DBA_TEMP_FILES

DBA_TEMP_FREE_SPACE

DBA_TS_QUOTAS

DBA_UNDO_EXTENTS

So our script should check for: 

• Tables, tab-partitions, tab-subpartitions
• Indexes, ind-partitions, ind-subpartitions
• Lob, lob-partitions, lob-subpartitions

Am I forgetting something? If not, my script will check for the above objects in my tablespace. If not found, it’s really empty and I can drop the tablespace.

Hence, my full script will be (for now):

define MYTS=&Possible_empty_tablespace

set verify off

col CFnd heading "Contents Found"

select 'Table: '||owner||'.'||table_name as CFnd

from dba_tables

where tablespace_name='&&MYTS'

union

select 'Table partitions: '||table_owner||'.'||table_name||'.'||partition_name as CFnd

from dba_tab_partitions

where tablespace_name='&&MYTS'

union

select 'Table sub-parts: '||table_owner||'.'||table_name||'.'||partition_name||'.'||subpartition_name as CFnd

from dba_tab_subpartitions

where tablespace_name='&&MYTS'

union

select 'Index: '||owner||'.'||index_name as CFnd

from dba_indexes where tablespace_name='&&MYTS'

union

select 'Index partitions: '||index_owner||'.'||index_name||'.'||partition_name as CFnd

from dba_ind_partitions

where tablespace_name='&&MYTS'

union

select 'Index sub-parts: '||index_owner||'.'||index_name||'.'||partition_name||'.'||subpartition_name as CFnd

from dba_ind_subpartitions

where tablespace_name='&&MYTS'

union

select 'Lob segment: '||owner||'.'||table_name||'('||column_name||')' as CFnd

from dba_lobs

where tablespace_name='&&MYTS'

union

select 'Lob partitions: '||table_owner||'.'||table_name||'.'||partition_name as CFnd

from dba_lob_partitions

where tablespace_name='&&MYTS'

union

select 'Lob sub-parts: '||table_owner||'.'||table_name||'.'||lob_partition_name||'.'||subpartition_name as CFnd

from dba_lob_subpartitions

where tablespace_name='&&MYTS'

;

undefine MYTS

If this returns no rows, it should be save to

drop tablespace PAULTS including contents and datafiles;