The
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 performed in SQLPLUS.EXE or DOS (command line),
dos can be performed because the install directory Ora81bin is set to the global path in Oracle 8i,
There are EXP.EXE and IMP.EXE files under this directory that are used to perform import export. The
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 example of an import export is described below.
Data export:
code is as follows |
copy code |
1 Completely export database TEST, user Name System Password Manager exported to d:daochu.dmp exp system/manager@test file=d:daochu.dmp full=y 2 Exports the table of system users and SYS users in the database exp system/manager@test file=d:daochu.dmp owner= (System,sys) 3 The tables in the database inner_notify, Notify_staff_relat export exp AICHANNEL/AICHANNEL@TESTDB2 file= D: Datanewsmgnt.dmp tables= (Inner_notify,notify_staff_relat) 4 Export the fields in the table table1 in the database to the data that starts with "00" exp system/manager@test file=d:daochu.dmp tables= (table1) query= "where filed1 like ' 00% '" |
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.
The code is as follows |
Copy Code |
Imp system/manager@test file=d:daochu.dmp Imp aichannel/aichannel@test full=y file=d:datanewsmgnt.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
The code is as follows |
Copy Code |
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.
Appendix I:
Actions to increase user access to import data
The code is as follows |
Copy Code |
First, start sql*puls Second, to System/manager landing Third, create user username identified by password (if you have already created a user, this step can be omitted) Four, 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 resides,
The code is as follows |
Copy Code |
Imp Userid=system/manager full=y file=*.dmp or imp Userid=system/manager full=y file=filename.dmp |
To execute the example:
The code is as follows |
Copy Code |
F:workoracle_databackup>imp userid=test/test full=y file=inner_notify.dmp |
Screen display
The code is as follows |
Copy Code |
Import:release 8.1.7.0.0-production on Thursday February 16 16:50:05 2006 (c) Copyright to Oracle Corporation. All rights reserved. |
Connect to:
The code is as follows |
Copy Code |
Oracle8i Enterprise Edition Release 8.1.7.0.0-production With the partitioning option Jserver Release 8.1.7.0.0-production |
To export a file created by export:v08.01.07 through a regular path
Import in the ZHS16GBK character set and ZHS16GBK NCHAR character set has been completed
The export server uses the UTF8 NCHAR character set (possible ncharset conversions)
. Importing Aichannel objects to Aichannel
. . Importing table "Inner_notify" 4 rows are imported
Ready to enable constraints ...
The import was successfully terminated, but a warning appears.
Appendix II:
Oracle does not allow direct changes to the owner of the table, using Export/import to achieve this.
First set up Import9.par,
Then, when used, the commands are as follows: Imp Parfile=/filepath/import9.par
Examples of Import9.par are as follows:
The code is as follows |
Copy Code |
Fromuser=tgpms TOUSER=TGPMS2 (Note: The user who changes the table owner 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 |
Adding feedback=1000 to the Import Export command allows the process to display an increasing number of "..." to change past flashing cursors
New:
Exp/imp has been very useful, but the only certainty is that the speed is too slow, if 1 tables of data has a millions, often import exports for a long time to stop in this table, but from Oracle 10g to provide a new tool called the data pump EXPDP/IMPDP, It provides high speed parallelism and large data migration for Oracle data.
Imp/exp can be invoked on the client, but EXPDP/IMPDP can only be on the server side, because you need to create a directory in the database before using EXPDP/IMPDP
The code is as follows |
Copy Code |
Create directory Dump_test as '/u01/oracle10g '; Grant read, write on directory dump_test to Piner |
You can then start importing the export
code is as follows |
copy code |
EXPDP Piner /piner directory=dump_test dumpfile=user.dmp Export user's data EXPDP piner/piner directory=dump_test dumpfile= TABLE.DMP tables=test1,test2 Export Table data IMPDP piner/piner directory=dump_test dumpfile=user.dmp Import the user data IMPDP Piner/piner directory=dump_test dumpfile=table.dmp Export table data |