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).

Published January 31, 2016 - Updated December 2, 2021

## Have a question? Get answers now.

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