Thursday, August 24, 2006

Segments & Blocks

Types of Segments:
  1. Table

  2. Table Partition : for partitioned table, each partition a segment & storage parameters can be specified to control them independently.

  3. Cluster : contains one of more tables which share same storage characteristics.

  4. Index : one index segment for each index

  5. Index-organised table

  6. Index partition

  7. Undo segment

  8. Temporary segment

  9. LOB segment

  10. Nested table : The nested table is stored in a separate segment

  11. Bootstrap Segment: helps to initialize data dictionary cache when the database is opened by an instance.

Storage parameters:


2:

Storage clause specified at segment level except for MINIMUM EXTENT & UNIFORM SIZE tablespace parameters.


1:

If not specified at segment level, default storage clause specified at tablespace level used


0:

If above two not specified than Oracle Server defaults are used

Note : Any alteration of staorage clause applies only to the extents not yet allocated

Database Blocks:


DB_2K_CACHE_SIZE

DB_8K_CACHE_SIZE

DB_32K_CACHE_SIZE


DB_4K_CACHE_SIZE

DB_16K_CACHE_SIZE


Creating tablespaces with non-standard block size :


CREATE TABLESPACE sktab
DATAFILE 'sk01.dbf'
SIZE 10M BLOCKSIZE 4K;

          • View : dba_tablespaces

Block space Utilization paramenters:

    Parameters



Default values
  • INITRANS
  • MAXTRANS
  • PCTFREE
  • PCTUSED
:
:
:
:
1 for data segment; 2 for index segment. Value range : 1-255.
Default value is a function of the data block size. Value range : 1-255.
10%
40%. A block is put on free list when its used space falls below PCTUSED.
  • FREELIST
: A freelist of a segment is a list of blocks that are candidates for future inserts. A segment by default is created with one free list, but it can be configured to be created with a higher number of free lists by setting the FREELIST parameter.

Segment space management (Automatic):


Tracking in-segment free space using bitmaps instead of FREELISTs. This bitmap is stored in a separate set of blocks referred to as bitmapped blocks (BMBs). It describes the status of each block in the segment with respect to its availability for space.

Advantages :

  • Ease of management : PCTUSED, FREELISTS, FREELIST GROUPS managed automatically.

  • Better space utlization : All objects, especially those with varying row sizes, utilize space more effeciently.

  • better performance for concurrent inserts.

Restrictions :

  • Can't be used for tablespaces with LOBs.

Configuring :

CREATE TABLESPACE sktab
DATAFILE 'D:/Oracle/Ora90/sktab01.dbf'
SIZE 10M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K
SEGMENT SPACE MANAGEMENT AUTO;

Notes :

  • Once bitmap segments are specified at tablespace creation, it can't subsequently be altered. Any specification of PCTUSED, FREELISTS, FREELIST GROUPS is ignored.

  • Segments that can be bitmap managed are regular tables, indexes, Index organised tables and LOBs.

Segment space management (Manual) : This is the default method. It was the only method available previous versions of Oracle. Allows configuration of data blocks using PCTUSED, FREELISTS, FREELIST GROUPS.

Views : See examples


DBA_EXTENTS

DBA_SEGMENTS

DBA_TABLESPACES

DBA_DATA_FILES

DBA_FREE_SPAC


Comments: Post a Comment

Links to this post:

Create a Link



<< Home
Advertisements

eBay.in - The World's Online Marketplace

ARCHIVES
  • March 2006
  • April 2006
  • May 2006
  • June 2006
  • July 2006
  • August 2006
  • September 2006

This page is powered by Blogger. Isn't yours?

Recent Postings

Google