注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天涯倦客的博客

祝福你朋友永远快乐!

 
 
 

日志

 
 

Oracle 删除用户和表空间  

2013-08-07 12:53:17|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
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;
  评论这张
 
阅读(493)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017