rollback tablespace not enough

[yls177]

Database error 1562 at UPD access to table ZPSIDG
> ORA-01562: failed to extend rollback segment number
> 28#ORA-01650: unable to extend rollback segment PRS_27 by 26
> in tablespace PSAPROLL#


hmm, i may wrote this where pple may seem silly, but isnt rollback tablespace dynamic, which means it will shrink and expand according to the growth of transactions?

[stecal]

What is the storage parameter for the tablespace? Unlimited? Probably not.

ORA-01650: unable to extend rollback segment %s by %s in tablespace %s
Cause: Failed to allocate extent for the rollback segment in tablespace.
Action: Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified tablespace.

Error: ORA 1562 Text: failed to extend rollback segment (id = %s)
--------------------------------------------------------------------
Cause: Failure occurred when trying to extent rollback segment Action: This is normally followed by another error message that caused the failure. Shutdown, restart and then take appropriate action for the error the caused the failure. If starting up the system again doesn't solve the problem, it is possible that there is an active transaction in the rollback segment and the system can't roll it back for some reason(s). Check the trace file generated by the PMON process for more information.

[abhaysk]

u may handle in application for avoiding such errors....

commit regularly in PL/SQL that involves huge TRAN

Abhay.

[yls177]

currently, my rollback tablespace is over 15gb. i dont really understand this. since this rollback thing will shrink and grow accordingly to transactions. then when these transactions have committed, why space is not released and instead we have to keep adding data files?

[stmontgo]

Originally posted by abhaysk
u may handle in application for avoiding such errors....

commit regularly in PL/SQL that involves huge TRAN

Abhay.

why would u ever want to frequently commit in a pl/sql loop?
You significantly increase the chance of ora-1555 and put the brakes
on performance

steve