Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

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
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).
Yogeeraj_1
Honored Contributor

Re: Locally Managed Tablespaces

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)