在使用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 ;
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 ;