As our developers, exp is often used to export all objects of a user from a database,
Then Import. During the import process, you will always encounter unexpected problems. One of the problems is that the database already has an object with the same name,
It also worries about complicated parameters. As a matter of fact, you only need to perform the following three actions to smoothly complete the import and export work:
1. Export
Exp <username> [/<password>] [@ <connect_string>] Owner = <user_name> File = <filename. dmp>
2. Drop all objects under the target user
Execute the following statement:
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 'processure', '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;
32end;
Be careful when executing the preceding statement because it cannot be recovered.
Second, if there are some problems in the execution and some objects cannot be deleted, it may be that there is a dependency between the objects and re-execute them.
2. Import
Exp <username> [/<password>] [@ <connect_string>] FromUser = <user_name> ToUser = <user_name> File = <fileName. dmp>