Oracle Data Import Export command

IMP and the EXP Command

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 System/[email protected]TEST file=d:\daochu.dmp tables= (table1, table2,table3 ... )

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= 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, the table must be completely removed and then imported.

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.

First establish the Import9.par, 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

An example of a user of a database that guides all the tables of a user to another database

Exp Userid=system/manager owner=username1 File=expfile.dmp

Imp Userid=system/manager fromuser=username1 touser=username2

ignore=y file=expfile.dmp ;

There are two types of backup methods for Oracle databases. The first class is a physical backup, which implements a full database recovery, but the database must be run in the back-up mode (the business database runs in non-return mode) and requires a great deal of external storage devices such as a tape library, a second type of backup as a logical backup, a business database in this way, This method does not require that the database be run in the back-up mode, it is simple to backup, and can not require an external storage device.

Database Logical Backup method

The logical backup of an Oracle database is divided into three modes: Table backup, user backup, and full backup.

Table mode

Backs up the specified object (table) in a user mode. The business database typically uses this backup method.

If you are backing up to a local file, use the following command:

Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536

feedback=100000 volsize=0

File=exp_icdmain_csd_yyyymmdd.dmp

Log=exp_icdmain_csd_yyyymmdd.log

Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo

If you are backing up to a tape device directly, use the following command:

Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536

feedback=100000 volsize=0

File=/dev/rmt0

Log=exp_icdmain_csd_yyyymmdd.log

Tables=icdmain.commoninformation,icdmain.serviceinfo,icdmain.dealinfo

Note: In case of disk space permitting, you should back up to the local server before copying to tape. For speed reasons, try not to back up directly to the tape device.

User mode

Backs up all objects in a user mode. The business database typically uses this backup method.

If you are backing up to a local file, use the following command:

Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536

feedback=100000 volsize=0

File=exp_icdmain_yyyymmdd.dmp

Log=exp_icdmain_yyyymmdd.log

If you are backing up to a tape device directly, use the following command:

Exp ICDMAIN/ICD owner=icdmain rows=y indexes=n compress=n buffer=65536

feedback=100000 volsize=0

File=/dev/rmt0

Log=exp_icdmain_yyyymmdd.log

Note: If the disk has space, it is recommended that you back up to disk and then copy to tape. If the amount of database data is small, this method can be used for backup.

Full mode

Back up the full database. The business database does not use this backup method. The backup command is:

Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536

feedback=100000 volsize=0 full=y

FILE=EXP_FULLDB_YYYYMMDD.DMP (tape device is/dev/rmt0)

Log=exp_fulldb_yyyymmdd.log

For database backups, it is recommended that you take an incremental backup that backs up only the data that has changed since the last backup. Incremental backup

Command:

Exp ICDMAIN/ICD rows=y indexes=n compress=n buffer=65536

feedback=100000 volsize=0 full=y Inctype=incremental

FILE=EXP_FULLDB_YYYYMMDD.DMP (tape device is/dev/rmt0)

Log=exp_fulldb_yyyymmdd.log

Note: For incremental backups, the following conditions must be met:

1. Only valid for full database backups, and the first time you need to full=y parameters, you need to inctype=incremental parameters later.

2. The user must have a exp_full_database system role.

3. Database backup can be used when the traffic volume is small.

4. If the disk has space, it is recommended that you back up to disk, and then back up to tape.

Service database backup method and cycle

Before backing up with Exp, run the Catexp.sql file under the SYS user (do not execute this script if the file has been previously run).

No special instructions are allowed to perform backup commands on the client.

The backup command refers to the backup command in table mode.

Backup from disk files to tape

If you first back up to a local disk file, you need to dump it on the tape device.

1. To view the tape devices configured on the host, use the following command:

LSDEV-CC Tape

The results shown are shown in the following example:

Rmt0 Available 30-58-00-2,0 SCSI 4mm Tape Drive

RMT1 Defined 30-58-00-0,0 SCSI 4mm Tape Drive

The device marked available is a tape device that is available.

2. To view the contents of the tape storage, use the following command:

Tar-tvf/dev/rmt0

The results shown are shown in the following example:

-rw-r--r--8089600 Jan 14:33:57 2001 EXP_ICDMAIN_20010111.DMP

If the display resembles the following, the backup data for that tape store is backed up from the database directly to tape, not the backup file from the local disk to the tape, so the operating system is not recognized.

tar:0511-193 An error occurred while reading from the media.

There is an input or output error.

Or

tar:0511-169 A Directory checksum error on media; -267331077 equal to 25626.

3. For new tapes or tapes that do not need to retain existing data, use the following command:

Tar-cvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp

Note: A. This command will unconditionally overwrite the existing data on the tape.

B. File names do not allow path information, such as:/backup/exp_icdmain_yyyymmdd.dmp.

4. For tapes that need to retain existing data, use the following command:

Tar-rvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp

Note: This command appends the file exp_icdmain_yyyymmdd.dmp to the end of the tape and does not overwrite the existing data.

In particular, if the backup is backed up directly from the database to tape, no additional files can be copied to the tape or the backup data will be invalidated.

5. If you want to copy the backup files to the local hard disk, use the following command:

A. Copy all the files on the tape to the current directory on your local hard disk

Tar-xvf/dev/rmt0

B. Copy the specified file on the tape to the current directory on the local hard disk

Tar-xvf/dev/rmt0 exp_icdmain_yyyymmdd.dmp

Backup Schedule

Because of the large impact on system I/O during backup, it is recommended that you perform backup work after 11 o'clock in the evening.

Recovery of the Oracle version of the business database, the recovery scenario is determined based on the backup scenario. Due to the combination of table backup and user backup in the business database, the recovery of the business database needs to be based on the actual scenario of table recovery and user recovery.

Recovery scenarios

The logical recovery of the database is divided into three modes: Table recovery, user recovery and full recovery.

Table mode

This method restores the data backed up according to the table schema.

A. Restoring the full contents of the backup data

If recovering from a local file, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0

File=exp_icdmain_csd_yyyymmdd.dmp

Log=imp_icdmain_csd_yyyymmdd.log

If recovering from a tape device, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=/dev/rmt0

Log=imp_icdmain_csd_yyyymmdd.log

B. Recovering a specified table from backup data

If recovering from a local file, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0

File=exp_icdmain_cs

D_yyyymmdd.dmp

Log=imp_icdmain_csd_yyyymmdd.log

Tables=commoninformation,serviceinfo

If recovering from a tape device, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0

File=/dev/rmt0

Log=imp_icdmain_csd_yyyymmdd.log

Tables=commoninformation,serviceinfo

User mode

This method will be restored based on data backed up in user mode.

A. Restoring the full contents of the backup data

If recovering from a local file, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0

File=exp_icdmain_yyyymmdd.dmp

Log=imp_icdmain_yyyymmdd.log

If recovering from a tape device, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=/dev/rmt0

Log=imp_icdmain_yyyymmdd.log

B. Recovering a specified table from backup data

If recovering from a local file, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0

File=exp_icdmain_yyyymmdd.dmp

Log=imp_icdmain_yyyymmdd.log

Tables=commoninformation,serviceinfo

If recovering from a tape device, use the following command:

Imp Icdmain/icd fromuser=icdmain touser=icdmain rows=y indexes=n

Commit=y buffer=65536 feedback=100000 ignore=n volsize=0 file=/dev/rmt0

Log=imp_icdmain_yyyymmdd.log

Tables=commoninformation,serviceinfo

Full mode

If the backup method is full mode, the following recovery methods are used:

If recovering from a local file, use the following command:

Imp system/manager rows=y indexes=n commit=y buffer=65536

feedback=100000 ignore=y volsize=0 full=y

File=exp_icdmain_yyyymmdd.dmp

Log=imp_icdmain_yyyymmdd.log

If recovering from a tape device, use the following command:

Imp system/manager rows=y indexes=n commit=y buffer=65536

feedback=100000 ignore=y volsize=0 full=y

File=/dev/rmt0

Log=imp_icdmain_yyyymmdd.log

Parameter description

1. Ignore parameters

When Oracle recovers data, when a table is restored, the table already exists and depends on the setting of the ignore parameter to determine what to do.

If Ignore=y,oracle does not execute the CREATE TABLE statement, inserts the data directly into the table, and if the inserted record violates the constraint, such as a primary key constraint, the record of the error is not inserted, but the valid record is added to the table.

If Ignore=n,oracle does not execute the CREATE TABLE statement and does not insert the data into the table, it ignores the table error and resumes the next table.

2. Indexes parameters

In the process of recovering data, the index on the table will not be restored, but the unique index corresponding to the primary key will be restored unconditionally, in order to ensure the integrity of the data indexes=n.

Character Set conversions

For a single-byte character set (for example, Us7ascii), when recovering, the database is automatically converted to the session's character set (Nls_lang parameter), and for multibyte character sets (for example, zhs16cgb231280), the character set should be the same as possible (avoid conversions), if you want to convert, The character set of the target database should be a superset of the output database character set.

Recovery method

The business database uses a table recovery scheme. Before recovering with IMP, run the Catexp.sql file under the SYS user (if the file was previously run, do not execute the script), and then execute the following command:

IMP icdmain/icd file= file name log=log file name rows=y

Commit=y buffer=y ignore=y tables= table name

Note: The table name to be restored references the table name of the backup

Recovery is the accumulation of data on the basis of the original table

No special instructions, do not allow recovery command on client

For more information, please login: online, www.xconnet.com

Oracle Data Import Export command