Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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.

Comments

Popular posts from this blog

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools

7 Steps to configure BDR replication in postgresql