Monitor Oracle user memory wise

set linesize 300
col "User" format a10
col "Machine" format a10
col "Statement" format a60
col MODULE format a30
col sid_serial format a15
SELECT SUBSTR(USERNAME,1,10) "User",
v$session.SID||','||v$session.serial# sid_serial,
SUBSTR(machine,1,10) "Machine",
sharable_mem, persistent_mem,
runtime_mem, executions,
-- v$sql.module,
SUBSTR(v$sql.sql_text,1,60) "Statement"
FROM v$session, v$sql, v$open_cursor
WHERE v$open_cursor.saddr = v$session.saddr
AND v$open_cursor.address = v$sql.address
and rownum < 50
ORDER BY SUBSTR(USERNAME,1,10), SUBSTR(machine,1,10);

User       SID_SERIAL      Machine    SHARABLE_MEM PERSISTENT_MEM RUNTIME_MEM EXECUTIONS Statement
---------- --------------- ---------- ------------ -------------- ----------- ---------- ------------------------------------------------------------
SYS        125,9           oel5u4            13870           3080        2424        261 select timestamp, flags from fixed_obj$ where obj#=:1
SYS        125,9           oel5u4            38694          12400       11380          1 SELECT SUBSTR(USERNAME,1,10) "User", v$session.SID||','||v$s
SYS        125,9           oel5u4            18053           5784        4232        310 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
SYS        125,9           oel5u4            13912          11868       11168          1 select oper, nclsrs, clpcstr from appqossys.wlm_classifier_p
SYS        125,9           oel5u4            18053           5684        4132          4 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
           133,1           oel5u4            26242           6244        5480         57 select size_for_estimate,                      size_factor *
           131,1           oel5u4            18034           4960        4276          4 insert into smon_scn_time (thread, time_mp, time_dp, scn, sc
           133,1           oel5u4            13995           3276        2512         57 select PGA_TARGET_FOR_ESTIMATE s,          PGA_TARGET_FACTOR
           10,7            oel5u4            13938           6752        6032          3 select lobtsn, lobrdba, lobobjid from x$lobstat   where lobs
           16,8            oel5u4            18113           4764        3396         14 select count(*) from sys.job$ where next_date < :1 and (fiel
           133,1           oel5u4            17966           8352        7820          5 delete /*+ CACHE_CB("ALERT_QT") */ from "SYS"."ALERT_QT" whe
           15,1            oel5u4            22099           7744        7068          6 select o.obj#, t.flags from tab$ t, obj$ o, user$ u where  u
           133,1           oel5u4            18028           7752        6968          7 select time_mp, scn, num_mappings, tim_scn_map from smon_scn
           133,1           oel5u4            13968           2576        1812         57 select shared_pool_size_for_estimate s,          shared_pool
           15,1            oel5u4            18053           5684        4132          4 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
           15,1            oel5u4            18053           5784        4232        310 select con#,type#,condlength,intcols,robj#,rcon#,match#,refa
           16,8            oel5u4            13901           3476        2692          8 select userid, name, subname, flags    from sys.scheduler$_l
           16,8            oel5u4            13901           3476        2692          1 select userid, name, subname, flags    from sys.scheduler$_l
           15,1            oel5u4            17991          13872       13340          5 update "SYS"."AQ$_ALERT_QT_H" set transaction_id = NULL wher
           15,1            oel5u4            13891           4200        3224        606 select pos#,intcol#,col#,spare1,bo#,spare2,spare3 from icol$
           7,1             oel5u4            22317           7352        5608        443 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d
           133,1           oel5u4            18143           1072         484          1 BEGIN  dbms_ha_alerts_prvt.clear_instance_resources(   :dbdo
           15,1            oel5u4            18095           6224        4952       1574 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
           7,1             oel5u4            18095           6224        4952       1574 select obj#,type#,ctime,mtime,stime, status, dataobj#, flags
           133,1           oel5u4            38685          19172       17236          5 select  tab.rowid, tab.msgid, tab.corrid, tab.priority, tab.
           15,1            oel5u4            34906          15312       12588          6 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
           133,1           oel5u4            17976           4632        4060          0 select TIME_WAITED_MICRO from V$SYSTEM_EVENT  where event =
           15,1            oel5u4            13968           4500        3716          8 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
           15,1            oel5u4            13968           4500        3716        318 select grantee#,privilege#,nvl(col#,0),max(mod(nvl(option$,0
           133,1           oel5u4             9772           2868        2296          8 select max(FA#) from SYS_FBA_FA
           16,8            oel5u4            22334           5284        3812        310 select job, nvl2(last_date, 1, 0) from sys.job$ where (((:1
           15,1            oel5u4            22154          11820       10588        314 select owner#,name,namespace,remoteowner,linkname,p_timestam
           15,1            oel5u4            22154          11820       10588         13 select owner#,name,namespace,remoteowner,linkname,p_timestam
           15,1            oel5u4            22154          11820       10588          1 select owner#,name,namespace,remoteowner,linkname,p_timestam
           133,1           oel5u4            22587           6760        6040          7 select /*+ FIRST_ROWS(1) */ x.C1, x.C2, x.C3 from  (select a
           15,1            oel5u4            13914           4312        3400        653 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
           7,1             oel5u4            13914           4312        3400        653 select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname,
           133,1           oel5u4            42824          27356       26316          5 insert into "SYS"."ALERT_QT"  (q_name, msgid, corrid, priori
           15,1            oel5u4            13887           3784        3000          4 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
           15,1            oel5u4            13887           3796        3012       1347 select intcol#,nvl(pos#,0),col#,nvl(spare1,0) from ccol$ whe
           133,1           oel5u4            14068           4920        3624          1 select CONNECTION_POOL_NAME, STATUS, MINSIZE, MAXSIZE,
           15,1            oel5u4            43138          17848       14932        407 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
           7,1             oel5u4            43138          17848       14932        407 select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.fla
           140,5           oel5u4            18096            992         444          1  begin      dbms_aqadm_sys.remove_all_nondurablesub(:1, :2);
           133,1           oel5u4            18231           7580        6136          2 select subscriber_id, name, address, protocol, subscriber_ty
           140,5           oel5u4            13916           2736        2164          2 select 1 from sys.aq$_subscriber_table where rownum < 2 and
           16,8            oel5u4            44090          13900       13136         56 select next_run_date, obj#, run_job, sch_job from (select de
           133,1           oel5u4            13978           2660        1896         57 select streams_pool_size_for_estimate s,           streams_p
           15,1            oel5u4            22317           7352        5608        443 select name,intcol#,segcol#,type#,length,nvl(precision#,0),d

49 rows selected.

List of only hidden files in unix

unix hidden file
List only hidden files with ls command


ls -a mydir/ | grep '^\.'

ls -a | grep "^\."

put .txt and of file

unix mv file and name will be xyz.txt .


cat mvfile.sh

for file in * ; do
mv "$file" "$file".txt
done



example
==========



-rw-r--r-- 1 root root 2411 2012-01-18 08:56 v$sql_plan_statistics~
-rw-r--r-- 1 root root 2699 2012-01-18 08:56 v$sql_plan_statistics
-rw-r--r-- 1 root root 8846 2012-01-18 08:56 vmstat~
-rw-r--r-- 1 root root 9677 2012-01-18 08:56 vmstat
-rw-r--r-- 1 root root 5496 2012-01-18 08:56 view~
-rw-r--r-- 1 root root 8102 2012-01-18 08:56 view
-rw-r--r-- 1 root root 75004 2012-01-18 08:56 version count~

apt-amd-02:/home/anujs/Aptus-text-file # ls -ltr *.sh
-rw-r--r-- 1 root root 48 2012-01-17 09:21 file.sh

apt-amd-02:/home/anujs/Aptus-text-file # sh file.sh



-rw-r--r-- 1 root root 2411 2012-01-18 08:56 v$sql_plan_statistics~.txt
-rw-r--r-- 1 root root 9677 2012-01-18 08:56 vmstat.txt
-rw-r--r-- 1 root root 8846 2012-01-18 08:56 vmstat~.txt
-rw-r--r-- 1 root root 8102 2012-01-18 08:56 view.txt
-rw-r--r-- 1 root root 5496 2012-01-18 08:56 view~.txt
-rw-r--r-- 1 root root 75004 2012-01-18 08:56 version count~.txt

Run shell script via dbms_scheduler

Run Shell script via Oracle Job



SQL> begin
dbms_scheduler.create_job
(
job_name => 'EXP_DATAPUMP_FULL_BACKUP',
job_type => 'EXECUTABLE',
job_action => '/usr/bin/ksh',
start_date =>sysdate+ 1/288 ,
number_of_arguments => 1,
repeat_interval => 'freq=daily; byhour=16; byminute=0; bysecond=0',
enabled => false,
comments => 'Oracle Export Data Pump Backup' );
end;
/

PL/SQL procedure successfully completed.


SQL> exec dbms_scheduler.set_job_argument_value(job_name=>'EXP_DATAPUMP_FULL_BACKUP',argument_position=>1,argument_value=>'/home/oracle/Oracle_Datapump/exp_pump.sh') ;

PL/SQL procedure successfully completed.


run this job manually

BEGIN
DBMS_SCHEDULER.RUN_JOB(job_name => 'EXP_DATAPUMP_FULL_BACKUP');
END;



SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
BSLN_MAINTAIN_STATS_JOB TRUE 48
DRA_REEVALUATE_OPEN_FAILURES TRUE 281
EXP_DATAPUMP_FULL_BACKUP FALSE 0 <<<<<<<--------
FGR$AUTOPURGE_JOB FALSE 0
FILE_WATCHER FALSE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 296
PURGE_LOG TRUE 296
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 294
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 294
XMLDB_NFS_CLEANUP_JOB FALSE 0



BEGIN
DBMS_SCHEDULER.enable (NAME => 'EXP_DATAPUMP_FULL_BACKUP');
END;
/



to disable

BEGIN
DBMS_SCHEDULER.disable (NAME => 'EXP_DATAPUMP_FULL_BACKUP', FORCE=> TRUE);
END;
/



SQL> select job_name, enabled, run_count from user_scheduler_jobs;

JOB_NAME ENABL RUN_COUNT
------------------------------ ----- ----------
BSLN_MAINTAIN_STATS_JOB TRUE 48
DRA_REEVALUATE_OPEN_FAILURES TRUE 281
EXP_DATAPUMP_FULL_BACKUP TRUE 0 <<<<<<<<<<<<<-------------
FGR$AUTOPURGE_JOB FALSE 0
FILE_WATCHER FALSE 0
HM_CREATE_OFFLINE_DICTIONARY FALSE 0
ORA$AUTOTASK_CLEAN TRUE 296
PURGE_LOG TRUE 296
RSE$CLEAN_RECOVERABLE_SCRIPT TRUE 294
SM$CLEAN_AUTO_SPLIT_MERGE TRUE 294
XMLDB_NFS_CLEANUP_JOB FALSE 0

11 rows selected.


TTITLE 'Results of Job Chain Testing (from DBA_SCHEDULER_JOB_LOG)'
COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL operation FORMAT A12 HEADING 'Operation'
COL status FORMAT A12 HEADING 'Step|Status'
COL condition FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,operation
,status
,TO_CHAR(additional_info) condition
FROM dba_scheduler_job_log
WHERE 1=1
-- and owner = 'ANUJ'
AND log_date > (SYSDATE - 1/12)
ORDER BY log_id DESC;


COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;




COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;

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




cat /aptus/oracle/admin/Scripts/Exp_Datapump1.sh

# !/bin/bash
TODAY=`date`
ORACLE_SID=aptdb ; export ORACLE_SID
ORACLE_BASE=/aptus/oracle ; export ORACLE_BASE
ORACLE_HOME=/aptus/oracle/product/11.2.0/db_1 ; export ORACLE_HOME
PATH=${ORACLE_HOME}/bin:$PATH ; export PATH
EXPFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.dmp; export EXPFILE
ERRFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.err; export ERRFILE
LOGFILE=$ORACLE_SID.datapump.`date +%d%m%Y`.log ; export LOGFILE
SUCC=/aptus/db-backup/OracleExpBackup/SUCC.txt ; export SUCC

# echo $EXPFILE

find /aptus/db-backup/OracleExpBackup -name "*.err" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.dmp*" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.log*" -ctime +1 -exec rm {} \;
find /aptus/db-backup/OracleExpBackup -name "*.dmp*" -ctime +1 -exec rm {} \;
expdp "'/ as sysdba'" directory=aptusbackup DUMPFILE=$EXPFILE full=y logfile=$LOGFILE EXCLUDE=STATISTICS parallel=4
exit ;
==========




metalink ref


Window issues:
Note 742683.1 Scheduled Job Works Does Not Start In Window.

Purging Diagnostics
Note 443364.1 How To Purge DBA_SCHEDULER_JOB_LOG and DBA_SCHEDULER_WINDOW_LOG
Note 749440.1 Dbms_scheduler.Purge Not Removing Entries from dba_scheduler_job_run_details

Schedule issues:
Note 312547.1 Advanced use of DBMS_JOB.SUBMIT �interval� Parameter
Note 428872.1 Scheduled Jobs Do Not Run After A Re-Start With Repeat_interval => Null.

Issues with Statistic Jobs:
Note 430636.1 GATHER_STATS_JOB Is Not Running
Note 377143.1 How to check what automatic statistics collection is scheduled on 10g
Note 803191.1 Checklist To Diagnose Issues Related To Automatic Run Of
Job GATHER_STATS_JOB

Issues with external Jobs:

http://forums.oracle.com/forums/thread.jspa?threadID=555102

NOTE:389685.1 Items to Check when Problems Running Executables through the Scheduler
NOTE:976049.1 DBMS_SCHEDULER fails with ORA-27369 and
STANDARD_ERROR= Launching external job failed: Invalid username or password
NOTE:1300215.1 ORA-27369: job of type EXECUTABLE failed with exit code: 274667

Please note that this article will guide through the correct analysis for Scheduler/Job Problems.

It is also practicable to go through a list of known issues / bugs which will be listed in
NOTE:1311355.1 Known Issues And Bugs With Using the Scheduler
This article is currently under edit and will be made publish in the near future


these file should be there



oracle@apt-amd-02:~/Oracle_Datapump> ls -ltr $ORACLE_HOME/rdbms/admin/externaljob.ora
-rw-r----- 1 root oinstall 1534 2005-12-21 12:30 /opt/app/oracle/product/11.2/rdbms/admin/externaljob.ora



SQL> !ls -ltr $ORACLE_HOME/bin/extjob
-rwsr-x--- 1 root oinstall 1232563 2011-01-10 15:16 /opt/app/oracle/product/11.2/bin/extjob

Oracle session info

e.sql


select ses.sid ||','||ses.serial#,sqa.SQL_TEXT SQL from
v$session ses, v$sqlarea sqa, v$process proc
where ses.paddr=proc.addr
and ses.sql_hash_value=sqa.hash_value
and proc.spid=&1;


-bash-3.00$ cat s.sql
set pagesize 32000
set linesize 200
column sid format a12
column username format a10
column terminal format a10
column sql_text format a40 wrap
Select ''''||s.sid||','||s.serial#||'''' SID
,s.status
,s.username
,s.terminal
,q.SQL_TEXT
,q.command_type
,s.sql_id
from
v$session s
,(select distinct
address
,sql_text
,command_type
from v$sql) q
where
q.address=s.sql_address
order by s.sid
/
prompt >>>>>>>>>>>>>>>>>>>>>>>>>>>-- run e.sql for explain plan alter system kill session IMMEDIATE;

Oracle SORT ACTIVITY

Oracle Sort Info


set linesize 150 pagesize 1400;

SELECT *
FROM (SELECT matching_criteria,
TO_CHAR(force_matching_signature) force_matching_signature,
sql_id,
child_number,
sql_text,
executions,
elapsed_time / 1000,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time / 1000,
num_sort_operations,
tot_active_time / 1000,
tot_optimal_executions,
tot_onepass_executions,
tot_multipasses_executions,
all_tot_active_time / 1000,
max_tempseg_size,
parsing_schema_name
FROM (SELECT force_matching_signature,
sql_id,
child_number,
sql_text,
matching_criteria,
SUM(executions) OVER(PARTITION BY matching_criteria) executions,
SUM(elapsed_time) OVER(PARTITION BY matching_criteria) elapsed_time,
operation_type,
policy,
estimated_optimal_size,
last_memory_used,
last_execution,
active_time,
num_sort_operations,
SUM(tot_active_time) OVER(PARTITION BY matching_criteria) tot_active_time,
SUM(tot_optimal_executions) OVER(PARTITION BY matching_criteria) tot_optimal_executions,
SUM(tot_onepass_executions) OVER(PARTITION BY matching_criteria) tot_onepass_executions,
SUM(tot_multipasses_executions) OVER(PARTITION BY matching_criteria) tot_multipasses_executions,
MAX(max_tempseg_size) OVER(PARTITION BY matching_criteria) max_tempseg_size,
SUM(tot_active_time) OVER() all_tot_active_time,
ROW_NUMBER() OVER(PARTITION BY matching_criteria ORDER BY tot_multipasses_executions DESC, tot_onepass_executions DESC, last_memory_used DESC) rnum,
parsing_schema_name
FROM (SELECT s.sql_id,
s.child_number,
s.sql_text,
s.executions,
s.elapsed_time,
s.force_matching_signature,
CASE
WHEN s.force_matching_signature > 0 THEN
TO_CHAR(s.force_matching_signature)
ELSE
s.sql_id
END matching_criteria,
ROW_NUMBER() OVER(PARTITION BY s.sql_id, s.child_number ORDER BY sw.multipasses_executions DESC, sw.onepass_executions DESC, sw.last_memory_used DESC) rnum,
sw.operation_type,
sw.policy,
sw.estimated_optimal_size,
sw.last_memory_used,
sw.last_execution,
MAX(sw.max_tempseg_size) OVER(PARTITION BY s.sql_id, s.child_number) max_tempseg_size,
sw.active_time * sw.total_executions active_time,
SUM(sw.active_time * sw.total_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_active_time,
COUNT(*) OVER(PARTITION BY s.sql_id, s.child_number) num_sort_operations,
SUM(sw.optimal_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_optimal_executions,
SUM(sw.onepass_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_onepass_executions,
SUM(sw.multipasses_executions) OVER(PARTITION BY s.sql_id, s.child_number) tot_multipasses_executions,
NVL(u.username, s.parsing_schema_name) parsing_schema_name
FROM v$sql s, v$sql_workarea sw, all_users u
WHERE sw.sql_id = s.sql_id
AND sw.child_number = s.child_number
AND u.user_id(+) = s.parsing_user_id)
WHERE rnum = 1)
WHERE rnum = 1
ORDER BY tot_multipasses_executions DESC,
tot_onepass_executions DESC,
last_memory_used DESC)
WHERE ROWNUM <= 200
/

SELECT *
FROM (SELECT s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
NVL(SUM(CASE
WHEN sn.name = 'sorts (memory)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_memory,
NVL(SUM(CASE
WHEN sn.name = 'sorts (disk)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_disk,
NVL(SUM(CASE
WHEN sn.name = 'sorts (rows)' THEN
ss.VALUE
ELSE
0
END),
0) sorts_rows,
NVL(SUM(CASE
WHEN sn.name = 'physical reads direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) reads_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'physical writes direct temporary tablespace' THEN
ss.VALUE
ELSE
0
END),
0) writes_direct_temp,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - optimal' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_optimal,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - onepass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_onepass,
NVL(SUM(CASE
WHEN sn.name = 'workarea executions - multipass' THEN
ss.VALUE
ELSE
0
END),
0) workarea_exec_multipass
FROM v$session s,
v$sesstat ss,
v$statname sn,
v$process p,
x$ksbdp b
WHERE s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
AND s.TYPE = 'USER'
AND s.sid = ss.sid
AND ss.statistic# = sn.statistic#
AND sn.name IN ('sorts (memory)',
'sorts (disk)',
'sorts (rows)',
'physical reads direct temporary tablespace',
'physical writes direct temporary tablespace',
'workarea executions - optimal',
'workarea executions - onepass',
'workarea executions - multipass')
GROUP BY s.sid,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '(')))
ORDER BY workarea_exec_multipass DESC,
workarea_exec_onepass DESC,
reads_direct_temp + writes_direct_temp DESC,
sorts_rows DESC)
WHERE ROWNUM <= 200
/

SELECT rawtohex(workarea_address) workarea_address,
sql_id,
sql_text,
operation_type,
policy,
sid,
active_time,
work_area_size,
expected_size,
actual_mem_used,
max_mem_used,
number_passes,
tempseg_size,
tablespace,
complete_ratio,
elapsed,
time_remaining,
opname,
machine,
program,
module,
osuser,
username
FROM (SELECT swa.workarea_address,
swa.sql_id,
sa.sql_text,
swa.operation_type,
swa.policy,
swa.sid,
swa.active_time / 1000 active_time,
swa.work_area_size,
swa.expected_size,
swa.actual_mem_used,
swa.max_mem_used,
swa.number_passes,
swa.tempseg_size,
swa.tablespace,
(CASE
WHEN sl.totalwork <> 0 THEN
sl.sofar / sl.totalwork
ELSE
NULL
END) complete_ratio,
sl.elapsed_seconds * 1000 elapsed,
sl.time_remaining * 1000 time_remaining,
sl.opname,
s.machine,
s.program,
s.module,
s.osuser,
NVL(DECODE(TYPE,
'BACKGROUND',
'SYS (' || b.ksbdpnam || ')',
s.username),
SUBSTR(p.program, INSTR(p.program, '('))) username,
ROW_NUMBER() OVER(PARTITION BY swa.sql_id ORDER BY sl.last_update_time DESC) rnum
FROM v$sql_workarea_active swa,
v$sqlarea sa,
(SELECT * FROM v$session_longops WHERE sofar <> totalwork) sl,
v$session s,
v$process p,
x$ksbdp b
WHERE sl.sid(+) = swa.sid
AND sl.sql_id(+) = swa.sql_id
AND swa.sid <> USERENV('sid')
AND sa.sql_id = swa.sql_id
AND s.sid = swa.sid
AND s.paddr = p.addr
AND b.inst_id(+) = USERENV('INSTANCE')
AND p.addr = b.ksbdppro(+)
ORDER BY swa.number_passes DESC, swa.work_area_size DESC)
WHERE rnum = 1
/

Oracle Job logs

DBMS_SCHEDULER FAILS log
Oracle SCHEDULER log
SCHEDULER log


COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;


COL log_id FORMAT 999999 HEADING 'Job|Log#'
COL job_name FORMAT A30 HEADING 'Job Name'
COL job_subname FORMAT A20 HEADING 'Step Name'
COL status FORMAT A12 HEADING 'Step Status'
COL error# FORMAT 9999999 HEADING 'Error|Code'
COL additional_info FORMAT A40 HEADING 'Additional Information' WRAP
SELECT
log_id
,job_name
,job_subname
,status
,error#
,additional_info
,to_char(LOG_DATE,'dd-mm-yyyy hh:mi:ss')
FROM dba_scheduler_job_run_details JRD
WHERE 1=1
-- JRD.owner = 'ANUJ'
-- AND log_date > (SYSDATE - 2)
and status!='SUCCEEDED'
ORDER BY log_id DESC;