Common Oracle import/export commands

Source: Internet
Author: User

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>

 

 

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.