Oracle to import Export data by command under CMD ____oracle

1, first in the CMD switch to Oracle's client Exp.exe in the bin directory, for example

D:\oracle\product\10.2.0\client_2\BIN

Data export: The exported database name is defined in the Tnsnames.ora file

1 The database zxcc completely exported, user name KF password ZX exported to the D:\ZXCC.DMP

Exp KF/ZX@ZXCC file=d:\zxcc.dmp full=y

Full=y represents a whole-library export. Full total of 2 options Yes (y)/no (n), by default Full=no, will only export objects under that user.

2 Export the ZXCC of the database to KF users and CC users

Exp KF/ZX@ZXCC file=d:\zxcc_ur.dmp owner= (KF,CC)

Full way to back up all the user's database objects, including tablespace, user information, etc., OWNER=XX can only back up the specified user's object, other users do not back up, exp full=y and owner=xx can not be used at the same time.

3 Export the table Kf_operator, kf_role in the database zxcc

Exp KF/ZX@ZXCC file= d:\zxcc_tb.dmp tables= (kf_operator,kf_role)

TABLES=XX indicates that the related tables are backed up and cannot be used concurrently with owner or full.

4 Export the fields in the table Kf_operator in the database oper_id with the data beginning with "00"

Exp KF/ZX@ZXCC file=d:\zxcc_t.dmp tables= (kf_operator) query=\ "where oper_id like ' 0% '"

Query is primarily a data that exports the right conditions. When you use this parameter, you need to be aware that the escape symbol is used for all operating system reserved characters. If you have parentheses () you also need to escape:

Query=\ "where dt=to_date\ (\ ' 2007-09-22\ ', \ ' yyyy-mm-dd\ ' \) \".

Frequent escape operations are not only time-consuming, but also prone to error if you encounter more complicated statements. We can use exp or EXPDP parfile parameters to avoid cumbersome escape problems with query content.

Cases:

Oracle DBALNP01 > Cat > Zxcc.par

Tables=kf_operator

File=zxcc.dmp

Query= "where Dt_time=to_date (' 2010-06-25 ', ' yyyy-mm-dd ')"

Then you can indulge in double quotes to write conditional statements.

The above is the commonly used export, for the larger database, we can the export file compression processing, can be WinZip to DMP file compression.

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

Import of data:

1. Import the data from the D:\ZXCC.DMP into the ZXCC database.

Imp KF/ZX@ZXCC file=d:\zxcc.dmp

When data is available, it is possible to make an error. Why. The following are the main reasons:

A. Imported objects (tables, views, methods, etc.) that were not originally part of the current connected user's

B. The imported object already exists under the specified user of the database

C. The original user of the imported object is not in this database

The approach to these three issues is as follows:

A/C, all objects are all imported into the specified account:

Imp kf_new/zx@zxcc_new file=d:\zxcc.dmp FROMUSER=KF touser=kf_new

Where FROMUSER=KF is the original owner of the object in the. dmp file, Touser=kf_new is the new owner of the object being imported.

b, ignore/Insert data:

Imp kf_new/zx@zxcc_new file= d:\zxcc.dmp ignore=y

Where Ignore=y tells Imp.exe to insert the data directly into the object (and if there are other objects in the imported object, such as constraints, indexes, etc., will be created after the data is inserted).

2, the d:\zxcc_tb.dmp in the table Tb_operator Import

Imp kf/zx@zxcc file=d:\zxcc_tb.dmp tables= (tb_operator)

Ignore load Constraint

Sometimes the guide data comes in, we do not need to put its constraints, such as some foreign key constraints, etc. are guided in, you can add parameters constraints=n

Do not load indexes (such as uniqueness indexes), you can add parameters indexs=n

Load only the structure, do not load the data, if the table structure, such as definition (constraints, triggers), and not inside the data, you can add parameters rows=n

For the above operation of the user needs to be an administrator, if not administrators, but ordinary users, then this user must have the right to create deleted objects, objects may include tables, views, methods, stored procedures and other common objects. Why "possible" includes. This depends on whether or not the related type of object is involved when the import is exported.

IMP kf/zx@zxcc_new file=d:\zxcc.dmp fromuser=kf touser=kf_new ignore=y

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

Attention:

(1), the operator should have sufficient permissions, insufficient permissions will be prompted.

(2), the database link is normal, can use tnsping ZXCC to detect the database zxcc can connect.

(3), Import/Export database attention to the character set. There may be an error in the database character set inconsistency when exporting/importing.

Other common commands for Oracle databases: