Oracle Export Import data

The recent use of Oracle Export Import data, read some other people's articles on the Internet, summarized as follows:

There are two ways to export and export the Oracle: first, import with exp imp, second, export import using oracel data pump EXPDP IMPDP.

First, using EXP IMP Export Import

The EXP IMP syntax is as follows:

Exp:

1) fully export the database ORCL

Exp System/[email protected] file=d:\orcl_bak.dmp full=y

2) Export the table of the system user in the database

Exp System/[email protected] file=d:\system_bak.dmp Owner=system

3) Export the table Table1,table2 in the database

Exp System/[email protected] file=d:\table_bak.dmp tables= (table1,table2)

4) Export the data from the table in the database to the field in customer, mobile with the start of "139"

Exp System/[email protected] file=d:\mobile_bak.dmp tables=customer query=\ "where mobile like ' 139% ' \"

Imp

1) Export the backup file bak.dmp the database

Imp System/[email protected] File=d:\bak.dmp

If the table already exists in the datasheet, it will prompt an error, and add Ignore=y to it later.

2) Import the table table1 in the backup file Bak.dmp

Imp system/[email protected] file=d:\bak.dmp tables= (table1)

Exp IMP the benefit of exporting imported data is that you can export the data from the server to your local computer as long as you have the Oracle client installed locally. You can also import DMP files from your local to the server database. However, there is a problem with this approach in the ORACLE11G version: You cannot export empty tables. ORACLE11G adds a new parameter deferred_segment_creation, meaning that segment delay is created, which is true by default. When you create a new table and do not use it to insert data into it, the table does not immediately allocate segment.

Workaround:

1, set the Deferred_segment_creation parameter to false, whether it is an empty table, or a non-empty table, are allocated segment.

In Sqlplus, execute the following command:

Sql>alter system set Deferred_segment_creation=false;

View:

Sql>show parameter deferred_segment_creation;

When this value is set, it only works on the tables that are added later, does not work on the empty table that was previously established, and you are aware that you want to restart the database for the parameters to take effect.

2. Use ALLOCATE Exten

You can assign extent to database objects using ALLOCATE Exten, with the following syntax:

ALTER TABLE TABLE_NAME ALLOCATE extent

To build an SQL command that allocates space to empty tables:

　　

Sql>select ' ALTER TABLE ' | | table_name| | ' allocate extent; ' from User_tables where num_rows=0

Bulk generate the statements to be modified.

These modification statements are then executed to allocate space for all empty tables.

With the exp command, all tables, including empty tables, can be exported.

　　

Second, using EXPDP IMPDP Export Import

In ORACLE10G exp IMP was redesigned to Oracle Data Pump (original exp IMP tool retained)

　　

The difference between data pump and traditional export import;

1) Exp and IMP are client tools that can be used either on the client or on the server side.

2) EXPDP and IMPDP are service-side tools that can only be used on Oracle server.

3) Imp only applies to exp export file, IMPDP only applies to EXPDP export file.

　　

EXPDP Export Data:

1. Set up a database directory object for the output path.

Create or replace directory Dumpdir as ' d:\ ';

Available by: SELECT * from Dba_directories; view.

2. Grant access to the user who will be exporting the data.

Grant Read,write on directory Dumpdir to TEST_EXPDP;

3. Export the data

EXPDP TEST_EXPDP/TEST_EXPDP directory=dumpdir dumpfile=test_expdp_bak.dmp logfile=test_expdp_bak.log schemas=test_ Expdp

Note: This sentence runs in the cmd window and does not end with a semicolon, otherwise it will prompt the error. Because this is the operating system command, not SQL.

IMPDP Import Data:

1. Grant access to the user who will be importing the data.

Grant Read,write on directory Dumpdir to TEST_IMPDP;

2. Import the data

IMPDP TEST_IMPDP/IMPDP Directory=dumpdir dumpfile=test_expdp_bak.dmp REMAP_SCHEMA=TEST_EXPDP:TEST_IMPDP

Finally: These are the content I found when looking for information, because of the relatively messy, now a little collation, convenient for later review. At the same time, it would be nice if we could give some help to those in need.

Oracle Export Import data