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;
