- 首先确认相关的用户，表空间，表空间文件信息
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>
