Useful SQL's

General:

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;

Tino Simon