Drop tablespace 有下面两种方式
来源:互联网 发布:气象数据共享网 编辑:程序博客网 时间:2024/04/30 00:08
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
- Drop tablespace 有下面两种方式
- drop 和 truncate , drop 两种删除表的方式对比评测
- DROP TABLESPACE相关内容
- drop tablespace ORA-02429
- offline tablespace 的几种方式
- Expert Tips on Drop Temporary Tablespace Hangs!!
- TSPITR恢复drop掉的tablespace
- Expert Tips on Drop Temporary Tablespace Hangs!!
- drop tablespace TEST including contents and datafiles;
- Expert Tips on Drop Temporary Tablespace Hangs!!
- ORA-959 signalled during: drop tablespace
- alter database drop datafile 与 drop tablespace file 的区别
- POST和GET两种方式,两种方式有什么区别
- 两种跳转方式分别是什么?有什么区别
- 两种跳转方式分别是什么?两者有什么区别?
- 两种跳转方式分别是什么?有什么区别
- ISP和IAP两种编程方式有何区别?
- ISP和IAP两种编程方式有何区别?
- 35个你也许不知道的Google开源项目
- Delphi 调用COM(VC编写)
- Qt 的一些心得
- CUDA 4.0中P2P与UVA的性特性使用方法
- 10个给程序员的建议
- Drop tablespace 有下面两种方式
- STM32与LPC系列ARM资源之比较
- javascript -基础知识
- C++【小结】1 - 内存指针
- 处女作
- 一个简单实用的SSAO实现
- 一些常见的javascript应用(入门级)
- 数据结构 0
- 手工破解PPS享受VIP的方法