How to Build PeopleSoft Portal Registry Search Index.

Navigate to: PeopleTools > Portal > Build Registry Search Index

Create a New Run Control ID = BUILD_SRCH_INDEX

Select all installed Languages and confirm that the base language is listed.



Click on Run Button



Select Process name PORTAL_INDEX.

Select OK

Go to Process Monitor a verify job is completed successfully.

Once the job is posted successful verify files are created in below navigation.
PS_HOME of your Process Scheduler server: Go to PS_HOME\data\search\Employee\{dbname}\ENG (assuming it is English) other languages will appear in their own folders if they exist). In this directory, look for below files and folders.







All should be well if the above files exist and there are no errors.

Logout and login again and search anything on the search box.

It should work now!!

Query - PeopleSoft Object in a Project

SELECT projectname, DECODE(objecttype,
0,'Record',
1,'Indexes',
2,'Fields',
3,'Field Formats',
4,'Translates',
5,'Pages',
6,'Menus',
7,'Components',
8,'Record PeopleCode',
9,'Menu PeopleCode',
10,'Queries',
11,'Tree Structures',
12,'Trees',
13,'Access Groups',
14,'Colours',
15,'Styles',
17,'Business Processes',
18,'Activities',
19,'Roles',
20,'Process Definitions',
21,'Server Definitions',
22,'Process Type Definitions',
23,'Job Definitions',
24,'Recurrence Definitions',
25,'Message Catalog Entries',
26,'Dimension Definition',
27,'Cube Definition',
28,'Cube Instance Definition',
29,'Business Interlink',
30,'SQL',
31,'File Layout Definitions',
32,'Component Interfaces',
33,'Application Engine Programs',
34,'Application Engine Sections',
35,'Message Nodes',
36,'Message Channels',
37,'Message Definitions',
38,'Approval Rule Set',
39,'Message PeopleCode',
40,'Subscription PeopleCode',
42,'Comp. Interface PeopleCode',
43,'Application Engine PeopleCode',
44,'Page PeopleCode',
46,'Component PeopleCode',
47,'Component Record PeopleCode',
48,'Component Rec Fld PeopleCode',
49,'Images',
52,'File References',
53,'Permission Lists',
54,'Portal Registry Definitions',
55,'Portal Registry Structures',
56,'URL Definitions',
57,'Application Packages',
58,'Application Package PeopleCode',
60,'Analytic Types',
62,'XSLT',
64,'Mobile Pages',
68,'File References',
69,'File Type Codes',
72,'Dignostic Plug Ins',
73,'Analytic Models',
79,'Service',
80,'Service Operation',
81,'Service Operation Handler',
82,'Service Operation Version',
83,'Service Operation Routing',
84,'IB Queues',
85,'XLMP Template Definition',
86,'XLMP Report Definition',
87,'XMLP File Definition',
88,'XMPL Data Source Definition') AS OBJECTTYPE
,objectvalue1, objectvalue2, objectvalue3,objectvalue4,
DECODE (sourcestatus,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same'
) AS SOURCE,
DECODE (targetstatus,
0, 'Unknown',
1, 'Absent',
2, 'Changed',
3, 'Unchanged',
4, '*Changed',
5, '*Unchanged',
6, 'Same'
) AS TARGET,
DECODE (upgradeaction,
0, 'Copy',
1, 'Delete',
2, 'None',
3, 'CopyProp'
) AS ACTION,
DECODE (takeaction, 0, 'No', 1, 'Yes') AS UPGRADE
FROM psprojectitem
WHERE projectname = 'Project Name'
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8

Temp Table and AE

AE Important Tables

SELECT * FROM PS_AEREQUESTTBL WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PS_AERUNCONTROL WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PS_AERUNCONTROLPC WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PS_AETEMPTBLMGR WHERE PROCESS_INSTANCE = 552;
SELECT * FROM PSPRCSRQST WHERE PRCSINSTANCE = 552;
SELECT * FROM PSPRCSPARMS WHERE PRCSINSTANCE = 552;
SELECT * FROM PSPRCSQUE WHERE PRCSINSTANCE = 552;


The following query lists the Temporary Records where there is no referencing Application Engine program where restart is enabled, the number of instances of each table, including the global instances.


SELECT r.recname, o.temptblinstances+NVL(c.temptblinstances,0) temptblinstancesFROM psoptions o, psrecdefn r LEFT OUTER JOIN pstemptblcntvw c ON c.recname = r.recnameWHERE r.rectype = 7AND NOT EXISTS( --omit tables where restart not disabled on referencing AE SELECT 'x' FROM psaeappltemptbl t , psaeappldefn a WHERE t.recname = r.recname AND a.ae_applid = t.ae_applid AND a.ae_disable_restart = 'N' --restart not disabled AND ROWNUM = 1)

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;