I tried it on my laptop:

SQL> select owner, segment_name, segment_type, extents, max_extents
  2        from dba_segments
  3       where max_extents-extents <= 3;

OWNER SEGMENT_NAME    SEGMENT_TYPE         EXTENTS MAX_EXTENTS
----- --------------- ------------------ --------- -----------
SYS   1.537           CACHE                      1           0

what can we do about it?
it is 8.1.5 

Tom,
"
ops$tkyte@8i> create table t ( x int, y char(2000) default '*' )
2 storage ( initial 40k next 40k minextents 5 )
3 tablespace system;

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
----------
sum 32
"

Why the 4th extent took 80K and 5th extent took 7K instead
of 5K

Thanks

My previous posting, rephrasing my question ;)

why the 4th extent took 10 blocks and 5th 7 blocks instead
of 5 blocks (40K / 8K db_block_size)

Thanks

Tom,

I was referring to your web-post
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079 <code>

where I found extents of sizes 40k, 40k, 40k, 80k, 56k
allocated when you created your sample table.

Thanks

We are monitoring extents utilization, to avoid obtaining ORA-1631 max extents reached error.

Can we alter all the tables and indexes to have max extents UNLIMITED? Is there any negative effect of doing this?

Thanks

Hi Tom,

You said that

"extents will grow larger as the number goes up" if we use LMT.

It would of great help if you could give us an example. It not that we dont believe. It would make use understand more.

Regards,
Sagi

Regards,
Sagi

ops$tkyte@ORA920.US.ORACLE.COM> create table t as select * from all_objects;

Table created.

ops$tkyte@ORA920.US.ORACLE.COM> begin
  2      for i in 1 .. 5
  3      loop
  4          insert /*+ append */ into t select * from t;
  5          commit;
  6      end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> exec print_table( 'select * from dba_tablespaces where tablespace_name = ''USERS'' ' )
TABLESPACE_NAME               : USERS
BLOCK_SIZE                    : 8192
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
MIN_EXTLEN                    : 65536
STATUS                        : ONLINE
CONTENTS                      : PERMANENT
LOGGING                       : LOGGING
FORCE_LOGGING                 : NO
EXTENT_MANAGEMENT             : LOCAL
ALLOCATION_TYPE               : SYSTEM
PLUGGED_IN                    : NO
SEGMENT_SPACE_MANAGEMENT      : AUTO
-----------------

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM>
ops$tkyte@ORA920.US.ORACLE.COM> select extent_id, blocks
  2    from user_extents
  3   where segment_name = 'T'
  4  /

 EXTENT_ID     BLOCKS
---------- ----------
         0          8
         1          8
         2          8
         3          8
         4          8
         5          8
         6          8
         7          8
         8          8
         9          8
        10          8
        11          8
        12          8
        13          8
        14          8
        15          8
        16        128
        17        128
        18        128
        19        128
        20        128
        21        128
        22        128
        23        128
        24        128
        25        128
        26        128
        27        128
        28        128
        29        128
        30        128
        31        128
        32        128
        33        128
        34        128
        35        128
        36        128
        37        128
        38        128
        39        128
        40        128
        41        128
        42        128
        43        128
        44        128
        45        128
        46        128
        47        128
        48        128
        49        128
        50        128
        51        128
        52        128
        53        128
        54        128
        55        128
        56        128
        57        128
        58        128
        59        128
        60        128
        61        128
        62        128
        63        128
        64        128
        65        128
        66        128
        67        128
        68        128
        69        128
        70        128
        71        128
        72        128
        73        128
        74        128
        75        128
        76        128
        77        128
        78        128
        79       1024
        80       1024
        81       1024
        82       1024
        83       1024

84 rows selected.

<b>there you go, see how the extent sizes just increased all by themselves in this SYSTEM allocation type tablespace...</b>

 

Tom,

We are using Dictionary Managed Tablespaces :( .. moving hopefully to LMT in a month or so.. :)
database size is about 2.5 gig.
We have a table that we use for auditing the DMLs for lot of tables...
Of course..this table is growing "really" fast..
and it gave ora-01631 today...
max extent on the tablespace as well as the table were set to 505 (8k db_block_size)..
We can increase that..to fix the issue...or set it to unlimited..
but the real reason this happened was because of the small next extent..
The table has 200000 rows now..
It just gets inserts...no updates or deletes

Question
1. Is there a method (a mathematical equation of some sort) to estimate the size of the initial and next extent
2. Will this table/tablespace be a good candidate for unlimited maxextents...
3.UNLIMITED sort of scares me...
What are some other guidelines for managing these large..always growing tables..besides watching them with free.sql...
4.Can you recommend a good book....that has lot of real world cases...please...

Thanks a zillion...

Hi Tom,

I have one snapshot with two indexes on it, see following detail info:

select index_name, column_name,column_length,column_position
from all_ind_columns where table_name='SNPSH_TRD_CNCLN_ASOF_TRD_DTL'
 and table_owner='IFDF'
SQL> /

INDEX_NAME     COLUMN_NAME     COLUMN_LENGTH COLUMN_POSITION
-------------------- --------------- ------------- --------
SYS_C003576        FDTP                  7                1
SYS_C003576   SBMTG_FIRM_ID              22               2
SYS_C003576    CLRG_FIRM_ID              22               3
SYS_C003576    CLRG_MPID_TX               5               4
SYS_C003576    CRSPT_FIRM_ID             22               5
SYS_C003576    CRSPT_MPID_TX              5               6
SYS_C003576          SEQ_NB              22               7
PF1_xxx        FIRM_PRCSD_DT              7               1

For these two indexes, the extent information is as following:
SEGMENT_NAME  EXTENTS INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS
------------------ ---------- -------------- ----------- ---
SYS_C003576     100        1048576     1048576         500
PF1_xxx         747        2097152     2097152         999

Notice that index of PF1_xxx is an index on one column however have more 600 more extents than SYS_C003576, while the later one indexes on more columns and bigger sizes. We manually increase max_extents for PF1_xxx when the ratio extents/max_extents exceeds 80% not long ago, and it seems extents number for PF1_xxx is still growing. The table size is about 1.1million records, with about 180 distinct FIRM_PRCSD_DT values. the growth is about 20k records/day.

They are in same snapshot table (completed refreshed daily). How does it happen? Will drop and rebuild PF1_xxx with bigger extent and smaller help reduce the extent growth?

I thought the index size is based on total column size; however this is conflict case. What should I do?

any suggestion is appreciated.  

followup:
it is regular one as:

create index PF1_xxx on snpsh_trd_cncln_asof_trd_dtl
(firm_prcsd_dt)
tablespace snpsh_index_tablespace
storage(initial 2m next 2m pctincrease 0)
nologging
;

Hi Tom,

We monitor a lot of customer databases thru standard database scripts. One such script is to find the availability of space for next extent of a segment. For DMTs we just query the size of next_extent, and initial_extent size for LMTs. Now, my problem is determining the size of next extent for "SYSTEM allocation type" LMTs. From your example above, as you can see, the next extent size increased from 8 to 128 and then to 1024 blocks for extent ids 16 and 79 respectively. Can you please explain me how these next extent sizes are determined so that I can incorporate this logic in my script.

Thankx Tom

Nope -- it is undocumented and I can show you examples where it doesn't follow that same progression.

You'll have to make assumptions, seems to go

64k 
1m
8m
64m

Here is from a 10gig table:

ops$tkyte@ORA920>

  1  select sum(mbytes*extents) over ()/1024 gig,
  2         sum(mbytes*extents) over (order by start_ext)/1024 cumgig,
  3         sum(extents) over () extents,
  4        a.*
  5    from (
  6  select bytes/1024/1024 mbytes,
  7         min(extent_id) start_ext,
  8        max(extent_id) end_ext,
  9        max(extent_id)-min(extent_id)+1 extents
 10    from user_extents
 11   where segment_name = 'T'
 12   group by bytes/1024/1024
 13*        ) a
ops$tkyte@ORA920> /

       GIG     CUMGIG    EXTENTS     MBYTES  START_EXT    END_EXT    EXTENTS
---------- ---------- ---------- ---------- ---------- ---------- ----------
    9.9375 .000976563        342      .0625          0         15         16
    9.9375      .0625        342          1         16         78         63
    9.9375          1        342          8         79        198        120
    9.9375     9.9375        342         64        199        341        143

so, the first 200extents are 8meg or less, you would be safe for those assuming that next is 8m (if they have less then 8m free, it is as good as full in my opinion).  If an object gets above 200 extents (larger then about 1gig in size), then you should start assuming at least 64m must be free. 

select owner, segment_name, segment_type, extents, max_extents from dba_segments where max_extents-extents <= 3;

Hahaha.

Tom,

I have a tablespace with 1682mb in size and when i query for the space used, it shows me 177mb used.

Previously, i moved those tables and indexes in that tablespace to another with new intial extent of 10K ...so, i got back the space. i did this because inital extent was more for those tables.

Now, the scenario is the intial extent is only 1mb .. there many tables, but all tables have initial extent of 1mb only.
why is that i am not able to resize the tablespace, it gives me the error

SQL> ALTER DATABASE
    DATAFILE '/u05/oradata/devdb/stage_d1_01.dbf' RESIZE
    500M  2    3  ;
ALTER DATABASE
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value


how can i reclaim the space?

Thanks a lot for all your time and considerations

Pravesh Karthik


 

I have a problem about "RA-1631: max # extents 4000 reached in table ARADMIN.B151C541870867"

I already increase max_extents to be 8000 from 4000. But still give same error. Please help.

SQL> select tablespace_name TABLESPACE, max(extent_id) EXTENSION_COUNT  from dba_extents group by tablespace_name;


TABLESPACE                     EXTENSION_COUNT
------------------------------ ---------------
ARSYSTEM                                  3999
IND_L                                       76
IND_M                                      185
IND_S                                      168
IND_XS                                     290
LOB_L                                       47
LOB_S                                       50
LOB_XS                                      16
RBS                                         23
SYSTEM                                      61
TAB_L                                      247
TAB_M                                      152
TAB_S                                      195
TAB_XL                                     151
TAB_XS                                     539
TAB_XXL                                    124
TOOLS                                        0

17 rows selected.

SQL> select TABLESPACE_NAME, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, STATUs from dba_tablespaces;


TABLESPACE_NAME                NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS STATUS
------------------------------ ----------- ----------- ----------- ---------
SYSTEM                               65536           1  2147483645 ONLINE
TOOLS                                32768           1        4096 ONLINE
RBS                                 524288           8        4096 ONLINE
TEMP                                 65536           1             ONLINE
USERS                               131072           1        4096 ONLINE
INDX                                131072           1        4096 ONLINE
ARTMPSPC                             40960           1         505 ONLINE
ARSYSTEM                            262144           1        8000 ONLINE
TAB_XS                               16384           1         505 ONLINE
TAB_S                                65536           1         505 ONLINE
TAB_M                               262144           1         505 ONLINE
TAB_L                              1048576           1         505 ONLINE
TAB_XL                             4194304           1         505 ONLINE
TAB_XXL                           16777216           1         505 ONLINE
IND_XS                               16384           1        1010 ONLINE
IND_S                                65536           1         505 ONLINE
IND_M                               262144           1         505 ONLINE
IND_L                              1048576           1         505 ONLINE
LOB_XS                               16384           1         505 ONLINE
LOB_S                               65536           1         505 ONLINE
LOB_L                             1048576           1         505 ONLINE


SQL> run
  1  select owner, segment_name, segment_type, extents, max_extents
  2  from dba_segments
  3* where max_extents-extents <= 3


OWNER
------------------------------
SEGMENT_NAME
--------------------------------------------------------------------------------
SEGMENT_TYPE          EXTENTS MAX_EXTENTS
------------------ ---------- -----------
ARADMIN
T69
TABLE                     505         505

ARADMIN
B151C541870867
TABLE                    4000        4000

SYS
1.173
CACHE                       1           0

 

SQL> connect USERA/xxxxxxxx@nl_prd
SQL> column owner format a12
SQL> column segment_name format a20
SQL> select owner, segment_name, segment_type, extents, max_extents
  2    from dba_segments
  3   where max_extents-extents <= 3;

OWNER        SEGMENT_NAME         SEGMENT_TYPE          EXTENTS MAX_EXTENTS
------------ -------------------- ------------------ ---------- -----------
SYS          1.128                CACHE                       1           0
SYS          1.24749              CACHE                       1           0

SQL> column file_name format a50
SQL> select file_name, tablespace_name from dba_data_files
  2   where tablespace_name = 'SYSTEM';

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/oracle/data/NL_PRD/sys/NL_PRD_SYSTEM_01.dbf       SYSTEM

SQL> connect USERA/xxxxxx@ek_plfrd
Connected.
SQL> /

FILE_NAME                                          TABLESPACE_NAME
-------------------------------------------------- ------------------------------
/oracle/data8/tab/EK_PLFRD/EK_PLFRD_system01.dbf   SYSTEM

SQL> select owner, segment_name, segment_type, extents, max_extents
  2    from dba_segments
  3   where max_extents-extents <= 3;

OWNER        SEGMENT_NAME         SEGMENT_TYPE          EXTENTS MAX_EXTENTS
------------ -------------------- ------------------ ---------- -----------
SYS          1.173                CACHE                       1           0



How come I have 2 CACHE segments in database A while there is only 1 CACHE segment in database B ?
(both databases have 1 datafile in the SYSTEM tablespace) 

Hi Tom,

In one of my Databases, most of the tables are created with larger intial extent sizes. The no.of rows in that tables are very less and some of them are dont have any data.

My duty is to reduce the size of the database as less as possible.

Because most of the tables are with 0 rows and bigger intial extent sizes. I would like to recreate the tables with small inttial extent sizes.

Can you please suggest me the best way for this.

Thanks you

For those still using dictionary managed tablespaces here is a procedure which should be compiled as SYS into your database which will create alerts in a report file which can then be interrogated by a batch job:-

START OF PROCEDURE
---------------------
rem Compile as SYS

CREATE OR REPLACE PROCEDURE monitor_extents AS
db_name_var varchar2(2000);
area1 number;
area2 number;
ts_name dba_tablespaces.tablespace_name%TYPE;
alert1 boolean;
alert2 boolean;
next number;
error_type varchar2(9);
error_message varchar2(255);
required_fix varchar2(255);
status varchar2(20);
--
cursor db is select global_name from global_name;
--
cursor ts is
select tablespace_name
from dba_tablespaces
where tablespace_name not in ('TEMP','RBS','SYSTEM')
order by tablespace_name;
--
cursor areas is
select bytes
from dba_free_space
where tablespace_name = ts_name
order by bytes desc;
--
cursor segs is
select segment_name,
next_extent,
extents,
max_extents
from dba_segments
where owner = 'FG_HELPDESK'
and tablespace_name = ts_name
and segment_name not like '%$%';
--
BEGIN
--
open db;
fetch db into db_name_var;
close db;
---
--- Loop for all tablespaces, get 2 largest free areas, get all segs in tablespace
--
for ts_rec in ts loop
dbms_output.put_line('ts = '||ts_rec.tablespace_name);
ts_name := ts_rec.tablespace_name;
open areas;
fetch areas into area1;
-- dbms_output.put_line('area1 = '||area1);
fetch areas into area2;
-- dbms_output.put_line('area2 = '||area2);
close areas;
--
for seg_rec in segs loop
-- dbms_output.put_line('seg = '||seg_rec.segment_name||', next = '||seg_rec.next_extent);
alert1 := FALSE;
alert2 := FALSE;
next := seg_rec.next_extent;
if ((2*next > area1) and (next > area2)) then
alert1 := TRUE;
dbms_output.put_line('******************** ALERT 1 ****************');
dbms_output.put_line('No room for 2 additional extents for '||seg_rec.segment_name);
end if;

if (next > area1) then
alert2 := TRUE;
dbms_output.put_line('******************** ALERT 2 ****************');
dbms_output.put_line('No room for next extent for '||seg_rec.segment_name);
end if;

--this section added so if 2 alerts occur at the same time, both are
--signalled.

if (alert1) then
status := 'ALERT1';
end if;
if (alert2) then
status := 'ALERT2';
end if;
if (alert1 or alert2) then
dbms_output.put_line('tablespace = '||ts_name||', Object = '||seg_rec.segment_name);
dbms_output.put_line('Next extent size = '||next);
end if;
alert1 := FALSE;
alert2 := FALSE;
--end of added section
--
if (seg_rec.extents > seg_rec.max_extents/2) then
alert1 := TRUE;
dbms_output.put_line('******************** ALERT 3 ****************');
dbms_output.put_line('50% + extents used by '||seg_rec.segment_name);
end if;

if (seg_rec.extents = seg_rec.max_extents) then
alert2 := TRUE;
dbms_output.put_line('******************** ALERT 4 ****************');
dbms_output.put_line('Max extents reached '||seg_rec.segment_name);
end if;

if (alert1) then
status := 'ALERT1';
end if;

if (alert2) then
status := 'ALERT2';
end if;

if (alert1 or alert2) then
dbms_output.put_line('Tablespace = '||ts_name||', Object = '||seg_rec.segment_name);
dbms_output.put_line('Next extent size= '||next);
end if;
end loop;
end loop;
----
END monitor_extents;
/


________________________

END OF PROCEDURE.

The steps to use tis are as follows:-

1. Edit the procedure as required for tablespace names etc.
2. Compile into the database as SYS.
3. Create a batch job to run at regular intervals which executes the procedure and writes to a report file.
4. Run another job to interrogate the output for 'alert' and send a message/e-mail etc to report the alert.

Referring to your answer regarding minimum available extents.

You Said...

I really liked 3 that day.
although 42 might be the ultimate answer (geek alert)

I never got what you mean by "42"

This is exactly what I was looking for, but I need the syntax of the alter table command to increase max_extents. This contained everything I needed to know except exactly how to do it.

I have a table which is 15 gig in size. I use BYTES from dba_segments column to determine the size of the table (object). The value of blocks in dba_segments is 800,000

Say I have to delete half of the table using DELETE statement.

After a while (say 60 minutes), if I see the value 65,000 in USED_UBLK column under v$transaction table for the session which I am deleting the data, can I assume it takes around 7 hours to delete half of the table (400k blocks)?

65K blocks--- one hour
400,000 blocks---- approximate 7 hours?

Thanks,

Using Oracle 9206 and I have an index tablespace that only has 1 datafile which is autoextensible, locally managed tablespace, segment mgmt. audo and plenty of free space on disk.

I get a ORA-1654: Unable to extend THEINDEX by 8192 error. Max extents on the index are set to 2147483645 on the index.

How can I get an ORA-1654? I'm nowhere near using 2147483645 extents and the datafile is on autoextend with plenty of free space.

I understand that Oracle's telling me it cannot find a contiguous extent free, but if Oracle cannot find an extent to use, the datafile should autoextend since that's the purpose of autoextend, no?

Guess I'm confused here on space management and re-read the docs, but according to what I read the data files should auto extend.

From the Oracle 9.2 docs when AUTOEXTEND on a datafile is on it "Ensures applications will not halt because of failures to allocate extents"

Can you please clarify how to set up a tablespace that has plenty of free disk space so that I never have to worry about extents?

MAXBLOCKS = 4194302 x BLOCKSIZE 8192 = 31.99 gb datafile max.

and wouldn't you know that the datafile size shows right at 32 gb.

Thanks. I won't forget that one anytime soon. Usually I don't put a max size on a datafile and I didn't create this database so that didn't even cross my mind. Doh. It's a good
thing though, it means my understanding of extents is still intact. :-)

On 9.2.0.7 unix platform when I create a tablespace extent management local uniform size 1m ( without specifying initial and next extents );

I get min_extlen 66k ( 11 times blocksize)

Is there a default min_extlen allocated by oracle based on block size

Following up the above posting, could it be 64k rounded up to nearest block size ? I do not have a test system to test with. My laptop is hosed up

66K is a rare number to see since it's rare to have a database with a 6K block size. Perhaps you were seeing min_extlen = 65536? If so, I guess could be rounded up and written by some people as 66KB (66*1000bytes) or 64KiB (64 * 1 Kilobyte or 1024 bytes).
If somehow the "uniform extent size 1m" clause was left out or ignored from the CREATE TABLESPACE statement, then the default of autoallocate is implied, which is only one of possibly many reasons you could see min_extlen=65536 (allocation_type = 'SYSTEM').
Just my wild guess.

One of the developers complained that any operations on a table (table A) with a little more than 10,000 rows is taking a very long time. I compared that table to another similar table (table B) with 7,000 rows. The main difference that I see is that the no of extents for table A is 15,700 and the no of extents for table B is 20. Also, if I see the Block IDs, for table B it is somewhat contiguous but for table A it is all over the place. This could directly impact the performance of the table, right? How do I make the performance better ?