Oracle 删除用户和表空间的解决办法

这篇文章主要为大家详细介绍了Oracle 删除用户和表空间的简单示例，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

Oracle 删除用户和表空间

Oracle 使用时间长了， 新增了许多user 和tablespace. 需要清理一下

对于单个user和tablespace 来说， 可以使用如下命令来完成。

步骤一： 删除user

代码如下：

drop user ×× cascade

说明： 删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。

步骤二： 删除tablespace

代码如下：

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

但是，因为是供开发环境来使用的db, 需要清理的user 和 table space 很多。

思路：

Export出DB中所有的user和tablespace, 筛选出系统的和有用的tablespace,把有用的信息load到一张表中去。然后写例程循环，把不在有用表的tablespace删掉

1. select username,default_tablespace from dba_users;

2.

代码如下：

create table MTUSEFULSPACE
(
  ID Number(4) NOT NULL PRIMARY KEY,
  USERNAME varchar2(30),
  TABLESPACENAME varchar2(60),
  OWNERNAME varchar2(30)
);

3.

代码如下：

declare icount number(2);
    tempspace varchar2(60);
begin
 for curTable in (select username as allusr,default_tablespace as alltblspace from dba_users)
 loop
 tempspace :=curTable.alltblspace;
 dbms_output.put_line(tempspace);
 select count(TABLESPACENAME) into icount from MTUSEFULSPACE where TABLESPACENAME = tempspace;
 if icount=0 then
  DROP TABLESPACE tempspace INCLUDING CONTENTS AND DATAFILES;
 end if;
 commit;
 end loop;
end;

执行后会报如下错误

代码如下：

ORA-06550: 第 10 行, 第 5 列: 
PLS-00103: 出现符号 "DROP"在需要下列之一时：
 begin case declare exit
  for goto if loop mod null pragma raise return select update
  while with <an identifier>
  <a double-quoted delimited-identifier> <a bind variable> <<
  close current delete fetch lock insert open rollback
  savepoint set sql execute commit forall merge pipe
06550. 00000 - "line %s, column %s:\n%s"
*Cause:  Usually a PL/SQL compilation error.
*Action:

好像是被锁了。。

没办法，例程不能写，就只能组出语句执行了。

把需要删除的user, tablespace 导出到Excel. 使用CONCATENATE 组出SQL.

贴到SQLdevelop 批量执行。

整个删除会比较耗时间， 100多个user. 用了12个小时左右。

如要找datafile的具体位置，可以使用

代码如下：

select t1.name,t2.name from v$tablespace t1, v$datafile t2 where t1.ts# = t2.ts#;

SQL code

--删除空的表空间，但是不包含物理文件

代码如下：

drop tablespace tablespace_name;

--删除非空表空间，但是不包含物理文件

代码如下：

drop tablespace tablespace_name including contents;

--删除空表空间，包含物理文件

代码如下：

drop tablespace tablespace_name including datafiles;

--删除非空表空间，包含物理文件

代码如下：

drop tablespace tablespace_name including contents and datafiles;

--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADE CONSTRAINTS

代码如下：

drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！

注：关于Oracle 删除用户和表空间的简单示例的内容就先介绍到这里，更多相关文章的可以留意