Oracle Database Import Export exp (EXPDP) imp (IMPDP)

Import and export of Oracle databases

First, it was embarrassing again.

In fact, there are two types of Oracle import and Export commands

That

EXP (EXPDP) imp (IMPDP)

First of all, to popularize common sense:

In the normal storage and database migration, when encountering a large database when using exp often takes a few hours, time consuming. oracle10g later can use EXPDP to export the database to spend much less time than Exp spent, and the file is much smaller.

1. Use EXPDP to first create the directory in the database and give the appropriate user Read,write permissions.

Sql>create directory Dmpdir as '/opt/mydbbackup ';

Sql>grant read,write on directory to test;

2. Common Export methods

$EXPDP Scott/tiger dumpfile=scott.dmp Directory=dmpdir schemas=test

$EXPDP Scott/tiger dumpfile=tmp_dump.dmp directory=dmpdir tables= (tmp_test:p1,tmp_test:p2) JOB_NAME=tmp_dump LOGFILE =tmp_dump.log

$EXPDP scott/tiger dumpfile=full.dmp directory=dmpdir full=y job_name=full

$EXPDP scott/timer dumpfile=tmp_200703.dmp directory=dmpdir tables=tmp query=\ "where to_char\ (create_time,\ ') Yyyy-mm-dd\ ' \) \<\ ' 2007-04\ ' \ "

Some common parameter description:

Scott/tiger: User/password (*)

DumpFile: Exported file name (*)

DIRECTORY: Export file location (on server side) (*)

Content: What is included in the export file (default: All, optional data_only/metadata_only)

FILESIZE: Specifies the size of the export file (in bytes).

Job_name: The name used by this export process to facilitate tracking of queries (optional)

LOGFILE: Log file name (default: Export.log)

Include: Contains the specified type when exporting

(Example: Include=table_data,

Include=table: "Like ' tab% '"

Include=table: "Not like ' tab% '" ...)

EXCLUDE: Data types excluded from export (example: exclude=table:emp)

Full: Used when the whole library is exported (same as exp, default = N)

Schema: Export all data under a schema

TABLES: Export by Table (the method here is the same as exp)

Tablespace: Specifies a table space export.

Query: When exporting by table, use conditional statements to qualify the export range (same as query in exp)

Transport_full_check:

Transport_tablespaces:

FLASHBACK_SCN:

Flashback_time:

PARALLEL: Parallel operation

Parfile:

Network_link:

When Data Pump Export (DPE) is running, press control-c; it will prevent the message from appearing on the screen, but the export process itself is not stopped. Instead, it displays the DPE prompt (shown below). The process is now considered to be in "interactive" mode:

Export>

This approach allows you to enter command queries and control the current job on this DPE job.

Data export:

1 The database test is fully exported, user Name System Password Manager exported to d:daochu.dmp exp System/[email protected] file=d:daochu.dmp full=y

2 Exporting the system user in the database with the SYS user's table exp system/[email protected] file=d:daochu.dmp owner= (System,sys)

3 Inner_notify The tables in the database, Notify_staff_relat export exp aichannel/[email protected] file= d:datanewsmgnt.dmp tables= (inner_not Ify,notify_staff_relat)

4 Export the field in table Table1 in the database filed1 the data beginning with "00" exp system/[email protected] file=d:daochu.dmp tables= (table1) query= "where Filed1 like ' 0% ' "above is commonly used for the export, for compression, both with WinZip to the DMP file can be very good compression. It can also be implemented by adding compress=y to the above command.

Import of data:

1 Import the data from the D:DAOCHU.DMP into the test database. Imp system/[email protected] file=d:daochu.dmp imp aichannel/[email protected] full=y file=d:datanewsmgnt.dmp ignore=y on The face may be a bit problematic, because some tables already exist, and then it is an error, and the table is not imported. Add Ignore=y to the back.

The way you export it is imported in a corresponding way, otherwise it will be wrong.

This article is from the "Isuncle Technology blog" blog, make sure to keep this source http://baoer7758.blog.51cto.com/8187563/1621501

Oracle Database Import Export exp (EXPDP) imp (IMPDP)