Import and export of Oracle databases

---restore content starts---

Import Export (dos window)

Database export:
1 Export the database test fully exported to D:\daochu.dmp
EXP username/password @orcl file=d:\daochu.dmp full=y
2 Exporting the system user in the database to the SYS user's table
EXP username/password @orcl file=d:\daochu.dmp owner= (System,sys)
3 Exporting a field in a table in a database that begins with a "00" Data
EXP username/password @orcl file=d:\daochu.dmp tables= (table name) query=\ "where field name like ' 0% ' \"

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.

Database Import

1 Import the data from the D:\DAOCHU.DMP into the test database.
IMP username/password @test file=d:\daochu.dmp
IMP username/password @test full=y file=file= d:\data\newsmgnt.dmp ignore=y
There may be a problem, because some tables already exist, and then it is an error, and the table is not imported.
Add Ignore=y to the back.
2 importing table tables from the D:\DAOCHU.DMP
IMP username/password @test file=d:\daochu.dmp tables= (table name)

Export table Space information

First, before exporting, in order to maintain the consistency of the data file, before the derivation contains the table space data dictionary information,

You should first turn the self-contained table space into a read-only state so that tablespace information does not change.

Otherwise there will be an error, as follows

The modified tablespace is read-only:
Sql>alter tablespace table space name ReadOnly;

Then execute the command in the DOS window:

EXP transport_tablespace=y tablespaces= Table space name, FILE=D:EXPDATA.DMP
Prompt to enter the user name, you must be logged in as SYSDBA, as follows
Username/password @orcl as Sysdba
Export Complete!
Parameters:

Transport_tablespace Move table space option, y means to export table space information;

Tablespaces is used to specify the table space to export;

File is used to specify the files that hold the exported information.

Table Space Import

After you copy the tablespace data file and the exported file to the same machine as the target database, you can import the tablespace information into the target database. Importing a self-contained tablespace is actually loading the data dictionary information from the exported file into the target data, such as the tablespace name, the data file that corresponds to the tablespace, and the data object name. The specific operating system commands are as follows:
　　
D:\>imp transport_tablespace=y datafiles= ' d:\localdb\local1.dbf '
　　
User name: System/[email protected] as Sysdba

When the above command is executed, the tablespace local and Local2 and all the data object information contained in it are imported into the target database.
Note: When you move a tablespace, the owner of all the data objects held in the source table space must exist in the target database, and if they do not exist, you should establish the appropriate user before importing the table space and then import the tablespace.

---restore content ends---

Import and export of Oracle databases