Explain the Oracle imp exp command, impexp

Explain the Oracle imp exp command, impexp

How to import dmp database files in oracle?

Importing and exporting oracle Data imp/exp is equivalent to restoring and backing up oracle data. The exp command can export data from the remote database server to the local dmp file, and the imp command can import the dmp file from the local to the distant database server. This function can be used to build two identical databases, one for testing and the other for formal use. Execution environment: it can be executed in sqlplus.exe or dos (command line). When it can be executed in dos, the installation directory \ ora81 \ bin in oracle 8i is set to a global path, the exp.exeand imp.exe files under this directory are used for import and export. The sqlplus.exe0000exp.exe0000imp.exe files may be packaged class files. Sqlplus.execall the class encapsulated by exp.exe0000imp.exe to complete the Import and Export function. The following describes the Import and Export instances.

 

Data export:
1. Export the database orcl (Instance) completely (only DBA is allowed, not common users), and the user name system password manager is exported to d: \ daochu. dmp.
Exp system/manager @ orcl file = d: \ daochu. dmp full = y
2. Export the tables of system users and sys users in the database
Exp system/manager @ orcl file = d: \ daochu. dmp owner = (system, sys)
3. Export the inner_policy and policy_staff_relat tables in the database.
Exp scott/tiger @ orcl file = d: \ test. dmp tables = (inner_policy, policy_staff_relat)
4. Export the data with the field filed1 in table 1 in the database starting with "00"
Exp system/manager @ orcl file = d: \ daochu. dmp tables = (table1) query = \ "where filed1 like 00% \"

The above is a commonly used export. For compression, you can use winzip to compress the dmp file. You can also add compress = y to the command above.

 

Data Import

1. import data from d: \ daochu. dmp to the test database.

Imp system/manager @ test file = d: \ daochu. dmp
Imp system/manager @ test full = y file = d: \ data \ newsmgnt. dmp ignore = y

　　
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.

Add ignore = y to the end.
2. Import table 1 in d: \ daochu. dmp
Imp system/manager @ test file = d: \ daochu. dmp tables = (table1)

　　
The preceding import and export operations are sufficient. In many cases, you must first completely delete the table and then import it.
Note: If the operator has sufficient permissions, a prompt is displayed. Databases can be connected. You can use tnsping test to obtain whether the database test can be connected.

 

Appendix 1:
Add data import permissions to users
First, start SQL * plus
Second, log in with system/manager
Third, create user "user Name" identified by "Password"

(If you have already created a user, skip this step)
Fourth, 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 "username"
Fifth, run-cmd-to enter 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: f: \ work \ oracle_data \ backup> imp userid = test/test full = y file = inner_policy.dmp

 

Appendix 2:
Oracle cannot directly change the table owner. export/import can be used to achieve this purpose.
Create import9.par first, and then run the following command during use: imp parfile =/filepath/import9.par
The content of import9.par is as follows:
Fromuser = tgpms
Touser = tgpms2

(Note: Change the table owner from fromuser to touser. Users of 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 3

EXP and IMP commands
Command details:

E: \> exp help = y

By entering the EXP command and user name/password, you can
Command after user/password:

Example: exp scott/TIGER

Alternatively, you can control the running mode of "Export" by entering the EXP command with various parameters.
To specify parameters, you can use the Keyword:

Format: exp keyword = value or KEYWORD = (value1, value2,..., valueN)
Instance: exp scott/tiger grants = y tables = (EMP, DEPT, MGR)
Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default)
---------------------------------------------------
USERID username/password
FULL export the entire file (N)
BUFFER data BUFFER size
OWNER username list
FILE output FILE (EXPDAT. DMP)
TABLES Table Name List
COMPRESS imports a range (Y)
Recordlength io record length
GRANTS export permission (Y)
INCTYPE incremental export type
INDEXES export index (Y)
Incremental export of RECORD tracking (Y)
ROWS export data ROWS (Y)
PARFILE parameter file name
CONSTRAINTS export limit (Y)
CONSISTENT cross tabulation consistency
LOG File output by LOG Screen
STATISTICS Analysis object (ESTIMATE)
DIRECT path (N)
TRIGGERS export trigger (Y)
FEEDBACK displays the progress of each x row (0)
FILESIZE maximum size of each dump file
QUERY the clause of the selected export table subset

The following keywords are only used for table spaces that can be transferred.
TRANSPORT_TABLESPACE export the table space metadata that can be transferred (N)
Table space list of TABLESPACES to be transmitted

E: \> imp help = y

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

Instance: imp scott/TIGER

Alternatively, you can control "import" by entering the IMP command and various independent variables according to different parameters.
To specify parameters, you can use the Keyword:

Format: imp keyword = value or KEYWORD = (value1, value2,..., vlaueN)
Instance: imp scott/tiger ignore = y tables = (EMP, DEPT) FULL = N
Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table

USERID must be the first parameter in the command line.

Keyword description (default)
----------------------------------------------
USERID username/password
FULL import of the entire file (N)
BUFFER data BUFFER size
FROMUSER User Name List
FILE input FILE (EXPDAT. DMP)
TOUSER User Name List
SHOW only lists file content (N)
TABLES Table Name List
IGNORE creation error (N)
Recordlength io record length
GRANTS import permission (Y)
INCTYPE incremental Import Type
INDEXES import index (Y)
COMMIT submits array insert (N)
ROWS import data ROWS (Y)
PARFILE parameter file name
LOG File output by LOG Screen
CONSTRAINTS import restrictions (Y)
DESTROY overwrite the tablespace data file (N)
INDEXFILE writes table/index information to the specified file
SKIP_UNUSABLE_INDEXES skips maintenance of unavailable indexes (N)
ANALYZE executes the ANALYZE Statement (Y) in the dump file)
FEEDBACK displays the progress of each x row (0)
TOID_NOVALIDATE skips the verification of the specified type id
FILESIZE maximum size of each dump file
RECALCULATE_STATISTICS recalculates the statistical value (N)

The following keywords are only used for table spaces that can be transferred.
TRANSPORT_TABLESPACE import the deletable tablespace metadata (N)
TABLESPACES tablespace to be transmitted to the database
Data files to be transmitted to the database
TTS_OWNERS has users who can transmit table space centralized data.

Bytes ---------------------------------------------------------------------------------------

If you only Export the table structure (Table creation statement) and do not Export the table data, you can use Tools -- Export User Objects.

Select the table to be exported, set the Export path and parameters, and click "Export ".

PS: This method can only export tables belonging to this user. Tables of other users cannot be exported. We recommend using command line Export (exp, imp)

You can use Tools -- Export Tables to Export all the table structures, table data, triggers, and functions.

You can select multiple tables to export. There are three export methods:

Oracle Export, SQL Insert, pl/SQL developer

The first is the file format exported as. dmp. The. dmp file is binary and can be cross-platform and contain permissions, which is very efficient and widely used.

The second type is exported. for SQL files, select the create tables option. Write where rownum in where clause <1 can be viewed in a text editor, which is more universal, but less efficient than the first one. It is suitable for importing and exporting small data volumes. Note that there cannot be large fields (blob, clob) in the table. If so, you will be prompted not to export them. You can export them in the first and third ways.

Third, export. ,. you can only use Pl/SQL developer to import and export files in the format of Pl/SQL developer. You cannot use the editor to view the files.

PS: The table structure and index structure remain unchanged after the "Oracle Export" method is used for Export and re-import. The other two methods both change the index type.

The import is the same as the export.

When the table owner cannot change, you can use the method of importing and exporting table structure and table data to move the table to the owner you want (note: in particular, if you log on as sysdba when creating a table, the owner of all tables is sys, which will cause you to have trouble accessing the database using c, I also mentioned 《. NET error connecting to the Oracle database, myobjects filter in pl/SQL), you can only change the owner in this way.

Import table structure:

Execute the exported SQL file and remember to delete the username before the table. For example, if the table name is sys. tablename, you must delete sys.

Import table data:

Execute the exported SQL File

 

Oracle exp/imp command usage?

By entering the EXP command and user name/password, you can
Command after user/password:
Example: exp scott/TIGER
Alternatively, you can control the running mode of "Export" by entering the EXP command with various parameters.
To specify parameters, you can use the Keyword:
Format: exp keyword = value or KEYWORD = (value1, value2,..., valueN)
Instance: exp scott/tiger grants = y tables = (EMP, DEPT, MGR)
Or TABLES = (T1: P1, T1: P2). If T1 is a partitioned table
USERID must be the first parameter in the command line.

Oracle Data Import and Export imp/exp command more than 10 Gb expdp/impdp command detailed

This Baidu search engine is detailed, and the landlord has not searched for it,
Data export:
1. Export the database TEST completely, and the username system Password manager is exported to D: daochu. dmp.
Exp system/manager @ TEST file = d: daochu. dmp full = y
2. Export the tables of system users and sys users in the database
Exp system/manager @ TEST file = d: daochu. dmp owner = (system, sys)
3. Export the inner_policy and policy_staff_relat tables in the database.
Exp aichannel/aichannel @ TESTDB2 file = d: datanewsmgnt. dmp tables = (inner_policy, policy_staff_relat)

4. Export the data with the field filed1 in table 1 in the database starting with "00"
Exp system/manager @ TEST file = d: daochu. dmp tables = (table1) query = "where filed1 like '201312 '"

The above is a commonly used export. For compression, you can use winzip to compress the dmp file.
You can also add compress = y to the command above.

Data Import
1. import data from D: daochu. dmp to the TEST database.
Imp system/manager @ TEST file = d: daochu. dmp
Imp aichannel/aichannel @ HUST full = y file = d: datanewsmgnt. dmp ignore = y
The above may be a problem, because some tables already exist, and then it will report an error, the table will not be imported.
Add ignore = y to the end.
2. Import table 1 in d: daochu. dmp
Imp system/manager @ TEST file = d: daochu. dmp tables = (table1)

The preceding import and export operations are sufficient. In many cases, you must first completely delete the table and then import it.

Note:
If the operator has sufficient permissions, a prompt is displayed.
Databases can be connected. You can use tnsping TEST to obtain whether the database TEST can be connected.

Of course, the above method may export a large package when exporting data, because your user may grant DBA permissions during authorization, therefore, you can use the following method to back up the database:

Database Backup
We recommend that you back up the data every week after the system transition. Or back up the data table to be changed before the data table changes significantly.
Perform the following steps to back up the data.
In the command line, type "cmd" and press enter to enter the command line window.
In the window, enter:
Exp mas/123456 @ mas

System prompt: enter an array to extract the buffer size: 4096>
You can press enter directly;

System prompt: Export File: EXPDAT. DMP>
Enter the location of the exported file. The path must exist. Oracle will not automatically create the path here, but the file name can be created. The backup file is suffixed with dmp.
Enter e: \ work \ mas_db_090925v1.dmp and press Enter.

System prompt: (1) E (complete database), (2) U (User) or (3) T (table): (2) U> u
You can enter u or press enter, because the default value is U.

System prompt: Export Permission (... full text>