Errors in Serveroutput and Record Count for Clean_PX_Delete Procedure Is Not Accurate When Running Data_Monitor Manually
(Doc ID 2575198.1)
Last updated on SEPTEMBER 26, 2019
Applies to:Primavera P6 Enterprise Project Portfolio Management - Version 126.96.36.199 to 188.8.131.52 [Release 16.2 to 18.8]
Information in this document applies to any platform.
The clean_px_delete procedure clears obsolete entries in the PX (Extended schema) tables (see Doc ID 1325657.1 - How to Cleanup P6 Extended Tables (PX) Data for details). The procedure is not included as part of of the regularly-scheduled DATA_MONITOR process in P6 EPPM versions prior to 184.108.40.206 and 220.127.116.11, which can create a backlog of data in the extended schema that needs to be cleaned up.
After upgrading to a version where the clean_px_delete procedure has become part of the DATA_MONITOR process, when an administrator manually runs the DATA_MONITOR process, the output information includes several errors such as the following:
ORA-00942: table or view does not exist
ORA-01403: no data found.
In addition, the output for some of the tables states Deleted # rows from <table>, but the table row counts do not decrease. This makes it difficult to tell if the data eligible for deletion has actually been removed. This is especially important when trying to determine if the Max Rows to Delete setting value for the CLEAN_PX_DELETE procedure needs to be increased from the default value, as manual row counts must be done to see if the row count is actually decreasing.
The issue can be reproduced at will with the following steps:
- Upgrade to a P6 version where the clean_px_delete procedure is part of DATA_MONITOR.
- Connect to the database as the schema owner (default admuser) via SQL Developer or equivalent tool.
- Run the following commands:
- Review the output to find the reported errors.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!