Locally Managed Tablespace

Oracle Version : 10.1.0.2.0
OS Platform : WinXP sp2
-------------------------------------------------------------------------------------
Locally Managed Tablespace
Benefits
1.Readable standby databases are allowed, because locally managed temporary tablespaces (used, for example, for sorts) are locally managed and thus do not generate any undo or redo.

2.Coalescing free extents is unnecessary for locally managed tablespaces
-------------------------------------------------------------------------------------
1.We can create Locally Managed Tbs specify "LOCAL" extent management clause.
create tablespace &tablespace_name
datafile 'path' size xxxk
EXTENT MANAGEMENT LOCAL;

2.If we want database extent manage automatically we should choose "AUTOALLOCATE" clause. It is default.
create tablespace &tablespace_name
datafile 'path' size xxxk
extent management LOCAL
AUTOALLOCATE;

3.If we want exact control on unused space and we can predict allocation for objects then "UNIFORM" size is best.This setting ensures that you will never have unusable space in your tablespace.
create tablespace &tablespace_name
datafile 'path' size xxxk
UNIFORM SIZE XXXK;
Note: If you omit SIZE clause with UNIFORM, then the default size is 1M

4.When we not specify explicity "EXTENT MANAGEMENT CLAUSE" then database determines extent management as fellows.
1.If we omit DEFAULT storage clause in create tablespace statement then database create tablespace in "Locally Managaned + Autoallocated".
create tablespace &tablespace
datafile 'path' size xxxk;

2.If we specify DEFAULT storage clause then
1.If MINIMUN EXTENT + INITIAL + NEXT are EQUAL AND PCTINCREASE is 0.then database create "Locally Managed + Uniform".

SQL> create tablespace TEST
2 datafile 'c:\test.dbf' size 2m
3 default storage (
4 initial 100k
5 next 100k
6 pctincrease 0);

Tablespace created.
SQL> create table scott.f as select * from all_objects where rownum <= 9000;

Table created.

SQL> select extent_id,block_id,bytes,blocks
2 from dba_extents
3 where owner = 'SCOTT' and segment_name = 'F';

EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 9 106496 13
1 22 106496 13
2 35 106496 13
3 48 106496 13
4 61 106496 13
5 74 106496 13
6 87 106496 13
7 100 106496 13
8 113 106496 13

9 rows selected.

SQL> alter database default tablespace test;


2.if MINIMUN EXTENT + INITIAL + NEXT are NOT EQUAL OR PCTINCREASE is 0.then database create "Locally Managed + Autoallocated".ignore any storage settings.

SQL> create tablespace TEST
2 datafile 'c:\test.dbf' size 7m
3 default storage (
4 initial 100k
5 next 200k
6 pctincrease 0);

Tablespace created.

SQL> alter database default tablespace test;

Database altered.

SQL> select extent_id,block_id,bytes,blocks
2 from dba_extents
3 where owner = 'SCOTT' and segment_name

EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
0 9 65536 8
1 17 65536 8
2 25 65536 8
3 33 65536 8
4 41 65536 8
5 49 65536 8
6 57 65536 8
7 65 65536 8
8 73 65536 8
9 81 65536 8
10 89 65536 8

EXTENT_ID BLOCK_ID BYTES BLOCKS
---------- ---------- ---------- ----------
11 97 65536 8
12 105 65536 8
13 113 65536 8
14 121 65536 8
15 129 65536 8
16 137 1048576 128
17 265 1048576 128
18 393 1048576 128
19 521 1048576 128
20 649 1048576 128

21 rows selected.

5.Segment Space Management
Two option
1.Manual ( default)
Manual segment-space management uses free lists to manage free space within segments.
you must specify and tune the PCTUSED, FREELISTS, and FREELIST GROUPS storage parameters for schema objects created in the tablespace

2.Auto
Automatic segment-space management uses bitmaps to manage the free space within segments.
You can specify automatic segment-space management only for permanent, locally managed tablespaces

Note :
The segment-space management you specify at tablespace creation time applies to all segments subsequently created in the tablespace. You cannot subsequently change the segment-space management mode of a tablespace.