0 Storage Managment in oracle

A database can be defined as combination of  tablespaces. A tablespace is a logical storage  in oracle that comes at the top of the hierarchy and is made up of one or more data files.These files might be used  files in a file system, ASM-managed database files, or files on a clustered file system.


         A block is smallest part of space allocation in oracle.A block can be row of data, or temporary results will be stored.Block in oracle are of four different sizes(16K,8K,4K,2K).


      extent is defined as logically contiguous allocation of space in a file.An extent can also be called as combination of different blocks is know as extent

          Segments are organizational structure with a tablespace.

  • Space demanding objects in oracle are know as segments
  • one segment can only be created in one tablespace
  • one tablespace can support one or more segments
  • each segment is made up of one or more extents
  • each extent is made up of one or more contiguous oracle blocks(COB)
  • one segment can have its extend in one or more datafiles which belong to same tablespace
  • when all the given extents are filled up with data oracle on its own offers another extent dynimically this is called dynamic space allocation
  • DSA is good as the show continous
  • too much dsa could be bad as the time of selecting data oracle has to select data from all these different extents which are spread over across-performance goes down.

Storage Parameters

         We can size an extent,with the help of storage parameters.

Parameters are as follows
                     1.initial extent
                     2.next extent
                     3.min extent
                     4.max extent
                     5.ptc(%) increase
  • We can  define the storage parrameters at the time of segment creation
  • If we don't offer any storage parameters then oracle takes whatever the default storage parameters given by dba at tablespace level.Assuming neither table creator mentioned any storage perameter nor dba did at tslevel then oracle itself offers dsp-defaut storage parameters at tablespace level.
  • once the segment is create, we cannot chenge initial and minextent.
  • Next, Max extens, Ptc increase can be altered.

Extent Management.

  • Upto oracle 8 we only had dictionary managed.
  • In 8i oracle had introduced LMTS(locally managed tables space) as an option
  • From 9i LM became default but still DM is optionally available
  • The load has been reduced on DBA goin with LM
  • LM also improves performance as the pressure on system tablespace is reduced.
  • Incase of DM,all the extent details used to be maiantain in system tablespace.
  • LM stores extent details in respective data file right after the first block(first block is ment for scn)
  • LM offers two choices
                      UNIFORM-----All extents are of same type
                       AUTO---------The size of the extent becomes bigger based on the growth of the table dynamically.

Oracle DBA Tutorial Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates