General
Load Specific
I plan on adding to this post over time. I have started with process scheduler related queries.
Alter session - so you do not have to preface everything with user.table
alter session set current schema=;
Example: If I needed to access a table(booklist) under the js schema and I was logged in at mj I could do the following.
Alter session - so you do not have to preface everything with user.table
alter session set current schema=
Example: If I needed to access a table(booklist) under the js schema and I was logged in at mj I could do the following.
- select count(*) from js.booklist;
- alter session set current schema=js;
- select count(*) from booklist;
Load Specific
I use some oracle specific sql some times.
Process Scheduler
Rundates with the number of processes that were queued when an item was scheduled to runselect to_char(a.rundttm,'yyyy-mm-dd hh24:mi:ss') rundate, count(*) from ( select distinct rundttm from sysadm.ps_pmn_prcslist) a join sysadm.ps_pmn_prcslist b on a.rundttm between b.rundttm and b.enddttm group by a.rundttm;
How long processes are talking to run
select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || (a.ENDDTTM-a.BEGINDTTM) * 1440 as ProcessIDProcessNameMinutesTaken from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;
select a.PRCSINSTANCE || ',' || a.PRCSNAME || ',' || floor(((((a.ENDDTTM-a.BEGINDTTM)*24*60*60)/3600)*3600)/60) as IdNameMinutes from sysadm.ps_pmn_prcslist a where a.BEGINDTTM > sysdate-7;
CSV export of the process scheduler table
select OPRID || ',' || PRCSINSTANCE || ',' || PRCSTYPE || ',' || PRCSNAME || ',' || to_char(rundttm,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(BEGINDTTM,'yyyy-mm-dd hh24:mi:ss') || ',' || to_char(ENDDTTM,'yyyy-mm-dd hh24:mi:ss') from sysadm.ps_pmn_prcslist;
No comments:
Post a Comment