An explanation of ORACLE exp/imp usage

Author: Eclipse

Import/Export is the oldest surviving Oracle two command-line tool, in fact I never think Exp/imp is a good backup method, the correct argument is that Exp/imp can only be a good dump tool, especially in the small database dump, table space migration, table extraction, Detection logic and physical conflict, etc., there is no small credit. Of course, we can also use it as a logical secondary backup of a small database after a physical backup, and it's a good idea. Exp/imp is increasingly struggling with the emergence of increasingly large databases, especially terabytes of databases and more and more data warehouses, and this time, database backups have shifted to Rman and third-party tools. The following describes the use of Exp/imp.
How to make Exp's help appear in a different character set: Set Nls_lang=simplified CHINESE_CHINA.ZHS16GBK, by setting environment variables, you can have exp help display in Chinese if set Nls_lang=american _america. Character set, then Help is in English.

Program code

All parameters of exp (default values for parameters in parentheses):
USERID username/password such as: userid=duanl/duanl
Full export entire database (N)
Size of buffer data buffers
Owner user Name list, which user object you want to export, use Owner=username
File output files (expdat. DMP)
TABLES table Name list, specifying exported table names, such as: Tables=table1,table2
COMPRESS Importing a extent (Y)
Length of RecordLength IO record
GRANTS Export Permissions (Y)
Inctype Incremental Export Type
INDEXES Export Index (Y)
RECORD Tracking Incremental Export (Y)
Rows Export Data rows (Y)
Parfile parameter file name, if you exp parameters a lot, can be saved as parameter files.
CONSTRAINTS export Constraints (Y)
Consistent cross-table consistency
Log file on screen output
STATISTICS Analysis Object (ESTIMATE)
Direct Path (N)
TRIGGERS Export Trigger (Y)
FEEDBACK shows the progress of each x line (0)
FILESIZE maximum size of each dump file
QUERY selects the clause that exports a subset of tables
The following keywords are only available for transportable tablespaces
Transport_tablespace exporting the Transportable tablespace metadata (N)
Tablespaces List of tablespaces to be transferred

Program code

All parameters of the IMP (default values for parameters in parentheses):
UserID username/Password
Full import of entire file (N)
Buffer size of data buffers
Fromuser Owner User Name list
File input files (expdat. DMP)
Touser List of user names
SHOW only lists file contents (N)
List of TABLES table names
IGNORE Ignore Create error (N)
Length of RecordLength IO record
GRANTS Import Permissions (Y)
Inctype Incremental Import Type
INDEXES Import Index (Y)
Commit commit array Insert (N)
Rows Import data rows (Y)
Parfile parameter file name
Log file on screen output
CONSTRAINTS Import Limit (Y)
DESTROY Overlay tablespace data file (N)
Indexfile Writing table/index information to the specified file
Skip_unusable_indexes Skip maintenance of indexes that are not available (N)
ANALYZE executing the ANALYZE statement in the dump file (Y)
FEEDBACK shows the progress of each x line (0)
Toid_novalidate skips a checksum of the specified type ID
FILESIZE maximum size of each dump file
Recalculate_statistics recalculation of statistical values (N)
The following keywords are only available for transportable tablespaces
Transport_tablespace import of transportable tablespace metadata (N)
Tablespaces the tablespace that will be transferred to the database
Datafiles data files that will be transferred to the database
Tts_owners has users who can transmit data in a table-space set

Description of the increment parameter: The increment of exp/imp is not a real increment, so it is best not to use it.


How to use:
Exp Parameter_name=value or exp parameter_name= (value1,value2 ...)
You can see all the help as soon as you enter the parameter help=y.

Exp Common Options
1.FULL, this is used to export the entire database, when used in conjunction with rows=n, you can export the entire database structure. For example:
Exp userid=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 an exp object. The owner defines the object that exports the specified user; table specifies the name of the exp table, for example:
Exp userid=test/test file=./db_str.dmp Log=./db_str.log owner=duanl
Exp userid=test/test file=./db_str.dmp Log=./db_str.log table=nc_data,fi_arap
3.BUFFER and feedback, when exporting more data, I will consider setting these two parameters. For example:
Exp userid=test/test file=yw97_2003.dmp log=yw97_2003_3.log feedback=10000 buffer=100000000 Tables=WO4,OK_YT
4.FILE and log, these two parameters specify the DMP name and log name of the backup, including the file name and directory, as shown in the example above.
The 5.COMPRESS parameter does not compress the contents of the exported data. The storage statement that controls how the exported object is generated. The default value is Y, which uses the default value, and the init extent of the object's storage statement equals the sum of the extent of the currently exported object. It is recommended to use Compress=n.
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,f5 filesize=2g Owner=scott
This will create a series of files, such as F1.dmp, F2.DMP, each size is 2G, if the total amount of exports is less than 10G
Exp does not have to create f5.bmp.

IMP Common Options
1, Fromuser and Touser, using them to import data from one schema into another schema. For example: Let's say we do exp when we export the object as test, 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, where the IGNORE parameter will ignore the existence of the table, continue to import, this is useful for the need to adjust the table storage parameters, we can first according to the actual situation with reasonable storage parameters to build a table, and then directly import data. While grants and indexes indicate whether to import authorizations and indexes, if you want to rebuild the index with new storage parameters, or to speed up to the speed, we can consider setting indexes to n, and grants is generally y. For example: Imp userid=test1/test1 file=expdat.dmp fromuser=test1 touser=test1 indexes=n

Table Space Transfer
Tablespace transfer is a new way to quickly move data between databases by attaching a format data file on a database to another database instead of exporting the data to a DMP file, which is useful in some cases, because the transport table space moves the data as fast as the file is copied.
There are some rules about transport table space, namely:
• The source and destination databases must be running on the same hardware platform.
• The source database and the target database must use the same character set.
• Source and target databases must have the same size data block
• The destination database cannot have a tablespace with the same name as the migration table space
· SYS objects cannot be migrated
• You must transfer the self-contained set of objects
• Some objects, such as materialized views, function-based indexes, etc. cannot be transmitted
You can use the following methods to detect whether a table space or a set of table spaces conforms to the transport standard:
exec sys.dbms_tts.transport_set_check (' Tablespace_name ', true);
SELECT * from Sys.transport_set_violation;
If there is no row selection, the table space contains only table data and is self-contained. For some non-self-contained table spaces, such as data table spaces and Index table spaces, you can transfer them together.
The following is a brief use step, and you can refer to the Oracle online Help if you want to refer to the detailed usage method.
1. Set Tablespace to read-only (assuming tablespace name App_Data and App_index)
Alter Tablespace App_Data Read only;
Alter Tablespace App_index Read only;
2. Issue exp Command
Sql>host exp userid= "" "Sys/password as Sysdba" ""
Transport_tablespace=y tablespace= (App_Data, App_index)
The above should be noted that
• In order to execute Exp,userid in SQL, you must use three quotation marks, and you must also avoid using "/" in Unix.
• After 816 and later, you must use SYSDBA to operate
• This command must be placed on one line in SQL (this is because the display problem is placed in two lines)
3. Copying data files to another location, i.e. the target database
can be either CP (Unix) or copy (Windows) or transfer files via FTP (must be in bin mode)
4. Set the local table space to read and write
5. Attach the data file to the target database
Imp file=expdat.dmp userid= "" "Sys/password as Sysdba" "" Transport_tablespace=y "datafile= (c:\temp\app_data,c:\temp\ App_index) "
6. Set the target database table space 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 required for Exp/imp is large, the process takes longer, 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 the data directly from the database file, and then writes the export file.
Can be observed in the export log: exp-00067:table XXX Will is 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, you cannot export a moveable tablespace with a direct path, or you can export a subset of a database with the query parameter.
When importing and exporting a database running under a different OS, you must ensure that the value of the RecordLength parameter is consistent.

IMP: Optimize by following several ways
1. Avoid disk sorting
Set the sort_area_size to a larger value, such as 100M
2. Avoid log switching waits
Increase the number of redo log groups and increase the log file size.
3. Tuning the log buffers
For example, expand the log_buffer capacity by 10 times times (maximum not more than 5M)
4. Using array insertion and submission
Commit = Y
Note: Arrays cannot handle tables that contain LOB and long types, and for such a table, if you use commit = Y, a commit is executed every time you insert a row.
5. Reduce the redo log size using the Nologging method
Specify parameter indexes=n on import, import data only and ignore index, create index from script after data is finished, specify nologging option

export/import with character set
When importing and exporting data, we need to be aware of the problem with the character set. In the EXP/IMP process we need to note the parameters of the four character sets: The client character set of the export side, the export-side database character set, the client character set of the import side, and the import-side database character set.
We first need to look at these four character set parameters.
To view information about the character set of a database:
Sql> select * from Nls_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 look at the character set information on the client side:
The parameters of the client character set nls_lang=_< territory.
Language: Specifies the language in which Oracle messages are used, and the day and month of the date display.
Territory: Specify currency and number formats, regions, and the habit of calculating week and date.
Characterset: Controls the character set used by the client application. Usually set or equal to the client's code page. Or, for Unicode apps, set to UTF8.
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 are multiple oracle_home.

In Unix:
$ env|grep Nls_lang
Nls_lang=simplified Chinese_china. Zhs16gbk

Modifications available:
$ export Nls_lang=american_america. UTF8

It is usually best to set the client charset to the same as the database side when exporting. When data is imported, there are two main cases:
(1) The source and target databases have the same character set settings.
At this point, simply set the export and import side of the client Nls_lang equal to the database character set.
(2) The source database and the target database character set are different.
The Nls_lang of the export client is set to the same as the database character set on the export side, the data is exported, then the Nls_lang of the import client is set to the same as the export side, and the data is imported so that the conversion occurs only on the database side and only once.
In this case, only if the import-side database character set is a strict superset of the export-side database character set, the data can be fully successful, otherwise there may be inconsistent or garbled data.

Exp/imp issues with different versions
In general, the issue of importing from a lower version to a higher version is not a problem, but it is troublesome to import the high version of the data into the lower version, and before Oracle9i, the exp/imp between different versions of Oracle can be resolved by the following method:
1. Run the bottom version of Catexp.sql on the high-version database;
2, use the lower version of EXP to export the high version of the data;
3. Use the lower version of IMP to import the database into the lower version database;
4. Rerun the high-version Catexp.sql script on the high-version database.
But in 9i, the above method does not solve the problem. If you use the lower version of Exp/imp directly, the following error will occur:
Exp-00008:oracle Error%lu encountered
Ora-00904:invalid Column Name
This is already a published bug, need to wait until Oracle10.0 to solve, the bug number is 2261722, you can go to Metalink to see more information about this bug.
Bugs are bugs, and our job is to do it, and we'll fix it ourselves before we have Oracle support. Perform the following SQL Rebuild Exu81rls view in oracle9i.
Create or REPLACE 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.stmt_type,1), 0, ', ' Select, ')
|| Decode (Bitand (r.stmt_type,2), 0, ', ' Insert, ')
|| Decode (Bitand (r.stmt_type,4), 0, ', ' Update, ')
|| Decode (Bitand (r.stmt_type,8), 0, ', ' Delete, '),
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 where role= ' Select_catalog_role ')
)
/
Grant SELECT on Sys.exu81rls to public;
/

You can use exp/imp across versions, but the versions of EXP and IMP must be used correctly:
1. Always use the version of IMP to match the version of the database, such as: to import into 817, use the 817 IMP tool.
2. Always use Exp's version to match the lowest version in two databases, such as import from 9201 to 817, use the 817 version of the Exp tool.

An explanation of ORACLE exp/imp usage