Things To Know About PS_TXN - ORA-00001: unique constraint violated - Using Multiple Schemas For A Single ADF Web Application.

Let me share with you an issue that we faced just recently. We had a requirement where we needed to show data from multiple schemas in our ADF Web application. Earlier, we were using a single schema for all the data in the database, but now some of our tables were moved to a new schema. Because of this new schema requirement, we had to think of its consequences on activation and passivation. As listed here:

http://docs.oracle.com/cd/E14571_01/web.1111/b31974/bcstatemgmt.htm

 The ADF internally used PS_TXN table to store passivation information. Here is the relevant text from the above link:

The passivated XML snapshot is written to a BLOB column in a table named PS_TXN, using a connection specified by the jbo.server.internal_connection property. Each time a passivation record is saved, it is assigned a unique passivation snapshot ID based on the sequence number taken from the PS_TXN_SEQ sequence. The ADF session cookie held by the application module data control in the ADF binding context remembers the latest passivation snapshot ID that was created on its behalf and remembers the previous ID that was used.

The following image shows how PS_TXN table’s data looks like:

In PS_TXN table, the COLLID column initially gets its value from PS_TXN_SEQ sequence, but after that its value is increased by the internal ADF generator. Because of this, when we use more than one schema and if each schema is having its own PS* objects then we can face a bug that is there in the framework, as listed in https://forums.oracle.com/forums/thread.jspa?threadID=2316855

If you are facing the following error while using multiple schemas, then you will be hitting that framework bug:

Caused By: java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (*******SCHEMA.PS_TXN_PK) violated

A workaround to this problem is to clean the PS_TXN tables regularly. This workaround is listed in the document, ADF 11g: How to Debug "JBO-28030: Could not insert row into table PS_TXN" [ID 1065862.1], available in support.oracle.com. Here is the summary of that document:

Cause

For some reason, your sequence PS_TXN_SEQ returns a value that was already inserted in to the PS_TXN table. This has caused a violation of the unique constraint (ICXPRODCS.PS_TXN_PK)

Solution

Cleaning up the table will help solve this issue.

The solution in this scenario is to use only one PS_TXN_SEQUENCE for all the schemas. So, if we have 2 schemas, then in the second schema instead of creating a new sequence, you can create a synonym that points to the sequence created in the first schema. Then this issue is avoided.

The best way to avoid this issue is to use just one PS_TXN table and PS_TXN_SEQ sequence, preferable in a separate schema independent from the current application specific schema.

For this we need to understand where these PS* objects are created and how can this be customized. As explained in the “Controlling Where the Temporary Tables Are Created “ section of http://www.oracle.com/technetwork/developer-tools/jdev/overview/bc4j-temp-tables-087270.html, the jbo.server.internal_connection property of AM is used to specify where these objects are created. Here is the summary of this link:

The BC4J framework recognizes a configuration property named   jbo.server.internal_connection to give the developer control over what database connection/schema should be used for the creation of the PCOLL temporary tables described below. If the value of this configuration parameter is not set by the developer (which is the default situation) then the framework will create the temporary tables using the credentials of the current application database connection. To keep the temporary information separate, it will use a different connection instance from the connection pool, but the database credentials will be the same as the current user.

 So, we can create a new schema just to store the PS* objects. After creating a new schema ( and creating PS* objects in that schema either manually or should be created automatically when passivation needs to be done), we can create a new Data Source to point to the new schema. After that, we just need to modify the jbo.server.internal_connection property of AM to give the JNDI of the new DS so that PS* objects of the new schema are used. We can test the fix by disabling the application module pooling to enable frequent passivation.

References:

http://docs.oracle.com/cd/E14571_01/web.1111/b31974/bcstatemgmt.htm
http://oraclefusionfacts.blogspot.in/2011/09/ora-01031-insufficient-privileges-jbo.html
https://forums.oracle.com/forums/thread.jspa?threadID=2316855
http://www.oracle.com/technetwork/developer-tools/jdev/overview/bc4j-temp-tables-087270.html