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

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

Postgres Database Patch

PostgreSQL Database startup / shutdown /restart

ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated ORA-01262: Stat failed on a file destination directory Linux-x86_64 Error: 2: No such file or directory

PostgreSQL Pgbadger Installation On Linux