[bbk5399] 第97集 -第12章 -数据移植 03

Data Pump Export using Database Control

Data Pump Export Example:Basic Options

Data Pump Export Example:Advanced Options

Data Pump Export Example:Files

Data Pump Export Example:Schedule

Data Pump Export Example:Review

Data Pump Import Example:impdp

Data Pump Import:Transformations

 

在原来的导入导出过程中，假如导出的表所在表空间为system_tablespace,导入时导入到test用户下，而test用户的默认表空间为app_data，然后使用imp命令导入时，还是会将新导入的表放在system_tablespace当中。而使用impdp，则可以改变这种映射关系，导入到指定的表空间当中.

对于数据映射REMAP_DATA功能，需要单独编写PL/SQL程序来实现；比如说：从HR用户下导出employees表中的数据，再次导入到另外一个系统的时候，需要将提成为空的用户，多给1000元抚恤金，要实现此功能就可以通过你REMAP DATA配合PL/SQL程序实现

Data Pump Legacy Mode

在oracle 9i中，如果使用exp导出的文件，在10g中使用impdp导入到数据库中，oracle是无法识别的;

查看oracle目录对象与实际物理路径的对应关系

SQL> col DIRECTORY_PATH format a60
SQL> select * from dba_directories;

OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ ------------------------------------------------------------
SYS                            SUBDIR                         /RealData/oracle/demo/schema/order_entry//2002/Sep
SYS                            SS_OE_XMLDIR                   /RealData/oracle/demo/schema/order_entry/
SYS                            LOG_FILE_DIR                   /RealData/oracle/demo/schema/log/
SYS                            DATA_FILE_DIR                  /RealData/oracle/demo/schema/sales_history/
SYS                            XMLDIR                         /ade/b/2125410156/oracle/rdbms/xml
SYS                            MEDIA_DIR                      /RealData/oracle/demo/schema/product_media/
SYS                            DATA_PUMP_DIR                  /RealData/admin/DATACENTER/dpdump/
SYS                            ORACLE_OCM_CONFIG_DIR          /RealData/oracle/ccr/state

8 rows selected.

Migration with Data Pump Legacy Mode

• Assistance in transitioning from imp and exp utilities to impdp and expdp utilities 
• Data Pump in legacy mode:
  • Encounters unique imp or exp parameter and enters legacy mode
  • Attempts to map the old syntax to the new syntax
  • Displays new syntax
  • Exits legacy mode

Best practice tip:Oracle strongly recommends that you view the new syntax and make script changes as time permits. 

Data Pump Legacy Mode

The Data pump export and import utilities:

• Read and write files only in Data Pump format
• Accept exp and imp utility commands in legacy mode
• Include legacy mode parameters that:
  • Can be identical to the new syntax:
    • FILESIZE=integer [B|K|M|G] 
  • Can be similar
    • QUERY=query_clause
  • Are ignored,when the command is superceded by Data Pump defaults
    • BUFFER=integer
    • COMPRESS={y|n}
    • DIRECT={y|n}
  • Cause an error when old and new syntax is mixed
• Legacy mode parameters
• Are mapped to Data Pump parameters,if possible:
  • • consistent={y|n}->FLASHBACK_TIME
    • GRANTS=n->EXCLUDE=CONSTRAINTS
    • INDEXES=n->EXCLUDE=INDEX
    • LOG=filename->LOGFILE=filename
    • FILE=filename->dumpfile=directory-object:filename
  • Can be similar,but not identical
    • FEEDBACK=integer->STATUS
  • Cause an error when incompatible with new Data Pump:
    • VOLSIZE=integer

Managing File Locations

• Original exp and imp utilities:Fully qualified file names
• Data Pump directory object for file locations
  • Default(in prior versions) :DATA_PUMP_DIR parameter
  • New optional DATA_PUMP_DIR_schema-name directory object
  • Managed with the CREATE DIRECTORY and GRANT SQL commands
  • Default location(independent of legacy mode),when
    • Command line without DIRECTORY parameter
    • User without EXP_FULL_DATABASE privilege