删除用户下所有对象
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;
--别碰!这只是一只小尾巴! →_→ 会掉的!!!