RE: extent check [message #21544] |
Mon, 12 August 2002 01:57 |
dhnish
Messages: 26 Registered: March 2002
|
Junior Member |
|
|
Hi
Recently I started to run this script to check on my extents. Initially for one of the tables the 'next extent" figure was somewhere near 200million when I encountered database full error. I changed the pctincrease from 50 to 0 and after running this script again this table did not appear and no error was shown.
Now it displays 50 over tables having next extent problems. Though I changed the pctincrease to 0 this tables are still displayed.
i.e: Table A -> 'Next Extent: 637,081,600
-> 'Largest : 507,717,632
Am I interpreting the script correctly?
What does the above details actually reflect?
Thank you
Prompt Segments that will cause errors when they try to extent!!!
column owner format a6
column segment_name format a18
column tablespace_name format a12
column largest format 999,999,990
column nextext format 999,999,999,990
select a.owner, a.segment_name, b.tablespace_name,
decode(ext.extents,1,b.next_extent,
a.bytes*(1+b.pct_increase/100)) nextext,
freesp.largest
from dba_extents a,
dba_segments b,
(select owner, segment_name, max(extent_id) extent_id,
count(*) extents
from dba_extents
group by owner, segment_name
) ext,
(select tablespace_name, max(bytes) largest
from dba_free_space
group by tablespace_name
) freesp
where a.owner=b.owner
and a.segment_name=b.segment_name
and a.owner=ext.owner
and a.segment_name=ext.segment_name
and a.extent_id=ext.extent_id
and b.tablespace_name = freesp.tablespace_name
and decode(ext.extents,1,b.next_extent, a.bytes*(1+b.pct_increase/100)) > freesp.largest;
|
|
|
Re: RE: extent check [message #21549 is a reply to message #21544] |
Mon, 12 August 2002 08:24 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
When table A was created, it probably had a default PCTINCREASE of 50% - that means that each time the table needs to extend, in increase the amount of additional space it grabs by 50%. PCTFREE 0% is much better (because it eliminates tablespace fragmentation if all tables/indexes in the TS have the same extent sizes). If you have 0% you need to set INITIAL and NEXT extent sizes more carefully when you create the segment in the first place. You can't change INITIAL once the table is in place (but you can easily get around that with MOVE to rebuild the table). You should change NEXT entent size to whatever you think is more appropriate(less than 507,717,632). Your script is warning that there is not enough contiguous space currently available to accommodate the net extent which table A may require as some stage. Having a table or index with hundreds of extents is no problem.
|
|
|
Re: RE: extent check [message #21553 is a reply to message #21549] |
Tue, 13 August 2002 01:38 |
dhnish
Messages: 26 Registered: March 2002
|
Junior Member |
|
|
Hi
Using the script as a guideline , I already changed pctincrease of Table A to 0.
But why does it still display the table when I execute the script again ?
Thank you
|
|
|
Re: RE: extent check [message #21559 is a reply to message #21553] |
Tue, 13 August 2002 08:27 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
the size for NEXT was already calculated the last time the table extended. PCTINCREASE 0 will stop NEXT from increasing next time the table extends. You need to reduce the value of NEXT to whatever you decide on.
alter table A storage (next 5M);
|
|
|