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$.
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 will happen
manual coalesce
more undo generated
|
free list maintain in data file header as 0’s & 1’s
less I/O process will happen
auto coalesce
Less undo generated
|
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.
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.
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
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 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
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.
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.
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;
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
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.
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.
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.
postgres 
