Usefull SQL

General

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.

• select count(*) from js.booklist;

or

• alter session set current schema=js;
• select count(*) from booklist;

Alter session works very well when you are working constantly in another schema like peoplesoft.

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 run
select 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;