What is datapump in oracle
Data Pump:
Data pump is a utility provided by Oracle to export data and/or structure from a database into a binary file called as dump file. Using the import utility data can be imported from the dump file into the database.
In older version this was called export/import utility instead of data pump utility. This utility is still available in Oracle 10g.
Main points of data pump:
• It is a server side utility. Older exp/imp was a client side utility.
• Itcanbestartedfromwhereitstopped
• It can export/import data from one database to another without generating a dump file
• It can extract only the structure of the database
• It is faster because it uses direct-path loading and unloading technologies
• Data Pump can be called using expdp & impdp commands as well as dbms_datapump package
• EXP_FULL_DATABASE role is required to export data from schema different than yours
How to export:
• Database(user, public synonyms, roles,profiles etc)
expdp system/secret full=Y dumpfile=expdir:fulla%U.dmp, expdir:fullb%U.dmp filesize 2G parallel=2 logfile=expdir:full.log
1. Generates multiple dump files in expdir directory (database directory object). Filesize limit is 2G. File names are fulla01.dmp, fulla02.dmp, fulla03.dmp, fullb01.dmp, fullb02.dmp, fullb03.dmp. Log file names full.log gets generated in expdir also. Data pump will start 2 slave processes to speed up the load.
• Schema (all data and metadata for a user(s))
expdp system/secret schemas=hr, finance dumpfile=expdir:schema.dmp logfile=expdir:sschema.log
• Tablespace (all objects in a tablesapce and dependent objects in other tablesapces e.g. indexes)
expdp system/password dumpfile=expdir:users_ts.dmp logfile=expdir:users_ts.out tablespaces=users
• Table
• Sub set of a table
expdp hr/hr dumpfile=expdir:job_tabs.dmp QUERY=hr.employees:”WHERE department_id in (10,20) and salary 1600 ORDER BY department_id”
Note: No data in exported for indexes. Only structure is exported. They are rebuilt on import
Export Parameters
• full=y Specifies a database mode export.
• schemas=schema_list Specifies a schema mode export
here schema_list is a comma-delimited list of schemas to export.
expdp hr/hr dumpfile=expdir:job_tabs.dmp nologfile=y content=metadata_only(data_only) tables=jobs,job_history
• tables=table_list Specifies a table mode export where table_list is a comma-delimited list of tables to export.
• tablesspaces=tablespace_list Specifies a tablespace mode export where tablespace_list is a comma-delimited list of tablespaces to export.
• content=content_option Specifies whether data, metadata, or both are exported. Valid values are: DATA_ONLY (data only), METADATA_ONLY (metadata only), or the default ALL (both).
• include=object_type [:name_clause] Specifies what objects should be exported
. INCLUDE=TABLE:“IN (‘EMPLOYEES’, ‘DEPARTMENTS’)” 2. INCLUDE=PROCEDURE
. INCLUDE=INDEX :”LIKE ‘EMP%'”
• exclude=oject_type[:name_clause]Specifies what objects should not be exported
• Sample=[[schema_name.]table_name:]sample_percentage Only sample data is exported. Very important feature for generating sample test data.
1. SAMPLE=”HR”.”EMPLOYEES”:50
2. SAMPLE=70
• ESTIMATE_ONLY=yes .Estimates the diskspace
requirement using blocks and statistics method.
Default is blocks
• ESTIMATE=[block|statistics]–Specifies which method
to be used for estimating disk space requirement
• FLASHBACK_SCN=nnnn
expdp hr/hr DIRECTORY=dpump_dir1 DUMPFILE=hr_exp.dmp FLASHBACK_SCN=150222
• FLASHBACK_TIME=time
expdp system/password12 DIRECTORY=dpump_dir1
DUMPFILE=hr_time.dmp FLASHBACK_TIME="TO_TIMESTAMP('25-
05-2005 17:22:00', 'DD-MM-YYYY HH24:MI:SS')"
• network_link=db_link Specifies that are database accessed via the database link db_link should be used as the export source.
• dumpfile=dir: file Specifies the dump file location and name. dir is a database directory object. file is the filename. If the filename includes a %U substitution variable, the database will substitute a two-digit file sequence number starting with 00.
• filesize=size_limit Specifies the maximum size of each dump file. Can be specified in bytes, kilobytes, megabytes, or gigabytes. The default is bytes.
• logfile=dir :file Specifies the log file location and name. dir is a database directory object and file is the filename.
• directory=dir Specifies the file location to use for both the dump file and log file. dir is a database directory object.
• nologfile=y Specifies that no log files should be written.
• job_name=identifier Specifies a name for the import job. This name is visible from data dictionary views. The default is system generated.
• Compression=option Specifies if metadata is compressed while exporting. Valid options are METADATA_ONLY AND NONE. You cannot compress the actual data.
• QUERY=table name: “WHERE department_id in(10,20) and salary 1600 ORDER BY department_id”
Creating directory objects for Data Pump
• CREATE DIRECTORY dump_dir AS ‘/oracle/data_pump/dumps’;
• CREATE DIRECTORY log_dir AS ‘/oracle/data_pump/logs’;
• GRANT read,write ON DIRECTORY dump_dir TO PUBLIC;
How to import:
• Read the dump file FULL.DMP and extract all DDL, placing it in the file FULL.SQL. Do not write a log file.
impdp system/password full=y dumpfile=expdir:FULL.DMP nologfile=y sqlfile=expdir:FULL.SQL
• Read the data accessed via database link PROD and import schema HR into schema HR_TEST,importing only metadata, writing the log file to the database directory expdir, and naming this log file HR_TEST.imp.
impdp system/password network_link=prod schemas="HR" remap_schema="HR:HR_TEST" content=metadata_only logfile=expdir:HR_TEST.imp
• Read the dump file HR.DMP and write to the SQLfile HR_proc_give. sql all the DDL to create any procedures with a name LIKE ‘GIVE%’. Do not write a log file.
impdp system/password full=y dumpfile=expdir:HR.DMP nologfile=y sqlfile=expdir:HR_proc_give.SQL include=PROCEDURE:"LIKE 'GIVE%'"
• Read the data accessed via databas elink PROD and import only the data from R.DEPARTMENTS into schema HR_TEST.DEPARTMENTS. Write a log file to file DEPT_DATA.log.
impdp system/password network_link=prod_link schemas="HR" remap_schema="HR:HR_TEST" content=data_only include=TABLE:"= 'DEPARTMENTS'" logfile=expdir:HR_TEST.imp
Import Parameters
• Full=y Specifies a full mode import.
• Schemas=schema_list Specifies a schema mode import.
schema_list is a comma-delimited list of schemas to import.
• Tables=table_list Specifies a table mode import.table_list is a comma delimited list of tables to
import.
• tablesspaces=tablespace_list Specifies a tablespace mode import. tablespace_list is a comma-delimited list
of tablespaces to import.
• content=content_option Specifies whether data, metadata, or both are imported. Valid values are:
DATA_ONLY (data only), METADATA_ONLY (metadata only),
and the default ALL (both).
• network_link=db_link : Specifies the live database accessed via the database link. db_link should be used as the import source.
• dumpfile=dir:file Specifies the dump file location and name. dir is a database directory object. file is the filename.
• logfile=dir: file Specifies the log file location and name. dir is a database directory object. file is the filename.
• directory=dir Specifies the file location to use for both the dump file and log file. dir is a database directory object.
• nologfile=y Specifies that no log file should be written.
• sqlfile=dir: file Specifies the file location and name to write the metadata/DDL statements to.
• job_name=identifier Specifies a name for the import job. This name is visible from data dictionary views. The default is system generated.
• parallel=degree Specifies the maximum number of active threads/processes operating on behalf of the import. The default is 1.
• parfile=dir: file Specifies the file location and name of the parameter file that Data Pump Import should use.
• remap_datafile=source:target Specifies how to change the source and target datafile names that appear in CREATE TABLESPACE, CREATE LIBRARY and CREATE DIRECTORY statements. source is the datafile name from the export or live database. target is the datafile name to use in the import.
• remap_schema=source:target Specifies the source and target schema names when importing into a different schema than the export was taken from. Multiple remap_schema entries can be used. source is the schema from the export or live database. target is the schema to import into.
• remap_tablespace=source:target Specifies the source and target tablespace names when they change between the source export files / live database and target database. Multiple remap_tablespace entries can be used. source is the tablespace name from the export or live database. target is the tablespace name that the source objects should be placed in.
• include=object_list Specifies a comma-delimited list of object types to include in the import.
• exclude=object_list Specifies a comma-delimited list
of object types to exclude from the import.
Monitoring Data Pump jobs:
SELECT owner_name, owner,job_name ,operation,job_mode,state from dba_datapump_jobs;
Advantages of Datapump
• Fine-grained object and data selection (Include,exclude, contents, query)
• Parallel execution(parallel)–master process (ora_shekhar_
• Estimation of the export job space consumption (estimate_only)
• Network mode in a distributed environment (network_link)
• Remapping capabilities during import(remap_schema, remap_tablespace, remap_datafile)
• Data sampling and metadata compression (sample, compression)
What is a master table:
• It is a table created during export process which keeps track of the progress of the export job. It is created in the schema of the user running the export job. If anything goes wrong with the job – e.g. running out of disk space – the job can be restarted with the help of information in the master table. This table is exported to the dump file when export is finished.
• This table is imported as a first step during the import from the dumpfile
How to attach to an already existing job
Expdp pankaj/pankaj dumpfile=’data:full.dmp’ full=yes job_name=exportabc Then,
in another window execute the following
Expdp pankaj/pankaj attach=exportabc
Export> help