Extents

What extents problems can occur with a datafile and how can these be solved?

the main problem I run into is the datafile runs out of space and cannot allocate a new extent. To avoid this, and having your database/tablespace lock up, is to either have a monitoring script which will notify you when a datafile is reaching "maximum density" (say 80-90%), OR just monitor it yourself on a daily basis. If you monitor it by hand, then you run the risk of filling up you datafile during a long weekend when you are not in the office (like Thankgiving 4-day weekend).

Does that help?

- Magnus

Do you ever have to resize or mainpulate the extents in any way?

All I do is increase the size of the Datafile to allow more extents. I do not change the size of the actual extents. I understand that you should not change extent sizes of a tablespace. If anything, you would create a NEW tablespace/datafile with a different extent configuration and then import the data to the new tablespace. My understanding is that is the best way if you were planning on resizing the extents.
Somewhere I read (or someone here told me) that you should change extent sizes within a tablespace on the fly due to performance or other issues. That it is much better to keep all extents in the same tablespace the same size.

let me know if you feel this is incorrect, but this is my understanding.

- Magnus

You can also do the following:

alter database datafile 'YOUR_DATAFILE_NAME'
autoextend on;

Steeve Bisson
EMail: [email protected]

Auto Extend on may create another problem like it will grow till the Disk is full. I suggest you to use monitoring scripts and increase it accordingly

I agree with Kishore
- Magnus

There's been a lot of debate back and forth actually about extents and how much overhead is involved w/ extent management and such.

With extents, really the idea is to get a good estimation of the row sizes of each of your tables, how much data you will initially load into the table, and size the extent to include the initial load in a single extent. If you have a good idea of how the table is going to grow then you size your next extents accordingly to allow for controlled growth. Basically you don't want it to be an administrative nightmare but you also just don't want them growing out of control. Personally, I keep pctincrease at 0, and allow for average growth of the table and administer them as needed. If your estimation is wrong and the extents are growing too quickly then export the table, recreate the table w/ more appropriate storage parameters and reimport the data.

Joe

What is the best way to size extents on table creation?

Hi there,
This is a script that i loaded from the web and use to monitor our table and index extents. copy & paste into notepad and save as sql file. Use SQL plus to run it daily or weekly depends on how heavy your db traffic is. I hope it help you.

/* Viewing Critical Object Extents
This select shows all objects, tables, or indexes, the number of extents allocated, the max
number of extents allocable and the number of extents again possible before the Oracle error.
The having statement restricts the output. */

SPOOL CRITICAL_OBJECT_EXTENTS.TXT
set echo off
set pages 37
set lines 100
set feedback off
tti '------------CRITICAL_OBJECT_EXTENTS----------------'

col name format a28
col type format a8
col sum_kbytes format 999999999
col man_ext format a3
col ext_poss format 99
col tbs format a10
col owner format a9
col num_ext format 999

select ext.owner,
segment_name name,
segment_type type,
sum(bytes)/1024 sum_kbytes,
count(bytes) num_ext,
decode(segment_type, 'TABLE', tab.max_extents,
'INDEX', ind.max_extents) max_ext,
decode(segment_type, 'TABLE', tab.max_extents,
'INDEX', ind.max_extents) -
count(bytes) ext_poss ,
ext.tablespace_name tbs
from dba_indexes ind,
dba_tables tab,
dba_extents ext
where ind.owner (+) = ext.owner
and ind.index_name (+) = ext.segment_name
and tab.owner (+) = ext.owner
and tab.table_name (+) = ext.segment_name
and ext.owner not in ('SYS', 'SYSTEM')
group by ext.owner,
segment_name,
segment_type,
decode(segment_type, 'TABLE', tab.max_extents,
'INDEX', ind.max_extents),
ext.tablespace_name
having decode(segment_type, 'TABLE', tab.max_extents,
'INDEX', ind.max_extents) -
count(bytes) < 60
or count(bytes) > 300;
SPOOL OFF
set echo on
set feedback on
tti off