Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to import and export Oracle data, many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

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, and the imp command can import dmp files from the local to a remote database server. Using this feature, you can build two identical databases, one for testing and one for formal use.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line). When executed in DOS, because the installation directory\ ora81\ BIN in oracle 8i is set to the global path, there are EXP.EXE and IMP.EXE files in this directory that are used to perform import and export. Oracle is written in java, and the two files SQLPLUS.EXE, EXP.EXE, and IMP.EXE may be wrapped class files.

SQLPLUS.EXE calls the classes wrapped by EXP.EXE and IMP.EXE to complete the import and export function. The following is an example of import and export.

Data export:

1 Export the database TEST completely, and export the user name system password manager 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 the tables inner _ notify and notify_staff_relat in the database

Exp aichannel/aichannel@TESTDB2 file= d:\ data\ newsmgnt.dmp tables= (inner_notify,notify_staff_relat)

4 export the field filed1 in the table table1 in the database with the data starting with "00"

Exp system/manager@TEST file=d:\ daochu.dmp tables= (table1) query=\ "where filed1 like'00%'\" above are commonly used exports. for compression, dmp files can be well compressed with winzip.

You can also add compress=y to the above command.

Import of data:

1 Import the data from D:\ daochu.dmp into the TEST database.

Imp system/manager@TEST file=d:\ daochu.dmp

Imp aichannel/aichannel@HUST full=y file=file= d:\ data\ newsmgnt.dmp ignore=y

There may be a problem above, because some table already exists, and then it reports an error and does not import the table.

Just add ignore=y at the end.

2 Import the table table1 in d:\ daochu.dmp

Imp system/manager@TEST file=d:\ daochu.dmp tables= (table1)

Basically, the above import and export is enough. In many cases, the table is deleted completely and then imported. Note:

The operator should have sufficient permissions, and it will prompt if the permissions are not enough.

You can connect to the database. You can use tnsping TEST to find out whether the database TEST can be connected.

Appendix I:

The operation of adding permissions to import data to users

First, start sql*puls

Second, log in with system/manager

Third, create user username IDENTIFIED BY password (if you have already created a user, this step can be omitted)

Fourth, 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

Fifth, run-cmd- to enter 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.

Perform an example:

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 Thursday February 16 16:50:05 2006

(C) Copyright 2000 Oracle Corporation. All rights reserved.

Connect 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 via a regular path

The import of ZHS16GBK character set and ZHS16GBK NCHAR character set has been completed

The export server uses the UTF8 NCHAR character set (possible ncharset conversion)

. Importing objects from AICHANNEL into AICHANNEL

. . Importing table "INNER_NOTIFY" 4 rows are imported

Prepare to enable constraints.

The import was terminated successfully with warnings.

Appendix II:

Oracle does not allow you to directly change the owner of the table, which can be achieved with Export/Import.

Set up import9.par first

Then, the command when in use is as follows: imp parfile=/filepath/import9.par

The contents of the example import9.par are as follows:

FROMUSER=TGPMS

TOUSER=TGPMS2 (Note: users who change the owner of a 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

=

/ / create a temporary tablespace

Create temporary tablespace zfmi_temp

Tempfile'D:\ oracle\ oradata\ zfmi\ zfmi_temp.dbf'

Size 32m

Autoextend on

Next 32m maxsize 2048m

Extent management local

/ / tempfile parameter must have

/ / create a data tablespace

Create tablespace zfmi

Logging

Datafile'D:\ oracle\ oradata\ zfmi\ zfmi.dbf'

Size 100m

Autoextend on

Next 32m maxsize 2048m

Extent management local

/ / datafile parameter must have

/ / Delete users and objects owned by users

Drop user zfmi cascade

/ / the cascade parameter is cascaded to delete all the objects of the user. It is often encountered that if the user has an object and does not add this parameter, the user cannot delete it, so it is customary to add this parameter.

/ / Delete tablespace

Premise: before deleting a tablespace, make sure that the tablespace is not used by other users before deleting it

Drop tablespace zfmi including contents and datafiles cascade onstraints

/ / including contents deletes the contents of the tablespace. If there are contents in the tablespace before deleting the tablespace without adding this parameter, the tablespace cannot be deleted, so it is customary to add this parameter.

/ / including datafiles deletes the data file in the tablespace

/ / cascade constraints also deletes the foreign key reference of the table in tablespace

If you delete the tablespace file before deleting the tablespace, the workaround is:

If the data file corresponding to the tablespace is deleted before clearing the tablespace, the database will not be able to start and shut down properly.

It can be restored using the following method (this method has been verified in oracle9i):

In the following procedure, filename is a data file that has been deleted and, if there is more than one, needs to be executed multiple times; tablespace_name is the name of the corresponding tablespace.

$sqlplus / nolog

SQL > conn / as sysdba

If the database is already started, you need to execute the following line first:

SQL > shutdown abort

SQL > startup mount

SQL > alter database datafile 'filename' offline drop

SQL > alter database open

SQL > drop tablespace tablespace_name including contents

/ / create a user and specify a tablespace

Create user zfmi identified by zfmi

Default tablespace zfmi temporary tablespace zfmi_temp

/ / identified by parameter must have

/ / Grant all permissions to the DBA role for message users

GRANT DBA TO zfmi

/ / Grant permissions to the user

Grant connect,resource to zfmi; (db2: specify all permissions)

Import and export commands:

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, and the imp command can import dmp files from the local to a remote database server. Using this feature, you can build two identical databases, one for testing and one for formal use.

Execution environment: can be executed in SQLPLUS.EXE or DOS (command line)

When it can be executed in DOS, the installation directory ora81BIN in oracle 8i is set to the global path.

There are EXP.EXE and IMP.EXE files in this directory that are used to perform import and export.

Oracle is written in java, and the two files SQLPLUS.EXE, EXP.EXE, and IMP.EXE may be wrapped class files.

SQLPLUS.EXE calls the classes wrapped by EXP.EXE and IMP.EXE to complete the import and export function.

The following is an example of import and export.

Data export:

1 Export the database TEST completely, and export the user name system password manager 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 the tables inner _ notify and notify_staff_relat in the database

Exp aichannel/aichannel@TESTDB2 file= d:datanewsmgnt.dmp tables= (inner_notify,notify_staff_relat)

4 export the field filed1 in the table table1 in the database with the data starting with "00"

Exp system/manager@TEST file=d:daochu.dmp tables= (table1) query= "where filed1 like'00%'"

The above are commonly used exports, for compression, both using winzip to dmp files can be very good compression.

You can also add compress=y to the above command.

Import of data

1 import the data from D:daochu.dmp into the TEST database.

Imp system/manager@TEST file=d:daochu.dmp

Imp aichannel/aichannel@HUST full=y file=d:datanewsmgnt.dmp ignore=y

There may be a problem above, because some table already exists, and then it reports an error and does not import the table.

Just add ignore=y at the end.

2 Import the table table1 in d:daochu.dmp

Imp system/manager@TEST file=d:daochu.dmp tables= (table1)

Basically, the above import and export is enough. In many cases, the table is deleted completely and then imported.

Note:

The operator should have sufficient permissions, and it will prompt if the permissions are not enough.

You can connect to the database. You can use tnsping TEST to find out whether the database TEST can be connected.

Appendix I:

The operation of adding permissions to import data to users

First, start sql*puls

Second, log in with system/manager

Third, create user username IDENTIFIED BY password (if you have already created a user, this step can be omitted)

Fourth, 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

Fifth, run-cmd- to enter 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.

Perform an example:

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

Screen display

Import: Release 8.1.7.0.0-Production on Thursday February 16 16:50:05 2006

(C) Copyright 2000 Oracle Corporation. All rights reserved.

Connect 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 via a regular path

The import of ZHS16GBK character set and ZHS16GBK NCHAR character set has been completed

The export server uses the UTF8 NCHAR character set (possible ncharset conversion)

. Importing objects from AICHANNEL into AICHANNEL

. . Importing table "INNER_NOTIFY" 4 rows are imported

Prepare to enable constraints.

The import was terminated successfully with warnings.

Appendix II:

Oracle does not allow you to directly change the owner of the table, which can be achieved with Export/Import.

Set up import9.par first

Then, the command when in use is as follows: imp parfile=/filepath/import9.par

The contents of the example import9.par are as follows:

FROMUSER=TGPMS

TOUSER=TGPMS2 (Note: users who change the owner of a 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

After reading the above, do you have any further understanding of how to import and export Oracle data? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.