Oracle DataPump Features:
Datapump is a server side utility.You initiate datapump jobs from a user process either sql*plus or through EM but all the work is done by server processes.This improves the performance dramatically over the old exp ort/import utilities because the datapump processes running on the server have direct acess to the datafiles and SGA;they don’t have to go via a session.
Data Pump takes the old export and import utilities one step further, you can have total control over the job running (stop it, pause it, check it, restart it). Data pump is a server side technology and it can transfer large amounts of data very quickly using parallel streams to achieve maximum throughput, they can be 15-45% faster than the older import/export utilities. Advantages using data pump are
ability to estimate jobs times
ability to restart failed jobs
perform fine-grained object selection
monitor running jobs
directly load a database from a remote instance via the network
remapping capabilities
improved performance using parallel executions
A couple of notes is that you cannot export to a tape device only to disk, and the import will only work with version of oracle 10.1 or greater.
Data Pump Uses
You can use data pump for the following:
migrating databases
copying databases
transferring oracle databases between different operating systems
backing up important tables before you change them
moving database objects from one tablespace to another
transporting tablespace's between databases
reorganizing fragmented table data
extracting the DDL for tables and other objects such as stored procedures and packages
Data Access methods
Data pump has two methods for loading data, direct path or external table path you as a dba have no control with what data pump uses, normally simple structures such as heap tables without triggers will use direct path more complex tables will use the external path, oracle will always try and use the direct-path method.
· Direct Path: Bypasses the database buffer cache and writes beyond the high water mark when finished adjusts the high water mark, No undo is generated and can switch off redo as well, minimal impact to users as does not use SGA. Must disable triggers on tables before use.
· External Path: Uses the database buffer cache acts as a SELECT statement into a dump file, during import reconstructs statements into INSERT statements, so whole process is like a normal SELECT/INSERT job. Both undo and redo are generated and uses a normal COMMIT just like a DML statement would.
In the following cases oracle will use the external path if any of the below are in use:
clustered tables
active triggers in the table
a single partition in a table with a global index
referential integrity constraints
domain indexes on LOB columns
tables with fine-grained access control enabled in the insert mode
Data Pump files
You will use three types's of files when using data pump, all files will be created on the server.
dump files - holds the data and metadata
log files - the resulting output from the data pump command
sql files - contain the DDL statements describing the objects included in the job but can contain data
Master data pump tables - when using datapump it will create tables within the schema, this is used for controlling the datapump job, the table is removed when finished.
Changing from Original Export/Import to Oracle Data Pump:
New concept of 'DIRECTORY' in expdp:
In order to use Data Pump, the database administrator must create a directory object and grant
Privileges to the user on that directory object. If a directory object is not specified, a default
directory object called data_pump_dir is provided. The default data_pump_dir is available only
to privileged users unless access is granted by the DBA.
SQL> Create directory FullDump as ‘C:\FullDump’;
Directory Created
SQL> Create directory SchemaDump as ‘C:\SchemaDump’;
Directory Created
SQL>
SELECT grantee, table_name ,directory_name, PRIVILEGE FROM user_tab_privs;
SELECT * FROM all_directories;
After a directory is created, you need to grant READ and WRITE permission on the directory
to other users. For example, to allow the Oracle database to read and to write to files on behalf
of user scott in the directory named by SchemaDump, you must execute the following
Command:
SQL> GRANT READ, WRITE ON DIRECTORY SchemaDump TO Scott;
Once the directory access is granted, the user scott can export his database objects in that directory using the below command:
expdp scott/tiger schemas=scott directory=SchemaDump dumpfile=scott.dmp logfile=scott.log
If user donot have read-write permission on direcotry then expdp will fail with below error:
ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name “SCHEMADUMP” is invalid.
Table Exports/Imports
The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax.
expdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log
impdp scott/tiger tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log
The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.
Schema Exports/Imports
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax.
Database Exports/Imports
The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax.
INCLUDE and EXCLUDE
The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export/import. When the EXCLUDE parameter is used, all objects except those specified by it will be included in the export/import. The two parameters are mutually exclusive, so use the parameter that requires the least entries to give you the result you require. The basic syntax for both parameters is the same.
Syntax:
INCLUDE=object_type[:name_clause] [, ...]
EXCLUDE=object_type[:name_clause] [, ...]
Example:
expdp scott/tiger schemas=SCOTT include=TABLE:"IN ('EMP', 'DEPT')" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
expdp scott/tiger schemas=SCOTT exclude=TABLE:"= 'BONUS'" directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log
expdp scott/tiger TABLES=SCOTT.EMP EXCLUDE=STATISTICS DIRECTORY=datapump DUMPFILE=EMP.dmp LOGFILE=EMP.log
We can exclude/Include index, constraints, statistics instead of INDEX=y,n or constraints=y,n and statistics=none as in old exp/imp
REMAP_SCHEMA: Fromuser/Touser has been replaced by remap_schema in oracle 10g
Syntax: REMAP_SCHEMA=source_schema:target_schema
If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessary CREATE USER metadata and you are importing with enough privileges.
If you do not have enough privileges to perform an import that creates dump files containing the metadata necessary to create a schema, then you must create the target schema before performing the import operation. This is because the dump files do not contain the necessary information for the import to create the schema automatically.
If the import operation does create the schema, then after the import is complete, you must assign it a valid password in order to connect to it. The SQL statement to do this, which requires privileges, is:
SQL> ALTER USER [schema_name] IDENTIFIED BY [new_pswd]
Suppose that you want to map HR schema to Scott schema:
NETWORK_LINK
With network mode imports, one doesn't need any intermediate dump files (No more FTP'ing of dump files). Data is exported across a database link and imported directly into the target database.
SQL> create user sigma_local identified by sigma_local;
User created.
SQL> grant connect, resource to sigma_local;
Grant succeeded.
SQL> grant read, write on directory SchemaDump to sigma_local;
Grant succeeded.
SQL> grant create database link to sigma_local;
Grant succeeded.
SQL> create database link old_sigma connect to sigma identified by sigma using 'orcl2';
### Now to import all objects of sigma schema (on remote db) to old_sigma (on local db) use the below command.
impdp sigma_local/sigma_local DIRECTORY=SchemaDump NETWORK_LINK=old_sigma remap_schema=sigma:sigma_local
Stop and restart an expdp/impdp in progress:
· expdp system/sys full=Y exclude=statistics directory=FullDump dumpfile=FullexportDump_stat.dmp logfile=expdpfull_stat.log job_name=SYS_EXPORT_FULL_01
· If you want to pause the job for some time then press CTL+C , type the command given below
Export> STOP_JOB=IMMEDIATE
Are you sure you wish to stop this job ([yes]/no): yes
· Now you want to restart the job then you need to attach the job
1. expdp system/sys attach=SYS_EXPORT_FULL_01
2. Export> START_JOB
3. Export> STATUS=600
4. Export> CONTINUE_CLIENT
The following commands are valid while in interactive mode.
· ADD_FILE:Add dumpfile to dumpfile set.
· CONTINUE_CLIENT:Return to logging mode. Job will be re-started if idle.
· EXIT_CLIENT:Quit client session and leave job running.
· FILESIZE:Default filesize (bytes) for subsequent ADD_FILE commands.
· HELP:Summarize interactive commands.
· KILL_JOB:Detach and delete job.
· PARALLEL: Change the number of active workers for current job.PARALLEL=.
· START_JOB: Start/resume current job.
· STATUS: Frequency (secs) job status is to be monitored where the default (0) will show new status when available.STATUS[=interval]
· STOP_JOB:Orderly shutdown of job execution and exits the client.
· STOP_JOB=IMMEDIATE performs an immediate shutdown of the Data Pump job.
