Glossary of Oracle database terms
The following is not intended as an exhaustive list of Oracle terms.
It is restricted to the most common terms and those which I previously had
trouble finding definitions for.
- A checkpoint is when the DBWR writes all modified
buffers in the SGA to the database files.
8i a checkpoint occurs at every redo log switch and also at intervals
specified by the DBA.
My thanks to Jan-Pieter
Van Impe for providing information to extend this entry
From Oracle 9i onward a checkpoint occurs on any of the following
- alter system checkpoint
- shutdown of a database
or on a tablespace checkpoint (which only flushes dirty blocks for
that tablespace to disk), which occurs when:
- dropping a tablespace
- making a tablespace readonly
- putting a tablespace in hot-backup mode
- taking a tablespace offline
- CPU Support Identification Number. Normally required if you contact
- Database Administrator.
- (Oracle) Database Writer Process. When the buffer cache fills the
DBWR selects buffers using the LRU
algorithm and writes them to disk.
- A database link defines a one-way communication path from an Oracle
database to another database. When an application uses a database link
to access a remote database, Oracle establishes a database session in
the remote database on behalf of the local application request.
- Distributed Lock Manager
- Free list
- Every table has an associated list of blocks that are eligible for
more rows to be inserted into. As blocks are filled they are no longer
candidates for insertion and are removed from the free list. When the
free list is empty and more rows need to be inserted into a table a
new extent will be created.
- Tables typically only have one free list but can have more than
- An index provides Oracle with pointers to the rows in a table that
contain a given key value.
- Initial extent
- The size of the first extent allocated when the object (typically
a table) is created.
- Join operators compare two or more tables (or views) by specifying
a column from each, comparing the values in those columns row by row,
and concatenating rows in which the comparison is true.
- Join, Inner
- Join returning only those rows where a specified column in each
table has the same value.
- Join, Left (Outer)
- Specifies that all rows from the left table that did not meet the
condition specified are included in the results set, and output columns
from the other table are set to NULL.
- Join, Right (Outer)
- If a row from either table does not match the selection criteria,
specifies the row be included in the results set and its output columns
that correspond to the other table be set to NULL.
- (Oracle) Log writer process. LGWR writes redo information from the
log buffer to the redo log files. LGWR is also responsible for performing
- Max extents (
- The maximum number of extents that can every be allocated. This
will be set to a finite value or may be unlimited.
- Min extents
- The total number of extents to be allocated when the object (typically
a table) is created.
Oracle definition: MTS allows many user processes to share very few
server processes. Without MTS, each user process requires its own dedicated
server process; a new server process is created for each client requesting
a connection. A dedicated server process remains associated to the user
process for the remainder of the connection. With MTS many user processes
connect to a dispatcher process. The dispatcher routes client requests
to the next available shared server process. The advantage of MTS is
that system overhead is reduced, so the number of users that can be
supported is increased.
- Next extent
- The size of each subsequent extent to be allocated. The size specified
may remain constant for each new extent or may change according to to
the value of PCTINCREASE.
- Oracle Parallel Server
- Parallel Cache Management
- Percent Free. The PCTFREE setting controls the percentage of space
left unused in a block when rows are first inserted. This extra space
is then available for any row expansion required by subsequent updates.
- A block will be removed from the free list when its free space falls
below PCTFREE. It can therefore be regarded as a high-water mark for
use during inserts.
- Percent Increase. The percent by which each next extent (beginning
with the third) will grow. The size of each subsequent extent is equal
to the size of the previous extent plus this percent increase.
- Percent Used. The PCTUSED is the minimum percentage of each block
that should be in use holding actual data. If a block ever becomes less
than PCTUSED full then the block will be added to the free lists, making
it eligible for inserts.
- Percent free
- See PCTFREE.
- Percent increase
- See PCTINCREASE.
- Percent used.
- See PCTUSED.
- Program Global Area. The PGA is a memory region containing data
and control information for a single process (server or background).
One PGA is allocated for each server process; the PGA is exclusive to
that server process and is read and written only by Oracle code acting
on behalf of that process. A PGA is allocated by Oracle when a user
connects to an Oracle database and a session is created.
- The collection of database objects that are owned by a particular
user are referred to as that user’s schema. Every object in the database
belongs to one and only one schema, and therefore, to one and only one
- System Global Area. It is made up of the database buffer cache,
shared pool and redo log buffer. Basically this is any data and program
caches that are shared among database users.
- Standby database
- A standby database maintains a copy of the production database in
a permanent state of recovery. If the production database fails, the
standby database can be opened with a minimum amount of recovery necessary.
- A synonym is an alias for any table, view, snapshot, sequence, procedure,
function, or package.
- Transparent Network Substrate. Network interface technology that
allows applications (notably SQL*Net) to communicate across a network.
TNS maps requests through to the underlying networking technologies
This page represents one page of many pages of notes I made whilst working
with Oracle 7 through to 10i. I now no longer work with Oracle databases,
but hope that these notes will continue to be of benefit to others.
About the author: Brian Cryer
is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.