Shulou(Shulou.com)06/03 Report--

Using Exp/Imp,Exp/Imp is two command line tools for oracle to back up data.

Exp Export imp Import

1. Import and export of local database

1. Export

(run-operate in cmd)

Exp username / password @ database instance name file= local storage path

For example: exp cmk/cmk@ORCL file=C:/jnjp.dmp full=y

two。 Import

Imp username / password @ database instance name file= local database file storage path full=y ignore=y

For example: imp cmk/cmk@orcl file=E:\ jnjp.dmp full=y ignore=y

two。 Remote database import and export

1. Export

Exp username / password @ ip address / database instance name file= local storage path

For example: exp cmk/cmk@192.168.10.1/orcl file=C:/jnjp.dmp full=y

two。 Import

Imp username / password @ ip address / database instance name file= local database file storage path full=y

For example: imp cmk/cmk@192.168.10.1/orcl file=C:/jnjp.dmp full=y

All parameters for 3.EXP (default values for parameters in parentheses)

USERID username / password such as: USERID=duanl/duanl

FULL exports the entire database (N)

The size of the BUFFER data buffer

OWNER owner user name list, which user's object you want to export, use owner=username

FILE output File (EXPDAT.DMP)

List of TABLES table names, specifying the exported table name, such as: TABLES=table1,table2

COMPRESS imports an extent (Y)

Length of RECORDLENGTH IO record

GRANTS Export permissions (Y)

INCTYPE incremental Export Typ

INDEXES Export Index (Y)

RECORD trace incremental export (Y)

ROWS Export data Row (Y)

PARFILE parameter file name, if you exp a lot of parameters, you can save as a parameter file.

CONSTRAINTS Export constraint (Y)

CONSISTENT crosstab consistency

Log file output from LOG screen

STATISTICS Analysis object (ESTIMATE)

DIRECT Direct path (N)

TRIGGERS Export trigger (Y)

FEEDBACK shows the progress of each x line (0)

Maximum size of each dump file in FILESIZE

QUERY selected clauses that export a subset of tables

The following keywords are used only for transferable tablespaces

TRANSPORT_TABLESPACE exports table space metadata that can be transferred (N)

List of tablespaces that TABLESPACES will transfer

All parameters for 4.IMP (default values for parameters in parentheses)

USERID username / password

FULL imports the entire file (N)

BUFFER data buffer size

FROMUSER owner user name list

FILE input File (EXPDAT.DMP)

List of TOUSER user names

SHOW lists only file contents (N)

List of TABLES table names

IGNORE ignores creation error (N)

Length of RECORDLENGTH IO record

GRANTS Import permissions (Y)

INCTYPE incremental Import Typ

INDEXES Import Index (Y)

COMMIT commit array insert (N)

ROWS Import data Row (Y)

PARFILE parameter file name

Log file output from LOG screen

CONSTRAINTS Import restrictions (Y)

DESTROY overrides tablespace data files (N)

INDEXFILE writes table / index information to the specified file

SKIP_UNUSABLE_INDEXES skips maintenance of unavailable indexes (N)

ANALYZE executes ANALYZE statements in the dump file (Y)

FEEDBACK shows the progress of each x line (0)

TOID_NOVALIDATE skips the check of the specified type id

Maximum size of each dump file in FILESIZE

RECALCULATE_STATISTICS recalculates statistics (N)

The following keywords are used only for transferable tablespaces

TRANSPORT_TABLESPACE imports transferable tablespace metadata (N)

The tablespace that TABLESPACES will transfer to the database

The data file that DATAFILES will transfer to the database

TTS_OWNERS has users who can transfer data in tablespace sets

Common options for 5.EXP

1.FULL

This is used to export the entire database, and when used together with ROWS=N, you can export the structure of the entire database. For example:

Exp userid=test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y

Note: in oracle10g, userid can write a username / password without writing, like this:

Exp test/test file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y

2.OWNER and TABLE

These two options are used to define objects for EXP. OWNER definition exports the object of the specified user; TABLE specifies the table name of the EXP, for example:

Exp userid=test/test file=./db_str.dmp log=./db_str.log owner=duanl

Note: sometimes we may encounter the situation of exporting only a single table. The following method can help you solve the problem.

Exp userid=test/test file=./db_str.dmp log=./db_str.log table=nc_data,fi_arap

3.FILE and LOG

These two parameters specify the DMP name and LOG name of the backup, including the file name and directory, as shown above.

4.COMPRESS

The COMPRESS parameter does not compress the contents of the exported data. Used to control how the storage statement of the exported object is generated. The default value is Y, and using the default value, the init extent of the object's storage statement is equal to the sum of the extent of the currently exported object. COMPRESS=N is recommended.

Common options for 6.IMP

1. FROMUSER and TOUSER

Use them to import data from one SCHEMA to another SCHEMA. For example, suppose we exported the object as test when we did exp, and now we want to import the object into the user:

Imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1

2. IGNORE, GRANTS and INDEXES

The IGNORE parameter will ignore the existence of the table and continue to import, which is very useful when you need to adjust the storage parameters of the table. We can first build the table with reasonable storage parameters according to the actual situation, and then import the data directly. While GRANTS and INDEXES indicate whether to import authorization and index, if you want to rebuild the index with the new storage parameters, or to speed up the entry speed, we can consider setting INDEXES to N, while GRANTS is usually Y. For example: imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=N

7. Import and export instance

The following is the import and export of the instance, the wizard import and export to see the instance can basically be completed, because import and export is very simple.

1. Data export:

1.1 fully export the database TEST, username system password manager to D:\ daochu.dmp

Exp system/manager@TEST file=d:\ daochu.dmp full=y

1.2Exporting tables for system and sys users in the database

Exp system/manager@TEST file=d:\ daochu.dmp owner= (system,sys)

1.3Exporting tables table1 and table2 in the database

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

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%'\"

two。 Import of data:

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

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

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, I delete the table completely and then import it.

3. Export, import the use of wildcards:

If you want to export a table in the format of TABLE_1,TABLE_2,TABLE_3, which has a total of 300 tables in the library, do you need to write statements one by one? Don't worry, oracle provides wildcards that can be used to meet such needs:

Exp system/test@192.168.1.198/orcl file=d:\ exptest.dmptables=TABLE% log=exp_test.log

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.