Oracle Data Export and Import

Preface: It has been a long time for me to export and import Oracle9i data, especially import. Today, under the guidance of a colleague, I finally succeeded. In order to remember the help of this colleague, I only marked "Kun" here.

Body:

Oracle data export:
To export the local Oracle database:
Exp pcisv62/11 @ orcl file = "D: \ pcisv62081226.dmp" Full = y
(Orcl is a local Database Listener)

To export ORACLE data on the server:
Exp pcisv62/11 @ tnsname file = "D: \ pcisv62081226.dmp" Full = y
(Tnsname is corev6_dbserver, Sid is corev6, and host is dbserver .)

Note: you must use a DBA to export the complete database or tablespace.

To import ORACLE data, perform the following steps:
1. log on to the Enterprise Manager Console of Oracle9i as sys/sys and sysdba, create the tablespace corev6 under "Storage" --> "tablespace", and allocate suitable space to it.
2. create a user pcisv62 under "security" --> "user", set the user's default tablespace to corev6, and assign connect, DBA, exp_full_database, imp_full_database, and resource to the "role.
3. Import the. dmp file, start --> RUN cmd, and import the command:
IMP pcisv62/11 @ orcl file = "D: \ pcisv62081226.dmp" ignore = y

Note: If the. dmp file is used by the user pcisv62 to export data in the corev6 space
1. The new user is preferably pcisv62; otherwise, the command is:
IMP pcisv62/11 @ orcl file = "pcisv62081226.dmp" fromuser = pcisv62 touser = new user ignore = y
2. the name of the new space associated with the new user must be corev6. Otherwise, Oracle Reports that the corev6 space cannot be found.

 

After the data is imported successfully, the next step is to configure the Tomcat server. xml file:
1. if data on the local machine is used, the configuration of the local data source is: username = "pcisv62" Password = "11"

Url = "JDBC: oracle: thin: @ localhost: 1521: orcl"
2. If the data on the server is used, the configuration in the data source is: username = "pcisv62" Password = "11"

Url = "JDBC: oracle: thin: @ dbserver: 1521: corev6 "(Dbserver is the server name, corev6 is the SID of Oracle on the server)

 

Oracle installation directory e: \ oracle \ ora92 \ Network \ ADMIN tnsnames. ora:
Local Configuration:
Orcl =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = Host Name) (Port = 1521 ))
)
(CONNECT_DATA =
(Server = dedicated)
(SERVICE_NAME = orcl)
)
)
Or Server Configuration:
Corev6_dbserver =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = dbserver) (Port = 1521 ))
)
(CONNECT_DATA =
(SID = corev6)
(Server = dedicated)
)
)

Reprinted below: Several Import and Export command methods for future reference.
Data export:
Exp qhmis/qhmis @ qhmis file = 'd: \ backup \ qhmis \ qhmis20060526.dmp 'grants = y full = N
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 tables Table1 and Table2 in the database
Exp system/manager @ test file = D: \ daochu. dmp tables = (Table1, table2)
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 '\"
Data Import:
1. import data from D: \ daochu. dmp to the test database.
IMP system/manager @ test file = D: \ daochu. dmp
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)