drop table 报错ora- 全分析
来源:互联网 发布:网络营销策划行业动态 编辑:程序博客网 时间:2024/06/07 04:13
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 table 报错ora- 全分析
- 61、drop table 报错ora- 全分析
- drop table 时发生ORA-00604 ORA-01422 错
- drop diskgroup报ora-15001
- drop table 报ora-00942 表或者视图不存在//SQL_TRACE的基本用法
- drop user cascade的时候报错ora-00600错误
- mysql alter table drop constraint 报错1064
- ORA-00600 15264 无法drop table
- ORA-12988: cannot drop column from table owned by SYS
- 删除ORACLE的临时表空间报错:ORA-12906: cannot drop default temporary tablespace
- hive drop table报错:FAILED: SemanticException MetaException(message:Timeout when executing method: g
- innodb中,truncate table与drop table操作分析
- 关于报错"ORA-01747: user.table.column, table.column 或列说明无效"的解决办法
- drop表时报 ORA-00604 ORA-01422 错
- shark执行drop table if exists table XX会报错?
- ORA-15040: diskgroup is incomplete 报错分析
- Oracle 关于报错"ORA-01747: user.table.column, table.column 或列说明无效"的终极解决办法
- ORACLE 8i 遇到报错:ORA-01631: max # extents (505) reached in table
- 黑客基础
- find / -name "libmysqlclient_r.so.*" -print
- Content-Disposition 的使用方法
- Flex Resource Bundle 在Localization方面的应用
- C#调用dll时的类型转换总结
- drop table 报错ora- 全分析
- 关于DataFormatString格式化
- unicode工程中使用CFile 和CStdioFile写入字符串bug
- 马云:CEO的本事就是会用别人的脑袋
- cookie重定向问题的解决!
- sysbench的安装及使用
- ajax缓存问题
- spring 编码过滤器
- XML笔记一