Brian Cryer

 

Cry about...


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.

Checkpoint
A checkpoint is when the DBWR writes all modified buffers in the SGA to the database files.

In Oracle 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 situations:

  • 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
CSI
CPU Support Identification Number. Normally required if you contact Oracle Support.
DBA
Database Administrator.
DBWR
(Oracle) Database Writer Process. When the buffer cache fills the DBWR selects buffers using the LRU algorithm and writes them to disk.
Link
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.
DLM
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 one.
Index
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
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.
LGWR
(Oracle) Log writer process. LGWR writes redo information from the log buffer to the redo log files. LGWR is also responsible for performing checkpoints.
Max extents (MAXEXTENTS)
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.
MTS
Multi-Threaded-Server.
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.
OPS
Oracle Parallel Server
PCM
Parallel Cache Management
PCTFREE
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.
PCTINCREASE
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.
PCTUSED
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.
PGA
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.
Schema
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 user.
SGA
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.
Synonym
A synonym is an alias for any table, view, snapshot, sequence, procedure, function, or package.
TNS
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 locally available.

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: 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.