Re: PeopleSoft SQR Problem BEN733

[Richard_Tomancik <Richard_Tomancik@TIGINSURANCE.COM>]

If the program is doing a large number of inserts without  executing a
commit (as I have seen in other PeopleSoft SQRs) then that may be why you
are running out of rollback space.  An SQR automatically commits only when
the program ends.  If you want to commit sooner, I'd suggest:
     - Increment a counter every time you insert a record
     - Check the counter after every commit
     - If the counter is 5000 or greater (that number has worked well in
the past) then execute a commit and reset the counter.

Just be aware that inserted records that have been committed will not
rollback if the SQR stops before it is completed.

RT





Bill Bowers <bowersb@PSPH.PROVIDENCE.ORG> on 09/10/98 01:57:56 PM

To:   Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
cc:    (bcc: Richard Tomancik/Consultant/Dallas/TIG)
Subject:  PeopleSoft SQR Problem BEN733




     We are unable to run our PeopleSoft delivered SQR, BEN733 the Base
     Benefits Audit, in our environment: Payroll, HR and Ben Admin.  It
     always stops at the same routine:  Employee and Spouse (or other
     Dependent) Both Electing Health Benefit.  The routine purpose and
     header are printed and nothing else.

     The error we get is:

     (SQR 5528) ORACLE OFETCH error - 1652 in cursor 6:
     ORA-01652: unable to extend temp seqment by 8 in tablespace PSTEMP

     Error on line 322:
     (SQR 3725) Bad return fetching row from database.

     SQRW: Program Aborting.


     We have tried the following without success:
     1.  Increasing the size of the temporary tablespace (PSTEMP) to 1GB.
     2.  Making PSTEMP permanent in order for Oracle's SMON process to
         clean it up.
     3.  The required tables do in fact exist.
     4.  Ensuring that the unique indexes are in place for all the affected
         tables.
     5.  Allocated sufficient size for batch rollback segments.

     Does anybody have any ideas on how this problem may be corrected?

     Bill