删除用户下所有对象

DECLARE TYPE name_list IS TABLE OF VARCHAR2(40); TYPE type_list IS TABLE OF VARCHAR2(20);

Tab_name name_list:=name_list(); Tab_type type_list:=type_list();

--语句定义除body，系统的lob和index外的对象

sql_str VARCHAR2(500); BEGIN sql_str := 'SELECT UO.OBJECT_NAME, UO.OBJECT_TYPE FROM USER_OBJECTS UO WHERE UO.OBJECT_TYPE NOT IN (''PACKAGE BODY'',''PACKAGE'',  ''LOB'' ) AND UO.OBJECT_NAME NOT LIKE ''SYS%'' ORDER BY UO.OBJECT_TYPE DESC'; EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type;

FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP sql_str := 'DROP ' || Tab_type(i) || ' ' || Tab_name(i); EXECUTE IMMEDIATE sql_str; END LOOP; END;