Skip to main content

ADF: PS_TXN unique constraint violated passivation

Ok so I had this problem in a few applications and want to solve it the best way once and for all.
java.sql.SQLIntegrityConstraintViolationException: ORA-00001: unique constraint (SCHEMA.PS_TXN_PK) violated or oracle.jbo.PCollException: JBO-28030: Could not insert row into table PS_TXN, collection id . What a pain.

So I have come across a couple of methods to solve this one:

1) Cleanup table regularly - this is a poor solution and is not guaranteed to work all the time. This involves creating a job to run (maybe twice a day) to sort the sequences out and clean up tables. Although it is the easiest to implement and requires no code changes. Can use this as a stop gap measure while your developers are fixing the real problem.

2) use a file store for passivation - better solution this one but I must test the performance impact and what to do if the filestore get corrupt. This involves setting the AM property  jbo.passivationstore = file on all Application Modules. Not convinced this is the best solution but it should do for small apps.

3) using one schema with jbo.server.internal_connection - just specify your datasource name in this property to point to a utility schema and you should be good to go.

4) Catching the error and doing a clean at this point - Lot of work this one better than solution 1 but really not a productive use of time. (I havent tried this one)

I really need to finish the performance testing on our current application with the 11.1.2.1 and unit test before I make the call.

http://www.oracle.com/technetwork/developer-tools/jdev/overview/bc4j-temp-tables-087270.html

Comments

  1. I have discovered that if you create the PS_TXN_SEQ as follows:

    create sequence PS_TXN_SEQ MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE;

    You will have this problem. Instead, do this:

    CREATE SEQUENCE PS_TXN_SEQ INCREMENT BY 50 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

    ADF BC seems to assume that it has the full 50 sequence values to use in session!

    ReplyDelete
  2. Hi Donovan,

    I am using JDev 11.1.1.5.0. I am also getting the same issue, on PS_TXN only in some scenarios of my application. My application uses only one schema having 16 application modules. So I tried to change jbo internal connection to point to the same database. I could not pass thru the error. I tried to increase referenced pool size, still no luck. I tried even clearing ADF BC temporary tables, still the same issue. Please let me know if I am missing anything.

    Thanks,
    Satya

    ReplyDelete
    Replies
    1. Have you tried dropping and recreating the sequence that generates the keys for ps_txn as the above comment from Christopher. Also is the above problem happening on a dev environment where multiple weblogic instances are running against a single database. - very odd that the scripts dont work have you tried dropping the ps_txn table and allowing adf to recreate it? (assuming you are not running in a critical environment)

      Delete
  3. Thanks Donovan for your response. I am trying in my development environment. I tried the purge script provided by ADF team (C:\Oracle\Middleware\oracle_common\common\sql\adfbc_purge_statesnapshots.sql). We have only one managed server running on the admin server.
    Issue: I am trying to open a tab on an entity( say contact id 23), it displays the info with out any error in a tab. Then I tried to open another entity (say contact id 24), I am getting the JBO 28030 exception. It always happening on second request.

    Thanks,
    Satya

    ReplyDelete
    Replies
    1. Sounds familiar, if the clean up script script does not work you have an actual bug and none of the above will help you, what I can imaging is happening is that you have two transactions on a page writing to PS_TXN from different app modules, if you want to recreate the error then zip the log file and email me at the email address on my profile I will take a look.

      Delete

Post a Comment

Popular posts from this blog

ADF Encountered deferred syntax #{ in template text.

OracleJSP error: oracle.jsp.parse.JspParseException:  Error: Encountered deferred syntax #{ in template text.  If intended as a literal, escape it or set directive  deferredSyntaxAllowedAsLiteral This normally happens when you have some tag lib dependancy problems but this was  not the case for me... My problem: For some reason my model project had web stuff in it(public html etc)  so I had to remove the public html stuff from my project and manually edit the Model.jpr project file and remove the tag lib entries at the bottom o the file. Go figure.    

JBO-25013: TooManyObjectsException

oracle.jbo.TooManyObjectsException: JBO-25013: Too many objects match the primary key oracle.jbo.Key[Key null ]. Ok so for you it may be trying to insert a duplicate record this should explain your problem (also check trigger they could be the cause.) NOTE: You can also try to create a new duplicate EO if you have a page with two VO's using the same EO. This could sort your problems. For me I needed to add a launch listener on my LOV and clear the cache of my vo. LOV <af:inputListOfValues id="NameId" popupTitle="#{bindings.Name.hints.label}" value="#{bindings.RolName1.inputValue}" label="#{bindings.RolName1.hints.label}" model="#{bindings.RolName1.listOfValuesModel}" required="#{bindings.RolName1.hints.mandatory}" columns="#{bindings.RolName1.hints.displayWidth}" shortDesc="#{bindings.RolName1.hints.tooltip}" launchPopupListener="#{backingBeanScope.backingBean.launchPop

OJDeploy: Documentation for the tool

Real DOCS:  http://docs.oracle.com/cd/E26098_01/user.1112/e17455/deploying_apps.htm#OJDUG645 OJDeploy Documentation if you run it from the command line - I keep looking for this so I though I would post it here so I remeber. Oracle JDeveloper Deploy 11.1.2.1.0.6081 Copyright (c) 2003, 2010, Oracle and/or its affiliates. All rights reserved. Usage:   ojdeploy -profile <name> -workspace <jws> [ -project <name> ] [ <options> ]   ojdeploy -buildfile <ojbuild.xml> [ <options> ]   ojdeploy -buildfileschema Arguments:   -profile               the name of the Profile to deploy   -workspace      full path to the JDeveloper Workspace file(.jws)   -project              name of the JDeveloper Project within the .jws where the Profile can be found. If omitted, the Profile is assumed to be in the Workspace.   -buildfile            full path to a build file for batch deploy   -buildfileschema  print XML Schema for the build file