oracle清用户,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/，如需转载，请注明出处，否则将追究法律责任。