Special lab exp & imp

Basic syntax and example: 1. Exp:

There are three main methods (complete, user, table)
1. Complete:
Exp icdmain/ICD rows = y indexes = n compress = n buffer = 65536 feedback = 100000 full = y file = exp _. dmp log = exp. Log

To perform full export, you must have special permissions.
2. User Mode:
Exp icdmain/ICD owner = icdmain rows = y indexes = n compress = n buffer = 65536 feedback = 100000 file = exp. dmp log = exp. Log
3. Table mode:
Exp icdmain/ICD rows = y indexes = n compress = n buffer = 65536 feedback = 100000 volsize = 0 file = exp. dmp log = exp. Log tables = tab1, tab2, tab3

Parameter

Keyword description (default) keyword description (default)

--------------------------------------------------------------------------

Userid username/password full export entire file (N)

Buffer size of data buffer owner list of owner usernames

File output files (expdat. dmp) Tables List of table names

Compress import into one extent (y) recordlength length of Io record

Grants export grants (y) inctype incremental export type

Indexes export indexes (y) record track incr. Export (y)

Direct direct path (n) triggers export triggers (y)

Log file of screen output statistics analyze objects (estimate)

Rows export data rows (y) parfile parameter filename

Consistent cross-Table consistency (n) Constraints export constraints (y)

Object_consistent transaction set to read only during object Export (N)

Feedback Display progress every X rows (0)

Filesize maximum size of each dump file

Flashback_scn used to set session snapshot back

Flashback_time time used to get the SCN closest to the specified time

Query select clause used to export a subset of a table

Resumable suspend when a space related error is encountered (N)

Resumable_name text string used to identify resumable statement

Resumable_timeout wait time for resumable

Tts_full_check perform full or partial dependency check for TTS

Volsize number of bytes to write to each Tape Volume

Tablespaces list of tablespaces to export

Transport_tablespace export transportable tablespace metadata (N)

Template Name which invokes IAS mode Export

 

2. IMP:

There are three modes (full, user, table) that correspond to exp
1. Complete:
IMP system/Manager Buffer = 64000 file = c: \ full. dmp full = y
2. User Mode:
IMP Sonic/sonic buffer = 64000 file = c: \ sonic. dmp fromuser = sonic touser = sonic
In this way, all the sonic objects are imported into the file. You must specify the fromuser and touser parameters to import data.
3. Table mode:
IMP Sonic/sonic buffer = 64000 file = c: \ sonic. dmp owner = sonic tables = (sonic)
In this way, the sonic table is imported.

Parameter

Keyword description (default) keyword description (default)

--------------------------------------------------------------------------

Userid username/password full import entire file (N)

Buffer size of data buffer fromuser list of owner usernames

File input files (expdat. dmp) touser list of usernames

Show just list file contents (n) Tables List of table names

Ignore ignore create errors (n) recordlength length of Io record

Grants import grants (y) inctype incremental Import Type

Indexes import indexes (y) Commit commit array insert (N)

Rows import data rows (y) parfile parameter filename

Log file of screen output constraints import constraints (y)

Destroy overwrite tablespace data file (N)

Indexfile write table/index info to specified file

Skip_unusable_indexes skip maintenance of unusable indexes (N)

Feedback Display progress every X rows (0)

Toid_novalidate skip validation of specified type IDs

Filesize maximum size of each dump file

Statistics import precomputed statistics (always)

Resumable suspend when a space related error is encountered (N)

Resumable_name text string used to identify resumable statement

Resumable_timeout wait time for resumable

Compile compile procedures, packages, and functions (y)

Streams_configuration import streams General metadata (y)

Streams_instantiation import streams instantiation metadata (N)

Volsize number of bytes in file on each volume of a file on tape

The following keywords only apply to transportable tablespaces

Transport_tablespace import transportable tablespace metadata (N)

Tablespaces to be transported into database

Datafiles to be transported into database

Tts_owners users that own data in the transportable tablespace set

Example

4.1 create a tablespace in Oracle and create a user

// Create a temporary tablespace

Create temporary tablespace test_temp

Tempfile '/u01/APP/Oracle/oradata/orcl/test_temp01.dbf'

Size 32 m autoextend on next 32 m maxsize 2048 m

Extent management local;

// Create a data table space

Create tablespace test_data Logging

Datafile '/u01/APP/Oracle/oradata/orcl/test_data01.dbf'

Size 32 m autoextend on

Next 32 m maxsize 2048 m

Extent management local;

// Create a user and specify the tablespace

Create User Username identified by password

Default tablespace test_data

Temporary tablespace test_temp;

// Grant permissions to users

Grant connect, resource to username;

First, create a user and table space. The user name is David and the password is David. Create a table in this table space: tianle. Insert some data at will. The Code is as follows:

SQL> Create tablespace test_data

3 datafile '/u01/APP/Oracle/oradata/orcl/test_data01.dbf'

4 size 5 m;

Tablespace created.

SQL> create user David identified by David default tablespace test_data;

SQL> grant connect, resource to David;

SQL> conn David/David

SQL> Create Table tianle (ID number, content varchar2 (100 ));

SQL> set wrap off

SQL> column ID format A20;

SQL> column content format A50;

4.2 table mode backup and recovery

Backup:

[[Email protected] orcl] $ exp David/David rows = y indexes = n compress = n buffer = 65536 file = exp_tianle_090101.dmp log = exp_tianle_090101.log tables = (tianle );

Recovery:

[[Email protected] orcl] $ imp David/David fromuser = David touser = David rows = y indexes = n commit = y buffer = 65536 file = exp_tianle_090101.dmp log = partition tables = (tianle ); ­

4.3 user mode backup and recovery

Backup:

[[Email protected] orcl] $ exp David/David owner = David rows = y indexes = n compress = n buffer = 65536 file = exp_david _ 090101.dmp log = exp_david _090101.log; ­

Recovery:

[[Email protected] orcl] $ imp David/David fromuser = David touser = David rows = y indexes = n commit = y buffer = 65536 file = exp_tianle_090101.dmp log = exp_tianle_090101.log;

4.4 full mode backup and recovery

Backup:

[[Email protected] orcl] $ exp David/David rows = y indexes = n compress = n buffer = 65536 full = y file = exp_fulldatabase_090101.dmp log = exp_fulldatabase_090101.log;

Restore

[[Email protected] orcl] $ imp David/David rows = y indexes = n commit = y full = y ignore = y buffer = 65536 file =/tmp/exp_fulldatabase_090101.dmp log =/tmp /imp. log;

Common exp options
1. Full. This is used to export the entire database. When rows = N is used together, the structure of the entire database can be exported. 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 the exp object. The owner defines to export the object of the specified user; the table specifies the table name of the exp, 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 a large amount of 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. The two parameters respectively specify the DMP name and log name of the backup, including the file name and directory. For example, see the preceding figure.

5. The compress parameter does not compress the exported data. Controls how the storage statement of the exported object is generated. The default value is Y. The default value is used. The init Extent of the Object Storage statement is equal to the total extent of the current exported object. Compress = N is recommended.

ICommon MP options
1. Use fromuser and touser to import data from one schema to another. For example, if we export the object 'test' as the exp, we want to import the object to the user:
IMP userid = test1/test1 file = expdat. dmp fromuser = test1 touser = test1

2. Ignore, grants, and indexes. The ignore parameter ignores the existence of the table and continues the import. This is useful when you need to adjust the storage parameters of the table, we can create a table with reasonable storage parameters according to the actual situation, and then import the data directly. Grants and indexes indicate whether to import authorization and indexes. If you want to use the new storage parameter to re-create the index, or to speed up the import, you can consider setting indexes to n, grants is generally y. Example: IMP userid = test1/test1 file = expdat. dmp fromuser = test1 touser = test1 indexes = N

Methods To optimize exp/IMP:
When the amount of data required for exp/imp is large, this process takes a long time. We can use some methods to optimize exp/IMP operations.
Exp: Use the direct path direct = y
Oracle will avoid the SQL statement processing engine, read data directly from database files, and then write the exported file.
The exp-00067: Table XXX will be exported in conventional path can be observed in the export log
If the direct path is not used, ensure that the value of the buffer parameter is large enough.
Some parameters are incompatible with direct = y and cannot be used to export movable tablespace directly or use query parameters to export database subsets.
When the imported and exported database runs under different operating systems, the recordlength parameter value must be consistent.

IMP: optimization through the following channels
1. Avoid disk sorting
Set sort_area_size to a large value, such as 100 m.
2. Avoid waiting for log Switching
Increase the number of redo log groups and the size of log files.
3. optimized the log buffer.
For example, increase the log_buffer capacity by 10 times (the maximum is 5 MB)
4. insert and submit Arrays
Commit = y
Note: arrays cannot process tables of the lob and long types. For such tables, if commit = y is used, a commit is executed for each row inserted.
5. Use nologging to reduce the size of redo logs
Specify the parameter indexes = N during the import. Only import data and ignore the index. After importing the data, create the index using the script and specify the nologging option.

Export/import and Character Set
When importing and exporting data, we should pay attention to the character set issue. In the exp/IMP process, we need to pay attention to the four character set parameters: the client character set at the export end, the database character set at the export end, the client character set at the import end, and the database character set at the import end.
First, we need to view the parameters of these four character sets.
View the character set information of the 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 check the character set information of the client:
The parameter nls_lang =_< territory>.
Language: Specifies the language in which Oracle messages are sent, including the date, day, And month.
Territory: Specifies the format of currency and number, 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 client's code page. Or set the Unicode application to utf8.
In Windows, you can query and modify nls_lang in the registry:
HKEY_LOCAL_MACHINE \ SOFTWARE \ oracle \ homexx \
XX indicates the system number when multiple ORACLE_HOME exists.

In Unix:
$ ENV | grep nls_lang
Nls_lang = simplified chinese_china.zhs16gbk
Change availability:
$ Export nls_lang = american_america.utf8

Generally, it is best to set the character set of the client to the same as that of the database during export. When importing data, there are two main situations:
(1) The source database and target database have the same character set settings.
In this case, you only need to set the Export and Import client nls_lang to be equal to the database character set.
(2) The character sets of the source and target databases are different.
First, set the export client's nls_lang to be consistent with the export database character set, export data, and then set the import client's nls_lang to be consistent with the export client, import data, in this way, the conversion only occurs on the Database End and only occurs once.
In this case, data can be completely imported only when the character set of the importing database is a strict superset of the character set of the exporting database. Otherwise, data inconsistency or garbled characters may occur.

EXP/IMP problems of different versions
Generally, it is not a problem to import data from a lower version to a later version. The trouble is to import data from a later version to a lower version. Before Oracle9i, EXP/IMP between Oracle versions can be solved through the following methods:
1. Run catexp. SQL of the base version on the High Version database;
2. Use exp of a lower version to export data of a later version;
3. Use the imp of a lower version to import the database to a lower version;
4. Run the catexp. SQL script in the later version of the database.
But in 9i, the above method cannot solve the problem. If you use a lower version of exp/IMP directly, the following error occurs:
EXP-00008: Oracle error % lu encountered
OrA-00904: Invalid column name
This is a published bug that can be solved only after oracle10.0. The bug number is 2261722. You can go to Metalink to view details about this bug.
Bug is a bug. We still need to do our work. We should solve the bug by ourselves before we have Oracle Support. Execute the following SQL statement in Oracle9i to recreate the exu81rls view.
Create or replace view exu81rls
(Objown, objnam, policy, polown, polsch, polfun, mongots, 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 you must use the exp and IMP versions correctly:
1. The IMP version is always used to match the database version. For example, to import data to analyticdb 817, use the IMP tool of analyticdb 817.
2. Always use the EXP version to match the lowest version of the two databases. For example, if you import data from MySQL 9201 to MySQL 817, use the exp tool of MySQL 817.