Oracle Tablespace Management

There are two type of tablespace management LMT and DMT.
LMT:(local managed tablespace)
It came from 9i. the block information(free or used) will maintain in datafile header as 0's & 1's.
DMT:(dictionary managed tablespace)
The extent information will maintain in data dictionary as FET$ and UET$
DIFFERENCE BETWEEN LMT AND DMT:

No	DMT	LMT
1 2 3 4	free list maintain in data dictionary as FET$,UET$ high  I/O  process  Manual Coalesce more undo generated	free list maintain in data file header as 0's & 1's  Less I/O process  Auto Coalesce Less undo generate

EXTENT MANAGEMENT:

• collection of blocks is called as extent.
• using local managed tablespace  the extent size can be managed like UNIFORM or AUTOALLOCATE.

UNIFORM: 
The extent size will allocated as same size for every new extent.
AUTOALLOCATED:
 It take the default values base on storage parameters.

(STORAGE PARAMETERS IN EXTENT MANAGEMENT):

• Intial   - first extent values
• Next    - next values in segments
• Min Extents    - minimum amount of extent get created at a time of table/segment.
• Max Extents   - unlimited
• Pct Increase    - 50% of increase for next extent value.
• Logging     -it allow all sql statement to write in online redo logs except (select).
• No-logging   - it opposite to logging and when giving the rebuild index,cts,insert into table from  select statement this ill not write in online redo logs.
• Blocksize  - this parameter is using to specifies the block size .
• Online  -   it show the tablespcae in online .
• Offline  -  after creation tablespace it show in offline.
• Permanent   - it hold the permanent database objects.
• Temporary   - it hold the temporary databasse objects.
• (next extent/growth=prev.extent + (pct increase x prev.extent)---- this is formula for 3rd extent

In DMT Tablespace:

create tablespace  sainora datafile '/u01/app/oracle/oradata/data/sainora.dbf' size 200m extent management dictionary default  storage (intial 1m next 2m minextents  1 maxextents 10 pct increase 50%);

In LMT Tablespace:

create tablespace sainora datafile '/u01/app/oracle/oradata/data/sainora.dbf' size 200m extent management local uniform size 1m;
create tablespace sainora datafile '/u01/app/oracle/oradata/data/sainora.dbf' size 200m extent management local autoallocate;

SEGMENT MANAGEMENT:

• Collection of extents is called as segment.
• It using to managed the free and used space with in a segments. it can mention as 

MANUAL or AUTO.
MANUAL:
Mention the data block size for each segments using the space parameter.
AUTO:
 It take automatic values based on data blocks.
 It managed the free space automatically.         
(SPACE  PARAMETER IN SEGMENT MANAGEMENT):

• Pct free  - 10% for updates.
• Pct used  - to find the block where free or  used.
• Init trans  - intial transaction for each block
• Max trans  - max transaction for each block

In DMT Tablespace (manual):

create table  san_tab(id number(9),name varchar2(9) pctfree 20 pctused 50 inittrans 3 maxtrans 10;

In LMT Tablespace(auto/manual):

 create table san_tab(id number(9),name varchar2(9));

VIEWS:

• DBA_EXTENTS
• USER_EXTENTS
• DBA_DATA_FILES
• V$DATAFILE
• V$TABLESPACE
• DBA_FREE_SPACE
• DBA_SEGMENTS
• USER_SEGMENTS
• DBA_TABLESPACES
• USER_TABLESPACES

ASSIGNMENT QUESTIONS:
1.increase the max extent.
2.find the 3rd extent size.
3.create LMT tablespace using  storage parameter(nologging).
4.how will you change the extent size,it is possible or not.

INTERVIEW  QUESTIONS:
1.find the num of extent in a table.
2.convert the dmt tablespace into lmt tablespace.
3.fine the tablespace where lmt or dmt.
4.diff between lmt and dmt.
5.how will u change the extent size.
6.when  show error like (unable to create extent)what u do?.
7.find block size and extent size in particular table or segments.