Locally Managed Tablespaces

[ericfjchen]

1. When using locally managed tablespaces, the default allocation type is system. Can we know the extent size?

2. What benifit does all extents have the same size in a locally managed tablespace?

Thanks

Eric

[twang]

1.
select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, MIN_EXTLEN, EXTENT_MANAGEMENT, ALLOCATION_TYPE, PLUGGED_IN from dba_tablespaces where tablespace_name like 'LOCAL%';
2. Better space management due to:
- uniform extent sizes;
- reduced data dictionary access.
Reduced tablespace fragmentation.
Better management of temporary space.

[ericfjchen]

select TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, MAX_EXTENTS, MIN_EXTLEN, EXTENT_MANAGEMENT, ALLOCATION_TYPE, PLUGGED_IN
from dba_tablespaces
where EXTENT_MANAGEMENT ='LOCAL'
and ALLOCATION_TYPE='SYSTEM'

===============
The cloumn "NEXT_EXTENT" is NULL.....

[Michael Schulte zur Sur]

Hi,

NEXT_EXTENT would be the same as INITIAL_EXTENT.

greetings,

Michael

[ericfjchen]

Can we define the initial extent size when creating a locally managed tablespace?

[Jean-Luc Oudart]

Hi Eric

we use Uniform extent for our tablespaces.
We have different tablespaces for DATA and Indexes and also group by average size of objects (SMALL,MEDIUM,LARGE).

As mentioned you save cpu and IO vs dictionary managed tablespaces and remove fragmentation.

example :
create tablespace SMALL_DATA datafile ''
SIZE 100M
extent management local uniform size 128K;

Regards
Jean-Luc

[Michael Schulte zur Sur · Accepted solution]

Eric,

initial extents are used when creating a table. You can specify the extent size for the tablespace and there is no initial and next there. See attachment.

greetings,

Michael

[Volker Borowski]

Hi,

the idea is to reduce space management.
With a uniform extentsize, it does not matter at all, if your tablespace is extent-fragmented, because all newly extents to be allocated will fit into any hole that a dropped object has left.

Volker

[ericfjchen]

1. If you choose locally managed tablespaces,
which one (auotallocate & uniform) do you prefer? Why?
2. If you choose locally managed tablespaces & uniform, how to determine a better extent size?

Thanks

Eric

[twang]

UNIFORM size should be the Initial_Extent of DBA_TABLESPACES.
In my view it should be 5, 10, or 20 times the block size and not 8 times the block size(oracle recommend).

[Yogeeraj_1]

hi,

question 1.
I prefer pctincrease = 0, initial=next. A locally managed tablespace with uniform extents makes that the "de-facto" rule, you cannot subvert it as you can with a dictionary managed tables. It totally avoids fragmentation -- it is impossible to have fragmentation in these tablespaces.

Also note that, Locally managed tablespaces are much faster at allocating and de-allocating extents - many order of magnitudes faster.

Remember that, Locally managed tablespaces decrease contention on the data dictionary.

question 2.
I put them in to "small", "medium" and "large" tablespaces based on my guess as to how big or small they will end up.

I use the following:
------------------------------------------------------------
Uniform Size Autoextend Striped
Extent Maxsize over disks
------------------------------------------------------------
tbs_lmt_small 64K 10M 50M all disks
tbs_lmt_medium 1024K 100M 1024M all disks
tbs_lmt_large 10M 1024M 2048M all disks

You may have many combinations of those!

hope this helps!

regards
Yogeeraj