Preface: About oracle9i Data export and import problem, toss me for a long time, especially import. Today, under the guidance of a colleague, is finally successful, in order to remember the help of this colleague, here only "Kun" as a mark.
Body:
Oracle Data export:
If you are exporting an Oracle database for this computer:
Exp PCISV62/11@ORCL file= "D:\pcisv62081226.dmp" full=y
(ORCL for local database monitoring)
If you are exporting Oracle data on the server side:
Exp pcisv62/11@tnsname file= "D:\pcisv62081226.dmp" full=y
(Tnsname is corev6_dbserver,sid for COREV6, host for DBServer.) )
Exp Pcisv6_ab/11@corev6_dbserver file=d:\v6100210.dmp rows=y buffer=1024000
Note: You must be a DBA to perform a full database or table space export operation.
Oracle data import, which is divided into the following steps:
1. First, under the Enterprise Manager console in Oracle9i, log in as Sys/sys and SYSDBA, create a tablespace--> under "Storage" COREV6 "tablespace" and assign the appropriate space to it.
2. Create a user pcisv62 under Security--> "user" to make the user default tablespace COREV6, and Grant Connect, DBA, Exp_full_database, Imp_full_database, RESOURCE.
3. Import the. dmp file, start--> run cmd, import command:
Imp PCISV6_AB/11@ORCL file=d:\v6100210.dmp full=y ignore=y
Note: If the. dmp file that is backed up is data that derives COREV6 space from the user pcisv62, then
1. The new user is best pcisv62, otherwise the command is:
Imp pcisv62/11@orcl file=d:\v6100210.dmp fromuser=pcisv62 touser=pcisv6_ab full=y ignore=y rows=y tablespaces=core V6
2. The new space name associated with the new user must be COREV6 or the Oracle report cannot find an COREV6 space error.
After the data has been successfully imported, the next step is to configure Tomcat's Server.xml file:
1. If you use the data on this computer, part of the local data source is configured to: Username= "pcisv62" password= "11"
Url= "Jdbc:oracle:thin: @localhost: 1521:orcl"
2. If the data on the server is used, the portion of the data source is configured to: Username= "pcisv62" password= "11"
Url= "Jdbc:oracle:thin: @dbserver: 1521:corev6" (DBServer is the name of the server, COREV6 is the SID of Oracle on the server)
Oracle's installation directory E:\oracle\ora92\network\admin under the 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)
)
)
Reproduced below: Several import and Export command methods for later inspection.
Data export:
Exp qhmis/qhmis@qhmis file= ' d:\backup\qhmis\qhmis20060526.dmp ' grants=y full=n
1 completely export database test, user Name System Password Manager exported to D:\daochu.dmp
Exp System/manager@test file=d:\daochu.dmp full=y
2 Exporting a table from the system user in the database to the SYS user
Exp system/manager@test file=d:\daochu.dmp owner= (System,sys)
3 Export the table table1, table2 in the database
Exp system/manager@test file=d:\daochu.dmp tables= (table1,table2)
4 Export the fields in the table table1 in the database filed1 with the data beginning with "00"
Exp system/manager@test file=d:\daochu.dmp tables= (table1) query=\ "where filed1 like ' 0% '"
Import of data:
1 Import the data from D:\DAOCHU.DMP into the test database.
Imp system/manager@test file=d:\daochu.dmp
There may be something wrong with it, because some tables already exist, and then it complains, and the table is not imported.
Just add ignore=y to the back.
2 Import the table table1 in D:\daochu.dmp
Imp system/manager@test file=d:\daochu.dmp tables= (table1)