SQL to find when PeopleSoft table was built and analyzed in Oracle – PeopleSoft Tutorial

SQL to find when PeopleSoft table was built and analyzed in Oracle

Ever wondered when a PeopleSoft table was last built / altered or when its database stats were gathered ?

Many times, you need to know that information to analyze what’s happening with a table. For example, your table may not be working correctly or the performance may be degraded.

You may want to check when was the last time changes were done to the table. Of course you can use several other tools to validate this but I find below SQL to be handy.

Here is all the information below SQL will pull for you:

  • PeopleSoft Record Name, Database table name, PeopleSoft Record description
  • Find when was the last time PeopleSoft table was built (for e.g. when you use application designer to build a table or run a SQL script to build it)
  • Find when was the last time stats were gathered and analyzed for PeopleSoft table

PS Basics Tip

PeopleSoft Record definition and corresponding database table are 2 different objects
-- Find when PeopleSoft table was built and analyzedSELECT A.OWNER,A.OBJECT_NAME AS TABLE_NAME,B.RECNAME AS RECORD_NAME,B.RECDESCR AS DESCRIPTION,A.CREATED,C.LAST_ANALYZEDFROM DBA_OBJECTS A, PSRECDEFN B, DBA_TABLES CWHERE     A.OWNER = 'SYSADM'AND A.OBJECT_TYPE = 'TABLE'AND (A.OBJECT_NAME = B.RECNAME OR A.OBJECT_NAME = 'PS_' || B.RECNAME)AND A.OBJECT_NAME = C.TABLE_NAMEAND C.NUM_ROWS > 0;

Above SQL will give you the data for all the PeopleSoft table but if you want you can filter it using C.TABLE_NAME OR A.OBJECT_NAME.

If you like this tip, just let us in the comment section. Share it with others too.

 

Related Posts you might find helpful

How to Write an Efficient SQL
Writing a good SQL helps your organization in several ways. It has a manifold effect, which many developers do not[...]
Quickest way to find hidden portal objects [SQL]
You can hide PeopleSoft portal objects (e.g. content reference) from PeopleSoft Navigation. This can be done by navigating to 'Structure[...]
SQL Tip : Find users logged into PeopleSoft PIA with this SQL
You can find the users logged into your PeopleSoft application in multiple ways as this activity is recorded at various[...]
How to monitor connections to PeopleSoft Database
Ever wondered how many users are connected to PeopleSoft database? This is different than users who login into PeopleSoft application[...]
Prashant
 

Click Here to Leave a Comment Below 0 comments