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

Import / export is the two oldest command-line tools that ORACLE survives. In fact, I never think Exp/Imp is a good backup method. The correct way to say is that Exp/Imp can only be a good dump tool, especially in small database dump, table space migration, table extraction, detection of logical and physical conflicts and so on. Of course, we can also use it as a logical auxiliary backup after a physical backup of a small database, which is also a good suggestion. For larger and larger databases, especially the emergence of TB-level databases and more and more data warehouses, EXP/IMP is becoming more and more powerless. At this time, database backups have turned to RMAN and third-party tools. The use of EXP/IMP is explained below.

How to make exp help display in a different character set: setnls_lang= simplified chinese_china.zhs16gbk, by setting environment variables, you can make exp help display in Chinese, if setnls_lang=American_america. Character set, then the help is in English.

All parameters for 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

Program code

All parameters for 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

Description of increment parameters: the increment of exp/imp is not really an increment, so it is best not to use it.

How to use it:

Expparameter_name= value or Expparameter_name= (value1,value2...)

You can see all the help as long as you enter the parameter help=y.

Common options for EXP

1.FULL, which 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.dmplog=./db_str.log full=y rows=n compress=y direct=y

2. OWNER and TABLE, these two options are used to define the object of 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.dmplog=./db_str.log owner=duanl

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

3.BUFFER and FEEDBACK, I will consider setting these two parameters when exporting more data. For example:

Exp userid=test/test file=yw97_2003.dmplog=yw97_2003_3.log feedback=10000 buffer=100000000 tables=WO4,OK_YT

4.FILE and LOG, which specify the DMP name and LOG name of the backup, including the file name and directory, respectively, as shown above.

The 5.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.

6. FILESIZE this option is available in 8i. If the exported dmp file is too large, it is best to use the FILESIZE parameter to limit the file size to no more than 2G. Such as:

Exp userid=duanl/duanl file=f1,f2,f3,f4,f5filesize=2G owner=scott

This will create a series of files, such as f1.dmp, f2.dmp, etc., each with a size of 2G, if the total amount of export is less than 10G

EXP does not need to create f5.bmp.

IMP Common option DBA

1. FROMUSER and TOUSER, which are used 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.dmpfromuser=test1 touser=test1

2. IGNORE, GRANTS and INDEXES, in which 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=test1indexes=N

Tablespace transmission

Tablespace transfer is a new way for 8i to move data quickly between databases, which is to attach a format data file on one database to another database instead of exporting the data to a Dmp file, which is very useful in some cases, because transferring tablespace moving data is as fast as copying files.

There are some rules about transport tablespaces, namely:

The source and target databases must run on the same hardware platform.

The source and target databases must use the same character set.

The source and target databases must have blocks of the same size

The target database cannot have a tablespace with the same name as the migration tablespace

SYS objects cannot be migrated

Must transfer a set of self-contained objects

Some objects, such as materialized views, function-based indexes, etc., cannot be transferred.

You can use the following methods to detect whether a tablespace or set of tablespaces meets transport standards:

Execsys.dbms_tts.transport_set_check ('tablespace_name',true)

Select * from sys.transport_set_violation

If there is no row selection, the tablespace contains only table data and is self-contained. Some non-self-contained tablespaces, such as data tablespaces and indexed tablespaces, can be transferred together.

The following are brief steps to use. If you want to refer to the detailed usage, you can also refer to the ORACLE online help.

1. Set the tablespace to read-only (assume the tablespace names are APP_Data and APP_Index)

Alter tablespace app_data read only

Alter tablespace app_index read only

two。 Issue the EXP command

SQL > host exp userid= "sys/password assysdba"

Transport_tablespace=ytablespace= (app_data, app_index)

What should be noted above is that

In order to execute EXP,USERID in SQL, three quotation marks must be used, and care must be taken to avoid the use of "/" in UNIX.

On and after 816, sysdba must be used to operate

This command must be placed on one line in SQL (this is because the display problem is placed on two lines)

3. Copy the data file to another location, the target database

It can be cp (unix) or copy (windows) or transfer files over ftp (be sure to be in bin mode)

4. Set the local tablespace to read and write

5. Attach the data file to the target database

Imp file=expdat.dmp userid= "" sys/passwordas sysdba "transport_tablespace=y" datafile= (c:\ temp\ app_data,c:\ temp\ app_index) "

6. Set the target database tablespace to read and write

Alter tablespace app_data read write

Alter tablespace app_index read write

Ways to optimize EXP/IMP:

When the amount of data that requires exp/imp is relatively large, this process takes a long time, and we can use some methods to optimize the operation of exp/imp.

Exp: using direct path direct=y

Oracle bypasses the sql statement processing engine, reads data directly from the database file, and then writes to the export file.

It can be observed in the export log: exp-00067:table xxx will be exported in conventional path

If you do not use a direct path, you must ensure that the value of the buffer parameter is large enough.

Some parameters are incompatible with direct=y, so you cannot export a removable tablespace with a direct path or a subset of the database with the query parameter.

When the imported and exported database runs under different os, the values of recordlength parameters must be consistent.

Imp: optimize in the following ways

1. Avoid disk sorting

Set sort_area_size to a large value, such as 100m

two。 Avoid waiting for log switching

Increase the number of redo log groups and increase the log file size.

3. Optimize log buffer

For example, increase the capacity of log_buffer by 10 times (no more than 5m at most)

4. Insert and submit using arrays

Commit = y

Note: arrays cannot handle tables that contain LOB and LONG types. For such a table, if you use commit = y, a commit will be performed each time a row is inserted.

5. Reduce the size of the redo log using NOLOGGING

Specify the parameter indexes=n on import, import only data and ignore index, create index through script after importing data, and specify NOLOGGING option

Export / Import and character set

When we import and export data, we should pay attention to the problem of character set. In the process of EXP/IMP, we need to pay attention to the parameters of four character sets: the client character set of the export side, the database character set of the export side, the client character set of the import side, and the database character set of the import side.

We first need to look at these four character set parameters.

View the information about the character set of the database:

SQL > select * fromnls_database_parameters

PARAMETER VALUE

-

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS.

NLS_CHARACTERSET ZHS16GBK

NLS_CALENDAR GREGORIAN

NLS_DATE_FORMAT DD-MON-RR

NLS_DATE_LANGUAGE AMERICAN

NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM

NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM

NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM

NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM

NLS_DUAL_CURRENCY $

NLS_COMP BINARY

NLS_NCHAR_CHARACTERSET ZHS16GBK

NLS_RDBMS_VERSION 8.1.7.4.1

NLS_CHARACTERSET:ZHS16GBK is the character set of the current database.

Let's take a look at the character set information of the client:

The parameter NLS_LANG=_. of the client character set

Language: specifies the language used by the oracle message and the display of the day and month in the date.

Territory: specify the format of currency and numbers, the region, and the habit of calculating the week and date.

Characterset: controls the character set used by client applications. Usually set or equal to the code page of the client. Or set it to UTF8 for unicode applications.

In windows, querying and modifying NLS_LANG can be done in the registry:

HKEY_LOCAL_MACHINE\ SOFTWARE\ Oracle\ HOMExx\

Xx refers to the system number when there is more than one Oracle_HOME.

In unix:

$env | grep NLS_LANG

NLS_LANG=simplified chinese_china.ZHS16GBK

Modifications are available:

$export NLS_LANG=AMERICAN_AMERICA.UTF8

It is usually best to set the client character set to the same as the database side when exporting. When you import data, there are two main situations:

(1) the source database and the target database have the same character set settings.

At this point, you only need to set the client NLS_LANG on the export and import side to be equal to the database character set.

(2) the character sets of the source database and the target database are different.

First, set the NLS_LANG of the export client to be consistent with the database character set of the export, export the data, then set the NLS_LANG of the import client to the same as the export, and import the data, so that the conversion only occurs on the database side and only happens once.

In this case, only when the character set of the database on the import side is a strict superset of the character set of the database on the export side, the data can be completely derived successfully, otherwise, there may be data inconsistencies or garbled codes.

Problems with different versions of EXP/IMP

Generally speaking, it is not a big problem to import from a low version to a high version. The trouble is to import the high version of data into a low version. Before Oracle9i, the EXP/IMP between different versions of Oracle can be solved by the following methods:

1. Run the bottom version of catexp.sql on the high version database

2. Use the lower version of EXP to export the higher version of data.

3. Import the database into the lower version of the database using the lower version of IMP

4. Rerun the high version of the catexp.sql script on the high version database.

But in 9i, the above method does not solve the problem. If you use an earlier version of EXP/IMP directly, the following error occurs:

EXP-00008: orACLE error% lu encountered

OrA-00904: invalid column name

This is already a published BUG, which can not be solved until Oracle10.0. The BUG number is 2261722. You can go to METALINK to see more information about this BUG.

BUG belongs to BUG, and we still have to do our work. Before we have the support of Oracle, we will solve it ourselves. Execute the following SQL in Oracle9i to rebuild the exu81rls view.

Create orREPLACE view exu81rls

(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)

AS select u.name, o.name, r.pname,r.pfschma, r.ppname, r.pfname

Decode (bitand (r.stmtexact type page1), 0reparentials and reproducibles')

| decode (bitand (r.stmtcompatible type page2), 0reparentials and insertpens') |

| decode (bitand (r.stmtpopular type page4), 0requnzixxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

| decode (bitand (r.stmtdistinct type page8), 0reparentific writing type page1') |

R.check_opt, r.enable_flag

DECODE (BITAND (r.stmt_type, 16), 0,0,1)

From user$ u, obj$ o, rls$ r

Where u.user# = o.owner#

And r.obj# = o.obj#

And (uid = 0 or

Uid = o.owner# or

Exists (select * from session_roles whererole='Select_CATALOG_ROLE')

)

/

Grant select on sys.exu81rls to public

/

You can use EXP/IMP across versions, but you must use the EXP and IMP versions correctly:

1. Always use the version of IMP to match the version of the database, such as: to import to 817, use the IMP tool of 817.

2. Always use the version of EXP to match the lowest version of the two databases. For example, if you import from 9201 to 817, use the EXP tool of 817.

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.