Oracle Data Import and Export

Oracle data import and export imp/exp is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and the IMP command can import the DMP file from a local to a distant database server. With this feature you can build two identical databases, one for testing and one for formal use.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line), DOS can be executed because the installation directory in Oracle 8i \ora81\bin is set to the global path, the directory has EXP.EXE and IMP.EXE files are used to perform import and export. Oracle is written in Java, SQLPLUS. EXE, EXP. EXE, IMP. EXE these two files are likely to be packaged after the class file.

SQLPLUS. EXE calls EXP.EXE, IMP. EXE to complete the import and export function. The following is an example of importing and exporting.

Data export:

1 full export of database test, 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 to the SYS user's table

Exp System/[email protected] file=d:\daochu.dmp owner= (System,sys)

3 Exporting Tables Inner_notify, Notify_staff_relat in the database

Exp Aichannel/[email protected] file= d:\data\newsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

4 Export the field filed1 in table table1 in the database with data beginning with "00"

Exp System/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \" Above is a common export, for compression, Both the DMP files can be well compressed with WinZip.

It can also be implemented by adding compress=y to the above command.

Iv. Export of data

1) According to the user guide

EXPDP Scott/[email protected] Schemas=scott dumpfile=expdp.dmp directory=dpdata1;

2) Parallel Process parallel

EXPDP Scott/[email protected] directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=s

Cott3

3) According to the table name guide

EXPDP Scott/[email protected] tables=emp,dept dumpfile=expdp.dmp directory=dpdata1;

4) Guided by query criteria

EXPDP Scott/[email protected] directory=dpdata1 dumpfile=expdp.dmp tables=emp query= ' WHERE

Deptno=20 ';

5) According to the Table space Guide

EXPDP System/manager directory=dpdata1 dumpfile=tablespace.dmp tablespaces=temp,exa

Mple;

6) Guide the entire database

EXPDP System/manager directory=dpdata1 dumpfile=full.dmp full=y;

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=file= d:\data\newsmgnt.dmp ignore=y

There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.

Add Ignore=y to the back.

2 Import the table table1 in D:\daochu.dmp

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

Basically, the above import and export is enough. In many cases, you must first delete the table and then import it. Attention:

The operator must have sufficient permissions, and it will prompt for insufficient permissions.

Database, you can connect to the. You can use tnsping test to get the database test to connect.

Appendix I:

Adding permissions to the import data to the user

First, start sql*puls

Second, landing with System/manager

Third, create user username identified by password (this step can be omitted if the user has already been created)

IV, GRANT create User,drop user,alter USER, create any VIEW,

DROP any view,exp_full_database,imp_full_database,

Dba,connect,resource,create SESSION to User name

V, run-cmd-into the directory where the DMP file is located,

Imp Userid=system/manager full=y file=*.dmp

or imp Userid=system/manager full=y file=filename.dmp

Example execution:

F:\work\oracle_data\backup>imp userid=test/test full=y file=inner_notify.dmp

Screen display

Import:release 8.1.7.0.0-production on Thu February 16 16:50:05 2006

(c) Copyright: Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0-production

With the partitioning option

Jserver Release 8.1.7.0.0-production

Export files created by export:v08.01.07 through a regular path

Import is completed in ZHS16GBK character set and ZHS16GBK NCHAR character set

The export server uses the UTF8 NCHAR character set (possible ncharset conversions)

. Importing Aichannel objects into Aichannel

. . Importing table "Inner_notify" 4 rows are imported

Prepare to enable constraints ...

The import was terminated successfully, but a warning appears.

Appendix II:

Oracle does not allow direct changes to the table owner, which can be achieved with export/import.

Build Import9.par First,

Then, use the command as follows: Imp Parfile=/filepath/import9.par

Example Import9.par content is as follows:

Fromuser=tgpms

TOUSER=TGPMS2 (Note: Users who change the owner of the table from Fromuser to Touser,fromuser and Touser can be different)

Rows=y

Indexes=y

Grants=y

Constraints=y

buffer=409600

File==/backup/ctgpc_20030623.dmp

Log==/backup/import_20030623.log

To export a table:

Exp Scott/[email protected] tables= (dept,emp) file=tab1.dmp

Export Users:

Exp System/[email protected] Owner=scott file=usr1.dmp

To export a database:

1. Full Export

Exp System/[email protected] full=y inctype=complete file=full1.dmp

2. Incremental Export

Exp System/[email protected] full=y inctype=incremental file=inc1.dmp

3. Cumulative Export

Exp System/[email protected] full=y inctype=cumulative file=cum1.dmp

IMP Example:

Import Table:

Imp system/[email protected] file=c:\tab1.dmp tables= (dept,emp) Touser=scott

To import Users:

Imp system/[email protected] file=usr1.dmp Fromuser=scott Touser=scott

Import Database:

1. Full Library Import

Imp system/[email protected] file=full1.dmp full=y

2. Incremental Import

1) Import database up-to-date information

Imp system/[email protected] Inctype=system full=y file=inc7.dmp

2) Import the most recent full export file

Imp system/[email protected] Inctype=restore full=y file=full1.dmp

3) Import all the cumulative export files

Imp system/[email protected] Inctype=restore full=y file=cum1.dmp

4) Import files for the most recent incremental export

Imp system/[email protected] Inctype=restore full=y file=inc1.dmp

Oracle data import and export imp/exp is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and the IMP command can import the DMP file from a local to a distant database server. With this feature you can build two identical databases, one for testing, and one for formal use ...

Oracle data import and export imp/exp is equivalent to Oracle data restore and backup. The EXP command can export data from a remote database server to a local DMP file, and the IMP command can import the DMP file from a local to a distant database server. With this feature you can build two identical databases, one for testing and one for formal use.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line), DOS can be executed because the installation directory in Oracle 8i \ora81\bin is set to the global path, the directory has EXP.EXE and IMP.EXE files are used to perform import and export.

Oracle is written in Java, SQLPLUS. EXE, EXP. EXE, IMP. EXE these two files are likely to be packaged after the class file.

SQLPLUS. EXE calls EXP.EXE, IMP. EXE to complete the import and export function.

Format:

IMP username/[email protected]_string Param=value ...

Exp Username/[email protected]_string Param=value ...

Note: If it is under the Sqlplus command, to add $ before the IMP or EXP command, the error will be: sp2-0734: Unknown command at the beginning "Exp Pafirc ...", if it is under the CMD Command window (if it is logged in with a sqlplus "username/password", Equivalent to the Sqlplus command window), it is not possible.

The following is an example of importing and exporting.

Data export:

1 full export of database test, 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 to the SYS user's table

Exp System/[email protected] file=d:\daochu.dmp owner= (System,sys)

3 Exporting Tables Inner_notify, Notify_staff_relat in the database

Exp Aichannel/[email protected] file= d:\data\newsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

4 Export the field filed1 in table table1 in the database with data beginning with "00"

Exp System/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \"

The 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=file= d:\data\newsmgnt.dmp ignore=y

There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.

Add Ignore=y to the back.

2 Import the table table1 in D:\daochu.dmp

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

Basically, the above import and export is enough. In many cases, you must first delete the table and then import it.

Attention:

The operator must have sufficient permissions, and it will prompt for insufficient permissions.

Database, you can connect to the. You can use tnsping test to get the database test to connect.

Appendix I:

Adding permissions to the import data to the user

First, start sql*puls

Second, landing with System/manager

Third, create user username identified by password (this step can be omitted if the user has already been created)

IV, GRANT create User,drop user,alter USER, create any VIEW,

DROP any view,exp_full_database,imp_full_database,

Dba,connect,resource,create SESSION to User name

V, run-cmd-into the directory where the DMP file is located,

Imp Userid=system/manager full=y file=*.dmp

or imp Userid=system/manager full=y file=filename.dmp

Example execution:

F:\work\oracle_data\backup>imp userid=test/test full=y file=inner_notify.dmp

Screen display

Import:release 8.1.7.0.0-production on Thu February 16 16:50:05 2006

(c) Copyright: Oracle Corporation. All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0-production

With the partitioning option

Jserver Release 8.1.7.0.0-production

Export files created by export:v08.01.07 through a regular path

Import is completed in ZHS16GBK character set and ZHS16GBK NCHAR character set

The export server uses the UTF8 NCHAR character set (possible ncharset conversions)

. Importing Aichannel objects into Aichannel

. . Importing table "Inner_notify" 4 rows are imported

Prepare to enable constraints ...

The import was terminated successfully, but a warning appears.

Appendix II:

Oracle does not allow direct changes to the table owner, which can be achieved with export/import.

Build Import9.par First,

Then, use the command as follows: Imp Parfile=/filepath/import9.par

Example Import9.par content is as follows:

Fromuser=tgpms

TOUSER=TGPMS2 (Note: Users who change the owner of the table from Fromuser to Touser,fromuser and Touser can be different)

Rows=y

Indexes=y

Grants=y

Constraints=y

buffer=409600

File==/backup/ctgpc_20030623.dmp

Log==/backup/import_20030623.log

This article is from the "Gentle Knife" blog, please be sure to keep this source http://wenrouyidao.blog.51cto.com/7368180/1610664

Oracle Data Import and Export