Import and export of Oracle database tables cmd command Daquan

In the actual project development often encountered in the import and export of the Oracle database table, the following are some common cmd commands:

First, the export of the data table

1 full export of database test, user Name System Password Manager exported to D:daochu.dmp

Exp file=d:daochu.dmp full=y or (exp rrs/[email protected] file=daochu.dmp full=y) This is to export the table all, if you only want to export the RRS table:

Exp Rrs/[email protected] file=daochu.dmp owner= (RRS);

2 Exporting the system user in the database to the SYS user's table

Exp Username/password file=d:aa.dmp owner=username;//Exporting a user's table

Exp file=d:daochu.dmp owner= (System,sys)

3 Exporting Tables Inner_notify, Notify_staff_relat in the database

Exp file= d:data ewsmgnt.dmp tables= (Inner_notify,notify_staff_relat)

4 Export the field filed1 in table table1 in the database with data beginning with "00"

Exp file=d:daochu.dmp tables= (table1) query= "where filed1 like ' 0% '"

Second, the Data table import operation

1 Import the data from the D:DAOCHU.DMP into the test database.

Imp file=d:daochu.dmp

Imp full=y file= d:data ewsmgnt.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 Import the table table1 in D:daochu.dmp

Imp 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.

Third, Operation Note:

The operator must have sufficient permissions, and it will prompt for insufficient permissions. The DBA is generally given maximum privileges.

Database, you can connect to the. You can use tnsping test to get the database test to connect.

1, to the user to increase the import data permissions operation

First, start cmd

Second, landing the database with/as SYSDBA

Third, create user username identified by password

Maximum permissions can be given directly: Grant DBA to User name

Give basic permissions: Grant Connect,resource to User

IV, 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 User name

Example execution:

F:workoracle_dataackup>imp userid=test/test full=y file=inner_notify.dmp

Screen display

Import:release 8.1.7.0.0-production on Thu February 16 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-production

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 table "Inner_notify" 4 rows are imported

Prepare to enable constraints ...

The import was terminated successfully, but a warning appears.

Appendix II:

Oracle does not allow direct changes to the table owner, which can be achieved with export/import.

Build Import9.par First,

Then, use the command as follows: Imp Parfile=/filepath/import9.par

Example Import9.par content is as follows:

Fromuser=tgpms

TOUSER=TGPMS2 (Note: Users who change the owner of the table from Fromuser to Touser,fromuser and Touser can be different)

Rows=y

Indexes=y

Grants=y

Constraints=y

buffer=409600

File==/backup/ctgpc_20030623.dmp

Log==/backup/import_20030623.log

Import and export of Oracle database tables cmd command Daquan