• 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=testExport: Release 11.2.0.4.0 - Production on Thu Mar 12 23:07:45 2020Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in AL32UTF8 character set and AL16UTF16 NCHAR character setAbout 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 TESTAbout 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 statisticsExport 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=TTB01Export: Release 11.2.0.4.0 - Production on Thu Mar 12 23:23:05 2020Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in AL32UTF8 character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table                          TTB01       5000 rows exportedExport 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=testImport: Release 11.2.0.4.0 - Production on Thu Mar 12 23:49:14 2020Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport 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 importedImport 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=TTB02Import: Release 11.2.0.4.0 - Production on Thu Mar 12 23:58:57 2020Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport 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 importedImport terminated successfully without warnings.

  • EXP-00091: Exporting questionable statistics
    呈现EXP-00091: Exporting questionable statistics的起因是因为客户端的字符集和数据库的字符集不统一，依据数据库的字符集批改客户端的字符集即可

    SQL> col value for a20SQL> 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=testImport: Release 11.2.0.4.0 - Production on Fri Mar 13 00:02:32 2020Copyright (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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORA-39002: invalid operationORA-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）