Oracle: Delete all of the current user objects (tables. View. Triggers. Stored procedures. Functions)

2011-01-05  来源:本站原创  分类:Database  人气:68 

Code posted here first, explain later, for fear forgot:)
If you find errors or have master the wrong place do not hesitate to point out that, thanks
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 ();

sql_str VARCHAR2 (500);
BEGIN
sql_str: = 'select uo.object_name, uo.object_type from user_objects uo where uo.object_type not in (''INDEX'','' LOB'') 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;

The simplest
drop user ...;

相关文章