关于oracle:Oracle-导入导出工具的基本使用

exp/imp 的应用办法次要有三种形式（齐全、用户、表），须要 dba 权限

EXP 齐全

[oracle@ystdb11g ~]$ exp system@ystdb file=/backups/full_exp_20200313.dmp 
log=/backups/full_exp_20200313.log full=y

EXP 用户

[oracle@ystdb11g ~]$ exp test@ystdb file=/backups/test_exp_20200313.dmp 
log=/backups/test_exp_20200313.log owner=test

Export: Release 11.2.0.4.0 - Production on Thu Mar 12 23:07:45 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export TEST's tables via Conventional Path ...
. . exporting table                          TTB01       5000 rows exported
. . exporting table                          TTB02       6000 rows exported
. . exporting table                          TTB03       7000 rows exported
. . exporting table                          TTB04      10000 rows exported
. . exporting table                          TTB05      15000 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

EXP 表

[oracle@ystdb11g ~]$ exp test@ystdb file=/backups/test_ttb01_exp_20200313.dmp 
log=/backups/test_ttb01_exp_20200313.log tables=TTB01

Export: Release 11.2.0.4.0 - Production on Thu Mar 12 23:23:05 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                          TTB01       5000 rows exported
Export terminated successfully without warnings.

IMP 齐全

[oracle@ystdb11g ~]$ imp system@ystdb file=/backups/full_exp_20200313.dmp 
log=/backups/full_imp_20200313.log full=y

IMP 用户

[oracle@ystdb11g ~]$ imp test@ystdb file=/backups/test_exp_20200313.dmp 
log=/backups/test_imp_20200313.log fromuser=test touser=test

Import: Release 11.2.0.4.0 - Production on Thu Mar 12 23:49:14 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. . importing table                        "TTB01"       5000 rows imported
. . importing table                        "TTB02"       6000 rows imported
. . importing table                        "TTB03"       7000 rows imported
. . importing table                        "TTB04"      10000 rows imported
. . importing table                        "TTB05"      15000 rows imported
Import terminated successfully without warnings.

IMP 表

[oracle@ystdb11g backups]$ imp test@ystdb file=/backups/test_ttb02_exp_20200313.dmp 
log=/backups/test_ttb02_imp_20200313.log tables=TTB02

Import: Release 11.2.0.4.0 - Production on Thu Mar 12 23:58:57 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table                        "TTB02"       6000 rows imported
Import terminated successfully without warnings.

EXP-00091: Exporting questionable statistics
呈现 EXP-00091: Exporting questionable statistics 的起因是因为客户端的字符集和数据库的字符集不统一，依据数据库的字符集批改客户端的字符集即可
```
SQL> col value for a20
SQL> select * from nls_database_parameters where parameter like 'NLS_CHAR%';

PARAMETER               VALUE
------------------------------ --------------------
NLS_CHARACTERSET           AL32UTF8
```
Linux 零碎长期批改，永恒须要在 Oracle 用户的.bash_profile 文件中减少
```
[oracle@ystdb11g ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
```
Windows 零碎长期批改，永恒批改须要增加零碎环境环境变量
```
C:\Users\Falkon>set NLS_LANG=AMERICAN_AMERICA.AL32UTF8
```

如果导出的 dmp 文件没有明确的命名和示意，那么咱们将不能确认导出的形式是 exp 还是 expdp，在应用 impdp 导入的时候将会报如下谬误

[oracle@ystdb11g backups]$ impdp test@ystdb file=/backups/test_exp_20200313.dmp 
log=/backups/test_imp_20200313.log fromuser=test touser=test

Import: Release 11.2.0.4.0 - Production on Fri Mar 13 00:02:32 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39088: file name cannot contain a path specification

对于 exp buffer 的阐明
官网阐明链接 https://docs.oracle.com/cd/A8…
Default: operating system-dependent. See your Oracle operating system-specific documentation to determine the default value for this parameter.

Specifies the size, in bytes, of the buffer used to fetch rows. As a result, this parameter determines the maximum number of rows in an array fetched by Export. Use the following formula to calculate the buffer size:

buffer_size = rows_in_array * maximum_row_size

If you specify zero, the Export utility fetches only one row at a time.
Tables with LONG, LOB, BFILE, REF, ROWID, LOGICAL ROWID, DATE, or type columns are fetched one row at a time.
Note: The BUFFER parameter applies only to conventional path Export. It has no effect on a direct path Export.

Example
This section shows an example of how to calculate buffer size.
Consider that the following table is created:

create table sample (name varchar(30), weight number);

The maximum size of the name column is 30, plus 2 bytes for the indicator. The maximum size of the weight column is 22 (the size of the internal representation for Oracle numbers), plus 2 bytes for the indicator.
Therefore, the maximum row size is 56 (30+2+22+2).
To perform array operations for 100 rows, a buffer size of 5600 should be specified.

对于数据量较大的能够通过设置 buffer 来减少导出的速度，具体的计算形式：须要导出的行数 *（每个字段的字节数 +2）

站内搜索