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.