Oracle 使用時(shí)間長(zhǎng)了， 新增了許多user 和tablespace. 需要清理一下

對(duì)于單個(gè)user和tablespace 來(lái)說(shuō)， 可以使用如下命令來(lái)完成。

? 步驟一：? 刪除user

drop user ×× cascade

說(shuō)明： 刪除了user，只是刪除了該user下的schema objects，是不會(huì)刪除相應(yīng)的tablespace的。

步驟二： 刪除tablespace

DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;

但是，因?yàn)槭枪╅_(kāi)發(fā)環(huán)境來(lái)使用的db, 需要清理的user 和 table space 很多。

思路 ：

?Export出DB中所有的user和tablespace, 篩選出系統(tǒng)的和有用的tablespace,把有用的信息load到一張表中去。

然后寫(xiě)例程循環(huán)，把不在有用表的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;

執(zhí)行后會(huì)報(bào)如下錯(cuò)誤

ORA-06550: 第 10 行, 第 5 列:?
PLS-00103: 出現(xiàn)符號(hào) "DROP"在需要下列之一時(shí)：
?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:

好像是被鎖了。。

沒(méi)辦法，例程不能寫(xiě)，就只能組出語(yǔ)句執(zhí)行了。

把需要?jiǎng)h除的user, tablespace 導(dǎo)出到Excel. 使用CONCATENATE 組出SQL.

貼到SQLdevelop 批量執(zhí)行。

整個(gè)刪除會(huì)比較耗時(shí)間， 100多個(gè)user.? 用了12個(gè)小時(shí)左右。

如要找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;
--如果其他表空間中的表有外鍵等約束關(guān)聯(lián)到了本表空間中的表的字段，就要加上CASCADE CONSTRAINTS
drop tablespace tablespace_name including contents and datafiles CASCADE CONSTRAINTS;

?

Oracle 刪除用戶和表空間