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, and the IMP command can import the DMP file from a local to 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 executed due to the installation of directory \ora**\bin in Oracle is set to the global path,
This directory has EXP.EXE and IMP.EXE files that are used to perform import and export.
Oracle is written in Java, SQLPLUS. EXE, EXP. EXE, IMP. EXE these two files are likely to be packaged after the class file.
SQLPLUS. EXE calls EXP.EXE, IMP. EXE to complete the import and export function.
The following is an example of importing and exporting.
Data export:
1 full export of database test, user Name System Password Manager exported to D:\daochu.dmp
Exp System/[email protected] file=d:\daochu.dmp full=y
2 Exporting the system user in the database to the SYS user's table
Exp System/[email protected] file=d:\daochu.dmp owner= (System,sys)
3 Exporting Tables Inner_notify, Notify_staff_relat in the database
Exp Aichannel/[email protected] file= d:\data\newsmgnt.dmp tables= (Inner_notify,notify_staff_relat)
4 Export the field filed1 in table table1 in the database with data beginning with "00"
Exp System/[email protected] file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% ' \"
The above is commonly used for the export, for compression, both with WinZip to the DMP file can be very good compression.
It can also be implemented by adding compress=y to the above command.
Import of data
1 Import the data from the D:\DAOCHU.DMP into the test database.
Imp System/[email protected] File=d:\daochu.dmp
Imp aichannel/[email protected] full=y file=file= d:\data\newsmgnt.dmp ignore=y
There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.
Add Ignore=y to the back.
2 Import the table table1 in D:\daochu.dmp
Imp system/[email protected] file=d:\daochu.dmp tables= (table1)
Basically, the above import and export is enough. In many cases, you must first delete the table and then import it.
Attention:
The operator must have sufficient permissions, and it will prompt for insufficient permissions.
Database, you can connect to the. You can use tnsping test to get the database test to connect.
Adding permissions to the import data to the user
First, start sql*puls
Second, landing with System/manager
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_full_database,
Dba,connect,resource,create SESSION to User name
V, run-cmd-into the directory where the DMP file is located,
Imp Userid=system/manager full=y file=*.dmp
or imp Userid=system/manager full=y file=filename.dmp
Example execution:
F:\work\oracle_data\backup>imp userid=test/test full=y file=inner_notify.dmp
How to import a DMP database file in Oracle