Oracle IMP Exp Command detailed

How do I import a DMP database file in Oracle?

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 because the installation directory in Oracle 8i \ora81\bin is set to the global path, the directory has Exp.exe and Imp.exe files are used to perform import and export.　Oracle is written in Java, and the two files Sqlplus.exe, Exp.exe, and Imp.exe are likely to be packaged class files.　Sqlplus.exe calls the classes wrapped by Exp.exe, Imp.exe, and completes the import and export function. The following is an example of importing and exporting.

Data export:
1 full export of database ORCL (instance) (only DBA can, normal user not), 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 Scott/[email protected] file= d:\test.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 00%\"

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 system/[email protected] full=y 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.
Note: The operator must have sufficient permissions to be prompted for insufficient permissions. Database, you can connect to the. You can use tnsping test to get the database test to connect.

Appendix I:
Adding permissions to the import data to the user
First, start Sql*plus
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

Appendix II:
Oracle does not allow direct changes to the table owner, which can be achieved with export/import.
First establish the Import9.par, then, use the command as follows: Imp Parfile=/filepath/import9.par
Example Import9.par content is as follows:
Fromuser=tgpms
Touser=tgpms2

(Note: Users who change the owner of the table 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

Appendix III

EXP, IMP command detailed
Detailed command:

E:\>exp help=y

By entering the EXP command and username/password, you can
Commands after the user/password:

Example: EXP Scott/tiger

Alternatively, you can control how export runs by entering an EXP command with various parameters.
To specify parameters, you can use keywords:

Format: EXP keyword=value or keyword= (value1,value2,..., Valuen)
Example: EXP scott/tiger grants=y tables= (emp,dept,mgr)
or tables= (T1:P1,T1:P2), if T1 is a partitioned table

The USERID must be the first parameter in the command line.

Keyword description (default)
---------------------------------------------------
UserID username/Password
Full export of the entire file (N)
Size of buffer data buffers
Owner User Name list
File output files (expdat. DMP)
List of TABLES table names
COMPRESS Importing a range (Y)
Length of RecordLength IO record
GRANTS Export Permissions (Y)
Inctype Incremental Export Type
INDEXES Export Index (Y)
RECORD Tracking Incremental Export (Y)
Rows Export Data rows (Y)
Parfile parameter file name
CONSTRAINTS Export Limit (Y)
Consistent cross-table consistency
Log file on screen output
STATISTICS Analysis Object (ESTIMATE)
Direct Path (N)
TRIGGERS Export Trigger (Y)
FEEDBACK shows the progress of each x line (0)
FILESIZE maximum size of each dump file
QUERY selects the clause that exports a subset of tables

The following keywords are only available for transportable tablespaces
Transport_tablespace exporting the Transportable tablespace metadata (N)
Tablespaces List of tablespaces to be transferred

E:\>imp help=y

You can enter the IMP command and your username/password
Follow the command with your username/password:

Example: IMP Scott/tiger

Alternatively, you can control "import" by different parameters by entering the IMP command and a variety of arguments.
To specify parameters, you can use keywords:

Format: IMP keyword=value or keyword= (value1,value2,..., Vlauen)
Example: IMP scott/tiger ignore=y tables= (emp,dept) full=n
or tables= (T1:P1,T1:P2), if T1 is a partitioned table

The USERID must be the first parameter in the command line.

Keyword description (default)
----------------------------------------------
UserID username/Password
Full import of entire file (N)
Buffer size of data buffers
Fromuser Owner User Name list
File input files (expdat. DMP)
Touser List of user names
SHOW only lists file contents (N)
List of TABLES table names
IGNORE Ignore Create error (N)
Length of RecordLength IO record
GRANTS Import Permissions (Y)
Inctype Incremental Import Type
INDEXES Import Index (Y)
Commit commit array Insert (N)
Rows Import data rows (Y)
Parfile parameter file name
Log file on screen output
CONSTRAINTS Import Limit (Y)
DESTROY Overlay tablespace data file (N)
Indexfile Writing table/index information to the specified file
Skip_unusable_indexes Skip maintenance of indexes that are not available (N)
ANALYZE executing the ANALYZE statement in the dump file (Y)
FEEDBACK shows the progress of each x line (0)
Toid_novalidate skips a checksum of the specified type ID
FILESIZE maximum size of each dump file
Recalculate_statistics recalculation of statistical values (N)

The following keywords are only available for transportable tablespaces
Transport_tablespace import of transportable tablespace metadata (N)
Tablespaces the tablespace that will be transferred to the database
Datafiles data files that will be transferred to the database
Tts_owners has users who can transmit data in a table-space set

---------------------------------------------------------------------------------------

If you export only the structure of the table (build table statement), do not export the table data, you can tools--export User Objects

Select the table you want to export, set the exported path and parameters, click Export to OK

PS: This method can only export tables belonging to this user, other users of the table can not be exported, it is recommended to export with the command line (exp, IMP)

If you want to export all of the table structure, table data, triggers, functions and so on, you can use Tools--export Tables

Select the tables you want to export (more than one), there are three ways to export them:

Oracle export,sql insert,pl/sql Developer

The first is the file format exported to. DMP, the. dmp file is binary, can be cross-platform, can also contain permissions, the efficiency is very good, the most extensive.

The second is to export to. sql file, check the Create tables option, where clause write where rownum<1 can be viewed with a text editor, versatility is better, but less efficient than the first, suitable for small data import export. In particular, it is important to note that there can be no large number of fields (BLOB,CLOB) in the table, and if you do, you will be prompted not to export, which is the first and third way to export.

The third type is exported to. Pde format, the. PDE is PL/SQL developer own file format, can only be imported and exported by PL/SQL developer; can't use the editor to view, efficiency don't know how

PS: Only the "Oracle export" method is exported and then imported, the table structure and the index structure have not changed, and two different ways have changed the index type.

The import situation is the same as exporting

In cases where the owner of the table cannot be changed, you can use the method of importing and exporting the table structure and table data to move the table under the owner you want (note: In particular, if you are logged in as a sysdba, the owner of all tables is SYS, which can cause trouble for you to access the database in C #, In my other article, I also mentioned ". NET connection to Oracle database errors, myobjects filter in PL/SQL), you can only change the owner in this way.

Import Table structure:

To execute the SQL file you just exported, remember to delete the user name before the table, for example, the previous name is Sys.tablename, you must remove the SYS

Import Table Data:

Execute the SQL file you just exported

Oracle IMP Exp Command detailed