Home » SQL & PL/SQL » SQL & PL/SQL » RE: extent check
RE: extent check [message #21544] Mon, 12 August 2002 01:57 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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);
Previous Topic: Re: date range
Next Topic: time taken in executing a query.
Goto Forum:
  


Current Time: Thu Mar 28 19:16:12 CDT 2024