ORA-01031: insufficient privileges JBO-28006: Could not create persistence table PS_TXN_seq

Applies to:

Oracle JDeveloper/ ADF server - Version: 11.1.1.0.0 and later   [Release: and later ]
Information in this document applies to any platform.

Symptoms

You developed an ADF BC ("ADF Business Components") application.
It was tested and was working correctly when run from JDeveloper, in the Integrated WebLogic Server.
However, in your Production environment, some users report intermittent errors like:
JBO-28006: Could not create persistence table PS_TXN_seq

When forcing passivation to occur with the parameter jbo.ampool.doampooling=false,
as explained in Note:417546.1 - How To Reproduce Problems Related to AM Passivation / Activation
you get the following errors occurring during the passivation:
(with the Java Options
"-Djbo.ampool.doampooling=false -Djbo.debugoutput=console -Djbo.logging.show.function=true"
set in the Project Properties)

[439] **createTable** created sequence error, sqlStmt=create sequence PS_TXN_seq increment by 50
[440] java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1035)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1223)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1775)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1744)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
at oracle.jbo.pcoll.OraclePersistManager.createTable(OraclePersistManager.java:832)
at oracle.jbo.pcoll.OraclePersistManager.queryNextCollectionId(OraclePersistManager.java:1444)
at oracle.jbo.pcoll.PCollManager.register(PCollManager.java:562)
at oracle.jbo.pcoll.PCollection.(PCollection.java:99)
at oracle.jbo.pcoll.PCollManager.createCollection(PCollManager.java:461)
at oracle.jbo.server.DBSerializer.setup(DBSerializer.java:150)
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:283)
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:264)
at oracle.jbo.server.ApplicationModuleImpl.passivateStateInternal(ApplicationModuleImpl.java:5645)
at oracle.jbo.server.ApplicationModuleImpl.passivateState(ApplicationModuleImpl.java:5516)
...
[441] JBO-28006: Could not create persistence table PS_TXN_seq
[442] *** Stack trace for detail exception ***
[443] java.sql.SQLSyntaxErrorException: ORA-01031: insufficient privileges

at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:91)
at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:206)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1035)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1223)
at oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1775)
at oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1744)
at oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
at oracle.jbo.pcoll.OraclePersistManager.createTable(OraclePersistManager.java:832)
at oracle.jbo.pcoll.OraclePersistManager.queryNextCollectionId(OraclePersistManager.java:1444)
at oracle.jbo.pcoll.PCollManager.register(PCollManager.java:562)
at oracle.jbo.pcoll.PCollection.(PCollection.java:99)
at oracle.jbo.pcoll.PCollManager.createCollection(PCollManager.java:461)
at oracle.jbo.server.DBSerializer.setup(DBSerializer.java:150)
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:283)
at oracle.jbo.server.DBSerializer.passivateRootAM(DBSerializer.java:264)
at oracle.jbo.server.ApplicationModuleImpl.passivateStateInternal(ApplicationModuleImpl.java:5645)
at oracle.jbo.server.ApplicationModuleImpl.passivateState(ApplicationModuleImpl.java:5516)
...

Cause

Your DB schema hasn't got sufficient privileges to create the passivation table, sequence and/or
index in the database.

The passivation will store information in a table called PS_TXN and will use a DB sequence
PS_TXN_SEQ

These DB objects will be created

• in the same DB schema as specified in the Application Module Configuration
  (JDBC connection),
• or in the schema specified in the parameter jbo.server.internal_connection (recommended).

In order to create these, the DB schema needs to be granted DB privileges that are:

• CREATE TABLE
• CREATE INDEX
• CREATE SEQUENCE

For more information, see "Overview of Temporary Tables Created By BC4J"
and the Oracle Fusion Middleware Fusion Developer's Guide for Oracle Application Development Framework,
Chapter "39 Application State Management"
topic "39.5 What Model State Is Saved and When It Is Cleaned Up"

Solution

We recommend that you create a DB schema specific for the creation of these table.
Ask you DBA to create this schema and grant him the following privileges:

• CREATE TABLE
• CREATE INDEX
• CREATE SEQUENCE

When done, edit your Application Module Configuration, in the "Properties" tab and search for the
property called "jbo.server.internal_connection".
Specify there the value of your JDBC connection to connect to the schema you created.

A fully-qualified JDBC connection URL like:

jdbc:oracle:thin:/@:: 

or a JDBC datasource name like:

java:/comp/env/jdbc/YourJ2EEDataSourceName