文件筆記--Oracle Data Pump 1

zecaro發表於2010-12-29
筆記Oracle

閱讀文件時，寫寫筆記而已。一般只寫綜述的那一章。

Oracle® Database Utilities
10g Release 2 (10.2)
Part Number B14215-01

1 Overview of Oracle Pump

Data Pump Components

Oracle Data Pump is made up of three distinct parts:

  • The command-line clients, expdp and  impdp
  • The DBMS_DATAPUMP PL/SQL package  (also known as the Data Pump API)
  • The DBMS_METADATA PL/SQL package  (also known as the Metadata API)

【Oracle Data Pump由上述三部分組成】

The Data Pump clients, expdp and impdp, invoke the Data Pump Export utility and Data Pump Import utility, respectively. They provide a user interface that closely resembles the original export (exp) and import (imp) utilities.

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command-line. These parameters enable the exporting and importing of data  and metadata  for a complete database or subsets of a database.

【實質是呼叫PL/SQL  package，使用命令列裡輸入的引數】

【可以匯入和匯出資料和後設資料】

Note:
All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system () selected by the specified database connect string.This means that, for nonprivileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server file system.For privileged users, a default directory object is available. See Default Locations for Dump, Log, and Files for more information about directory objects.

When data is moved, Data Pump automatically uses either direct path load (or unload) or the external tables mechanism, or a combination of both. When metadata is moved, Data Pump uses functionality provided by the DBMS_METADATA PL/SQL package. The DBMS_METADATA package provides a centralized facility for the extraction, manipulation, and resubmission of dictionary metadata.

【對於資料，Data Pump 自動選擇direct path 、external tables 或混合兩種同時使用】

【對於後設資料，Data Pump 使用DBMS_METADATA PL/SQL包提供的功能】

The DBMS_DATAPUMP and  DBMS_METADATA  PL/SQL packages can be used independently of the Data Pump clients.

【兩個包可以透過client獨立呼叫】

See Also:

Oracle Database PL/SQL Packages and Types Reference for descriptions of the DBMS_DATAPUMP andDBMS_METADATA packages

 

What New Features Do Data Pump Export and Import Provide?

The new Data Pump Export and Import utilities (invoked with the expdp and impdp commands, respectively) have a similar look and feel to the original Export (exp) and Import (imp) utilities, but they are completely separate. Dump files generated by the new Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, files generated by the original Export (exp) utility cannot be imported with the Data Pump Import (impdp) utility.

【exp/imp與expdp/impdp不相容，所以，由原先的exp生成的檔案無法使用impdp匯入】

Oracle  recommends that you use the new Data Pump Export and Import utilities because they support all Oracle Database features, except for XML schemas and XML schema-based tables. Original Export and Import support the full set of Oracle database release 9.2 features. Also, the design of Data Pump Export and Import results in greatly enhanced data movement performance over the original Export and Import utilities.

【Oracle建議使用新的expdp/impdp，除了XML schemas and XML schema-based tables；新的expdp/impdp在效能上有極大地提高】

The following are the major new features that provide this increased performance, as well as enhanced ease of use:

【以下是新特性和優勢】
  • The ability to specify the maximum number of threads of active execution operating on behalf of the Data Pump job. This enables you to adjust resource consumption versus elapsed time. See PARALLEL for information about using this parameter in export. See PARALLEL for information about using this parameter in import. (This feature is available only in the Enterprise Edition of Oracle Database 10g.)

【1、使用PARALLEL引數指定最大的活動執行緒，以讓你能夠在資源利用和時間上進行調節，這隻在10g企業版中可以使用】
  • The ability to restart Data Pump jobs. See START_JOB for information about restarting export jobs. See START_JOB for information about restarting import jobs.

【2、能夠重新開始Data Pump任務】

  • The ability to detach from and reattach to long-running jobs without affecting the job itself. This allows DBAs and other operations personnel to monitor jobs from multiple locations. The Data Pump Export and Import utilities can be attached to only one job at a time; however, you can have multiple clients or jobs running at one time. (If you are using the Data Pump API, the restriction on attaching to only one job at a time does not apply.) You can also have multiple clients attached to the same job. See ATTACH for information about using this parameter in export. See ATTACH for information about using this parameter in import.

【3、客戶端就可以退出或重新連線，而不影響任務任】

  • Support for export and import operations over the network, in which the source of each operation is a remote instance. See NETWORK_LINK for information about using this parameter in export. See NETWORK_LINK for information about using this parameter in import.

【4、支援透過網路對源資料是遠端instance的匯入匯出操作】

  • The ability, in an import job, to change the name of the source datafile to a different name in all DDL statements where the source datafile is referenced. See REMAP_DATAFILE.

【5、在匯入時，可以修改資料檔案的名字】

  • Enhanced support for remapping tablespaces during an import operation. See REMAP_TABLESPACE.

【6、在匯入時，增強了remap表空間的操作】

  • Support for filtering the metadata that is exported and imported, based upon objects and object types. For information about filtering metadata during an export operation, see INCLUDE and EXCLUDE. For information about filtering metadata during an import operation, see INCLUDE and EXCLUDE.

【7、匯入匯出時，支援過濾後設資料】

  • Support for an interactive-command mode that allows monitoring of and interaction with ongoing jobs. See Commands Available in Export's Interactive-Command Mode and Commands Available in Import's Interactive-Command Mode.

【8、支援對正在進行的任務進行互動式的命令列】

  • The ability to estimate how much space an export job would consume, without actually performing the export. See ESTIMATE_ONLY.

【9、能夠不執行匯出，而估算出匯出所需的空間】

  • The ability to specify the version of database objects to be moved. In export jobs, VERSION applies to the version of the database objects to be exported. See VERSION for more information about using this parameter in export.

【10、能夠在匯出時透過指定database版本來選擇性匯出，為了向低版本的資料庫中匯入資料】

In import jobs, VERSION applies only to operations over the network. This means that VERSION applies to the version of database objects to be extracted from the source database. See VERSION for more information about using this parameter in import.

For additional information about using different versions, see Moving Data Between Different Database Versions.

  • Most Data Pump export and import operations occur on the Oracle database server. (This contrasts with original export and import, which were primarily client-based.) See Default Locations for Dump, Log, and SQL Files for information about some of the implications of server-based operations.

【11、大多數 Data Pump 的匯入匯出任務是在server端進行的，而原先的匯入匯出主要在客戶端進行】

The remainder of this chapter discusses Data Pump technology as it is implemented in the Data Pump Export and Import utilities. To make full use of Data Pump technology, you must be a privileged user. Privileged users have the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles. Nonprivileged users have neither.

【為了更好的使用Data Pump技術，你必須是獲得許可權的使用者，即EXP_FULL_DATABASE和IMP_FULL_DATABASE的角色】

Privileged users can do the following:

【獲得許可權的使用者可以做到：】
  • Export and import database objects owned by others

    【匯入匯出其它使用者的內容】

  • Export and import nonschema-based objects such as tablespace and schema definitions, system privilege grants, resource plans, and so forth

    【匯入匯出nonschema-based objects：如表空間和schema的定義，系統許可權， resource plans 】

  • Attach to, monitor, and control Data Pump jobs initiated by others

    【接觸、監控、其他人建立的Data Pump任務】

  • Perform. remapping operations on database datafiles

    【對 datafiles進行remap】

  • Perform. remapping operations on schemas other than their own

    【對其他人的datafiles進行remap】

How Does Data Pump Access Data?

Data Pump supports two access methods to load and unload table row data: direct path and external tables.Because both methods support the same external data representation, data that is unloaded with one method can be loaded using the other method. Data Pump automatically chooses the fastest method appropriate for each table.

【Data Pump 獲得資料兩種方式：直接路徑和外部表，為每個表自動選擇最快的方式】

Note:
Data Pump will not load tables with disabled unique indexes. If the data needs to be loaded into the table, the indexes must be either dropped or reenabled.

【Data Pump不會載入有diabled唯一索引的表】

Direct Path Loads and Unloads

The Oracle database has provided direct path unload capability for export operations since Oracle release 7.3 and a direct path loader API for OCI since Oracle8i. Data Pump technology enhances direct path technology in the following ways:

【Data Pump 在direct path的改進】

  • Support of a direct path, proprietary format unload.

    【支援direct path, proprietary format unload】

  • Improved performance through elimination of unnecessary conversions. This is possible because thedirect path internal stream format is used as the format stored in the Data Pump dump files.

    【減少不必要的轉換，提高效能】

  • Support of additional datatypes and transformations.

    【支援額外的資料型別和轉換】

The default method that Data Pump uses for loading and unloading data is direct path, when the structure of a table allows it. Note that if the table has any columns of datatype LONG, then direct path must be used.

【當表的結構允許時，預設的方式是 direct path；當表有LONG欄位時，必須使用direct path】

The following sections describe situations in which direct path cannot be used for loading and unloading.

Situations in Which Direct Path Load Is Not

If any of the following conditions exist for a table, Data Pump uses external tables rather than direct path to load the data for that table:

【Load時，以下情形，Data Pump 使用external tables 而非direct path】

  • Aglobal indexon multipartition tables existsduring a single-partition load. This includes object tables that are partitioned.
  • Adomain indexexists for aLOB column.
  • A table is in a  cluster.
  • There isan active triggeron a pre-existing table.
  • Fine-grained access control is enabled in insert mode on a pre-existing table.
  • A table containsBFILEcolumns or columns ofopaque types.
  • A referential integrity constraint is present on a pre-existing table.
  • A table containsVARRAYcolumns withan embedded opaque type.
  • The table has encrypted columns
  • The table into which data is being imported is a pre-existing table  and at least one of the following conditions exists:
    • There is an active trigger
    • The table is partitioned
    • fgac is in insert mode
    • A referential integrity constraint exists
    • A unique index exists
  • Supplemental loggingis enabled and the table has  at least 1 LOB column.

Situations in Which Direct Path Unload Is Not Used

If any of the following conditions exist for a table, Data Pump uses the external table method to unload data, rather than direct path:

【unload時，以下情形，Data Pump 使用external tables 而非direct path】

  • Fine-grained accesscontrol for SELECT is enabled.
  • The table is aqueue table.
  • The table contains one or more columns oftype BFILE or opaque,or an object type containing opaque columns.
  • The table containsencryptedcolumns.
  • A column of anevolved type that needs upgrading.
  • A column of typeLONG or LONG RAW  that isnot last.

External Tables

External TablesThe Oracle database has provided an external tables capability since Oracle9i that allows reading of data sources external to the database. As of Oracle Database 10g, the external tables feature also supports writing database data to destinations external to the database. Data Pump provides an external tables access driver (ORACLE_DATAPUMP) that reads and writes files.The format of the files is the same format used with the direct path method.This allows for high-speed loading and unloading of database tables. Data Pump uses external tables as the data access mechanism in the following situations:

【自9i其支援從外部資料來源讀資料，到了10g，支援向外部寫資料。Data Pump 提供外部表的driver(ORACLE_DATAPUMP)來讀和寫。以下情形Data Pump 使用外部表作為獲取資料的機制：】

  • Loading and unloadingvery largetables and partitions in situations whereparallel SQLcan be used to advantage
  • Loading tables withglobal or domain indexesdefined on them, including partitioned object tables
  • Loading tables withactive triggers or clustered tables
  • Loading and unloading tables withencryptedcolumns
  • Loading tables withfine-grained access controlenabled for inserts
  • Loading tables thatare partitioned differently at load time and unload time

Note:

 When Data Pump uses external tables as the data access mechanism, it uses the ORACLE_DATAPUMP access driver.However, it is important to understand that the files that Data Pump creates when it uses external tables are not compatible with files created when you manually create an external table using the SQL CREATE TABLE ... ORGANIZATION EXTERNAL statement. One of the reasons for this is that a manually created external table unloads only data (no metadata), whereas Data Pump maintains both data and metadata information for all objects involved.

【Data Pump 使用ORACLE_DATAPUMP access driver來提供外部表機制。】

【Data Pump用外部表建立的檔案與你用SQL CREATE TABLE ... ORGANIZATION EXTERNAL手工建立的外部表不相容。其中的一個原因是，你手工建立的外部表沒有後設資料，而Data Pump有】

See Also:
Chapter 14, "The ORACLE_DATAPUMP Access Driver"

Accessing Data Over a Database Link

When you perform. an export over a database link, the data from the source database instance is written to dump files on the connected database instance. In addition, the source database can be a read-only database.

When you perform. an import over a database link, the import source is a database, not a dump file set, and the data is imported to the connected database instance.

Because the link can identify a remotely networked database, the terms database link and network link are used interchangeably.

See Also:
NETWORK_LINK for information about performing exports over a database link

NETWORK_LINK for information about performing imports over a database link

 


來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23650854/viewspace-682833/，如需轉載，請註明出處，否則將追究法律責任。

相關文章