Oracle data export instance Parsing

The following article mainly introduces Oracle export instances. If you are not familiar with Oracle data export operations or Oracle data export application code, you can click the following article, have a better understanding of its related knowledge.

Oracle 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 
 
 

Full = y if TEST has DBA permission, it will export the database under the entire SID and use full-y to export the entire user of TEST.

2. Export the tables of system users and sys users in the Oracle 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_notify,notify_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 '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.

Oracle 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.

Oracle 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 * puls

Second, log in with system/manager

Third, create user username 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:WorkOracle_Databackup>imp userid=test/test full=
y file=inner_notify.dmp 
 
 

Screen Display

 
 

  
  
Import: Release 8.1.7.0.0-Production on Thursday February
16 16:50:05 2006
  
  
(C) Copyright 2000 Oracle Corporation. All rights reserved.
 
 

Connect:

 
 

  
  
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production  
  
  
With the Partitioning option  
  
  
JServer Release 8.1.7.0.0 - Production  
 
 

EXPORT the files created by EXPORT: V08.01.07 in the normal path

The ZHS16GBK Character Set and ZHS16GBK NCHAR character set have been imported.

The export server uses the UTF8 NCHAR character set (possible ncharset conversion)

. Importing AICHANNEL object to AICHANNEL

.. Importing table "inner_policy" 4 rows

Prepare to enable constraints...

Import is terminated successfully, but a warning is displayed.

Appendix 2:

Oracle cannot directly change the table owner. Export/Import can be used to achieve this purpose.

First create import9.par,

The command is as follows: imp parfile =/filepath/import9.par

The content of import9.par is as follows:

 
 

  
  
FROMUSER=TGPMS 
  
  
TOUSER=TGPMS2   
  
  
ROWS=Y 
  
  
INDEXES=Y 
  
  
GRANTS=Y 
  
  
CONSTRAINTS=Y 
  
  
BUFFER=409600 
  
  
file==/backup/ctgpc_20030623.dmp  
  
  
log==/backup/import_20030623.log   
 
 

The above content is an introduction to Oracle data export instances. I hope you will find some gains.