Oracle Import and Export Database

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.

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 (this step can be omitted if you have already created a user)
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_policy.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 to: 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 (Note: You can 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