Syntax for Oracle Import Export Database

Introduction: Oracle In addition to the graphical way in the Oracle editor to import the export of arts and Sciences file data, you can also import the export database in a command-line manner, and its syntax is as follows:

IMP [username[/password[@service]]]

Step: 1, first in the DOS, and then switch to the Oracle database physical files in the directory that is (DMP file is located in the directory). Next, enter the following statement!

Imp username/password@service file=psmis.dmp fromuser=psmis touser=psmis Log=psmis.log below explains the syntax of the statement:

IMP: is the syntax prefix for importing the database;

Username/password@service: It means landing the specified service;

File: Refers to an Oracle database physical file that has been detached;

Fromuser: Refers to the source user;

Touser: Refers to the target user;

LOG: Refers to the current directory of logs file;

View the IMP,EXP command in Oracle in DOS:

Imp help=y

Exp help=y

1. Export Table data:

The filename is represented by backup_yymmddhhmm.dmp

datestring= ' Date +%y%m%d%h%m '

#echo $dateString

SU-ORACLE-C "Exp Userid/password tables=table1,table2 file= path/backup_$datestring.dmp BUFFER=8192 grants=y compress= N

Compress:n indicates that the file is not compressed in a block of data, when the table data in the database is particularly large, if placed in a different database, there will be problems in the recovery.

The exported data includes table creation statements, sequences, triggers, and so on.

2. Import Table Data

Table Import Process: Create a table, import data, create a sequence

Echo Backup file:$1

Filename=$1

Echo $fileName

#if ["$fileName" = "]

#then

# echo Please specify a file name for backup recovery, specify an absolute file path

# exit 0;

#fi

#echo filename= $fileName

Su-oracle-c "Imp userid/password file= $fileName show=n buffer=2048000 ignore=y commit=y grants=y full=y"

Ignore:y indicates that the table creation process is ignored, and only the data in the table is imported into the table.

Part II:

How do I import DMP database files in Oracle?

2006/2/16 Zhanghua-A-create

Oracle Data Import 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 IMP commands the DMP file to be imported locally into 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 performed because the install directory Ora81bin in Oracle 8i is set to the global path,

There are EXP.EXE and IMP.EXE files under this directory that are used to perform import export.

Oracle is written in Java, Sqlplus. EXE, EXP. EXE, IMP. EXE these two files may be packaged after the class file.

Sqlplus. EXE call EXP.EXE, IMP. EXE package, complete the import and export function.

The following is an example of an import export.

Data export:

1 completely export database test, user Name System Password Manager exported to D:daochu.dmp

Exp SYSTEM/MANAGER@TESTFILE=D:DAOCHU.DMP full=y

2 Exporting a table from the system user in the database to the SYS user

Exp system/manager@testfile=d:daochu.dmp owner= (System,sys)

3 Export the table inner_notify, Notify_staff_relat in the database

Exp aichannel/aichannel@testdb2file= d:data ewsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

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

Exp system/manager@testfile=d:daochu.dmp tables= (table1) query= "where filed1 like ' 0% '"

The above is commonly used for export, for compression, both with WinZip to DMP file can be very good compression.

You can also implement it by adding compress=y to the command below.

Import of data

1 Import the data from D:DAOCHU.DMP into the test database.

Imp system/manager@test file=d:daochu.dmp

Imp aichannel/aichannel@hustfull=y file=file= d:data ewsmgnt.dmp ignore=y

There may be something wrong with it, because some tables already exist, and then it complains, and the table is not imported.

Just add ignore=y to the back.

2 Import the table table1 in D:daochu.dmp

Imp system/manager@test file=d:daochu.dmp tables= (table1)

Basically, the import export above is sufficient. In many cases, you should first delete the table completely and then import it.

Attention:

The operator must have sufficient permissions, and the permission is not sufficient for it to prompt.

Database can be connected to. You can use tnsping test to get the database test to connect.

Here about ORACL Import and export database of the syntax of all introduced, I hope that after the study of this article, and then encounter related to the issue of Oracle Import and export can be easily resolved, I hope to be able to help everyone.