Oracle Import and Export Database syntax

Introduction: oracleIn addition to using graphical methods in the Oracle EditorImport and ExportIn addition to the data mode of Arts and Sciences files, you can also use command lines to import and export databases.SyntaxExample:

Imp [username [/password [@ service]

Step: 1. First, go to dos and switch to the directory where the physical file of the oracle database is located (the directory where the dmp file is located ). Enter the following statement!

Imp username/password @ service file = psmis. dmp fromuser = psmis touser = psmis log = psmis. log the statement syntax is explained below:

Imp: the syntax prefix of the imported database;

Username/password @ service: Indicates logging on to the specified service;

File: refers to the physical files of the oracle database that have been separated;

Fromuser: refers to the source user;

Touser: refers to the target user;

Log: refers to the log files in the current directory;

View imp and exp commands in oracle in dos:

Imp help = y

Exp help = y

1. Export table data:

The file name 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 exported file does not need to be compressed into a data block. When the table data in the database is very large, if it is stored in different databases, there will be problems during recovery.

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

2. Import table data

Table import process: Create a table, import data, and create a sequence

Echo backup file: $1

FileName = $1

Echo $ fileName

# If ["$ fileName" = ""]

# Then

# Echo "Please specify the Backup recovery file name, please specify the 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, but the data in the table is imported into the table.

Part 2:

How to import dmp database files in oracle?

2006/2/16 zhanghua first create

Importing and exporting Oracle Data imp/exp is equivalent to restoring and backing up oracle data. The exp command can export data from the remote database server to the local dmp file, and the imp command can import the dmp file from the local to the distant database server. This function can be used to build two identical databases, one for testing and the other for formal use.

Execution environment: it can be executed in SQLPLUS. EXE or DOS (command line,

When DOS can be executed, the \ ora81 \ BIN installation directory in oracle 8i is set to a global path,

The EXP. EXE and IMP. EXE files in this directory are used for import and export.

Oracle is written in java. SQLPLUS. EXE, EXP. EXE, and IMP. EXE files may be packaged class files.

SQLPLUS. EXE calls the classes encapsulated by EXP. EXE and IMP. EXE to complete the Import and Export function.

The following describes the Import and Export instances.

Data export:

1. Export the database TEST completely, and the username system Password manager is exported to D: \ daochu. dmp.

Exp system/manager @ TESTfile = d: \ daochu. dmp full = y

2. Export the tables of system users and sys users in the database

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

3. Export the inner_policy and policy_staff_relat tables in the database.

Exp aichannel/aichannel @ TESTDB2file = d: \ data \ newsmgnt. dmp tables = (inner_policy, policy_staff_relat)

4. Export the data with the field filed1 in table 1 in the database starting with "00"

Exp system/manager @ TESTfile = d: \ daochu. dmp tables = (table1) query = \ "where filed1 like '201312 '\"

The above is a commonly used export. For compression, you can use winzip to compress the dmp file.

You can also add compress = y to the command above.

Data Import

1. import data from D: \ daochu. dmp to the TEST database.

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

Imp aichannel/aichannel @ HUSTfull = y file = d: \ data \ newsmgnt. dmp ignore = y

The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.

Add ignore = y to the end.

2. Import table 1 in d: \ daochu. dmp

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

The preceding import and export operations are sufficient. In many cases, you must first completely delete the table and then import it.

Note:

If the operator has sufficient permissions, a prompt is displayed.

Databases can be connected. You can use tnsping TEST to obtain whether the database TEST can be connected.

The syntax for importing and exporting a database using CMDL is all described here. I hope you can easily solve the Oracle import and export problems in the future after learning this article, hope to help you.