ORACLE EXPORT/IMPORT UTILITY - DATA PUMP

==================ROLES======================================


SQLPLUSW sys/linux@sns0910srv as sysdba;

create user dpuser identified by dpuser;

grant connect, resource to dpuser;

CREATE DIRECTORY dpump_dir1 AS 'E:\oracle\product\10.2.0\flash_recovery_area\sns0910test\dp';

GRANT create session, create table to dpuser;

GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE to dpuser;

grant read, write on directory dpump_dir1 to dpuser;



=======================Init.ora parameters======================

Init.ora parameters that affect the performance of Data Pump:

Oracle recommends the following settings to improve performance.

Disk_Asynch_io= true

Db_block_checking=false

Db_block_checksum=false

=========================================
FULL=Y

expdp dpuser/dpuser@sns0910srv full=Y directory=dpump_dir1 dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp dpuser/dpuser@sns0910srv full=Y directory=dpump_dir1 dumpfile=DB10G.dmp logfile=impdpDB10G.log

=========================================
SCHEMAS=schema, schema, schema…

expdp dpuser/dpuser@sns0910srv SCHEMAS=LDBO directory=dpump_dir1 dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp dpuser/dpuser@sns0910srv SCHEMAS=LDBO directory=dpump_dir1 dumpfile=DB10G.dmp logfile=impdpDB10G.log

============

TABLES=[schemas].tablename, [schemas].tablename,…

expdp dpuser/dpuser@sns0910srv TABLES=LDBO.ACCOUNTS directory=dpump_dir1 dumpfile=LDBO.dmp logfile=expdpLDBO.log

impdp dpuser/dpuser@sns0910srv TABLES=LDBO.ACCOUNTS directory=dpump_dir1 dumpfile=DB10G.dmp logfile=impdpDB10G.log


===============

TABLESPACES=tablespacename, tablespacename, tablespacename…

TRANSPORT TABLESPACES=tablespacename…

================

Data pump performance can be improved by using the PARALLEL parameter. This should be used in conjunction with the "%U"

wildcard in the DUMPFILE parameter to allow multiple dumpfiles to be created or read:

expdp dpuser/dpuser@sns0910srv schemas=LDBO directory=dpump_dir1 parallel=4 dumpfile=LDBO_%U.dmp logfile=expdpLDBO.log

==============

expdp dpuser/dpuser@sns0910srv schemas=LDBO include=TABLE:"IN ('ACCOUNTS', 'ACCOUNTADDRESSDETAIL')" directory=dpump_dir1

dumpfile=LDBO.dmp logfile=expdpLDBO.log

expdp dpuser/dpuser@sns0910srv schemas=LDBO exclude=TABLE:"= ''" directory=dpump_dir1 dumpfile=LDBO.dmp logfile=expdpLDBO.log

=================

SELECT * from dba_datapump_jobs

==================ERROR=====

ORA-31631: privileges are required
ORA-39109: Unprivileged users may not operate upon other users' schemas

solutions----

SQL> GRANT create session, create table to dpuser;

SQL> GRANT EXP_FULL_DATABASE,IMP_FULL_DATABASE to dpuser;



====================Data Pump API======================

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_dp_handle NUMBER;
l_last_job_state VARCHAR2(30) := 'UNDEFINED';
l_job_state VARCHAR2(30) := 'UNDEFINED';
l_sts KU$_STATUS;
BEGIN
l_dp_handle := DBMS_DATAPUMP.open(
operation => 'EXPORT',
job_mode => 'FULL',
remote_link => NULL,
job_name => 'DB_EXPORT',
version => 'COMPATIBLE');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'LDBO.dmp',
directory => 'dpump_dir1');

DBMS_DATAPUMP.add_file(
handle => l_dp_handle,
filename => 'LDBO.log',
directory => 'dpump_dir1',
filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

DBMS_DATAPUMP.metadata_filter(
handle => l_dp_handle,
name => 'SCHEMA_EXPR',
value => '= ''LDBO''');

DBMS_DATAPUMP.start_job(l_dp_handle);

DBMS_DATAPUMP.detach(l_dp_handle);
END;
/


----------------ERROR---------------------------
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2926
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3162
ORA-06512: at line 14