"RenameQueries.sql"
UPDATE PSQRYDEFN set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYDEFNLANG set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYBIND set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYBINDLANG set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYCRITERIA set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYEXPR set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYFIELD set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYFIELDLANG set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
—UPDATE PSQRYFLAGS set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYLINK set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYRECORD set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');
UPDATE PSQRYSELECT set qryname = replace(qryname,'XGL','ZGL') where qryname in (select objectvalue1 from psprojectitem where projectname = 'VCR_QUERIES_BM');

update psprojectitem set objectvalue1 = replace(objectvalue1,'XGL','ZGL') where projectname = 'VCR_QUERIES_BM';

"SetQryVersion.sql"
—This script increments the version number on an operator after you update it
UPDATE PSLOCK SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'QDM'
/
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'SYS'
/
UPDATE PSVERSION SET VERSION = VERSION + 1 WHERE OBJECTTYPENAME = 'QDM'
/
UPDATE PSQRYDEFN SET VERSION = (SELECT VERSION FROM PSVERSION WHERE OBJECTTYPENAME = 'QDM') where oprid = ' ' and upper(QRYNAME) like upper('%&1')
/
"ShowActiveSQL.sql"
rem Author: Mark Lang, 1998
rem Name: lastsql.sql
rem Purpose: Display last executed SQL statement by session with stats
rem Usage: @lastsql <%user%> <%status%>
rem Subject: session
rem Attrib: sql dba
rem Descr:
rem Notes:
rem SeeAlso: @sqlarea
rem History:
rem 01-feb-98 Initial release

@setup

column username format a10
column status format a10
column sid format a15
column serial# format a15
column osuser format a10
column machine format a8
column process format a9
column client_info format a15
column text format a80 word
set long 20000
set lines 200
set pages 1000

define usr="upper('SYSADM')"
define sta="upper('ACTIVE')"

select
s.username
,s.sid sidd
,s.serial# serialno
,s.osuser
,s.machine
,s.process
,s.client_info
,'(diskr='||l.disk_reads

text
from v$session s, v$sql l
where s.sql_address = l.address
and s.username is not null and s.username <> 'SYSTEM'
and s.status like &&sta
;

undef usr sta

@setdefs

"ShowActiveSQLG.sql"
rem Author: Mark Lang, 1998
rem Name: lastsql.sql
rem Purpose: Display last executed SQL statement by session with stats
rem Usage: @lastsql <%user%> <%status%>
rem Subject: session
rem Attrib: sql dba
rem Descr:
rem Notes:
rem SeeAlso: @sqlarea
rem History:
rem 01-feb-98 Initial release

@setup

column username format a10
column status format a10
column sid format a15
column serial# format a15
column osuser format a10
column machine format a8
column process format a9
column client_info format a15
column text format a80 word
set long 20000
set lines 200
set pages 1000

define usr="upper('SYSADM')"
define sta="upper('ACTIVE')"

select
s.username
,s.sid sidd
,s.serial# serialno
,s.osuser
,s.machine
,s.process
,s.client_info
,'(diskr='||l.disk_reads

text
from gv$session s, gv$sql l
where s.sql_address = l.address
and s.username is not null and s.username <> 'SYSTEM'
and s.status like &&sta
;

undef usr sta

@setdefs

"ShowAppEngine.sql"
SELECT
SERVERNAMERUN,
case when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=1 then 'Cancel'
when RUNSTATUS=2 then 'Delete'
when RUNSTATUS=3 then 'Error'
when RUNSTATUS=4 then 'Hold'
when RUNSTATUS=5 then 'Queued'
when RUNSTATUS=6 then 'Initiated'
when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=8 then 'Cancelled'
when RUNSTATUS=9 then 'Success'
when RUNSTATUS=10 then 'Not Successful'
when RUNSTATUS=11 then 'Posted'
when RUNSTATUS=12 then 'Unable to Post'
when RUNSTATUS=13 then 'Resend'
when RUNSTATUS=14 then 'Posting'
when RUNSTATUS=15 then 'Content Generated'
when RUNSTATUS=16 then 'Pending'
when RUNSTATUS=17 then 'Success with Warning'
when RUNSTATUS=18 then 'Blocked'
when RUNSTATUS=19 then 'Restart'
else RUNSTATUS END status,
COUNT(*) RunningAppEngineCnt
FROM PSPRCSQUE PSPRCSRQST —PS_PMN_PRCSLIST
WHERE
—PRCSJOBSEQ = 0 AND
PRCSTYPE = 'Application Engine'
group by
SERVERNAMERUN,
case when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=1 then 'Cancel'
when RUNSTATUS=2 then 'Delete'
when RUNSTATUS=3 then 'Error'
when RUNSTATUS=4 then 'Hold'
when RUNSTATUS=5 then 'Queued'
when RUNSTATUS=6 then 'Initiated'
when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=8 then 'Cancelled'
when RUNSTATUS=9 then 'Success'
when RUNSTATUS=10 then 'Not Successful'
when RUNSTATUS=11 then 'Posted'
when RUNSTATUS=12 then 'Unable to Post'
when RUNSTATUS=13 then 'Resend'
when RUNSTATUS=14 then 'Posting'
when RUNSTATUS=15 then 'Content Generated'
when RUNSTATUS=16 then 'Pending'
when RUNSTATUS=17 then 'Success with Warning'
when RUNSTATUS=18 then 'Blocked'
when RUNSTATUS=19 then 'Restart'
else RUNSTATUS END
/
CASE WHEN sal>1000 THEN 'Over paid' ELSE 'Under paid' END

"ShowComponentNavigation.sql"
set pages 0
set lines 255
set trimsp on
column navigation format a132
column url format a255
set verify off
set feedback off

select SYS_CONNECT_BY_PATH(A.PORTAL_LABEL,'\') navigation,
'/EMPLOYEE/ERP/c/' || PORTAL_URI_SEG1 || '.' || PORTAL_URI_SEG2 || '.' || PORTAL_URI_SEG3 url
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
portal_name = 'EMPLOYEE' and
portal_objname <> portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
where PORTAL_URI_SEG2 like '&&1' || '%'
start with A.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
connect by prior A.portal_objname = A.portal_prntobjname
/
undef 1
set verify on
set feedback on
set pages 100

"ShowComponentRecords.sql"
select recname, rectype, max(updatable) updatable, recdescr
from (
select b.RECNAME, c.rectype, case when min(mod(b.fielduse,2))=0 and min(a.hidden) = 0 then '1' else '0' END Updatable, c.recdescr
from pspnlgroup a, pspnlfield b, psrecdefn c
where
a.pnlname = b.pnlname and
b.recname = c.recname and
a.pnlgrpname = '&1' and
b.recname <> ' '
group by b.recname, c.rectype, c.recdescr
union
select b.RECNAME, c.rectype, case when min(mod(b.fielduse,2))=0 then '1' else '0' END Updatable, c.recdescr
from pspnlfield b, psrecdefn c
where
b.recname = c.recname and
b.recname <> ' ' and
b.pnlname in (
select distinct subpnlname
from pspnlgroup a, pspnlfield d
where a.pnlname = d.pnlname and
a.pnlgrpname = '&1' and
subpnlname <> ' '
)
group by b.recname, c.rectype, c.recdescr
order by 2,1
) a
group by recname, rectype, recdescr
order by 2,3 desc,1
/

"ShowDB.sql"
set lines 132
select a.name, b.dbname, a.created , b.ownerid, c.longname, c.guid —, c.license_code
from v$database a, ps.psdbowner b, psoptions c;

"ShowDBLocks.sql"
rem ---------------
rem Filename: lock.sql
rem Purpose: Display database locks and latched (with tables names, etc)
rem Date: 12-Apr-1998
rem Author: Frank Naude (frank@onwe.co.za)
rem ---------------

set pagesize 23

col sid format 999999
col serial# format 999999
col username format a12 trunc
col process format a8 trunc
col terminal format a12 trunc
col type format a12 trunc
col lmode format a4 trunc
col lrequest format a4 trunc
col object format a73 trunc
— spool c:\temp\locks.txt
select s.sid, s.serial#,
decode(s.process, null,
decode(substr(p.username,1,1), '?', upper(s.osuser), p.username),
decode( p.username, 'ORACUSR ', upper(s.osuser), s.process)
) process,
nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username,
decode(s.terminal, null, rtrim(p.terminal, chr(0)),
upper(s.terminal)) terminal,
decode(l.type,
— Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
— Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', decode(u.name, null,
'DICTIONARY OBJECT', u.name||'.'||o.name),
'TD', u.name||'.'||o.name,
'TM', u.name||'.'||o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE',
'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) object
from sys.v_$lock l, sys.v_$session s, sys.obj$ o, sys.user$ u,
sys.v_$process p
where s.paddr = p.addr(+)
and l.sid = s.sid
and l.id1 = o.obj#(+)
and o.owner# = u.user#(+)
and l.type <> 'MR'
UNION ALL /* LATCH HOLDERS */
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from sys.v_$process p, sys.v_$session s, sys.v_$latchholder h
where h.pid = p.pid
and p.addr = s.paddr
UNION ALL /* LATCH WAITERS */
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from sys.v_$session s, sys.v_$process p, sys.v_$latch l
where latchwait is not null
and p.addr = s.paddr
and p.latchwait = l.addr
/
— spool off
Here's a script I got from http://ora.seiler.us/2007/03/how-to-find-blocking-locks-and.html
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

"ShowDBLocksG.sql"
rem ---------------
rem Filename: lock.sql
rem Purpose: Display database locks and latched (with tables names, etc)
rem Date: 12-Apr-1998
rem Author: Frank Naude (frank@onwe.co.za)
rem ---------------

set pagesize 23

col sid format 999999
col serial# format 999999
col username format a12 trunc
col process format a8 trunc
col terminal format a12 trunc
col type format a12 trunc
col lmode format a4 trunc
col lrequest format a4 trunc
col object format a73 trunc
— spool c:\temp\locks.txt
select s.sid, s.serial#,
decode(s.process, null,
decode(substr(p.username,1,1), '?', upper(s.osuser), p.username),
decode( p.username, 'ORACUSR ', upper(s.osuser), s.process)
) process,
nvl(s.username, 'SYS ('||substr(p.username,1,4)||')') username,
decode(s.terminal, null, rtrim(p.terminal, chr(0)),
upper(s.terminal)) terminal,
decode(l.type,
— Long locks
'TM', 'DML/DATA ENQ', 'TX', 'TRANSAC ENQ',
'UL', 'PLS USR LOCK',
— Short locks
'BL', 'BUF HASH TBL', 'CF', 'CONTROL FILE',
'CI', 'CROSS INST F', 'DF', 'DATA FILE ',
'CU', 'CURSOR BIND ',
'DL', 'DIRECT LOAD ', 'DM', 'MOUNT/STRTUP',
'DR', 'RECO LOCK ', 'DX', 'DISTRIB TRAN',
'FS', 'FILE SET ', 'IN', 'INSTANCE NUM',
'FI', 'SGA OPN FILE',
'IR', 'INSTCE RECVR', 'IS', 'GET STATE ',
'IV', 'LIBCACHE INV', 'KK', 'LOG SW KICK ',
'LS', 'LOG SWITCH ',
'MM', 'MOUNT DEF ', 'MR', 'MEDIA RECVRY',
'PF', 'PWFILE ENQ ', 'PR', 'PROCESS STRT',
'RT', 'REDO THREAD ', 'SC', 'SCN ENQ ',
'RW', 'ROW WAIT ',
'SM', 'SMON LOCK ', 'SN', 'SEQNO INSTCE',
'SQ', 'SEQNO ENQ ', 'ST', 'SPACE TRANSC',
'SV', 'SEQNO VALUE ', 'TA', 'GENERIC ENQ ',
'TD', 'DLL ENQ ', 'TE', 'EXTEND SEG ',
'TS', 'TEMP SEGMENT', 'TT', 'TEMP TABLE ',
'UN', 'USER NAME ', 'WL', 'WRITE REDO ',
'TYPE='||l.type) type,
decode(l.lmode, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.lmode) ) lmode,
decode(l.request, 0, 'NONE', 1, 'NULL', 2, 'RS', 3, 'RX',
4, 'S', 5, 'RSX', 6, 'X',
to_char(l.request) ) lrequest,
decode(l.type, 'MR', decode(u.USERNAME, null,
'DICTIONARY OBJECT', u.USERNAME||'.'||o.name),
'TD', u.USERNAME||'.'||o.name,
'TM', u.USERNAME||'.'||o.name,
'RW', 'FILE#='||substr(l.id1,1,3)||
' BLOCK#='||substr(l.id1,4,5)||' ROW='||l.id2,
'TX', 'RS+SLOT#'||l.id1||' WRP#'||l.id2,
'WL', 'REDO LOG FILE#='||l.id1,
'RT', 'THREAD='||l.id1,
'TS', decode(l.id2, 0, 'ENQUEUE',
'NEW BLOCK ALLOCATION'),
'ID1='||l.id1||' ID2='||l.id2) object
from gv$lock l, gv$session s, sys.obj$ o, all_users u,
gv$process p
where s.paddr = p.addr(+)
and l.sid = s.sid
and l.id1 = o.obj#(+)
and o.owner# = u.user_id(+)
and l.type <> 'MR'
UNION ALL /* LATCH HOLDERS */
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'X', 'NONE', h.name||' ADDR='||rawtohex(laddr)
from gv$process p, gv$session s, gv$latchholder h
where h.pid = p.pid
and p.addr = s.paddr
UNION ALL /* LATCH WAITERS */
select s.sid, s.serial#, s.process, s.username, s.terminal,
'LATCH', 'NONE', 'X', name||' LATCH='||p.latchwait
from gv$session s, gv$process p, gv$latch l
where latchwait is not null
and p.addr = s.paddr
and p.latchwait = l.addr
/
— spool off
Here's a script I got from http://ora.seiler.us/2007/03/how-to-find-blocking-locks-and.html
select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from gv$lock l1, gv$session s1, gv$lock l2, gv$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
—and l2.id2 = l2.id2 ;

"ShowDBWaits.sql"
set lines 255
set pages 66

select event,
total_waits,
round(100 * (total_waits / sum_waits),2) pct_waits,
time_wait_sec,
round(100 * (time_wait_sec / greatest(sum_time_waited,1)),2)
pct_time_waited,
total_timeouts,
round(100 * (total_timeouts / greatest(sum_timeouts,1)),2)
pct_timeouts,
average_wait_sec
from
(select event,
total_waits,
round((time_waited / 100),2) time_wait_sec,
total_timeouts,
round((average_wait / 100),2) average_wait_sec
from sys.v_$system_event
where event not in
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'rdbms ipc reply',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data from client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep',
'jobq slave wait',
'Queue Monitor Wait',
'wakeup time manager',
'PX Idle Wait') AND
event not like 'DFS%' AND
event not like 'KXFX%'),
(select sum(total_waits) sum_waits,
sum(total_timeouts) sum_timeouts,
sum(round((time_waited / 100),2)) sum_time_waited
from sys.v_$system_event
where event not in
('lock element cleanup',
'pmon timer',
'rdbms ipc message',
'rdbms ipc reply',
'smon timer',
'SQL*Net message from client',
'SQL*Net break/reset to client',
'SQL*Net message to client',
'SQL*Net more data from client',
'dispatcher timer',
'Null event',
'parallel query dequeue wait',
'parallel query idle wait - Slaves',
'pipe get',
'PL/SQL lock timer',
'slave wait',
'virtual circuit status',
'WMON goes to sleep',
'jobq slave wait',
'Queue Monitor Wait',
'wakeup time manager',
'PX Idle Wait') AND
event not like 'DFS%' AND
event not like 'KXFX%')
order by 4 desc, 1 asc
/

"ShowMaint.sql"
select update_id, descr, DTTM_IMPORTED
from ps_maintenance_log a
where update_id = '&1';

"ShowPageRecords.sql"
select distinct a.RECNAME, c.rectype, c.recdescr
from pspnlfield a, psrecdefn c
where a.recname = c.recname and
PNLNAME = '&1'
order by c.rectype;

"ShowPRCS.sql"
set verify off

SELECT
SERVERNAMERUN, PRCSTYPE,
case when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=1 then 'Cancel'
when RUNSTATUS=2 then 'Delete'
when RUNSTATUS=3 then 'Error'
when RUNSTATUS=4 then 'Hold'
when RUNSTATUS=5 then 'Queued'
when RUNSTATUS=6 then 'Initiated'
when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=8 then 'Cancelled'
when RUNSTATUS=9 then 'Success'
when RUNSTATUS=10 then 'Not Successful'
when RUNSTATUS=11 then 'Posted'
when RUNSTATUS=12 then 'Unable to Post'
when RUNSTATUS=13 then 'Resend'
when RUNSTATUS=14 then 'Posting'
when RUNSTATUS=15 then 'Content Generated'
when RUNSTATUS=16 then 'Pending'
when RUNSTATUS=17 then 'Success with Warning'
when RUNSTATUS=18 then 'Blocked'
when RUNSTATUS=19 then 'Restart'
else RUNSTATUS END status,
COUNT(*)
FROM PSPRCSRQST —PS_PMN_PRCSLIST
WHERE
(SERVERNAMERUN like '&1' || '%')
group by SERVERNAMERUN, PRCSTYPE,
case when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=1 then 'Cancel'
when RUNSTATUS=2 then 'Delete'
when RUNSTATUS=3 then 'Error'
when RUNSTATUS=4 then 'Hold'
when RUNSTATUS=5 then 'Queued'
when RUNSTATUS=6 then 'Initiated'
when RUNSTATUS=7 then 'Processing'
when RUNSTATUS=8 then 'Cancelled'
when RUNSTATUS=9 then 'Success'
when RUNSTATUS=10 then 'Not Successful'
when RUNSTATUS=11 then 'Posted'
when RUNSTATUS=12 then 'Unable to Post'
when RUNSTATUS=13 then 'Resend'
when RUNSTATUS=14 then 'Posting'
when RUNSTATUS=15 then 'Content Generated'
when RUNSTATUS=16 then 'Pending'
when RUNSTATUS=17 then 'Success with Warning'
when RUNSTATUS=18 then 'Blocked'
when RUNSTATUS=19 then 'Restart'
else RUNSTATUS END
/

"ShowProcessNavigation.sql"
set pages 132
set lines 132
column navigation format a132
column url format a100
set verify off

select SYS_CONNECT_BY_PATH(A.PORTAL_LABEL,'~') navigation, '/EMPLOYEE/ERP/c/' || PORTAL_URI_SEG1 || '.' || PORTAL_URI_SEG2 || '.' || PORTAL_URI_SEG3 url
from (SELECT distinct A.PORTAL_NAME, A.PORTAL_LABEL, A.PORTAL_OBJNAME, a.PORTAL_PRNTOBJNAME,
A.PORTAL_URI_SEG1, A.PORTAL_URI_SEG2, A.PORTAL_URI_SEG3, A.PORTAL_REFTYPE
FROM PSPRSMDEFN A
WHERE
portal_name = 'EMPLOYEE' and
portal_objname <> portal_prntobjname and
not exists (
select 'x'
from PSPRSMSYSATTRVL
where portal_name = A.PORTAL_NAME AND
portal_Reftype = A.PORTAL_REFTYPE and
portal_objname = A.PORTAL_OBJNAME and
PORTAL_ATTR_NAM = 'PORTAL_HIDE_FROM_NAV' and
A.PORTAL_OBJNAME not in ('CO_NAVIGATION_COLLECTIONS', 'PORTAL_BASE_DATA')
)
) A
where PORTAL_URI_SEG2 in (select PNLGRPNAME from ps_prcsdefnpnl where prcsname like '&&1' || '%')
start with A.portal_prntobjname = 'PORTAL_ROOT_OBJECT'
connect by prior A.portal_objname = A.portal_prntobjname
/
undef 1

"ShowProject.sql"
— This script finds projects that contain an object
set lines 255

select a.projectname, b.PROJECTDESCR,
decode(a.objecttype,0,'Records',
decode(a.objecttype,1,'Indexes',
decode(a.objecttype,2,'Fields',
decode(a.objecttype,3,'Field Format',
decode(a.objecttype,4,'Translate',
decode(a.objecttype,5,'Page',
decode(a.objecttype,6,'Menu',
decode(a.objecttype,7,'Component',
decode(a.objecttype,8,'PeopleCode',
decode(a.objecttype,9,'Menu PeopleCode',
decode(a.objecttype,10,'Query',
decode(a.objecttype,11,'Tree Structures',
decode(a.objecttype,12,'Tree',
decode(a.objecttype,13,'Access Group',
decode(a.objecttype,14,'Color',
decode(a.objecttype,15,'Style',
decode(a.objecttype,16,'Business Process Map',
decode(a.objecttype,17,'Business Process',
decode(a.objecttype,18,'Activity',
decode(a.objecttype,19,'Role',
decode(a.objecttype,20,'Process Definition',
decode(a.objecttype,21,'Process Server',
decode(a.objecttype,22,'Process Type',
decode(a.objecttype,23,'Process Job',
decode(a.objecttype,24,'Process Recurrence',
decode(a.objecttype,25,'Message',
decode(a.objecttype,26,'Dimension',
decode(a.objecttype,27,'Analysis Model',
decode(a.objecttype,28,'Cube Template',
decode(a.objecttype,53,'Permission List',
decode(a.objecttype,54,'Portal Registry Definition',
decode(a.objecttype,55,'Portal Registry Structure',
a.objecttype)))))))))))))))))))))))))))))))) OBJECTTYPE,
RTRIM(a.objectvalue1 || '.' ||
DECODE(a.OBJECTVALUE2,' ','',a.OBJECTVALUE2) || '.' ||
DECODE(a.OBJECTVALUE3,' ','',a.OBJECTVALUE3) || '.' ||
DECODE(a.OBJECTVALUE4,' ','',a.OBJECTVALUE4) || '.','.') OBJECT
from psprojectitem A, psprojectdefn b
where a.projectname = b.projectname and
(upper(a.objectvalue1) like upper('&1%') or
upper(a.objectvalue2) like upper('&1%') or
upper(a.objectvalue3) like upper('&1%') or
upper(a.objectvalue4) like upper('&1%') or
upper(a.projectname) like upper('&1%'))
/

"ShowQueryLog.sql"
set lines 1000
set pages 1000
set trimspool on
set echo off

select 'Queries that run longer than 5 seconds' from dual
/
select * from PSQRYEXECLOG
where exectime + fetchtime > 5
order by EXECTIME desc
/
select 'Most frequently run queries' from dual
/
select oprid, qryname, count(*)
from PSQRYEXECLOG
group by oprid, qryname
having count(*) > 1
order by count(*) desc
/
—Queries with the most rows returned
select * from PSQRYEXECLOG
where numrows > 100000
order by numrows desc
/

"ShowRecordComponents.sql"
— need to have it check subpanels
— Joins to primary index to get a sense of relevance
select a.pnlgrpname, count(*)
from pspnlgroup a, pspnlfield b, psrecdefn c, all_ind_columns d
where
a.pnlname = b.pnlname and
b.recname = c.recname and
a.pnlgrpname <> ' ' and
b.recname = upper('&&1') and
d.index_owner = 'SYSADM' and
d.index_name = 'PS_' || b.recname and
d.column_name = b.fieldname
group by a.pnlgrpname
order by 2 desc,1
/
undef 1