Features and Contents of Control File
Is a binary file that is necessary for the database to start and operate successfully.
Every time an instance mounts an Oracle database, it reads the control file to locate the data files and online redo log files.
Is updated continuously during database use and must be available whenever the database is mounted or opened.
Provides information about database consistency used during recovery.
If any of the control files currently being used by the database becomes unavailable , then the database cannot function properly.
In summary, control file contains the following information
--Database name
--Data file location
--Redo log file location
--Tablespace names
--Current log sequence number
--Checkpoint information
--Log history
--Backup information
////////////////////////////
Tablespaces
A tablespace can belong to only one database.
Each tablespace consists of one or more operating stystem files.
Tablespaces can be brought online while the database is running.
Except for the SYSTEM tablespace or a tablespace with an active rollback segment, tablespaces can be taken offline, leaving the database running.
Tablespaces can be swtiched between read-write and read-only status.
Controlling space allocation and assigning space quotas to users.
Controlling availability of data by taking individual tablespaces online or offline
Distributing data storage across devices to improve I/O performance and to reduce I/O contention agsinst a single disk.
Performing partial backup and partial recovery operations.
Keeping large amounts of static data on read-only devices.
///////////////////////////
Data Files
Each tablespace in a Oracle consists of one or more files called data files. These are physical structures that conform with the operating system on which the Oracle Server us running.
An Oracle server creates a data file for a tablespace by allocating the specified amount of disk space plus a small overhead.
The database administrator can change the size of a data file after its creation or can specify that a data file should dynamically grow as objects in the tablespace grow.
/////////////////////////////////////
Segment
A segment is the space allocated for a specific type of logical storage structure within a tablespace. The following are example of segments:
--Table segment
--Index segment
--Temporary segment
--Rollback segment
A segment such as a data segment may span multiple files that belong to the same tablespace.
/////////////////////////////////////
Extents
An extends is a set contiguous number of blocks.
Each type of segment is made up of one or more extents.
An extent may not span a data file, but muts exist in one data file.
/////////////////////////////////////////////////////
Data Blocks
At the finest level of granularity , the data in an Oracle database is stored in data blocks.
One data block corresponds to one or more physical file blocks allocated from an existing data file.
Data block size is specified for each Oracle database by the initialization parameter DB_BLOCK_SIZE when the database is created.
The samllest unit of input-output.
//////////////////////////////////////////////////////
SYSTEM and Non-SYSTEM Tablespaces
SYSTEM Tablespace
Automatic created after the database is created
Required in all databases for database operation.
Contains data dictionary information, definitions of stored procedures, packages, and database triggers.
Contains the SYSTEM rollback segment.
Should not contain use data although it is allowed.
Non-SYSTEM Tablespace
Enable more flexibility in database administration
Can store rollback segments,temporary segments, application data, and application indexes.
CREATE TABLESPACE app_data
DATAFILE '/DISK4/app01.dbf' SIZE 100M,
'/DISK5/app02.dbf' SIZE 100M
MINIMUM EXTENT 500K
DEFUALT STORAGE(INITIAL 500K
NEXT 500K
MINEXTENTS 3
MAXEXTENTS 500
PCTINCREASE 50);
Method 1: Adding Data Files to a Tablespace
ALTER TABLESPACE app_data ADD DATAFILE
'/DISK5/app03.dbf' SIZE 200M;
Method 2: Enabling Automatic Extension of new created Data Files
ALTER TABLESPACE app_data ADD DATAFILE
'/DISK6/app04.dbf' SIZE 200M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Method 3: Enabling Automatic Extension of existing Data Files
ALTER DATABASE
DATAFILE '/DISK5/app03.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
Method 4:Changing the Size of Data Files Manually
ALTER DATABASE DATAFILE '/DISK5/app02.dbf'
RESIZE 200M;
READ-ONLY tablespace
Making tablespaces read-only prevents further write operations on the data files. The purpose of read-only tablespaces is to ensure that on changes are made and to eliminate the need to perform backup and recovery of large, static portions of a database. The Oracle server never updates the files of a read-only tablespace, and therefore the files can reside on read-only media, such as CD ROMs or WORM drives.
Making tablespace APP_DATA only available for read operations.
ALTER TABLESPACE app_data READ ONLY;
DBA_TABLESPACES
/////////////////////////////////////////////////////
Storage Structure and Relationships
select a.segment_name,a.tablespace_name,a.extents,a.blocks
from dba_segments a
where owner='TPL'
select a.extent_id,a.file_id,a.block_id,a.blocks from dba_extents a where owner='TPL'
select tablespace_name ,count(*),max(blocks),sum(blocks)
from dba_free_space
group by tablespace_name
//////////////////////////////////////
Rollback Segment
Purpose of Rollback Segment
Transaction Rollback
When a transaction makes changes to a row in a table, the old image is saved in the rollback segment. If the transaction is rolled back, the value in the rollback segment is written back to the row, restoring the original value.
Read Consistency
When transactions are in progress, other users in the database should not see any uncommitted changes made by these transactions. In addition, a statement should not see any changes that were committed after the statement commences execution. The old values in the rollback segments are also used to provide the readers a consistent image for a given statement.
Transaction Recovery
If the instance fails when transactions are in progress, Oracle server needs to rollback the uncommitted changes when the database is opened again. This rollback is known as transaction recovery and is only possible if changes made to the rollback segment are also protected by the redo log files.
/////////////////////////////////////////
How transactions use Rollback Segment
1.When a transaction begins, a rollback segment needs to be assigned to this transaction.
2.A transaction may request a specific rollback segment using the following command:
SET TRANSACTION USE ROLLBACK SEGMENT rollback_segment
3.If no such request is made, the Oracle server chooses the rollback segment with the fewest transactions, and assigns it to the transaction.
4.Transactions use extends of a rollback segment in an ordered circular fashion, moving from one to the next after the current extent is full.
5.The pointer or the head of the rollback segment moves to the next extent when the current extet is full.
6.The OPTIMAL parameter specifies the size in bytes that a rollback segment must shrink to, if possible. Specifying OPTIMAL minimizes the waste of space in a rollback segment. If the OPTIMAL parameter is specified, a rollback segment can release space on completion of transactions that caused the growth.
create rollback segment rbs01
tablespace USERS
storage (initial 100k
next 100k
optimal 4M
minextents 20
maxextents 100);
alter rollback segment rsb01 online;
alter rollback segment rsb01 offline;
drop rollback segment rsb01;
select a.USN,a.EXTENTS,a.OPTSIZE,a.HWMSIZE,a.XACTS,a.STATUS from v$rollstat a
//////////////////////////////////////////////////
Temporary Segment
Temporary segment are used when statement such as the following are executed and the Oracle server cannot perform the sorting needed in memory because it is bigger that SORT_AREA_SIZE.
--SELECT ...ORDER BY
--CREATE INDEX
--SELECT DISTINCT
--SELECT...GROUP BY
The amount of memory used by a process for sorting is determined by the SORT_AREA_SIZE initialization parameter. If the sort volume exceeds this size, serveral sort runs are needed, and intermedidate results are stored on disk.
Temporary segments are created and used by the Oracle server in the tablespace that has been assigned to the user for sorting.
Temporary Segments in a Temporary Tablespace
Known as sort segments
Only one segment per tablespace per instance
Created when the first disk sort occurs in the instance after startup
Reused by serveral transactions based on information in the SORT Extent Pool
Release on instance shutdown.
select *from v$sort_segment
select * from v$sort_usage
///////////////////////////////////
Managing Indexes
B-Tree Index
////////////////////////////////////////////////////////////