Oracle SQL V$

• By psgeek
• October 28, 2019

Here are some popular commands to use in Oracle Database.

If some of these don’t work, it is most likely permission issues.

Session Wait Events

• V$SESSION
• V$SESSION_WAIT
• V$SESSION_EVENT
• V$SESS_TIME_MODEL

Long Running Queries

• V$SQL
• V$SESSION_LONGOPS

Session Counters

• V$SESSTAT

Which Processes Did A User Run

• By psgeek
• September 14, 2016

This SQL is useful if you want to know which processes a user has recently run.

It shows the Process Name and Type, the Process Description, their Run Control ID, the number of runs, and a min/max on the Run date.

Note:  This SQL may have limited data if the process request table gets purged often.

In this SQL, we chose to use the PS_PMN_PRCSLIST view.  We also use a process to pull the description from the Process Defn or the Job Defn table (see alias C).

SELECT P.PRCSNAME, P.PRCSTYPE
, C.DESCR "Process Description"
, P.RUNCNTLID
, COUNT(*) "Number of Runs"
, MIN(P.RUNDTTM) "First Ran"
, MAX(P.RUNDTTM) "Last Ran"
FROM PS_PMN_PRCSLIST P
, (SELECT PRCSTYPE, PRCSNAME, DESCR FROM PS_PRCSDEFN UNION SELECT 'PSJob', PRCSJOBNAME, DESCR FROM PS_PRCSJOBDEFN) C
WHERE P.PRCSTYPE = C.PRCSTYPE
AND P.PRCSNAME = C.PRCSNAME
AND P.OPRID = 'VP1'
AND P.RUNDTTM > '07/01/2015'
--AND P.PRCSNAME <> 'FS_STREAMLN'
GROUP BY P.PRCSTYPE, P.PRCSNAME, P.RUNCNTLID, C.DESCR
ORDER BY 1,2,3,4

Find Translates and Prompt Tables Via SQL

• By psgeek
• September 13, 2016

Here are two tricks to get the translate values and the prompt table, from App Designer, without logging into App Designer.

Trick #1

A lot of times, the values you are searching for are in a table called Translates (PSXLATITEM).  Most of these are delivered by PeopleSoft.  Note that the table is effective dated, but you usually can get by without the effective dated logic.  Also, note that the table does not start with PS_, but PS (no underscore).  That means it is a tools table, not an application table.  Also, use the FIELDVALUE field to link to the actual field.  Also, you don’t usually need to outer join this, but for the LEDGER_KK and this field (KK_BUDG_TRANS_TYPE), you should, since only the budget rows use the table.

SELECT * FROM PSXLATITEM
WHERE FIELDNAME = 'KK_BUDG_TRANS_TYPE'

Trick #2

If you can’t find the fieldname in translate table, here is another trick.

Sometimes a field is tied to a prompt table and not the translate table.  The developer (on the record definition) will sometimes store the prompt table.  The below SQL lets you see what is on the App Designer (without having to log into App Designer).  Note:  If the prompt value starts with %EditTable, then that means the prompt table is dynamic and a developer will have to find this value for you, in the code.

SELECT RD.RECNAME, RD.RECTYPE, RD.RECDESCR, RI.FIELDNUM, RI.FIELDNAME, RI.RECNAME_PARENT, RI.EDITTABLE
FROM PSRECDEFN RD, PSRECFIELDDB RI
WHERE RD.RECNAME = RI.RECNAME
AND RD.RECNAME = 'LEDGER_KK'
ORDER BY RI.FIELDNUM

Find Projects To Backup Before a Refresh

• By psgeek
• July 14, 2016

This script will list all of the recent projects in a non-production environment, that may need to be backed up before a refresh.

If your development environment is about to be refreshed with a copy of production, you may have the same nagging feeling I get: what if I have a development project that I forgot to back up.  True, you can have your admin dba backup development before the refresh, but restoring the backup may not be time well spent for your admin dba.

I built the following script to show all project definitions, in descending order of last touched, to show me which projects I may want to backup on my own.  It also has the last target database that was used.  If this field is blank (Last Target DB), and the project name is important work, you will want to pay close attention to these.

SELECT P.LASTUPDDTTM, P.PROJECTNAME, P.PROJECTDESCR, P.LASTUPDOPRID, O.OPRDEFNDESC, P.TGTDBNAME "Last Target DB", COUNT(*) "Count of Project Items"
FROM PSPROJECTDEFN P LEFT OUTER JOIN PSOPRDEFN O ON O.OPRID = P.LASTUPDOPRID, PSPROJECTITEM I
WHERE P.PROJECTNAME = I.PROJECTNAME
AND P.LASTUPDDTTM >= '04/20/2016'
GROUP BY P.LASTUPDDTTM, P.PROJECTNAME, P.PROJECTDESCR, P.LASTUPDOPRID, O.OPRDEFNDESC, P.TGTDBNAME
ORDER BY P.LASTUPDDTTM DESC