Oracle Database 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, andtheimp command can take DMP the files are imported locally into a remote database server. With this feature we can export the database from the production library and then import the database into the test library.

Execution environment: can be in SQLPLUS. EXE or dos(command line),dos can be performed because in Oracle , the installation directory \ora9i\bin is set to the global path (can also be set directly in the system environment variable), the directory has EXP. EXE with IMP. EXE file is used to perform import and export.

The following is an example of importing and exporting.

Data export:

1 database zxcc fully exported, user name KF password ZX exported to D:\zxcc.dmp in

Exp Kf/[email protected] file=d:\zxcc.dmp full=y

Full=y represents a full-library export. full there are 2 optional Yes (y)/no (n), by default full=no, Only the objects under that user will be exported.

2 Export the tables of KF users and cc users in database zxcc

Exp Kf/[email protected] file=d:\zxcc_ur.dmp owner= (KF,CC)

Full mode can back up all the user's database objects, including tablespace, user information, etc.,owner=xx can only back up the object of the specified user, the other user does not back up,EXP full=y and owner=xx cannot be used at the same time.

3 Export the table kf_operator,kf_role in the database zxcc

Exp Kf/[email protected] file= d:\zxcc_tb.dmp tables= (kf_operator,kf_role)

Tables=xx indicates that the related tables are backed up and cannot be used with both owner and full .

4 Export the field in the table kf_operator in the database oper_id The data that begins with "XX"

Exp Kf/[email protected] file=d:\zxcc_t.dmp tables= (kf_operator) query=\ "where oper_id like ' 0% ' \"

Query is the main export of the appropriate conditions of data. 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 time-consuming and error-prone if you encounter more onerous statements. We can use the exp or expdp parfile parameter to avoid The tedious escaping of 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 ')"

At this point, you can enjoy the double quotation marks to write the conditional statement.

The above is commonly used for export, for the larger database, we can compress the export file, using WinZip to compress the dmp file.

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

Import of data:

1 . Import the data from the D:\zxcc.dmp into the zxcc database.

Imp Kf/[email protected] File=d:\zxcc.dmp

When the data is available, there may be an error. Why? There are the following main reasons:

　　A. imported objects (tables, views, methods, and so on) that are not originally part of the current connected user

　　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

Here are the three ways to handle these problems:

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

Imp kf_new/[email protected]_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 object as an imported Owner.

b, ignore / Insert data:

　　imp kf_new/[email protected]_new file= d:\zxcc.dmp ignore=y

where ignore=y tells imp.exe to insert 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 . Import The table tb_operator in d:\zxcc_tb.dmp

Imp kf/[email protected] file=d:\zxcc_tb.dmp tables= (tb_operator)

Ignore load constraints

Sometimes when the data comes in, we do not need to put its constraints, such as some foreign key constraints, etc., can be combined with parameters Constraints=n

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

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

For the above operation logged in the user needs to be an administrator, if not an administrator, but a normal user, then this user must have the right to create deleted objects, objects may include tables, views, methods, stored procedures and so on common objects. Why is "possible" included? This is dependent on whether the import and export is related to objects of the relevant type.

IMP kf/[email protected]_new file=d:\zxcc.dmp fromuser=kf touser=kf_new ignore=y

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

Attention:

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

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

(3), when importing / Exporting the database, note the character set. Errors may occur when the database character set is inconsistent during export/import.

Other common commands for Oracle databases:

1, to the user to increase the import data permissions Operation

First, start sql*puls

Second, log in as an administrator (DBA) User

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_ful L_database, DBA, Resource, create session to user name ;

Fifth , run cmd into the directory where the DMP file is located ,

IMP userid= Administrator user name / password full=y file= filename.dmp

or imp userid= Administrator user name / password full=y file=filename.dmp

2,Oracle does not allow direct changes to the table owner , using export/import can achieve this purpose .

First create a . Par file ()

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

Example import9.par content is as follows:

Fromuser=user

Touser=user_new (Note: Change the owner of the table from fromuser to touser,fromuser and touser users can be different)

Rows=y

Indexes=y

Grants=y

Constraints=y

buffer=409600

File==/filepath/xxxx.dmp

Log==/filepath/import_log.log

Oracle Database Import and Export