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
Labels:

Comments

  1. UnknownJuly 5, 2012 at 9:54 AM

    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. satyaAugust 12, 2012 at 5:58 AM

    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. Donovan SherriffsAugust 12, 2012 at 6:53 AM

      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. satyaAugust 13, 2012 at 12:25 AM

    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. Donovan SherriffsAugust 13, 2012 at 6:55 AM

      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

MANIFEST.MF merge JDeveloper for an executable jar

Goto your project > properties. Then click on deployment in the menu. Edit or add a jar deployment profile. Fill in the details under jar options (select Include manifest and give it a main class name) Also remember that the merge functionality only works with a BLANK line at the end of the merge file. REALLY this caught me. My merge file contents: Class-Path: commons-codec-1.3.jar [...empty line here CRLF...]
1 comment
Read more

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...
Post a Comment
Read more

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.    
3 comments
Read more