Oracle删除指定用户下所有对象

标签：object prompt name 删除 -- drop 指定 user Oracle

  --.sql脚本 --唯一注意的是下面的d:\dropuserobj.sql为操作的.sql; --用于删除当前用户的所有对象 --use for drop all objects in current user; set heading off; set feedback off; spool d:\dropuserobj.sql; prompt --删除约束条件 select 'alter table '||table_name||' drop constraint '||constraint_name||' ;' from user_constraints where constraint_type='R'; prompt --删除表空间 select 'drop table '||table_name ||';' from user_tables; prompt --删除视图 select 'drop view ' ||view_name||';' from user_views; prompt --删除数字序列 select 'drop sequence ' ||sequence_name||';' from user_sequences; prompt --删除函数 select 'drop function ' ||object_name||';' from user_objects where object_type='FUNCTION'; prompt --删除存储过程 select 'drop procedure '||object_name||';' from user_objects where object_type='PROCEDURE'; prompt --删除包 prompt --删除包内容 select 'drop package '|| object_name||';' from user_objects where object_type='PACKAGE'; prompt --删除数据库链接 select 'drop database link '|| object_name||';' from user_objects where object_type='DATABASE LINK'; spool off; set heading on; set feedback on; @@d:\dropuserobj.sql; host del d:\dropuserobj.sql; --done for drop objects in current user; --在SQL*PLUS 将这整段作为.sql导入或者直接复制黏贴，按F5执行，完成。   truncate table bizflow_entrypoint_req; truncate table bizflow_entrypoint_task; truncate table bizflow_entrypoint_task_log;    

--查询当前用户回收站
SELECT object_name, original_name, type, droptime FROM recyclebin;

      查询表空间 SELECT * FROM (Select Segment_Name,Sum(bytes)/1024/1024 AS leng From User_Extents Group By Segment_Name) a ORDER BY a.leng DESC;

SELECT sum(a.leng) FROM (Select Segment_Name,Sum(bytes)/1024/1024 AS leng From User_Extents Group By Segment_Name) a ORDER BY a.leng DESC;

  查询索引状态 SELECT OWNER, INDEX_NAME,STATUS FROM ALL_INDEXES WHERE STATUS='UNUSABLE' ; 修改索引状态 ALTER INDEX PK_STD_CUSTOMER_INFO REBUILD;  

重置统计信息

exec dbms_stats.gather_table_stats(ownname=>'PFWXT_STD',tabname=>'STD_TRANS_APPLY_ORDER_DTL',estimate_percent=>10,method_opt=>'for all indexed columns');

标签：object,prompt,name,删除,--,drop,指定,user,Oracle
From： https://www.cnblogs.com/thb-blog/p/17899223.html