下面的脚本在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;