topic Re: oracle 1562 error in Operating System - HP-UX

oracle 1562 error

Pieter_5 — Fri, 12 Sep 2003 11:58:56 GMT

Hi,

I just increased the tablespace by adding a datafile to it. THe users have a new run but while they tried to import a lot of data they receive a oracle 1562 error. The error claims that it cannot extent the rollback segment because the max extents are reached. What to do next?

Re: oracle 1562 error

Ian Lochray — Fri, 12 Sep 2003 12:12:10 GMT

Andre,
I can think of a couple of options.

If the users are using the Oracle "imp" utility then you can specify the "commit=y" option on the command line. This will commit the data every so often rather than for each whole table, cutting down the amount of rollback required.

If they are not, you need to alter the storage parameters on your rollback segments. Check the current settings from sqlplus when logged on as the system user with:-
select * from dba_rollback_segs;
You can then alter the storage parameters with commands like :-
alter ROLLBACK SEGMENT RBS03 STORAGE (MAXEXTENTS 249);

You may also have to extend the size of your rollback tablespace.

Re: oracle 1562 error

John Jayaseelan — Fri, 12 Sep 2003 13:20:15 GMT

Andre,

Try to do this import when no other processes are utilising the Roll back segments.

Re: oracle 1562 error

Steven E. Protter — Fri, 12 Sep 2003 13:24:31 GMT

Why not this?

alter ROLLBACK SEGMENT RBS03 STORAGE (MAXEXTENTS UNLIMITED);

SEP

Re: oracle 1562 error

Jean-Luc Oudart — Fri, 12 Sep 2003 14:31:16 GMT

I would not put an unlimited nbr extents for a rollback segment.

Tune it . Change extent size.

As said, you can use commit=Y .
Also change the buffer parameter
BUFFER=

That will reduce the number of commits.

Rgds,
JL

Re: oracle 1562 error

Brian Crabtree — Fri, 12 Sep 2003 20:05:19 GMT

Andre,

Take a look in the v$rollstat view. There is a column called "AVEACTIVE". A good idea is to double this column (try to keep it in round 1m or 500k sections though) for your initial extent, with 8-10 minextents for each segment.

If that doesn't work, 50m extents with 8 minextents normally works. =)

Brian