Oracle Sql-remove A user ' s all objects

DECLARE

TYPE cst_table_list is Table of VARCHAR2 (40); TYPE Cst_list is TABLE of VARCHAR2 (40);

TYPE Name_list is TABLE of VARCHAR2 (40); TYPE Type_list is TABLE of VARCHAR2 (20);

Cst_tab_name cst_table_list:=cst_table_list (); Cst_name cst_list:=cst_list ();

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

Sql_str VARCHAR2 (500);

V_err_code number; V_err_msg VARCHAR2 (200);

Begin-Drop all reference constraints begin SQL_STR: = ' Select Table_name,constraint_name from user_constraints     where constraint_type= ' R ';     EXECUTE IMMEDIATE sql_str BULK COLLECT into Cst_tab_name,cst_name; For I in Cst_tab_name. First: Cst_tab_name. Last LOOP

Begin SQL_STR: = ' ALTER TABLE ' | | Cst_tab_name (i) | | ' DROP CONSTRAINT ' | |         Cst_name (i);         EXECUTE IMMEDIATE Sql_str; Dbms_output.put_line (SQL_STR);

Exception when others then v_err_code: = SQLCODE;             V_err_msg: = SUBSTR (SQLERRM, 1, 200); Dbms_output. Put_Line (' Error code: ' | |             V_err_code); Dbms_output. Put_Line (' Error message: ' | | V_ERR_MSG);

End

END LOOP;

--Drop all reference constraints end

--drop all tables, sequences ... 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 begin sql_str: = ' DROP ' | | Tab_type (i) | | "| |             Tab_name (i);             EXECUTE IMMEDIATE Sql_str; Dbms_output.put_line (SQL_STR);

Exception when others then v_err_code: = SQLCODE;                 V_err_msg: = SUBSTR (SQLERRM, 1, 200); Dbms_output. Put_Line (' Error code: ' | |                 V_err_code); Dbms_output. Put_Line (' Error message: ' | |         V_ERR_MSG); End

END LOOP; --drop all tables, sequences ... end end;

Oracle Sql-remove A user ' s all objects