oracle刪除當前用戶下所有表

1、如果有刪除用戶的權限，則可以：

drop user user_name cascade;

加了cascade就可以把用戶連帶的數據全部刪掉。

刪除后再創建該用戶。
--創建管理員用戶
create user 用戶名 identified by 密碼 default tablespace space_data（表空間名稱） temporary tablespace space_temp（臨時表空間名稱）;
--授權
grant connect,dba to 用戶名;
--修改限額
ALTER USER "用戶名" QUOTA UNLIMITED ON SPACE_DATA(表空間名稱);

--查看所有用戶對象
select uo.object_name,uo.object_type from user_objects uo where uo.object_type<>'LOB' order by uo.object_type desc

?

2、如果沒有刪除用戶的權限，則可以執行：

select 'drop table '||table_name||';'
from cat
where table_type='TABLE'

將會輸出一批刪除表的sql語句，這些SQL語句執行一下就可以了。（需要有drop table的權限）

?

/*分為四步 */ /*第1步：創建臨時表空間 */ create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第2步：創建數據表空間 */ create tablespace user_data logging datafile 'D:\oracle\oradata\Oracle9i\user_data.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; /*第3步：創建用戶并指定表空間 */ create user username identified by password default tablespace user_data temporary tablespace user_temp; /*第4步：給用戶授予權限 */ grant connect,resource,dba to username;

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; posted on 2014-04-16 22:19 秦瑞It行程實錄 閱讀(...) 評論(...) 編輯 收藏

轉載于:https://www.cnblogs.com/ruiy/p/del.html

總結

以上是生活随笔為你收集整理的oracle删除当前用户下所有表的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。