Operating System - HP-UX
1748185 Members
4376 Online
108759 Solutions
New Discussion юеВ

Locally Managed Tablespaces

 
SOLVED
Go to solution
ericfjchen
Regular Advisor

Locally Managed Tablespaces

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
10 REPLIES 10
twang
Honored Contributor

Re: Locally Managed Tablespaces

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
Regular Advisor

Re: Locally Managed Tablespaces

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
Honored Contributor

Re: Locally Managed Tablespaces

Hi,

NEXT_EXTENT would be the same as INITIAL_EXTENT.

greetings,

Michael
ericfjchen
Regular Advisor

Re: Locally Managed Tablespaces

Can we define the initial extent size when creating a locally managed tablespace?
Jean-Luc Oudart
Honored Contributor

Re: Locally Managed Tablespaces

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
fiat lux
Michael Schulte zur Sur
Honored Contributor
Solution

Re: Locally Managed Tablespaces

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
Honored Contributor

Re: Locally Managed Tablespaces

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
Regular Advisor

Re: Locally Managed Tablespaces

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
Honored Contributor

Re: Locally Managed Tablespaces

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).