Statistics on a Table

Query to find out Index on Table

SELECT OWNER,
TABLE_NAME,
COUNT(*) INDEX_COUNT
FROM DBA_INDEXES
where OWNER not in ('SYS','SYSTEM')
and table_name = 'Tabel Name'
group by OWNER, TABLE_NAME

Query to find out column of an Index

SELECT TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME,
INDEX_NAME from dba_ind_columns WHERE TABLE_NAME = 'Table name'

Query to find out when the table was last analyzed and by what percentage

SELECT
NUM_ROWS,
SAMPLE_SIZE,
ROUND((SAMPLE_SIZE/NUM_ROWS)*100) "SAMPLE%",
TO_CHAR(last_analyzed,'DD-MM-YYYY:HH24:MI:SS') "Last_Analyzed"
FROM dba_tables
WHERE table_name ='Table_name';

List of objects in a PeopleSoft Project

Execute the Below Query in oracle database to get the list of objects in a PeopleSoft Project


SELECT projectname, 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 = 'Name of project'
ORDER BY 1, 2, 3, 4, 5, 6, 7, 8

PeopleSoft Object Type and Record Name

Overview

This below table list the various PeopleSoft Object type and it corresponding Mater record table.

Object ID	Object Type	Record Name
0	Record	PSRECDEFN
1	Indexes	PSKEYDEFN
2	Fields	PSDBFIELD
3	Field Formats	PSFMTDEFN
4	Translates	PSXLATITEM
5	Pages	PSPNLDEFN
6	Menus	PSMENUDEFN
7	Components	PSPNLGRPDEFN
8	Record PeopleCode	PSPCMPROG
9	Menu PeopleCode	PSPCMPROG
10	Queries	PSQRYDEFN
11	Tree Structures	PSTREESTRCT
12	Trees	PSTREEDEFN
13	Access Groups	PS_ACCESS_GRP_TBL
14	Colours	PSCOLORDEFN
15	Styles	PSSTYLEDEFN
17	Business Processes	PSBUSPROCDEFN
18	Activities	PSACTIVITYDEFN
19	Roles	PSROLEDEFN
20	Process Definitions	PRCSDEFN
21	Server Definitions	PS_SERVERDEFN
22	Process Type Definitions	PS_PRCSTYPEDEFN
23	Job Definitions	PS_PRCSJOBDEFN
24	Recurrence Definitions	PS_PRCSRECUR
25	Message Catalog Entries	PSMSGCATDEFN
26	Dimension Definition	PS_DIMENSION
27	Cube Definition	TO_Find
28	Cube Instance Definition	TO_Find
29	Business Interlink	PSIODEFN
30	SQL	PSSQLDESCR
31	File Layout Definitions	PSFLDDEFN
32	Component Interfaces	PSBCDEFN
33	Application Engine Programs	PSAEAPPLDEFN
34	Application Engine Sections	PSAESECTDEFN
35	Message Nodes	PSMSGNODEDEFN
36	Message Channels	PSMSGCATDEFN
37	Message Definitions	PSMSGDEFN
38	Approval Rule Set	APPR_RULE_HDR
39	Message PeopleCode	PSPCMPROG
40	Subscription PeopleCode	PSPCMPROG
42	Comp. Interface PeopleCode	PSPCMPROG
43	Application Engine PeopleCode	PSPCMPROG
44	Page PeopleCode	PSPCMPROG
46	Component PeopleCode	PSPCMPROG
47	Component Record PeopleCode	PSPCMPROG
48	Component Rec Fld PeopleCode	PSPCMPROG
49	Images	PSCONTDEFN
52	File References	PSFILEREDEFN
53	Permission Lists	PSCLASSDEFN
54	Portal Registry Definitions	TO_Find
55	Portal Registry Structures	PSPRSMDEFN
56	URL Definitions	PSURLDEFN
57	Application Packages	PSPACKAGEDEFN
58	Application Package PeopleCode	PSPCMPROG
60	Analytic Types	TO_Find
62	XSLT	TO_Find
64	Mobile Pages	PSMPDEFN
68	File References	PSFILEREDEFN
69	File Type Codes	PSTYPECODEDEFN
72	Dignostic Plug Ins	TO_Find
73	Analytic Models	TO_Find
79	Service	PSSERVICE
80	Service Operation	PSOPERATION
81	Service Operation Handler	PSOPRHDLR
82	Service Operation Version	PSOPRVERDFN
83	Service Operation Routing	SIBRTNGDEFN
84	IB Queues	SIBQUEUEINST
85	XLMP Template Definition
86	XLMP Report Definition
87	XMLP File Definition
88	XMPL Data Source Definition

How to Clone PeopleSoft Production database

Overview

This document describes the steps required to create a clone of the PeopleSoft production database. This is the most common task of a PeopleSoft Administrator / in any PeopleSoft upgrade project.

Here are some of the steps that need to be consider when creating a Development or Test environment from a production database on PeopleTools 8.4X.


Steps

1. Create a backup of the production database. (This is your source database)

2. Stop the Target Application Environment. (If application environment is running)

1. Application Servers
2. Process Schedulers
3. Web Servers
   

3. Overlay the target database with a recent backup of the production database.

4. Start the database

5. Set DBNAME in PSDBOWNER back to the target database name.

UPDATE PSDBOWNER SET DBOWNER = 'Database Name';

6. Set GUID to ' ' in PSOPTIONS table.

UPDATE PSOPTIONS SET GUID = ' ';

This will cause PeopleSoft to generate a new GUID so that change assistant can track it separately from the source database.

7. Delete the data from

• Reporting tables
• Process scheduler tables
• Application messaging tables
  

This data isn't relevant in the target database.
Below DMS scripts are provided by PeopleSoft. Use these scripts to clean the above tables.
prcsclr.dms (Process Scheduler Tables)
rptclr.dms (Reporting Tables)
appmsgpurgeall.dms (Application Messaging Tables)

8. Reset email address to blank or predefined value.

Since you are creating a clone of the production database you will have actual email address of all the employees. Email address is a very critical data. Reset everyone's e-mail address to a pre-defined value so that workflow messages from the Test environment don't get sent to real users.

Some of the common tables of PeopleSoft ERP are as follows

PSOPRDEFN
PS_ROLEXLATOPR
PSUSEREMAIL


You need to take care of Application specific tables. You can find out these tables by using below script.

SELECT RECNAME
FROM PSRECFIELD B
WHERE EXISTS (SELECT 'X' FROM PSRECDEFN A WHERE RECTYPE = 0 AND A.RECNAME = B.RECNAME)
AND B.FIELDNAME = 'EMAILID'

8. Change application password (VP1)

Logon to PeopleSoft data mover script and execute the below scripts if you want to chage the application password.

UPDATE PSOPRDEFN SET OPERPSWD = 'PASSWORD', ENCRYPTED = 0 WHERE OPRID = VP1;
ENCRYPT_PASSWORD VP1;

@Cincinnati