Tablespace tablespace deletion
1. Common Tablespace Deletion:
Oracle 11g Delete Tablespace Syntax Description:
DROP TABLESPACE tablespace_name [ including contents [ and datafiles ] [ CASCADE CONSTRAINT search] ];
No option--Delete only if the table space is empty;
including contents â € "delete tablespaces and objects;
including contents and datafiles â € "Delete tablespaces, objects, and datafiles;
including contents CASCADE CONSTRAINT -Delete association;
including contents and datafiles cascade constraint --Contains the first two items.
Generate script:
select 'drop tablespace '||tablespace_name||' including contents and datafiles cascade constraint;' from dba_data_files where tablespace_name not in('SYSTEM','SYSAUX','USERS','EXAMPLE','UNDOTBS2','UNDOTBS1')
Second, partition table space deletion:
select 'alter table '||owner||'. '||segment_name||' drop partition '||partition_name||' ;'
from dba_segments
where segment_name in (select distinct segment_name
from dba_segments
where tablespace_name = 'p1'
and segment_type like '%PART%')
and tablespace_name 'p1';
It follows that:
alter table CP.IDX_CP_HANDLE_BATCH_NO drop partition SYS_P200 ;
alter table CP.IDX_CP_HANDLE_REQUEST_ID drop partition SYS_P200 ;
alter table CP.IDX_CP_PAYMENT_REQUEST_ID drop partition SYS_P201 ;
alter table CP.IDX_CP_PAYMENT_TRAN_NO drop partition SYS_P201 ;
alter table CP.IDX_CP_REQUEST_ID drop partition SYS_P199 ;
alter table CP.IDX_CP_REQUEST_TRAN_NO drop partition SYS_P199 ;
alter table CP.TBL_CP_HANDLE drop partition SYS_P200 ;
alter table CP.TBL_CP_PAYMENT drop partition SYS_P201 ;
alter table CP.TBL_CP_REQUEST drop partition SYS_P199 ;
III. Exception handling:
There are several types of errors:
I. ORA-23515
--- ORA-23515: materialized views and/or their indices exist in the tablespace
drop tablespace crm_data including contents and datafiles
*
ERROR at line 1:
ORA-23515: materialized views and/or their indices exist in the tablespace
This table space CRM_DATA contains a materialized view, or an index of a materialized view
Solution:
--First delete the materialized view in this table space
select 'drop materialized view '||owner||'. '||segment_name||' ;'
from dba_segments
where segment_name in (select mview_name from dba_mviews)
and tablespace_name = 'CRM_DATA'
--Then delete the indexes created by materialized views in other tablespaces under this tablespace
select *
from dba_segments
where tablespace_name = 'CRM_DATA'
and segment_name in
(select index_name
from dba_indexes
where table_name in (select mview_name from dba_mviews));
II. ORA-02429
---ORA-02429: cannot drop index used for enforcement of unique/primary key
drop tablespace crm_idx including contents cascade constraints
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key
ORA-02429 allows you to delete the primary key and unique key below the table space.
Treatment:
select 'alter table '||owner||'. '||table_name||' drop constraint '||constraint_name||' ;'
from dba_constraints
where constraint_type in ('U', 'P')
and (index_owner, index_name) in
(select owner, segment_name
from dba_segments
where tablespace_name = 'CRM_IDX');
III. ORA-14404
--ORA-14404: partitioned table contains partitions in a different tablespace
drop tablespace crm_arc_data including contents and datafiles
*
ERROR at line 1:
ORA-14404: partitioned table contains partitions in a different tablespace
This table space contains one or more partitions of the partition table: all partitions contained in this partition OR partitions table are not under a table space:
Treatment:
select 'alter table '||owner||'. '||segment_name||' drop partition '||partition_name||' ;'
from dba_segments
where segment_name in (select distinct segment_name
from dba_segments
where tablespace_name = 'CRM_ARC_DATA'
and segment_type like '%PART%')
and tablespace_name 'CRM_ARC_DATA';
Killer: drop the partition table directly (if allowed)
IV. ORA-02449
--- ORA-02449: unique/primary keys in table referenced by foreign keys
drop tablespace crm_data including contents and datafiles
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
The table space to be deleted contains primary keys on which tables from other tablespaces have foreign keys
Solution: Remove these garbage foreign keys
select 'alter table '||owner||'. '||table_name||' drop constraint '||constraint_name||' ;'
from dba_constraints
where constraint_type = 'R'
and table_name in (select segment_name
from dba_segments
where tablespace_name = 'CRM_DATA'
and segment_type like '%TABLE%');
If not, use this statement to delete the table space:
drop tablespace crm_data including contents cascade constraints
