ORACLE删除表空间会入到的问题及解决方案.zip_oracle_oracle 解决方案

1.drop tablespace mytbs01;表空间已丢弃，这种删除方式相关的数据文件仍然存在于磁盘上. 2.如果表空间里面含有数据对象,那么该表空间就不能像上面那样做直接删除了.如果按照上述删除，则会报ORA- 01549: 表空间非空，请使用 INCLUDING CONTENTS 选项错误。 解决方法：drop tablespace mytbs01 including contents and datafiles; 3. Drop tablespace 有下面两种方式： drop tablespace crm_data including contents and datafiles; drop tablespace crm_data including contents cascade constraints; 报错有下面几种： 一. 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 意思是：该表空间 CRM_DATA含有物化视图，或者含有物化视图的索引 解决办法： -- 首先删掉该表空间下的的物化视图 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' -- 然后删除该表空间下的其他表空间下物化视图在本表空间下创建的索引 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)); 二. 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的意思是： 让你删除该表空间下面的 primary key 和 unique key 处理办法： 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'); 三. 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 partition OR partitions的table所包含的全部 partitions不在一个表空间下面： 处理办法： 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'; 杀手锏： 直接drop 这个分区表（如果允许的话） 四. 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 意思是： 这个要删除的表空间 里面含有这么样的一些主键： 其他表空间的表在这些主键上建有外键 处理办法： 去掉这些垃圾外键 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%'); 如果还是不行的话，就用这个语句来删表空间吧： drop tablespace crm_data including contents cascade constraints