Why Rollback still can not extend ?

[ligang]

after set the optimal size and adjust the inital , next and minextends value of all the rollback seg and rollback tablespace is also big enough , But when a long batch is running , still the rollback segment can not extend error is reported .

Confused ....why ?

[grjohnson]

What about the MAXEXTENTS value?

[ligang]

It's Unlimited .

And Rollback Tablespace is autoextend .

[aarroyob]

Do you have enough space in disk to autoextend?

And what about the maxextents of Rollback TB?

Take care with space in disk having a Rollback TB in autoextend mode.

Cheers

Angel

[sarannn_99]

Obviously what Mr.Aarroyob said correct so, do check the tablespace maxextents and check whether the tb reached the end point.
bye

[Orca777]

Hi ligang,

Could it be that your rollback-segments had different storage before and you have now to 'COALESCE' the Tablespace if it's not organized LOCAL so that your new sized extents can allocate the whole Tablespace.

And do not mix samll RBS with big RBS in one Tablespace.
The small ones could fragment the Tablespace too;

Orca

[ligang]

the current extends are only 2 , (so only 20 MB allocated
for each rollback seg)

There are still tuns of disk space ...

[Sureshy]

a. you have enough OS disk space
b. you have enough tablespace free space
c. your freespace pieces are big enough to house a next extent.
d. your maxextents has not been breached

rbs should extend.

The following script will point out those objects that are not able to extend by their specified amount :

prompt
prompt
prompt
prompt Listing Those Objects And Tablespaces Which Will Not Be Able To
prompt Extend By Their Specified Amount
prompt --------------------------------------------------------------
pause

select owner,
segment_name,
segment_type,
s.tablespace_name,
fs.max_contig/1024/1024 "(MB)",
next_extent,
decode(sign(fs.max_contig - next_extent),-1, fs.max_contig - next_extent) Bytes
from (select tablespace_name, max(bytes) max_contig from dba_free_space group by tablespace_name) fs,
dba_segments s
where fs.tablespace_name = s.tablespace_name and
fs.max_contig - s.next_extent < 0
order by fs.tablespace_name, s.owner, s.segment_type, s.segment_name;


This script will give you free space information and largest free size piece.

prompt Listing Available Free Space In Tablespaces/Fragmentation
prompt ---------------------------------------------------------
pause



select substr(f.tablespace_name,1,40) "Tablespace Name",
to_char(d.bytes/1024/1024,'9999.99')||' MB' Total,
to_char(sum(f.bytes/1024/1024),'9999.99')||' MB' Free,
to_char(round(max(f.bytes/1024/1024),2),'9999.99')||' MB' Biggest_Chunk,
to_char(round(min(f.bytes/1024/1024),2),'9999.99')||' MB' Smallest_Chunk,
count(f.bytes/1024/1024) Pieces
from dba_free_space f,
dba_data_files d
where f.file_id = d.file_id
group by f.tablespace_name,
to_char(d.bytes/1024/1024,'9999.99');