Shulou(Shulou.com)06/01 Report--

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.