注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

和申的个人主页

专注于java开发,1985wanggang

 
 
 

日志

 
 

Drop tablespace 有下面两种方式  

2010-05-07 21:51:55|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

org.quartz.impl.jdbcjobstore.LockException: Failure obtaining db row lock: - 和申 - 和申的个人主页

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

 

Failed to convert property value of type [java.lang.String] to required type [double] for property price; nested exception is java.lang.IllegalArgumentException: Cannot convert value of type [java.lang.String] to required type [double] for property price: - 和申 - 和申的个人主页
  评论这张
 
阅读(8024)| 评论(1)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016