Wednesday, January 25, 2012

sap Tablespace administration

Tablespace administration

Table space: Table spaces are logical storage objects, contains the database data and indexes

Indexes: Indexes are objects associated with tables that are used to speed up the search and execution of SQL
              statements, creating or deleting indexes does not affect the associated table.

Segments: Collection of extents, we have some common segments in the database
            Index segments:            Contains index data, and the 1st block of 1st extent contains the segment header, it contains
The   free list information.
            Rollback Segments :     stores the value of particular data block before it was modified
            Temporary Segments:   Stored in PSAPTEMP tablespace.this is uses temporarily for doing sort operation or
creating indexes. These segments are increase performance when dealing with large
tables or indexes, so we need assign this space a size twice as big as the size of the
biggest index.

Extent: Collection of oracle blocksWe have some Storage parameters for Extent
INITIAL (First extent):     It’s the size of the first extents, it should be large enough. we need to set “compress
extents = NO”.
            NEXT (New Extent):       The size of the new extent.this value can be caluculated automatically by TGORA ,IGORA
 tables and suggested next extent sizes for the data base objects.the number of extents
reaching the lmit,we can change the NEXT parameter value to large size ,”max_next_sze” 
= max 2GB in init<SID>.dba file.
            MAXEXTENT:               The maxmum number of allowed extents for the segment.It’s generally set to 100 for oracle
            MINEXTENT:               It’s the starting numberof the extents when the object is created. It’s normally always set to 1.
Block:   It’s the smallest physical storage unit in the data base. Tables and indexes are stored in individual data blocks. The
 size is defined in parameter ”db_block_size” in init<SID>.ora file, default 8k



I have read your blog its very attractive and impressive. I like it your blog.

Post a Comment