//数据库连接
sqlplus sys/pwd as sysdba
或者sqlplus "/as sysdba"(oracle账户登录系统)
//查询所有表空间
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;