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');