Oracle : Important Queries for Data Pump

—– Create user for using EXPDP/IMPDP :

create user admin identified by PW;
grant connect,resource to admin;
grant unlimited tablespace to admin;
grant create any view to admin;
create or replace directory DBA_EXPORT as ‘/u01/app/oracle/admin/cloud12c/dpdump’
grant write on directory DBA_EXPORT to admin;
grant EXP_FULL_DATABASE to admin;
grant IMP_FULL_DATABASE to admin;

—– Checking Datapump Sessions  :

SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;

PROMPT
PROMPT +————————————————————————+
PROMPT | Report : Data Pump Sessions |
PROMPT | Instance : &current_instance |
PROMPT +————————————————————————+

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN instance_name FORMAT a9 HEADING ‘Instance’
COLUMN owner_name FORMAT a15 HEADING ‘Owner Name’
COLUMN job_name FORMAT a20 HEADING ‘Job Name’
COLUMN session_type FORMAT a15 HEADING ‘Session Type’
COLUMN sid FORMAT 999999 HEADING ‘SID’
COLUMN serial_id FORMAT 99999999 HEADING ‘Serial ID’
COLUMN oracle_username FORMAT a18 HEADING ‘Oracle User’
COLUMN os_username FORMAT a18 HEADING ‘O/S User’
COLUMN os_pid FORMAT a8 HEADING ‘O/S PID’

BREAK ON report ON instance_name_print ON owner_name ON job_name

SELECT
i.instance_name instance_name
, dj.owner_name owner_name
, dj.job_name job_name
, ds.type session_type
, s.sid sid
, s.serial# serial_id
, s.username oracle_username
, s.osuser os_username
, p.spid os_pid
FROM
gv$datapump_job dj
, gv$datapump_session ds
, gv$session s
, gv$instance i
, gv$process p
WHERE
s.inst_id = i.inst_id
AND s.inst_id = p.inst_id
AND ds.inst_id = i.inst_id
AND dj.inst_id = i.inst_id
AND s.saddr = ds.saddr
AND s.paddr = p.addr (+)
AND dj.job_id = ds.job_id
ORDER BY
i.instance_name
, dj.owner_name
, dj.job_name
, ds.type;

 

 

—– Checking Datapump Progress :

SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;

PROMPT
PROMPT +————————————————————————+
PROMPT | Report : Data Pump Job Progress |
PROMPT | Instance : &current_instance |
PROMPT +————————————————————————+

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN instance_name FORMAT a9 HEADING ‘Instance|Name’
COLUMN owner_name FORMAT a15 HEADING ‘Owner|Name’
COLUMN job_name FORMAT a20 HEADING ‘Job|Name’
COLUMN session_type FORMAT a8 HEADING ‘Session|Type’
COLUMN start_time FORMAT a19 HEADING ‘Start|Time’
COLUMN time_remaining FORMAT 9,999,999,999 HEADING ‘Time|Remaining (min.)’
COLUMN sofar FORMAT 9,999,999,999,999 HEADING ‘Bytes Completed|So Far’
COLUMN totalwork FORMAT 9,999,999,999,999 HEADING ‘Total Bytes|for Job’
COLUMN pct_completed FORMAT a10 HEADING ‘Percent|Completed’

BREAK ON report ON instance_name_print ON owner_name ON job_name

SELECT
i.instance_name instance_name
, dj.owner_name owner_name
, dj.job_name job_name
, ds.type session_type
, TO_CHAR(sl.start_time,’mm/dd/yyyy HH24:MI:SS’) start_time
, ROUND(sl.time_remaining/60,0) time_remaining
, sl.sofar sofar
, sl.totalwork totalwork
, TRUNC(ROUND((sl.sofar/sl.totalwork) * 100, 1)) || ‘%’ pct_completed
FROM
gv$datapump_job dj
, gv$datapump_session ds
, gv$session s
, gv$instance i
, gv$session_longops sl
WHERE
s.inst_id = i.inst_id
AND ds.inst_id = i.inst_id
AND dj.inst_id = i.inst_id
AND sl.inst_id = i.inst_id
AND s.saddr = ds.saddr
AND dj.job_id = ds.job_id
AND sl.sid = s.sid
AND sl.serial# = s.serial#
AND ds.type = ‘MASTER’
ORDER BY
i.instance_name
, dj.owner_name
, dj.job_name
, ds.type;

—– Checking Data Pump Jobs

set linesize 300
col state for a15
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;

PROMPT
PROMPT +————————————————————————+
PROMPT | Report : Data Pump Jobs |
PROMPT | Instance : &current_instance |
PROMPT +————————————————————————+

SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN owner_name FORMAT a25 HEADING ‘Owner Name’
COLUMN job_name FORMAT a20 HEADING ‘Job Name’
COLUMN operation FORMAT a10 HEADING ‘Operation’
COLUMN job_mode FORMAT a10 HEADING ‘Job Mode’
COLUMN state FORMAT a15 HEADING ‘State’
COLUMN degree FORMAT 999999 HEADING ‘Degree’
COLUMN attached_sessions FORMAT 999,999 HEADING ‘Attached Sessions’

SELECT
dpj.owner_name owner_name
, dpj.job_name job_name
, dpj.operation operation
, dpj.job_mode job_mode
, dpj.state state
, dpj.degree degree
, dpj.attached_sessions attached_sessions
FROM
dba_datapump_jobs dpj
ORDER BY
dpj.owner_name
, dpj.job_name;

 

—– Checking Expdp/IMPDP moving or not :

col name for a30
set linesize 300
col sql_text for a100
col status for a10
col error_msg for a30
select name,sql_text,status,error_msg from dba_resumable;

—– Attach Job for EXPDP/IMPDP Process :

select job_name,state from dba_datapump_jobs;

impdp ‘/ as sysdba’ attach=SYS_IMPORT_FULL_02

select job_name,state from dba_datapump_jobs;

expdp ‘/ as sysdba’ attach=SYS_EXPORT_FULL_02

—– Full DB Backup

expdp admin/PWdirectory=REF_DIR dumpfile=full.dmp logfile=full.log full=y