Unable to run PSJob type jobs
Article ID: 93091
Updated On:
Products
Issue/Introduction
Having problems with Peoplesoft with the following error?
04/16/2018 14:03:56.516 CDT-0500 1 PSPlugin.ps Internal Thread.CybWOBRunPsHandler.processWob[:177] - SQL Exception ORA-00942: table or view does not exist
We are working to update out autosys to 11.3.6, and have been able to run jobs except for PSJob types. trying to run those shows this information in the peoplesoft.log file:
04/16/2018 14:03:56.386 CDT-0500 3 PSPlugin.ps Internal Thread.PsDbHandler.runSimpleQuery[:472] - Query: SELECT SEQUENCENO FROM PS_PRCSSEQUENCE WHERE PRCSSEQKEY = '0' 04/16/2018 14:03:56.387 CDT-0500 3 PSPlugin.ps Internal Thread.PsDbHandler.runSimpleQuery[:479] - value = 33118963 04/16/2018
14:03:56.387 CDT-0500 3 PSPlugin.ps Internal Thread.PsDbHandler.runUpdateNoReconnect[:589] - runUpdate: UPDATE PS_PRCSSEQUENCE SET SEQUENCENO = 33118964 WHERE SEQUENCENO = 33118963 AND PRCSSEQKEY = '0'
04/16/2018 14:03:56.430 CDT-0500 3 PSPlugin.ps Internal Thread.PsDbHandler.runUpdateNoReconnect[:597] - 1 row(s) updated.
04/16/2018 14:03:56.430 CDT-0500 4 PSPlugin.ps Internal Thread.PsDbHandler8_4.incrementSequence[:138] - SEQUENCENO = 33118964
04/16/2018 14:03:56.514 CDT-0500 3 PSPlugin.ps Internal Thread.PsDbHandler.runSimpleQuery[:472] - Query: select OPRID from PSOPRDEFN where OPRID = 'AUTOSYS'
04/16/2018 14:03:56.516 CDT-0500 1 PSPlugin.ps Internal Thread.CybWOBRunPsHandler.processWob[:177] - SQL Exception ORA-00942: table or view does not exist AFM: 20180416 14035637+0500 WA_AGENT JavaAgent#tcpip@XXX_SCH 4484.27037_2/WAAE_WF0.1/MAIN RUN PS SkipParmUpdates(No) RunCntlId(XXXX) Dr(No) ProcessType(PSJob) ProcessName(XXXXXX) PsOprId(AUTOSYS) PsOpr(XXXXX)EmailWebReport(No) EmailLog(No) DistrListRoles(XXXXX) OutDestType(EMAIL) MFUser(autosys) WOBRequestID(4C90F9033A414BCF5F513B3A4411B33DA9FF58AF15239054363830)
Resolution
Check with your DBA to make sure that you have created synonyms for all sysadm tables in the PS database. In the article below, it shows how to create public synonyms for all tables under SYSADM schema.
I found information from here on how to do it:
----------------------------------
http://learnpsdba.blogspot.com.au/2010/04/creating-public-synonym-of-all-tables.html?_sm_au_=iVV5k6NtWTjnrRNV
Creating public synonym of all the tables under SYSADM schema
Creating public synonym for a single table is easy in oracle but at times we might need to create public synonym for all the tables under a schema. In
Peoplesoft, we might use it for below purpose:
a. To help other Database users to directly access SYSADM tables without prefixing owner name eg. sysadm.PSOPRDEFN.
b. To create 2nd access id and granting rights on tables of primary access id.
1. Generating Script for Creating Public synonyms
Open the SQL Tool and run the following commands.
SQL> Spool synonyms.sql;
SQL> select 'create public synonym ' ||table_name|| ' for ' ||owner||'.'||table_name ||';' from dba_tables where owner='SYSADM';
SQL> spool off;
Open synonyms.sql in a text editor.
This file would contain the sql to create public synonym for all the tables in SYSADM schema.There would be multiple entries as shown below
'CREATEPUBLICSYNONYM'||TABLE_NAME||'FOR'||OWNER||'.'||TABLE_NAME||';' .
These entries can be simple replaced with blank space.
We are left with script that be executed to create public synonym of all SYSADM'S table.
2. Granting rights on these public synonyms to another userid.
SQL> Spool grantsyn.sql;
SQL> select 'grant select on ' ||table_name|| ' to sysadm2 ;' from dba_tables where owner='SYSADM';
SQL> spool off;
OR
SQL> Spool grantsyn.sql;
SQL> select 'grant select on ' ||synonym_name|| ' to sysadm2;' from dba_synonyms where owner='SYSADM';
SQL> spool off;
3. Run synonyms.sql
4. Run grantsyn.sql
Feedback
