Benefits Of Oracle Data Pump
- Oracle Data Pump jobs are server based. Mean if you submit the job from the client and you close the client connection the job will continue to run on server.
- We can attach to existing job which is stalled or failed because of lack of space and start the job.
- Data Pump provide PARALLEL parameter which enables you to provide number of parallel workers.
- Data Pump provide you capability to attach to running job and change parameter as required.
- Ability to import the database from some other database without having to create a dump file.
- Provides finer control on which objects to import.
- Enhancement to traditional TOUSER parameter. You have the capability to remap tablespace, schemas and datafiles.
- You can estimate the space without even running the export job.
Three File Of Oracle Data Pump
We will use three type's of files when using data pump, all files will be created on the server.
- Dump files: The dump files holds the data and metadata
- Log file: The log file resulting output from the data pump command
- SQL files: The SQL file contain the DDL statements describing the objects included in the job but can contain data
- Master data pump tables: When using data pump it will create tables within the schema, this is used for controlling the data pump job, the table is removed when finished.
Two Component Of Oracle Data pump
- The command-line clients,
expdp and impdp
- DBMS_DATAPUMP
- DBMS_METADATA
-
DBMS_DATAPUMP: The DBMS_DATAPUMP is a package which has the actual data pump engine to export and import data from the database. This component can be use directly in a program. The DBMS_DATAPUMP package use for starting, Stopping restarting a job, monitoring a job and detaching from a job.
-
DBMS_METADATA: The DBMS_METADATA is a package. The DBMS_METADATA is used to extract and upload the metadata about data.
- Command Line: The expdp and impdp are the export and import equivalents.
Two Parameter Of Oracle Data Pump
- Export Parameter
- Import Parameter
1. Export Utility Parameter
The Export utility parameter can be used to transfer data objects between oracle databases. The objects and the data in Oracle database can be moved to other Oracle database running even on a different hardware and software configurations. The Export utility parameter reads data from the database Oracle according to your request. The Export is used to provide backups of the Oracle tables and database. The export is also used to move data from one Oracle database to another. The Export utility parameter has additional functions for users with Oracle Database Administrator authority.
Export Utility Parameter with description:
- UserId:- The userid is the first parameter of export utility. This is like the username and password.
- Buffer:-Size of data buffer.
- File:-Output File (EXPDAT.DMP)
- Compress:-Import into one extent (y)
- Grants:- Export grants (y)
- Indexes:- Export indexes (y)
- Direct:- Direct path (N)
- Log:- Log file of screen output
- Rows:-Export data rows (Y)
- Consistent:- Cross-table consistency (N)
- Full:- Export entire file (N)
- Owner:- List of owner usernames
- Tables:-List of table names
- Record Length:-Length of IO record
- Inctype:-Increment export type
- Record:-Track incr. export (Y)
- Triggers:-Export triggers
- Statistics:-Analyze objects (ESTIMATE)
- Par File:-Parameter filename
- Constraints:-Export constraints (Y)
- Object_Consistent:-Transaction set to read only during object export (N)
- Feedback:-Disply progress every x rows (0)
- Filesize:-Maximum size of each dump file
- Flashback_SCN:-SCN used to set session snapshot back to
- Flashback_Time:-Time used to get the SCN closest to the specified time
- Query:-Select clause used to export a subset of table
- Resumable:-Suspend when a space related error is encountered (N)
- Resumable_Name:-Text string used to identify resumable statement
- Resumable_Timeout:-Wait time for Resumable
- TTS_Full_Check:-Perform full or partial dependency check for TTS
- Tablespaces:-List of tablespaces to export
- Transport_Tablespace:-Export transportable tablespace metadate (N)
- Template:-Template name which invokes iAS mode export
Example:
C:\Users\pardeepmalik> exp help=y
Output:
2. Import Utility Parameter
The Import utility parameter reads data from a sequential data set prepared by the Export utility parameter and writes data into an Oracle database. The Import utility parameter has additional functions for users with Oracle Database Administrator authority.
Import Utility Parameter with description:
- UserId:- The userid is the first parameter of export utility. This is like the username and password.
- Buffer:-Size of data buffer.
- File:-Iutput File (EXPDAT.DMP)
- Show:-Just list file contents (N)
- Ignore:-Ignore create error (N)
- Grants:- Import grants (y)
- Indexes:- Import indexes (y)
- Log:- Log file of screen output
- Rows:-Import data rows (Y)
- Full:- Import entire file (N)
- FromUser:-List of owner usernames
- ToUser:-List of username
- Tables:-List of table names
- Record Length:-Length of IO record
- Inctype:-Increment import type
- Commit:- Commit array insert (N)
- ParFile:-Parameter filename
- Constraints:-Import constraints (Y)
- Destroy:-Overwrite tablespace data file (N)
- Indexfile:-Write table/Index info to specified file
- Skip_Unusable_Indexes:-Skip maintenance of unusable indexes (N)
- Feedback:-Display progress every x rows (0)
- TOID_Novalidate:-Skip validation of specified type ids
- Statistics:-Maximum size of each dump file
- Resumable:-Suspend when a space related error is encountered (N)
- Resumable_Name:-Text string used to identify resumable statement
- Resumable_Timeout:-Wait time for Resumable
- Compile:-Compile procedures general metadata (Y)
- Streams_Configuration:-Import streams general metadata (Y)
- Streams_Instantiation:-Import streams instantiation metadata (N)
Example:
C:\Users\pardeepmalike> imp help=y
Output:
