Date and Time of Database refresh from PeopleSoft Production database

Question

In a PeopleSoft application running on an Oracle database, how can I find out when a non-Production database got copied/refreshed from Production?

Is there a PeopleSoft/Oracle table (or tables) that have the date and time of the refresh?

I tried to glean this information by cross referencing PeopleSoft tables PSPRCSRQST and PSPRCSQUE with the Process Instance table (PRCSINSTANCE). That wasn't good enough though because it's unclear which Process Instance numbers exist in both production and the clone, and which exist only in the clone.

Is there any other way to find the last refresh date of a non-Production database cloned from a Production database?

Answer 1

There are a few places you can check, but some of them require privileges a normal user may not have.

Here is an Oracle metadata view typically meant for use by the DBA:

SELECT name, created
FROM v$database
/

There are also a couple of PeopleSoft tables I am aware of, but in my (limited) experience, they are not always reliably populated.

SELECT lastrefreshdttm 
FROM sysadm.psstatus
/

SELECT releasedttm, releaselabel
FROM sysadm.psrelease
ORDER BY 1 DESC
/

psrelease is the table that shows up when you perform a PeopleTools Application Designer compare, and depending on your environment configuration will also show up in your PIA when you Ctrl+Shift+J).