oracle 1562 error

[Pieter_5]

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?

[Ian Lochray]

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.

[John Jayaseelan · Accepted solution]

Andre,

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

[Steven E. Protter]

Why not this?

alter ROLLBACK SEGMENT RBS03 STORAGE (MAXEXTENTS UNLIMITED);


SEP

[Jean-Luc Oudart]

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

[Brian Crabtree]

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