Oracle删除用户操作

• 首先确认相关的用户，表空间，表空间文件信息

select username,default_tablespace from dba_users where username="gumdbhn";
select * from dba_tablespaces a where a.tablespace_name="gumdbhn";
select * from dba_data_files b where b.tablespace_name ="gumdbhn";

• 其次删除用户和该用户下的schema objects，但是不会删除相应的tablespace

drop user GUMDBHN cascade;

• 删除表空间

SQL> alter tablespace GUMDBHN offline;
alter tablespace GUMDBHN offline
*
ERROR at line 1:
ORA-01191: file 42 is already offline - cannot do a normal offline
ORA-01110: data file 42: "/u02/oradata/GUMDBHN01.dbf"

移除用户后，表空间自动offline

SQL> DROP TABLESPACE GUMDBHN INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE GUMDBHN INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-38301: can not perform DDL/DML over objects in Recycle Bin

此时，需要将回收站清空后在删除

SQL> purge dba_recyclebin;
DBA Recyclebin purged.
SQL> DROP TABLESPACE GUMDBHN INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.

• 重启正常

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 2.0243E+10 bytes
Fixed Size		    2261968 bytes
Variable Size		 6375345200 bytes
Database Buffers	 1.3824E+10 bytes
Redo Buffers		   41164800 bytes
Database mounted.
Database opened.
SQL>