Dear Readers,
In this article, we will see the following Important Monitoring commands for EXPDP/IMPDP (SecureCRT).
MenuBar : Datapump (Exports and Imports)
Tab : DataPump_Sessions
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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 : ¤t_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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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; Instance Owner Name Job Name Session Type SID Serial ID Oracle User O/S User O/S PID --------- --------------- -------------------- --------------- ------- --------- ------------------ ------------------ -------- orcltest12 SYS SYS_EXPORT_FULL_01 DBMS_DATAPUMP 38 27306 SYS oracle 94647 orcltest12 MASTER 213 48974 SYS oracle 94669 orcltest12 WORKER 59 38644 SYS oracle 94689 |
Tab : Datapump_Progress
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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 : ¤t_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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
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; Instance Owner Job Session Start Time Percent Name Name Name Type Time Remaining (min.) SOFAR TOTALWORK Completed --------- --------------- -------------------- -------- ------------------- ---------------- ---------- ---------- ---------- orcltest11 SYS SYS_EXPORT_FULL_01 MASTER 02/17/2021 14:18:50 6 13009 26840 48% |
Tab : Datapump_jobs
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
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 : ¤t_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' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
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; Owner Name Job Name Operation Job Mode State Degree Attached Sessions ------------------------- -------------------- ---------- ---------- --------------- ------- ----------------- SYS SYS_EXPORT_FULL_01 EXPORT FULL EXECUTING 1 1 |
Tab : DBA Directories
|
1 2 3 4 |
set linesize 300 col owner format a10 col directory_name for a60 col directory_path format a100 |
|
1 2 3 4 5 6 |
SELECT * FROM dba_directories; SQL> OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID ---------- --------------------- ------------------------------------------------------ ------------- SYS DATA_PUMP_DIR /u01/app/oracle/product/12.1.0.2/dbhome_1/rdbms/log/ 0 SYS DBA_EXPORT /dbbackup/datapump/orcltest 0 |
Tab : expdp full bkup syntax
|
1 |
expdp user/user123 directory=REF_DIR dumpfile=full.dmp logfile=full.log full=y compress=y |
Tab : Instance info
|
1 2 3 4 5 |
select name||' - '||instance_name||'@'||host_name||' - '||DB_UNIQUE_NAME||' - '||version||' - '||open_mode||' - '||to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') || ' - '|| database_role from v$database, v$instance; NAME||'-'||INSTANCE_NAME||'@'||HOST_NAME||'-'||DB_UNIQUE_NAME||'-'||VERSION||'-'||OPEN_MODE||'-'||TO_CHAR(STARTUP_TIME,'DD-MON-YYYYHH24:MI:SS')||'-'||DATABASE_ROLE ------------------------------------------------------------------------------------------------------------------------------------------------------------------- ORCLTEST - ORCLTEST11@awsserver1 - ORCLTEST - 12.1.0.2.0 - READ WRITE - 10-JAN-2021 19:26:37 - PRIMARY |
Tab : Create directory
|
1 2 |
--create or replace directory DBA_EXPORT as '/backup2/export/ORCL' --GRANT WRITE ON DIRECTORY <directory_name> TO <schema_name> |
Tab : IMP_Attach_Job_EditButton_for_CorrectSyntax
|
1 2 |
select job_name,state from dba_datapump_jobs; impdp \'/ as sysdba\' attach=SYS_IMPORT_FULL_02 |
Tab : EXP_Attach_Job_EditButton_for_CorrectSyntax
|
1 2 |
select job_name,state from dba_datapump_jobs; expdp \'/ as sysdba\' attach=SYS_IMPORT_FULL_02 |
Tab : Chk_IMPDP_Moving_or_Not
|
1 2 3 4 5 6 |
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; |
Tab : Finding_Errors_in_imp_log
|
1 |
cat scott_IMP.log | grep "ORA-" |egrep -v "ORA-01917|ORA-39083" |
Tab : Schema_Bkp
|
1 |
expdp directory=DBA_EXPORT dumpfile=Schemaname_Oct162020.dmp logfile=SchemaName_Oct162020_log.log schemas=Schemaname rows=y cluster=N flashback_time=systimestamp VERSION=12.1 |
Tab : killbysid
|
1 2 3 4 5 6 7 |
set linesize 300 col a for a60 col machine for a20 col username for a20 col terminal for a20 col program for a20 col osuser for a20 |
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT 'alter system kill session ''' || s.sid || ',' || s.SERIAL# || ''' immediate;' a, s.program, to_char(s.logon_time,'DD-MON-YYYY HH24:MI:SS'), s.status, s.username, s.machine, s.program, s.osuser, 'kill -9 ' || p.SPID FROM v$session s, v$process p WHERE ( (p.addr(+) = s.paddr)) and s.username is not null and s.username not in ('SYSTEM','DBSNMP','RMAN') AND s.sid = &sid; |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
