oracle 刪除使用者

 

對於單個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
   <<
   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;