CONTROL FILE MANAGEMENT
DATA BLOCK STRUCTURE
Block header Table directory
Row directory Free space
Row data Or Used space
Block header: It contains general block info such as the block address and the type of segment (table or indexes)
Table directory:- This portion of datafiles contain information about the tables having rows in the blocks.
Row directory:- This portion of data block contains info about the actual rows in block (including address of each row piece in the row data area). After the apce has been allocated in the row chaining of a data block overhead this space is not reclaimed when the row is deleted therefore, a database that is currently empty but had up to 50 rows at one time continous to have 100 bytes allocated in the header of row directory oracle databases reuses this space only when new rows are inserted in the blocks.
Overhead: The data blocks header table directory and row directory are referred to collectively as overhead some block overhead is fixed in size. The total block overhead size is variable. On average the fixed and variable portions of data block overhead total 84 to 107 bytes.
Rowdata: This portion of data block contains table or index data. Row can space blocks.
FreeSpace: Free space is allocated for insertion of new rows and updates to rows that require addition.
Pctused: This parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to that block before new row are added to that block, after a block is filled to the limit determine by pctfree, oracle database consider the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter pctused until the value is achieved, oracle database uses the free spaces of the data block only for updates to rows already contains in the data block.
For example:- pctused 40
In this case a data block used for this tables data segments is considered unavailable tables data segments is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 89% or less.
Init trans: This parameter specifies how many transactions can be accessed to the dbblock at any point of particular time
Freelists : this parameter is used in rac:
Tip: once the primary block size is mentioned you can create new tablespace with alternate block size for creating table with parameters.
Syntax:- create table abc(a number)
Pctfree 10 , pctused 30, initrans 10;
To obtain object level parameters
information:-Select table_name,pct_free,pct_used,init_trans,max_trans from dba_tables where table_name=’abc’;
By default :- pctfree-10,pctused-40,initrans-1,max_trans 255
If we specify parameters pctfree - 10, pctused -30, inittrans-30, max_trans -255
Extent Management
An extent is an uninterrupted or contiguous allocation of blocks within a segment.
Extents are assigned to a segment automatically by oracle. Oracle will allocate anything in the form of extents.
An extent must be an contiguous blocks within a single datafile, so an extent cannot span multiple datafiles. Oracle will allocate the size of extents based on type of tablespace.
When we create a table, oracle database allocates a segment and a initial extent of a specified number of data blocks. The size of extent is determined by storage parameter.
These parameters are also called as object level parameter Storage Parameters:
• INITIAL
• NEXT
• MINEXTENTS
• MAXEXTENTS
• PCTINCREASE
INITIAL: The parameter specifies the first extent and the size of extent
NEXT: If the datablocks of a segment initial extent became full and more space is required to hold new data oracle database automatically allocates an incremental extent
for that segment. The size of incremental extent is same or greater than the previously allocated extent i.e, we specify the extent after initial extent through this parameter.
MINEXTENT: This parameter specifies the total number of extents to be allocates when we create a (table (segement) or index)
MAXEXTENT: This parameter specifies the up to how many extents a segment can hold.
PCTINCREASE: This parameter specifies the incremental percentage of extent that is to be created after the NEXT EXTENT. By default its value is 50%.
For example consider a)
Initial 1m Next 1m Minextents 1 Maxextenst 4 Pctincrease 50%
1m 1m 1.5M 2.25M 3.375M
b)
Initial 1m Next 1m Minextents 2 Maxextenst 5 Pctincrease 20%
1m 1m 1m 1.2m 1.5m
If we don’t specify storage parameters for a extent. Oracle itself allocates the default storage parameters.
Segment is creation of extents.
Segment name is nothing but as object name. when we create a table or index it creates segment.
By default each extent contains max of 5 blocks and min of 2 blocks.
Create a tablespace and segment find the storage parameters without specifying them?
Sql> create tablespace tbs datafile “ “ 10m;
Sql> alter user kittu identified by kittu;
Sql> conn kittu/kittu
Sql> create table ram(a munber);
We can archive the extents information from dba_extents and dba_segments.
Sql> select initial_extent, next_extent, max_extent, min_extent, pct_increase, blocks, bytes from dba_segments where
segment_name=’ram’;
Sql> save sess.sql
All the parameters, blocks and their size for extents are allocated as per operating system.
Create a table in the tablespace with some parameters and check the parameters?
Sql> create table chinni(a number)
Storage (initial 1m nect 1m minextents 1 maxextents 5 pctincrease 100%);
Sql> @sess.sql
Now also maxextents will be taken as o/s specific When database having db_block_size as 8k If we define extent size as 1m
Then each extent holds 128 blocks
Q) How can we determine table or index size from dba_extenst or dba_segments?
Select segment_name,bytes from dba_extents where segment_name=’RAM’’;
OR
Select segment_name,bytes from dba_segments where segment_name=’RAM’;
Extent Management
A tablespace is a logical storage unit.
Why are we saying a tablespace is not visible in the file system. oracle store data physically in datafile.
How to create tablespace?
Create tablespace ts_name Datafile ‘………….’ Size 2m
Minimum extents (this ensures that every used extent size in the tbs is a multiple of integer)
Blocksize
Logging: By default tbs have all changes written redo No logging: Tbs do not have changes written redo Online: Tablespace is online i.e available
Offline: Tablespace unavailable immediately after creation Permanent: Tablespace can be used to hold permanent object.
Temporary: Tablespace can hold temp data Extent Management is of two types
a) Dictionary Extent Management b)Locally Extent management
The tablespace are maintained in dictionary extent management is dictionary managed tablespace.
The tablespace which are maintained in local extent management is called locally managed tablespace.
Locally managed tablespace: The extents are managed with in tablespace in locally managed tablespaces all the tablespace information and extent information is stored in datafile header of that tablespace and don’t use data dictionary table for storing information.
Advantage of LMTS is that no DML generate and reduce contention on data dictionary tables and no undo generated when space allocation or deallocation occurs.
The storage parameters NEXT, PCTINCRESE, MINEXTENTS, MAXEXTENTS, and default STORAGE are not valid for segments stored in LMTS.
To create a locally managed tablespace,you specify local in extent management clause of create tablespace statement.
We have 2 options for lmts: -1) system or auto allocate 2) uniform
q) how to create lmts
create tablespace tbs datafile ‘star.dbf’ size 10m extent management local;
SYSTEM (or) AUTOALLOCATE: Autoallocate specifies that extent size are system managed oracle will choose “optimal” next extent sizes starting with 64kb as the segment grown larger extent size will increase to 1mn,8mb and eventually to 64mb .This is recommended only for a low or unmanaged environment.
Default autoallocate i.e it takes database default storage.
Parameter
Syntax:- create tablespace tbs
datafile ‘star.dbf’ size 10m
extent management local autoallocate;
UNIFORM:
It specifies that the tbs is managed with uniform extents of size bytes. The default size is 1m . The uniform extent size of lmts cannot be over written when a scheme object such as table or index created
Syntax: - create tablespace tbs
Datafile ‘/oraapps/star.dbf’ size 10m Extent management local uniform size 128k;
Dictionary Managed Tablespace:
When we are declaring tablespace as dictionary managed tablespace, the data dictionary manages the extents. The oracle server updates the appropriate tables(sys.fet$
and sys.uet$) in the data dictionary whatever an extent is allocated or deallocated.
Syntax: - create tablespace tbs
Datafile ‘har1.dbf’ size 10m Extent management dictionary
Default storage(initial 1m next 1m minextent 2 maxextents 121 pctincrease 0)
We can alter all parameters except initial and minextents in dmts i.e if we create dmts then extent info is stored in dictionary and real data is stored in datafile of that tablespace. In that case we need more I/O i.e the oracle has to search for extents in dictionary. which degrades the performance.
In oracle 8i > only dmt available From 9i> both dmt and lmt (default) From 10g> both dmt and lmt (default) SEGEMENT
Segments are the storage objects within the oracle database. A segment might be table, an index, a cluster etc.
The level of logical database storage above an extent is called segment.
A segment is a set of extents that contains all the data for a specific logical storage structure within a tablespace.
For example for each table oracle database allocates one or more extents to form that tables data segment and for each index, oracle database allocates one or more extents to form its index segment
There are 11types of segments in oracle
• table
• table partition
• index
• index partition
• rollback
• deferred rollback
• lobindex
• temporary
• cache
• permanent
These types can be grouped into four segment
• data segment
• index segment
• rollback segment
• temporary data segment
Data Segments:
A single data segment in a oracle database holds all of the data for one of the follow..
• A table that is partitioned or clustered
• A partition of partitioned table
• A cluster of table.
Oracle database creates the data segment when you create the table or cluster with create statement.
The storage parameters for a table cluster determine how its segments extents are allocated you can set there storage parameters directly with appropriate create or alter the efficiency of data retrieval and storage for data segment associated with the object.
Index Segment:
Oracle database creates the index segment for an index or an index partition when you issue the create index statement. In this statement we can specify storage parameters for creation of index.
The segments of table and index allocated with it do not have to occupy the same tablespace setting the storage parameters directly affect the efficiency of data retrieval and storage.
Temporary segments: When processing queries oracle database often requires temporary workspace for intermediate stages of sql statement parsing and execution oracle database automatically allocates this disk space called a temporary segment. Typically oracle database requires a temporary segment as a database area for sorting.
Undo segments: Oracle database maintains information to reverse changes made to the database. This information consists of search of the action of transactions, collectively known as Undo .undo is stored in undo segments in an undo tablespace.
How extents are allocated:
Oracle database uses different algorithms to allocate extents, depending on whether they are locally managed or dictionary managed.
With LMTS, oracle database looks for free space to allocate to a new extent by first determing a candidate datafile in a tbs and the search the datafiles bitmap for the required number.
When extents are allocated:
In general the extents of a segment do not return to the tablespace until you drop the schema object where data is stored in the segement.
A dba can deallocate the unused extent using the following sql Syntax: Alter table table_name deallocate unused ;
Periodically, oracle database modifies the bitmap of the datafile(for lmts) or update the data dictionary (for dmts) to reflect the regained extents as available space An data in the blocks of freed extents becomes inaccessible.
Periodically oracle database deallocates one or more extents of a rollback segment if it has optimal size specified.
If the rollback segment in larger than optimal (i.e it has too many extents) the oracle database automatically deallocates one or more extents from rollback segment.
How temporary segments are allocated?
Oracle database allocates temporary segments differently for queries and temporary tables.