08 October 2014

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;