Oracle删除用户操作

  • Z时代
  • 2024-01-10
  • 分类：综合

database

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

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>

以上是 Oracle删除用户操作 的全部内容， 来源链接： utcz.com/z/532250.html

回到顶部