Oracle 清除用户对象方法

 

Oracle 清除用户对象方法

 

In Beijing on 2007-6-30

 

 

       在使用Oracle数据库的时候，经常会遇到需要把一个用户的所有对象重新导入的操作。这里介绍两种常用的方法，方便大家使用。

 

一、使用 drop user <user> cascade; 可以删除用户及所相关的所有对象，可以通过重新创建一个同名对象，然后使用imp 命令从dmp文件导入新的对象。

二、通过脚本把清空用户的所有对象，包含数据表、函数、存储过程等其他对象。采用这种方法，可以无需dba权限，在你需要完整导入一个用户数据的时候非常好用。

 

 

附：清除用户对象脚本

 

PL/SQL Developer Test script 3.0

120

------------------------------------------------------------------

-- Created on 2007-5-18 by GUIPEI

-- drop oracle user's all objects

-- 

--

------------------------------------------------------------------

DECLARE

 -- Local variables here

 i INTEGER;

 

 CURSOR cur_objects(obj_type VARCHAR2) IS

    SELECT object_name FROM user_objects WHERE object_type IN (obj_type);

 

 obj_name VARCHAR(200);

 sql_str VARCHAR(500);

 

BEGIN

 

 --drop all tables;

 OPEN cur_objects('TABLE');

 

 LOOP

    FETCH cur_objects

      INTO obj_name;

    EXIT WHEN cur_objects%NOTFOUND;

 

    dbms_output.put_line('delete table: ' || obj_name);

    sql_str := 'drop table ' || obj_name || ' CASCADE CONSTRAINTS ';

 

    EXECUTE IMMEDIATE sql_str;

 

 END LOOP;

 

 CLOSE cur_objects;

 

 --drop all SEQUENCE;

 OPEN cur_objects('SEQUENCE');

 

 LOOP

    FETCH cur_objects

      INTO obj_name;

    EXIT WHEN cur_objects%NOTFOUND;

 

    dbms_output.put_line('delete SEQUENCE: ' || obj_name);

    sql_str := 'drop SEQUENCE ' || obj_name;

    EXECUTE IMMEDIATE sql_str;

 

 END LOOP;

 

 CLOSE cur_objects;

 

 --drop all VIEW;

 OPEN cur_objects('VIEW');

 

 LOOP

    FETCH cur_objects

      INTO obj_name;

    EXIT WHEN cur_objects%NOTFOUND;

 

    dbms_output.put_line('delete VIEW: ' || obj_name);

    sql_str := 'drop VIEW ' || obj_name || ' CASCADE CONSTRAINTS ';

 

    EXECUTE IMMEDIATE sql_str;

 

 END LOOP;

 

 CLOSE cur_objects;

 

 --drop all FUNCTION;

 OPEN cur_objects('FUNCTION');

 

 LOOP

    FETCH cur_objects

      INTO obj_name;

    EXIT WHEN cur_objects%NOTFOUND;

 

    dbms_output.put_line('delete FUNCTION: ' || obj_name);

    sql_str := 'drop FUNCTION ' || obj_name;

 

    EXECUTE IMMEDIATE sql_str;

 

 END LOOP;

 

 CLOSE cur_objects;

 

 --drop all PROCEDURE;

 OPEN cur_objects('PROCEDURE');

 

 LOOP

    FETCH cur_objects

      INTO obj_name;

    EXIT WHEN cur_objects%NOTFOUND;

 

    dbms_output.put_line('delete PROCEDURE: ' || obj_name);

    sql_str := 'drop PROCEDURE ' || obj_name;

    EXECUTE IMMEDIATE sql_str;

 

 END LOOP;

 

 CLOSE cur_objects;

 

 --drop all PACKAGE;

 OPEN cur_objects('PACKAGE');

 

 LOOP

    FETCH cur_objects

      INTO obj_name;

    EXIT WHEN cur_objects%NOTFOUND;

 

    dbms_output.put_line('delete PACKAGE: ' || obj_name);

    sql_str := 'drop PACKAGE ' || obj_name;

    dbms_output.put_line(sql_str);

 

    EXECUTE IMMEDIATE sql_str;

 

 END LOOP;

 

 CLOSE cur_objects;

 

END;

0

0