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:
EXTENT MANAGEMENT:
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):
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):
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.
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
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
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:
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
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
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.
Comments
Post a Comment