Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

java.sql.SQLSyntaxErrorException: ORA-00942 for PR_PERF_STAT

SA-2181

Summary



PRPC 7.1.5/ AES 7.1 is set up in a split-schema configuration (rule schema PRPC_RULE_ADM and data schema PRPC_AES_ADM).

very night at 1:00 AM there is an exception java.sql.SQLSyntaxErrorException - "ORA-00942: table or view does not exist" related to the table PR_PERF_STATS that is located in the data schema PRPC_AES_ADM.

As workaround I have created a private synonym for the table in the connect user PRPC_AES_CONNECT that is used in the PegaRULES Data Source.

When this private synonym is present there is no exception. So the problem is that somewhere in the PRPC or AES code the schema prefix for that SQL statment is missing.

Attached:
- logs

Error Messages



2014-10-03 01:00:00,870 [fault (self-tuning)'] [ STANDARD] [ PegaRULES:07.10] (l.access.ConnectionManagerImpl) ERROR detop1vr.dc-ratingen.de|10.207.129.87|SOAP|PegaAESRemote|RuleData|ExecuteMBeanOperation|AFE9E1924047C33C0F72E7B5B51803685 - Not returning connection 245074 for database "pegadata" to the pool as it previously encountered the following error
User ID: (unknown)
Last SQL: SELECT MAX(pxProcessCPU) as "pxProcessCPU", MAX(pxRequestorType) as "RequestorType", MAX(pxSystemNodeID) as "pxSystemNodeID", MAX(pxSystemNode) as "pxSystemNode", MAX(pxSystemName) as "pxSystemName", COUNT(DISTINCT(pxRequestorID)) as "UniqueRequestors", COUNT(DISTINCT(pyUserIdentifier)) as "UniqueUsers", SUM(pxActivationDataTimeCPU) as "pxActivationDataTimeCPU", SUM(pxActivationDataTimeElapsed) as "pxActivationDataTimeElapsed", SUM(pxActivityCount) as "pxActivityCount", SUM(pxAlertCount) as "pxAlertCount", SUM(pxCommitCount) as "pxCommitCount", SUM(pxCommitElapsed) as "pxCommitElapsed", SUM(pxCommitRowCount) as "pxCommitRowCount", SUM(pxConnectCount) as "pxConnectCount", SUM(pxConnectElapsed) as "pxConnectElapsed", SUM(pxDBInputBytes) as "pxDBInputBytes", SUM(pxDBOpExceedingThresholdCount) as "pxDBOpExceedingThresholdCount", SUM(pxDBOutputBytes) as "pxDBOutputBytes", SUM(pxRDBIOElapsed) as "pxRDBIOElapsed", SUM(pxRDBIOCount) as "pxRDBIOCount", SUM(pxDeclarativeRuleReadCount) as "pxDeclarativeRuleReadCount", SUM(pxDeclarativeRulesInvokedCount) as "pxDeclarativeRulesInvokedCount", SUM(pxDeclarativeRulesInvokedCPU) as "pxDeclarativeRulesInvokedCPU", SUM(pxDeclarativeRulesLookupCount) as "pxDeclarativeRulesLookupCount", SUM(pxDeclarativeRulesLookupCPU) as "pxDeclarativeRulesLookupCPU", SUM(pxDeclExprCtxFreeUseCount) as "pxDeclExprCtxFreeUseCount", SUM(pxDeclExprCtxSensUseCount) as "pxDeclExprCtxSensUseCount", SUM(pxDeclNtwksBuildConstCPU) as "pxDeclNtwksBuildConstCPU", SUM(pxDeclNtwksBuildConstElapsed) as "pxDeclNtwksBuildConstElapsed", SUM(pxDeclNtwksBuildHLCPU) as "pxDeclNtwksBuildHLCPU", SUM(pxDeclNtwksBuildHLElap) as "pxDeclNtwksBuildHLElap", SUM(pxDeclRulesInvokedBckGrdCnt) as "pxDeclRulesInvokedBckGrdCnt", SUM(pxDeclRulesInvokedElapsed) as "pxDeclRulesInvokedElapsed", SUM(pxDeclRulesLookupElapsed) as "pxDeclRulesLookupElapsed", SUM(pxDecryptCount) as "pxDecryptCount", SUM(pxDecryptCPU) as "pxDecryptCPU", SUM(pxDecryptElapsed) as "pxDecryptElapsed", SUM(pxEncryptCount) as "pxEncryptCount", SUM(pxEncryptCPU) as "pxEncryptCPU", SUM(pxEncryptElapsed) as "pxEncryptElapsed", SUM(pxFlowCount) as "pxFlowCount", SUM(pxIndexCount) as "pxIndexCount", SUM(pxInferGeneratedJavaCount) as "pxInferGeneratedJavaCount", SUM(pxInferGeneratedJavaCPU) as "pxInferGeneratedJavaCPU", SUM(pxInferGeneratedJavaElapsed) as "pxInferGeneratedJavaElapsed", SUM(pxInferGeneratedJavaHLElapsed) as "pxInferGeneratedJavaHLElapsed", SUM(pxInputBytes) as "pxInputBytes", SUM(pxInteractions) as "pxInteractions", SUM(pxJavaAssembleCount) as "pxJavaAssembleCount", SUM(pxJavaAssembleCPU) as "pxJavaAssembleCPU", SUM(pxJavaAssembleElapsed) as "pxJavaAssembleElapsed", SUM(pxJavaAssembleHLElapsed) as "pxJavaAssembleHLElapsed", SUM(pxJavaCompileCount) as "pxJavaCompileCount", SUM(pxJavaCompileCPU) as "pxJavaCompileCPU", SUM(pxJavaCompileElapsed) as "pxJavaCompileElapsed", SUM(pxJavaGenerateCount) as "pxJavaGenerateCount", SUM(pxJavaGenerateCPU) as "pxJavaGenerateCPU", SUM(pxJavaGenerateElapsed) as "pxJavaGenerateElapsed", SUM(pxJavaStepCount) as "pxJavaStepCount", SUM(pxJavaSyntaxCount) as "pxJavaSyntaxCount", SUM(pxJavaSyntaxCPU) as "pxJavaSyntaxCPU", SUM(pxJavaSyntaxElapsed) as "pxJavaSyntaxElapsed", SUM(pxLegacyRuleAPIUsedCount) as "pxLegacyRuleAPIUsedCount", SUM(pxListRowWithFilteredStrmCnt) as "pxListRowWithFilteredStrmCnt", SUM(pxListRowWithoutStreamCount) as "pxListRowWithoutStreamCount", SUM(pxListRowWithUnfilteredStrmCnt) as "pxListRowWithUnfilteredStrmCnt", SUM(pxListWithFilteredStreamCount) as "pxListWithFilteredStreamCount", SUM(pxListWithoutStreamCount) as "pxListWithoutStreamCount", SUM(pxListWithUnfilteredStrmCnt) as "pxListWithUnfilteredStrmCnt", SUM(pxLookupListDBFetches) as "pxLookupListDBFetches", SUM(pxNewFUAInstances) as "pxNewFUAInstances", SUM(pxOtherBrowseCPU) as "pxOtherBrowseCPU", SUM(pxOtherBrowseElapsed) as "pxOtherBrowseElapsed", SUM(pxOtherBrowseFilterCnt) as "pxOtherBrowseFilterCnt", SUM(pxOtherBrowseFilterCPU) as "pxOtherBrowseFilterCPU", SUM(pxOtherBrowseFilterElapsed) as "pxOtherBrowseFilterElapsed", SUM(pxOtherBrowseReturned) as "pxOtherBrowseReturned", SUM(pxOtherCount) as "pxOtherCount", SUM(pxOtherFromCacheCount) as "pxOtherFromCacheCount", SUM(pxOtherIOCount) as "pxOtherIOCount", SUM(pxOtherIOCPU) as "pxOtherIOCPU", SUM(pxOtherIOElapsed) as "pxOtherIOElapsed", SUM(pxOutputBytes) as "pxOutputBytes", SUM(pxParseRuleCount) as "pxParseRuleCount", SUM(pxParseRuleTime) as "pxParseRuleTime", SUM(pxParseRuleTimeCPU) as "pxParseRuleTimeCPU", SUM(pxPassivationCount) as "pxPassivationCount", SUM(pxPassivationDataTimeCPU) as "pxPassivationDataTimeCPU", SUM(pxPassivationDataTimeElapsed) as "pxPassivationDataTimeElapsed", SUM(pxPassivationIdleTimeElapsed) as "pxPassivationIdleTimeElapsed", SUM(pxPassivationSizeAverage) as "pxPassivationSizeAverage", SUM(pxPassivationSizeLast) as "pxPassivationSizeLast", SUM(pxPassivationSizePeak) as "pxPassivationSizePeak", SUM(pxProceduralRuleReadCount) as "pxProceduralRuleReadCount", SUM(pxPropertyReadCount) as "pxPropertyReadCount", SUM(pxRDBRowWithoutStreamCount) as "pxRDBRowWithoutStreamCount", SUM(pxRDBRowWithStreamCount) as "pxRDBRowWithStreamCount", SUM(pxRDBWithoutStreamCount) as "pxRDBWithoutStreamCount", SUM(pxRDBWithStreamCount) as "pxRDBWithStreamCount", SUM(pxRuleBrowseCPU) as "pxRuleBrowseCPU", SUM(pxRuleBrowseElapsed) as "pxRuleBrowseElapsed", SUM(pxRuleBrowseFilterCnt) as "pxRuleBrowseFilterCnt", SUM(pxRuleBrowseFilterCPU) as "pxRuleBrowseFilterCPU", SUM(pxRuleBrowseFilterElapsed) as "pxRuleBrowseFilterElapsed", SUM(pxRuleBrowseReturned) as "pxRuleBrowseReturned", SUM(pxRuleCount) as "pxRuleCount", SUM(pxRuleCPU) as "pxRuleCPU", SUM(pxRuleFromCacheCount) as "pxRuleFromCacheCount", SUM(pxRuleIOElapsed) as "pxRuleIOElapsed", SUM(pxRulesExecuted) as "pxRulesExecuted", SUM(pxRulesUsed) as "pxRulesUsed", SUM(pxRunModelCount) as "pxRunModelCount", SUM(pxRunOtherRuleCount) as "pxRunOtherRuleCount", SUM(pxRunStreamCount) as "pxRunStreamCount", SUM(pxRunWhenCount) as "pxRunWhenCount", SUM(pxSavedCbAfterIntCount) as "pxSavedCbAfterIntCount", SUM(pxSavedCbAfterIntCPU) as "pxSavedCbAfterIntCPU", SUM(pxSavedCbAfterIntElapsed) as "pxSavedCbAfterIntElapsed", SUM(pxSavedCxtAfterIntCount) as "pxSavedCxtAfterIntCount", SUM(pxSavedCxtAfterIntCPU) as "pxSavedCxtAfterIntCPU", SUM(pxSavedCxtAfterIntElapsed) as "pxSavedCxtAfterIntElapsed", SUM(pxServiceActivityCPU) as "pxServiceActivityCPU", SUM(pxServiceActivityTime) as "pxServiceActivityTime", SUM(pxServiceCount) as "pxServiceCount", SUM(pxServiceDataVolume) as "pxServiceDataVolume", SUM(pxServiceInMapReqCPU) as "pxServiceInMapReqCPU", SUM(pxServiceInMapReqTime) as "pxServiceInMapReqTime", SUM(pxServiceNumFileRecords) as "pxServiceNumFileRecords", SUM(pxServiceOutMapReqCPU) as "pxServiceOutMapReqCPU", SUM(pxServiceOutMapReqTime) as "pxServiceOutMapReqTime", SUM(pxTotalReqCPU) as "pxTotalReqCPU", SUM(pxTotalReqTime) as "pxTotalReqTime", SUM(pxTrackedPropertyChangesCount) as "pxTrackedPropertyChangesCount", SUM(pxTransientJavaAssembleCount) as "pxTransientJavaAssembleCount", SUM(pxTransientJavaAssembleCPU) as "pxTransientJavaAssembleCPU", SUM(pxTransientJavaAssembleElapsed) as "pxTransientJavaAssembleElapsed", SUM(pxTransientJavaCompileCount) as "pxTransientJavaCompileCount", SUM(pxTransientJavaCompileCPU) as "pxTransientJavaCompileCPU", SUM(pxTransientJavaCompileElapsed) as "pxTransientJavaCompileElapsed"FROM pr_perf_stats WHERE (pxSystemNodeID = ?) AND pxRequestorType = ? AND (pxSnapshotTime BETWEEN ? AND ?)
java.sql.SQLSyntaxErrorException: ORA-00942: table or view does not exist


Steps to Reproduce



Deploy PRPC 7.1.5 with AES 7.1 utilising a split-schema configuration.

Root Cause



The root cause of this problem is a defect in Pegasystems’ code/rules. 

Resolution



This issue has been resolved in PRPC 7.1.6 (ML6).

A local change mitigation can also be employed (i.e. private synonym for PR_PERF_STATS).
Suggest Edit

Published January 31, 2016 - Updated December 2, 2021

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us