Oracle清空用户下的所有对象

--清空用户所有对象的脚本

DECLARE 
CURSOR procInfo_cur IS
SELECT o.object_name,o.object_type 
FROM (
SELECT object_name, object_type, status, last_ddl_time, object_id
FROM sys.user_objects
WHERE 1=1
AND object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'TABLE','VIEW','SYNONYM','DATABASE LINK')
) o, sys.all_probe_objects d
WHERE o.OBJECT_ID = d.object_id (+)
AND O.Object_Name = D.Object_Name (+)
AND ((d.object_type IS NULL) OR (d.object_type IN ('FUNCTION', 'PROCEDURE', 'PACKAGE', 'TABLE','VIEW','SYNONYM','DATABASE LINK')))
ORDER BY 2, 1;
object_name VARCHAR2(50);
object_type VARCHAR2(20);
BEGIN
OPEN procInfo_cur ;
LOOP 
FETCH procInfo_cur INTO object_name,object_type;
EXIT WHEN procInfo_cur%NOTFOUND; 
IF object_type = 'TABLE' 
THEN
EXECUTE IMMEDIATE 'DROP'||' '||object_type||' '||object_name||' '||'CASCADE CONSTRAINTS'; 
ELSE 
EXECUTE IMMEDIATE 'DROP'||' '||object_type||' '||object_name ; 
END IF;
END LOOP;
CLOSE procInfo_cur;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2136608/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31448824/viewspace-2136608/