Oracle 中如何删除一个用户拥有的所有对象

下面的脚本在oracle中如何将一个用户所拥有的所有对象，表，sequence，procedure。。。全部删掉。

 

1，drop 对象的方式，注意要先将所有的外键约束删掉。

 

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;

 

2，删除用户的方式，但是要有drop user的权限且user建立的时候必须划分出其专用的tablespace 。

drop user username cascade;
drop tablespace username_tablespace including contents and datafiles;