How to validate Primavera P6 database Schemas
When we upgrade or migrate the Primavera P6 schemas there might of losing DB objects. P6 in-built provide a utility called Validate.bat (or .sh) that checks all the DB object are correctly populated by comparing local copy (file) with the database schema. But this not tells if any indexes are skewed or any objects are invalid, So In this article, I have covered how to check and compile invalid P6 database objects & rebuild missing or invalid indexes. I would suggest run the below tests before running the P6 validation tool.
Compile Invalid P6 database objects
- Login into Oracle database as SYSTEM or SYSDBA account
2. Run the result of the below query; Run until the below query returns 0 results
select 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' || OBJECT_NAME || ' COMPILE;'
from dba_objects where status = 'INVALID' and owner in
If any of the objects are not compiling, then expand corresponding schemas and troubleshoot it. For example, many triggers in ADMUSER throws an error if it does not has read access to REPUTIL packages in SYS user.
Rebuild Primavera P6 Indexes
- Follow the instruction under P6 EPPM Index Rebuilding for Oracle Databases present in the DOC ID: 1327603.1 to rebuild all P6 indexes. You need Oracle support account to run this SQL.
Validate the P6 schema
Click on the P6 Validate schema for instructions to validate Primavera P6 database objects
The views expressed on this blog are my personal views and do not necessarily reflect the views of my employer.
Please feeling free to reach me on any comments and feedbacks you have. Would be more than glad to listen and reply 🙂