First ask the table space name and size of the other database, then create the appropriate tablespace in your Oracle, and finally use the command to import and export the data.
Add:
1. To create a new database;
Oracle data import and export imp/exp is equivalent to Oracle data restore and backup.
The EXP command can export data from a remote database server to a local DMP file.
The IMP command can import DMP files from a local to a remote database server.
With this feature you can build two identical databases, one for testing and one for formal use.
For example: Imp command Imports data: Imp username/[email protected] file=xxx.dmp fromuser=xxx touser=xxx tables= (xxx,xxx) where, Fromuser refers to the other database user name, touser refers to the user name of your database;
Fromuser If there is more than one table space, use () to enclose it: fromuser= (A, b), touser parameter imitation fromuser parameter, if only part of the table is imported, use the tables parameter, enclose the table to be imported with (), if you want to import all, You do not need to specify the tables parameter
The following is an example of importing and exporting. Data export:
1, the database test is fully exported, user Name System Password Manager exported to D:\daochu.dmp
Exp system/[email protected] file=d:\daochu.dmp full=y
2. Export the system user in the database and the SYS user's table
Exp system/[email protected] file=d:\daochu.dmp owner= (System,sys)
3. Export the table inner_notify and Notify_staff_relat in the database
Exp aichannel/[email protected] file= d:\data\newsmgnt.dmp tables= (Inner_notify,notify_staff_relat)
The above is commonly used for the export, for compression, both with WinZip to the DMP file can be very good compression. It can also be implemented by adding compress=y to the above command.
Import of data
1. Import the data from the D:\DAOCHU.DMP into the test database.
Imp system/[email protected] file=d:\daochu.dmp
Imp aichannel/[email protected] full=y file=d:\data\newsmgnt.dmp ignore=y above may be a problem, because some tables already exist, and then it is an error, No import is made to the table. Add Ignore=y on the back, and if there is a primary key, the existing record still cannot be imported.
2, the d:\daochu.dmp in the table table1 import imp system/[email protected] file=d:\daochu.dmp tables= (table1)
Basically, the above import and export is enough. In many cases, you must first delete the table and then import it.
Note: The operator must have sufficient permissions to be prompted for insufficient permissions. Database, you can connect to the. You can use tnsping test to get the database test to connect.
Appendix I: Adding permissions for importing data to users
First, start sql*puls
Second, landing with System/manager
Third, create user username identified by password (this step can be omitted if the user has already been created)
IV, GRANT CREATE user,drop user,alter USER, create any VIEW, DROP any view,exp_full_database,imp_full_database, D Ba,connect,resource,create SESSION to User name
V, run-cmd-into 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 execution: F:\work\oracle_data\backup>imp userid=test/test full=y
File=inner_notify.dmp
Screen display import:release 8.1.7.0.0-production on Thu February 16:50:05 2006 (c) Copyright: Oracle Corporation. All rights reserved.
Connected to: Oracle8i Enterprise Edition release 8.1.7.0.0-production with the partitioning option Jserver release 8.1.7.0.0-p Roduction
Export files created by export:v08.01.07 through a regular path
Import is completed in ZHS16GBK character set and ZHS16GBK NCHAR character set
The export server uses the UTF8 NCHAR character set (possible Ncharset conversions).
Importing Aichannel objects into Aichannel. .
Importing tables
"Inner_notify"
4 rows are imported ready to enable constraints ...
The import was terminated successfully, but a warning appears.
Http://www.cnblogs.com/pinbo/archive/2010/10/22/1858246.html
http://blog.csdn.net/caijing3210/article/details/7528788
Oracle Exp,imp