Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mac.

Asked: March 28, 2017 - 5:55 pm UTC

Last updated: April 03, 2017 - 12:45 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Tom, because when I create a table with the following values ​​in each of its partitions, I generate 195 extents?
If the partitions have an INITIAL if the size of the partition.


COMPRESS BASIC
STORAGE (
INITIAL 7748954362 - 7.2 GB
NEXT 1048576 - 1 MB
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0),


Thank you.

and Connor said...

By default, tablespaces are now locally managed, which means we use a bit map system to map out the file. Which means extents are defined in preset equally sized chunks (64k, 1m, 8m, ... etc)

For example

SQL> create table T ( x char(1000) )
  2  tablespace demo;

Table created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL>
SQL> select extent_id, bytes
  2  from   dba_extents
  3  where  owner = user
  4  and    segment_name = 'T'
  5  order by 1
  6  /

 EXTENT_ID      BYTES
---------- ----------
         0      65536


We gave you a 64k chunk. What if you asked for larger initial ? Now I drop the table and repeat

SQL> create table T ( x char(1000) )
  2  tablespace demo
  3  storage ( initial 20m next 7m);

Table created.

SQL>
SQL> insert into t values (1);

1 row created.

SQL>
SQL> select extent_id, bytes
  2  from   dba_extents
  3  where  owner = user
  4  and    segment_name = 'T'
  5  order by 1
  6  /

 EXTENT_ID      BYTES
---------- ----------
         0    8388608
         1    8388608
         2    1048576
         3    1048576
         4    1048576
         5    1048576

6 rows selected.


You asked for 20m, so we gave you 2 x 8M, plus 4 x 1M

This is a good thing, because as tables comes and go, grow and shrink, then they'll all be able to grab free chunks from each other, because all of the size allocations are consistent.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mac, March 29, 2017 - 3:00 pm UTC


OK, so the value I have of INITIAL will not generate an EXTENTS of that size?
What would be the maximum size for an EXTENTS?

Thank you.
Connor McDonald
March 30, 2017 - 12:35 am UTC

In a system managed tablespace, we incrementally go up to keep the number down, ie,

64k, then 1m, then 8m, then 64m and so forth.

If you are using a partitioned table, then (depending on the version) we start at 8m (because after all, partitioning suggests its gonna be a big table).


Mac, March 30, 2017 - 4:03 pm UTC

So the maximum size for an extents is 64M?
Connor McDonald
April 03, 2017 - 12:45 am UTC

I've never seen above 64M. I've heard it said that 256M would be the next size chosen, but I've never seen it.