linux下删除表空间<转>

//数据库连接

sqlplus sys/pwd as sysdba

//查询所有表空间

select tablespace_name from dba_tablespaces;

//选择目标表空间删除

drop tablespace MYDB including contents and datafiles cascade constraints;

//删除相应用户

drop user MYDB_USER cascade;

===============================================================

操作中可能会出现如下报错：

报错信息：ORA-01940: cannot drop a user that is currently connected

解决方法：//查找当前帐号下哪些连接在运行（此处需要大写）：

                SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='MYDB_USER';

                //删除用户进程；

                ALTER  SYSTEM  KILL SESSION '1,1333';

                ALTER  SYSTEM  KILL SESSION '33,786';

---------------------------------------------------------------------------------------------------

报错信息：ORA-00604: error occurred at recursive SQL level 1

                ORA-02429: cannot drop index used for enforcement of unique/primary key

报错原因：中途因先删除用户再删除表空间

解决方法：//查询该表空间所有仍然存在的主键约束

                select segment_name,partition_name,tablespace_name from dba_extents where tablespace_name=upper('MYDB');

                //执行后出现如下约束

                TROLE_PK

                TMENU_PK

               //生成删除主键约束的脚本

               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=upper('MYDB'));

              //执行删除主键约束的脚本

              alter table MYDB.TROLE drop constraint TROLE_PK;

              alter table MYDB.TMENU drop constraint TMENU_PK;