作为我们开发人员经常使用exp导出某一个用户下的所有对象从某一个数据库,
然后导入。通常导入的时候总是会遇到一些意想不到的问题,其中一个问题就是数据库中已经存在同名对象,
同时也为那些比较复杂的参数而烦恼。其实对于我们,只需要做下面三个动作,就能顺利完成你的导入导出工作:
1.导出
Exp <username>[/<password>][@<connect_string>] Owner=<user_name> File=<filename.dmp>
2.Drop掉目标用户下的所有对象
执行下面的语句:
1
declare
2 cursor c_cursor is
3 select OBJECT_NAME ,OBJECT_TYPE
4 from user_objects
5 where OBJECT_TYPE IN ( ' TABLE ' , ' VIEW ' ,
6 ' PROCEDURE ' , ' FUNCTION ' ,
7 ' PACKAGE ' , ' PACKAGE BODY ' ,
8 ' SEQUENCE ' , ' MATERIALIZED VIEW ' ,
9 ' SYNONYM ' , ' TYPE ' , ' TYPE BODY ' )
10 ORDER BY OBJECT_TYPE, OBJECT_NAME ;
11 message varchar2 ( 100 );
12 errorNums integer ;
13 begin
14 errorNums : = 0 ;
15 dbms_output.put_line( ' Dropping objects ' );
16 FOR tab_rec IN c_cursor loop
17 dbms_output.put_line( ' dropping ' || tab_rec.OBJECT_TYPE || ' ' || TAB_REC. OBJECT_NAME );
18 begin
19 execute immediate ' drop ' || TAB_REC.OBJECT_TYPE || ' ' || tab_rec. OBJECT_NAME ;
20 exception
21 when others then
22 errorNums : = errorNums + 1 ;
23 dbms_output.put_line( ' dropping err. ' );
24 dbms_output.put_line(sqlerrm);
25 end ;
26 end loop;
27 if errorNums > 0 then
28 dbms_output.put_line( ' Finished with error. Please execute again. ' );
29 else
30 dbms_output.put_line( ' Finished ' );
31 end if ;
32 end ;
2 cursor c_cursor is
3 select OBJECT_NAME ,OBJECT_TYPE
4 from user_objects
5 where OBJECT_TYPE IN ( ' TABLE ' , ' VIEW ' ,
6 ' PROCEDURE ' , ' FUNCTION ' ,
7 ' PACKAGE ' , ' PACKAGE BODY ' ,
8 ' SEQUENCE ' , ' MATERIALIZED VIEW ' ,
9 ' SYNONYM ' , ' TYPE ' , ' TYPE BODY ' )
10 ORDER BY OBJECT_TYPE, OBJECT_NAME ;
11 message varchar2 ( 100 );
12 errorNums integer ;
13 begin
14 errorNums : = 0 ;
15 dbms_output.put_line( ' Dropping objects ' );
16 FOR tab_rec IN c_cursor loop
17 dbms_output.put_line( ' dropping ' || tab_rec.OBJECT_TYPE || ' ' || TAB_REC. OBJECT_NAME );
18 begin
19 execute immediate ' drop ' || TAB_REC.OBJECT_TYPE || ' ' || tab_rec. OBJECT_NAME ;
20 exception
21 when others then
22 errorNums : = errorNums + 1 ;
23 dbms_output.put_line( ' dropping err. ' );
24 dbms_output.put_line(sqlerrm);
25 end ;
26 end loop;
27 if errorNums > 0 then
28 dbms_output.put_line( ' Finished with error. Please execute again. ' );
29 else
30 dbms_output.put_line( ' Finished ' );
31 end if ;
32 end ;
执行上面的语句一定要小心,因为它是不可恢复的.
第二,如果发现执行中有一些问题,某些对象不能被删除掉,那可能是对象之间有一种依赖关系,重新执行一遍
2.导入
Exp <username>[/<password>][@<connect_string>] FromUser=<user_name> ToUser=<user_name> File=<fileName.dmp>