Oracle 删除用户和表空间详细介绍
Oracle删除用户和表空间
Oracle使用时间长了，新增了许多user和tablespace.需要清理一下
对于单个user和tablespace来说，可以使用如下命令来完成。
步骤一： 删除user
dropuser××cascade
说明：删除了user，只是删除了该user下的schemaobjects，是不会删除相应的tablespace的。
步骤二：删除tablespace
DROPTABLESPACEtablespace_nameINCLUDINGCONTENTSANDDATAFILES;
但是，因为是供开发环境来使用的db,需要清理的user和tablespace很多。
思路：
Export出DB中所有的user和tablespace,筛选出系统的和有用的tablespace,把有用的信息load到一张表中去。然后写例程循环，把不在有用表的tablespace删掉
1.selectusername,default_tablespacefromdba_users;
2.
createtableMTUSEFULSPACE ( IDNumber(4)NOTNULLPRIMARYKEY, USERNAMEvarchar2(30), TABLESPACENAMEvarchar2(60), OWNERNAMEvarchar2(30) );
3.
declareicountnumber(2); tempspacevarchar2(60); begin forcurTablein(selectusernameasallusr,default_tablespaceasalltblspacefromdba_users) loop tempspace:=curTable.alltblspace; dbms_output.put_line(tempspace); selectcount(TABLESPACENAME)intoicountfromMTUSEFULSPACEwhereTABLESPACENAME=tempspace; ificount=0then DROPTABLESPACEtempspaceINCLUDINGCONTENTSANDDATAFILES; endif; commit; endloop; end;
执行后会报如下错误
ORA-06550:第10行,第5列: PLS-00103:出现符号"DROP"在需要下列之一时： begincasedeclareexit forgotoifloopmodnullpragmaraisereturnselectupdate whilewith<anidentifier> <adouble-quoteddelimited-identifier><abindvariable><< closecurrentdeletefetchlockinsertopenrollback savepointsetsqlexecutecommitforallmergepipe 06550.00000-"line%s,column%s:\n%s" *Cause:UsuallyaPL/SQLcompilationerror. *Action:
好像是被锁了。。
没办法，例程不能写，就只能组出语句执行了。
把需要删除的user,tablespace导出到Excel.使用CONCATENATE组出SQL.
贴到SQLdevelop批量执行。
整个删除会比较耗时间，100多个user. 用了12个小时左右。
如要找datafile的具体位置，可以使用
selectt1.name,t2.namefromv$tablespacet1,v$datafilet2wheret1.ts#=t2.ts#;
SQLcode
--删除空的表空间，但是不包含物理文件
droptablespacetablespace_name;
--删除非空表空间，但是不包含物理文件
droptablespacetablespace_nameincludingcontents;
--删除空表空间，包含物理文件
droptablespacetablespace_nameincludingdatafiles;
--删除非空表空间，包含物理文件
droptablespacetablespace_nameincludingcontentsanddatafiles;
--如果其他表空间中的表有外键等约束关联到了本表空间中的表的字段，就要加上CASCADECONSTRAINTS
droptablespacetablespace_nameincludingcontentsanddatafilesCASCADECONSTRAINTS;
感谢阅读，希望能帮助到大家，谢谢大家对本站的支持！