puupx07 issue

DROP TABLE PSRECFIELD_TMP
/
CREATE TABLE PSRECFIELD_TMP AS SELECT * FROM PSRECFIELD
/
CREATE UNIQUE INDEX PS_PSRECFIELD_TMP ON PSRECFIELD_TMP (RECNAME,
FIELDNAME) TABLESPACE PSINDEX
/
CREATE INDEX PSAPSRECFIELD_TMP ON PSRECFIELD_TMP (FIELDNAME,
RECNAME) TABLESPACE PSINDEX
/
CREATE INDEX PSBPSRECFIELD_TMP ON PSRECFIELD_TMP (DEFRECNAME) TABLESPACE
PSINDEX
/
CREATE INDEX PSCPSRECFIELD_TMP ON PSRECFIELD_TMP (DEFFIELDNAME,
DEFRECNAME) TABLESPACE PSINDEX
/
CREATE INDEX PSDPSRECFIELD_TMP ON PSRECFIELD_TMP (EDITTABLE) TABLESPACE
PSINDEX
/
CREATE INDEX PSEPSRECFIELD_TMP ON PSRECFIELD_TMP (CURCTLFIELDNAME)
TABLESPACE PSINDEX
/
CREATE INDEX PSFPSRECFIELD_TMP ON PSRECFIELD_TMP (SUBRECORD,
RECNAME,
FIELDNAME) TABLESPACE PSINDEX
/
COMMIT
/

Query to find Navigation in 8.1x PeopleTools Release

SELECT DISTINCT
A.MENUNAME,
C.PNLGRPNAME ",",
A.MENULABEL ",",
B.BARLABEL ",",
RTRIM(A.MENUGROUP) || ' -->' || RTRIM(A.MENULABEL) || ' -->'
|| RTRIM(B.BARLABEL) || ' -->' || RTRIM(B.ITEMLABEL) "PIA_MENU"
FROM PSMENUDEFN A,
PSMENUITEM B,
PSPNLGROUP C
WHERE A.MENUNAME = B.MENUNAME
AND B.PNLGRPNAME = C.PNLGRPNAME
AND B.MARKET = C.MARKET
and C.PNLGRPNAME in (
'TM_ARCH_SETUP') -- Component Name
GROUP BY A.MENUNAME,
A.MENUGROUP,
A.MENULABEL,
B.BARLABEL,
B.ITEMLABEL,
A.MENUNAME,
C.PNLGRPNAME

Table Statistics syntax

EXECUTE DBMS_STATS.GATHER_TABLE_STATS (ownname=> 'SYSADM', tabname=> 'Table Name', estimate_percent=>25,cascade=>TRUE);

How to determine table locks in oracle

select * From v$locked_object

select * From v$session where SID = '<>'

select * from dba_objects where object_id = '<>'

-- Locked objects

Select object_name, owner, object_type from dba_objects
Where object_id in (select object_id from v$locked_object);

-- KILL SESSION COMMAND

ALTER SYSTEM KILL SESSION '38,53336' IMMEDIATE;

Query to find Portal Navigation from Menu and Component name in People Tools 8.4X

Use the below Query to find the Navigation for a component. Provide
• Menu Name
• Component Name
• Market
in the below query as your input parameter. Run this on SQL Plus.


WITH PORTAL_REGISTRY AS (
SELECT RTRIM(REVERSE(SYS_CONNECT_BY_PATH(REVERSE(PORTAL_LABEL)
, ' >> '))
, ' >> ') PATH
, LEVEL LVL
FROM PSPRSMDEFN
WHERE PORTAL_NAME = 'EMPLOYEE' START WITH PORTAL_REFTYPE = 'C'
AND PORTAL_URI_SEG1 = 'TM_ARCH_DATA' -- Menu Name
AND PORTAL_URI_SEG2 = 'TM_ARC_PAY_SUMMARY' -- Component Name
AND PORTAL_URI_SEG3 = 'USA' -- Market
CONNECT BY PRIOR PORTAL_PRNTOBJNAME = PORTAL_OBJNAME )
SELECT PATH
FROM PORTAL_REGISTRY
WHERE LVL = (
SELECT MAX(LVL)
FROM PORTAL_REGISTRY )

SQL Logging Syntax

SPOOL ;
WHENEVER SQLERROR EXIT SQL.SQLCODE
SET ECHO ON;
SET TIME ON;

-- SQL Or PL/SQL

SPOOL OFF;


SPOOL ;
WHENEVER SQLERROR CONTINUE;
SET ECHO ON;
SET TIME ON;

-- SQL Or PL/SQL

SPOOL OFF;

Query to find out PeopleSoft Record Definition

SELECT
A.RECNAME
, A.FIELDNAME
, A.FIELDNUM
, DECODE (B.FIELDTYPE ,
0 ,'Char' ,
1 ,'LongChar' ,
2 ,'Number' ,
3 ,'SignNumber' ,
4 ,'Date' ,
5 ,'Time' ,
6 ,'DateTime' ,
8 ,'Image' ,
9 ,'ImgRef' )
,B.LENGTH
FROM PSRECFIELD A
, PSDBFIELD B
WHERE A.FIELDNAME = B.FIELDNAME
AND A.RECNAME = "Record Name"
ORDER BY FIELDNUM

PeopleSoft Field Type Number Mapping

0 - Char
1 - LongChar
2 - Number
3 - SignNumber
4 - Date
5 - Time
6 - DateTime
8 - Image
9 - ImgRef
8 - Attachment