How to identify PeopleSoft application information from database tables?

When you are supporting your PeopleSoft application you might have come across some common questions like

What is the PeopleTools version?
What is the database version?
Whether database is Unicode or Non-Unicode?
What is the current production database size?
When was this particular database was refreshed?
What is the latest patch applied?
How often patches are applied?

You can find these information's from PeopleSoft tables itself. In this doodle I will post some tips that will help you to get these information's from PeopleSoft database.

What is the PeopleTools version?
 
TOOLSREL field in PeopleTools record PSSTATUS stores current PeopleTools release like 8.53.
 
select TOOLSREL from PSSTATUS
 
What is the database version?

Below solution is for Oracle database only.

SELECT VERSION FROM V$INSTANCE
or
SELECT * FROM V$VERSION
 
Whether database is Unicode or Non-Unicode?
 
UNICODE_ENABLED field in PeopleTools record PSSTATUS stores value 1 or 0. Value 1 indicates this database is UNICODE database.

select UNICODE_ENABLED from PSSTATUS

What is the current production database size?

select tablespace_name, owner, SUM(BYTES)/1047685/1024 SIZE_GB from DBA_SEGMENTS
group by rollup(owner,tablespace_name);

When was this particular database was refreshed?

select CREATED from V$DATABASE

What is the latest patch applied? or How often patches are applied?

PeopleSoft table PS_MAINTENANCE_LOG has the information like Update ID, Update Release date, date when this update was imported into the system which will help you determine what was the latest update applied to the system

select * from PS_MAINTENANCE_LOG  order by DTTM_IMPORTED desc