- 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）