Oracle Sql Fundamentals Glossary

Front 1

ACID

Back 1

Atomicity, consistency, isolation, and durability. Four characteristics that a relational database must be able to maintain for transactions.

Front 2

ADDM

Back 2

Automatic Database Diagnostic Monitor. A tool that generates performance tuning reports based on snapshots in the AWR

Front 3

AES

Back 3

Advanced Encryption Standard. A widely used data encryption method.

Front 4

AL16UTF16

Back 4

A unicode fixed-width 2-byte character set, commonly specified for the NLS character set for NVARCHAR2, NCHAT, and NCLOB data types.

Front 5

alias

Back 5

In Oracle Net, a pointer to a connect string. An alias must be resolved into the address of a listener and the name of a service or instance.

Front 6

ANSI

Back 6

American National Standards Institute. A U.S. body that defines a number of standards relevant to computing.

Front 7

API

Back 7

Application Programming Interface. A defined method for manipulating data, typically implemented as a set of PL/SQL procedures in a package.

Front 8

ASCII

Back 8

American Standard Code for Information Interchange. A standard (with many variations) for coding letters and other characters as bytes.

Front 9

ASM

Back 9

Automatic Storage Management. An LVM provided with the Oracle database.

Front 10

attribute

Back 10

one element of a tuple (aka a column)

Front 11

AVG

Back 11

A function that divides the sum of a column or expression by the number of nonnull rows in a group.

Front 12

Background process

Back 12

A process that is part of the instance: launched at startup.

Front 13

BFILE

Back 13

A large object data type that is stored as an operating system file. The value in the table column is a pointer to the file.

Front 14

Bind variable

Back 14

A value passed from a user process to a SQL statement at statement execution time.

Front 15

BLOB

Back 15

Binary Large Object. A LOB data type for binary data, such as photographs and video clips.

Front 16

block

Back 16

the units of storage into which data files are formatted. The size can be 2 KB, 4 KB, 8 KB, 16 KB, 32 KB, or 64 KB. Some platforms will not permit all these sizes.

Front 17

Cartesian product

Back 17

Sometimes called a cross join. A mathematical term that refers to the set of data created by merging the rows from two or more tables.

Front 18

CET

Back 18

Central European Time. A time zone used in much of Europe (though not Great Britain) that is one hour ahead of UTC with daylight saving time in effect during the summer months.

Front 19

Character set

Back 19

The encoding system for representing data within bytes. Different character sets can store different characters and may not be suitable for all languages. Unicode character sets can store any characters.

Front 20

Check constraints

Back 20

A simple rule enforced by the database that restricts the values that can be entered into a column.

Front 21

Client-server architecture

Back 21

A processing paradigm where the application is divided into client software that interacts with the user and server software that interacts with the data.

Front 22

CLOB

Back 22

Character Large Object. A LOB data type for character data, such as text documents, stored in the database character set.

Front 23

cluster

Back 23

A hardware environment where more than one computer share access to storage. A RAC database consists of several instances on several computers opening one database on the shared storage.

Front 24

cluster segment

Back 24

A segment that can contain one or more tables, denormalized into a single structure.

Front 25

COALESCE

Back 25

a function that returns the first nonnull value from its parameter list. If all its parameters are null, then a null value is returned.

Front 26

Column

Back 26

An element of a row; tables are two-dimensional structures, divided horizontally into rows and vertically into columns.

Front 27

Commit

Back 27

To make permanent a change to data.

Front 28

Complete Recovery

Back 28

Following a restore of damaged database files, this applies each redo to bring the database up to date with no loss of data.

Front 29

connect identifier

Back 29

An Oracle Net alias.

Front 30

Connect String

Back 30

The database connection details needed to establish a session: the address of the listener and the service or instance name.

Front 31

Consistent backup

Back 31

A backup made while the database is closed.

Front 32

Constraint

Back 32

A mechanism for enforcing rules on data: that a column value must be unique or may only contain certain values. A primary key constraint specifies that the column must be both unique and not null.

Front 33

Control file

Back 33

The file containing pointers to the rest of the databases, critical sequence information, and the RMAN repository.

Front 34

CPU

Back 34

Central Processing Unit. The chip that provides the processing capability of a computer, such as an Intel Pentium or a Sun SPARC.

Front 35

data blocks

Back 35

The units into which data files are formatted.

Front 36

Data dictionary

Back 36

The tables owned by SYS in the SYSTEM tablespace that define the database and the objects within it.

Front 37

Data dictionary views

Back 37

Views on the data dictionary tables that let the DBA investigate the state of the database.

Front 38

Data guard

Back 38

A facility whereby a copy of the production database is created and updated (possibly in real time) with all changes applied to the production database.

Front 39

Data pump

Back 39

A facility for transferring large amounts of data at high speed into, out of, or between databases.

Front 40

Database Buffer Cache

Back 40

An area of memory in the SGA used for working on blocks copied from data files.

Front 41

Database link

Back 41

A connection from one database to another, based on a username and password and a connect string.

Front 42

Data file

Back 42

The disk-based structure for storing data.

Front 43

DBA

Back 43

Database Administrator. The person responsible for creating and managing Oracle databases.

Front 44

DBA Role

Back 44

A preseeded role provided for backward compatibility that includes all the privileges needed to manage a database, except that needed to start up or shut down.

Front 45

DBCA

Back 45

The Database Configuration Assistant. A GUI tool for creating, modifying, and dropping instances and databases.

Front 46

DBMS

Back 46

Database Management System. Often used interchangeably with RDBMS

Front 47

DBWn or DBWR

Back 47

The Database Writer. The background process responsible for writing changed blocks from the database buffer cache to the data files. An instance may have up to 10 database writer processes, DBW0 through DBW9.

Front 48

DDL

Back 48

Data Definition Language. The subset of SQL commands that change object definitions within the data dictionary: CREATE, ALTER, DROP, and TRUNCATE.

Front 49

deadlock

Back 49

A situation where two sessions block each other, such that neither can do anything. Deadlocks are detected and resolved automatically by the database.

Front 50

DECODE

Back 50

A function that implements if-then-else conditional logic by testing two terms for equality and returning the third term if they are equal or, optionally, returning some other term if they are not.

Front 51

direct path

Back 51

A method of I/O on data files that bypasses the database buffer cache.

Front 52

directory object

Back 52

An oracle directory: an objects within the database that points to an operating system directory.

Front 53

DML

Back 53

Data Manipulation Language. The subset of SQL commands that change data within the database: INSERT, UPDATE, DELETE, and MERGE.

Front 54

DHCP

Back 54

Dynamic Host Configuration Protocol. The standard for configuring the network characteristics of a computer, such as its IP address, in a changing environment where computers may be moved from one location to another.

Front 55

DNS

Back 55

Domain Name Service. The TCP mechanism for resolving network names into IP addresses.

Front 56

domain

Back 56

The set of values an attribute is allowed to take. Terminology: tables have rows; rows have columns with values. Or: relations have tuples; tuples have attributes with values taken from their domain.

Front 57

DSS

Back 57

Decision Support System. A database, such as a data warehouse, optimized for running queries as against OLTP work.

Front 58

easy connect

Back 58

A method of establishing a session against a database by specifying the address on the listener and the service name without using an Oracle Net alias.

Front 59

EBCDIC

Back 59

Extended Binary Coded Decimal Interchange Code. A standard developed by IBM for coding letters and other characters in bytes.

Front 60

environment variable

Back 60

a variable set in the operating system shell which can be used by application software and by shell scripts.

Front 61

equijoin

Back 61

A join condition using an equality operator.

Front 62

Fact table

Back 62

The central table in a star schema, with columns for values relevant to the row and columns used as foreign keys to the dimension tables.

Front 63

FGA

Back 63

Fine Grained Auditing. A facility for tracking user access to data based on the rows that are seen or manipulated.

Front 64

full backup

Back 64

a backup containing all blocks of the files backed up, not only those blocks changed since the last backup.

Front 65

Grid computing

Back 65

An architecture where the delivery of a service to end users is not tied to certain server resources but can be provided from anywhere in a pool of resources.

Front 66

Group by

Back 66

A clause that specifies the grouping attribute rows must have in common for them to be clustered together.

Front 67

GUI

Back 67

Graphical user interface. A layer of an application that lets users work with the application through a graphical terminal, such as a PC with a mouse.

Front 68

HTTP

Back 68

Hypertext Transfer Protocol. THe protocol that enables the World Wide Web this is layered protocol that runs over TCP/IP.

Front 69

HWM

Back 69

High water mark. This is the last block of a segment that has ever been used-- blocks above this are part of the segment but are not yet formatted for use.

Front 70

I/O

Back 70

Input/output. The activity of reading from or writing to disks--often the slowest point of a data processing operation.

Front 71

IBM

Back 71

International Business Machines. A well known computer hardware, software, and services company.

Front 72

Inconsistent Backup

Back 72

A backup made while the database was open.

Front 73

INITCAP

Back 73

A function that accepts a string of characters and returns each word in title case.

Front 74

incremental backup

Back 74

A backup containing only blocks that have been changed since the last backup was made.

Front 75

Inner Join

Back 75

When equijoins and nonequijoins are performed, rows from the source and target tables are matched. These are referred to as inner joins.

Front 76

INSTR

Back 76

A function that returns the positional location of the nth occurrence of a specified string of characters in a source string.

Front 77

Instance recovery

Back 77

The automatic repair of damage caused by a disorderly shutdown of the database.

Front 78

IOT

Back 78

Index Organized Table. A table type where the rows are stored in the leaf blocks of an index segment.

Front 79

IP

Back 79

Internet Protocol. Together with the Transmission Control Protocol, TCP/IP: the de facto standard communication protocol used for client/server communication over a network.

Front 80

IPC

Back 80

Interprocess Communications Protocol. The platform-specific protocol, provided by your OS vendor, used for processes running on the same machine to communicate with each other.

Front 81

ISO

Back 81

International Organization for Standardization. A group that defines many standards, including SQL.

Front 82

J2EE

Back 82

Java 2 Enterprise Edition. The standard for developing Java applications.

Front 83

JOIN...On

Back 83

A clause that allows the explicit specification of join columns regardless of their column names. This provides a flexible joining format.

Front 84

JOIN....USING

Back 84

A syntax that allows a natural join to be formed on specific columns with shared names.

Front 85

joining

Back 85

Involves linking two or more tables based on common attributes. Joining allows data to be stored in third normal form in discrete tables, instead of in one large table.

Front 86

JVM

Back 86

Java Virtual Machine. The run-time environment needed for running code written in Java. Oracle provides a JVM within the database, and there will be one provided by your operating system.

Front 87

LAST_DAY

Back 87

A function used to obtain the last day in a month given any valid date item.

Front 88

LDAP

Back 88

Lightweight Directory Access Protocol. The TCP implementation of the X25 directory standard, used by the Oracle Internet Directory for name resolution, security, and authentication. LDAP is also used by other software vendors, including Microsoft and IBM

Front 89

LENGTH

Back 89

A function that computes that number of characters in a string including spaces and special characters.

Front 90

LGWR

Back 90

Log writer. The background process responsible for flushing change vectors from the log buffer in memory to the online redo logfiles on disk.

Front 91

library cache

Back 91

A memory structure within the shared pool, used for caching SQL statements parsed into their executable form.

Front 92

listener

Back 92

the server-side process that listens for database connection requests from user processes and launches server processes to establish sessions.

Front 93

LOB

Back 93

Large Object. A data structure that is too large to store within a table. LOBs are defined as columns of a table but are physically stored in a separate segment.

Front 94

log switch

Back 94

The action of closing one online logfile group and opening another; triggered by the LGWR process filling the first group.

Front 95

LVM

Back 95

Logical Volume Manager. A layer of software that abstracts the physical storage within your computer from the logical storage visible to an application.

Front 96

MMON

Back 96

Manageability Monitor. The background process responsible for gathering performance monitoring information and raising alerts.

Front 97

MOD

Back 97

The modulus operation, a function that returns the remainder of a division operation.

Front 98

MONTHS_BETWEEN

Back 98

A function that computes the number of months between two given date parameters and is based on a 31-day month.

Front 99

mounted database

Back 99

A situation where the instance has opened the database control file but not the online redo logfiles or data files.

Front 100

MTBF

Back 100

Mean time between failure. A measure of the average length of running time for a database between unplanned shutdowns.

Front 101

MTTR

Back 101

Mean time to recover. The average time it takes to make the database available for normal user after a failure.

Front 102

multiplexing

Back 102

to maintain multiple copies of files.

Front 103

namespace

Back 103

A logical grouping of objects within which no two objects may have the same name.

Front 104

Natural join

Back 104

A join performed using the NATURAL JOIN syntax when the source and target tables are implicitly equijoined using all identically named columns.

Front 105

NCLOB

Back 105

National Character Large Object. A LOB data type for character data, such as text documents, stored in the alternative national database character set.

Front 106

NETBEUI

Back 106

NETBIOS Extended User Interface. An enhanced version of NETBIOS.

Front 107

NETBIOS

Back 107

Network Basic Input Output System. The network communications protocol that was burnt onto the first network card that IBM ever produced.

Front 108

NLS

Back 108

National Language Support. The capability of the Oracle database to support many linguistic, geographical, and cultural environments--now usually referred to as globalization.

Front 109

node

Back 109

A computer attached to a network.

Front 110

Nonequijoin

Back 110

Performed when the values in the join columns fulfill the join condition based on an inequality expression.

Front 111

null

Back 111

The absence of a value, indicating that the value is not known, missing, or inapplicable.

Front 112

NULLIF

Back 112

A function that tests two terms for equality. If they are equal, the function returns null; else it returns the first of the two terms tested.

Front 113

NVL

Back 113

A function that returns either the original item unchanged or an alternative item if the initial term is full.

Front 114

NVL2

Back 114

A function that returns a new if-null item if the original item is null or an alternative if-not-null item if the original term is not null.

Front 115

OC4J

Back 115

Oracle Containers for J2EE. The control structure provided by the Oracle Application Server for running Java programs.

Front 116

OCI

Back 116

Oracle Call Interface. An API, published as a set of C libraries, that programmers can use to write user processes that will use an Oracle database.

Front 117

ODBC

Back 117

Open Database Connectivity. A standard developed by Microsoft for communicating with relational databases. Oracle provides an ODBC driver that will allow clients running Microsoft products to connect to an Oracle database.

Front 118

offline backup

Back 118

a backup made while the database is closed.

Front 119

OLAP

Back 119

Online Analytical Processing. Select intensive work involving running queries against a (usually) large database. Oracle provides OLAP capabilities as an option, in addition to the standard query facilities.

Front 120

OLTP

Back 120

Online Transaction Processing. A pattern of activity within a database typified by a large number of small, short, transactions.

Front 121

online backup

Back 121

a backup made while the database is open.

Front 122

online redo log

Back 122

the files to which change vectors are streamed by the LGWR.

Front 123

OS

Back 123

Operating system. Typically, in the Oracle environment, this will be a version of Unix (perhaps Linux) or Microsoft Windows

Front 124

Oracle Net

Back 124

Oracle's proprietary communications protocol, layered on top of an industry standard protocol.

Front 125

ORACLE_BASE

Back 125

The root directory into which Oracle products are installed.

Front 126

ORACLE_HOME

Back 126

The root directory of any one Oracle product.

Front 127

outer join

Back 127

a join performed when rows, which are not retrieved by an inner join, are included for retrieval.

Front 128

parse

Back 128

An action that converts SQL statements into a form suitable for execution.

Front 129

PGA

Back 129

Program Global Area. The variable sized block of memory used to maintain the state of a database session. PGAs are private to the session and controlled by the session's server process.

Front 130

PL/SQL

Back 130

Procedural Language/ Structured Query Language. Oracle's proprietary programming language, which combines procedural constructs, such as flow control, and user interface capabilities with SQL.

Front 131

PMON

Back 131

Process Monitor. The background process responsible for monitoring the state of user's sessions against an instance.

Front 132

Primary key

Back 132

The column (or combination of columns) whose value(s) can be used to identify each row in a table.

Front 133

Projection

Back 133

The restriction of columns selected from a table. Using projection, you retrieve only the columns of interest and not every possible column.

Front 134

RAC

Back 134

Real Application Clusters. Oracle's clustering technology, which allows several instances in different machines to open the same database for scalability, performance, and fault tolerance.

Front 135

RAID

Back 135

Redundant Array of Inexpensive Disks. Techniques for enhancing performance and/or fault tolerance by using a volume manager to present a number of physical disks to the operating system as a single logical disk.

Front 136

raw device

Back 136

An unformatted disk or disk partition

Front 137

RDBMS

Back 137

Relational Database Management System. Often used interchangeably with DBMS

Front 138

RAM

Back 138

Random Access Memory. The chips that make up the real memory in your computer hardware, as opposed to the virtual memory presented to software by the operating system.

Front 139

Referential Integrity

Back 139

A rule defined on the table specifying that the values in a column (or columns) must map onto those of a row in another table.

Front 140

Relation

Back 140

A two-dimensional structure consisting of tuples with attributes (aka a table).

Front 141

REPLACE

Back 141

A function that substitutes each occurrence of a search item in the source string with a replacement term and returns the modified source string.

Front 142

RMAN

Back 142

Recovery Manager. Oracle's backup and recovery tool.

Front 143

rowid

Back 143

The unique identifier of every row in the database, used as a pointer to the physical location of the row.

Front 144

schema

Back 144

The objects owned by a database user.

Front 145

SCN

Back 145

System Change Number. The continually incrementing number used to track the sequence and exact time of all events within a database.

Front 146

Segment

Back 146

A database object, within a schema, that stores data.

Front 147

Selection

Back 147

The extraction of rows from a table. Selection includes the further restriction of the extracted rows based on various criteria or conditions. This allows you to retrieve only the rows that are of interest and not every row in the table.

Front 148

Self-join

Back 148

A join required when the join columns originate from the same table. Conceptually, the source table is duplicated and a target table is created. The self-join then works as regular join between two discrete tables.

Front 149

Sequence

Back 149

A database object, within a schema, that can generate consecutive numbers.

Front 150

Service name

Back 150

A logical name registered by an instance with a listener, which can be specified by a user process when it issues a connect request.

Front 151

Session

Back 151

A user process and a server process, connected to the instance.

Front 152

SGA

Back 152

System Global Area. The block of shared memory that contains the memory structures that make up an oracle instance.

Front 153

SID

Back 153

(1) System Identifier. The name of an instance, which must be unique on the computer the instance is running on. (2) Session identifier. The number used to identify uniquely a session logged on to an Oracle instance.

Front 154

SMON

Back 154

System Monitor. The background process responsible for opening a database and monitoring the instance.

Front 155

spfile

Back 155

Server parameter file. The file containing the parameters used to build an instance in memory.

Front 156

SQL

Back 156

Structured Query Language. An international standard language for extracting data from and manipulating data in relational databases.

Front 157

SSL

Back 157

Secure Sockets Layer. A standard for securing data transmission, using encryption, checksumming, and digital certificates.

Front 158

SUBSTR

Back 158

A function that extracts and returns a segment from a given source string.

Front 159

SUM

Back 159

A function that returns an aggregated total of all the nonnull numeric expression values in a group.

Front 160

Synonym

Back 160

An alternative name for a database object

Front 161

sysdba

Back 161

The privilege that lets a user connect with operating system or password file authentication and create, start up, and shut down a database.

Front 162

sysope

Back 162

The privilege that lets a user connect with operating system or password file authentication and start up and shut down (but not create) a database.

Front 163

System

Back 163

A preseeded schema used for database administration purposes.

Front 164

table

Back 164

A logical two-dimensional data storage structure, consisting of rows and columns.

Front 165

tablespace

Back 165

The logical structure that abstracts logical data storage in tables from physical data storage in data files.

Front 166

TCP

Back 166

Transmission Control Protocol. Together with the Internet Protocol, TCP/IP: the de facto standard communication protocol used for client/server communication over a network.

Front 167

TCPS

Back 167

TCP with SSL. The secure sockets version of TCP.

Front 168

tempfile

Back 168

The physical storage that makes up a temporary tablespace, used for storing temporary segments.

Front 169

TNS

Back 169

Transparent Network Substrate. The heart of Oracle Net, a proprietary layered protocol running on top of whatever underlying network transport protocol you choose to use--probably TCP/IP.

Front 170

TO_CHAR

Back 170

A function that performs date-to-character and number-to-character data type conversions.

Front 171

TO_DATE

Back 171

A function that explicitly transforms character items into date values.

Front 172

TO_NUMBER

Back 172

A function that changes character items into number values.

Front 173

transaction

Back 173

a logical unit of work that will complete in total or not at all.

Front 174

tuple

Back 174

A one-dimensional structure consisting of attributes (aka a row)

Front 175

UGA

Back 175

User Global Area. That part of the PGA that is stored in the SGA for sessions running through shared servers.

Front 176

UI

Back 176

User interface. The layer of an application that communicates with end users--nowadays, frequently graphical: a GUI

Front 177

URL

Back 177

Uniform Resource Locator. A standard for specifying the location of an object on the Internet consisting of a protocol, a host name and domain, an IP port number, a path and filename, and a series of parameters.

Front 178

UTC

Back 178

Coordinated Universal Time. Global standard time zone, all others relate to it as offsets, ahead or behind.

Front 179

X-Windows

Back 179

The standard GUI environment used on most computers, except those that run Microsoft Windows.

Front 180

XML

Back 180

Extensible Markup Language. A standard for data interchange using documents, where the format of the data is defined by tags within the document.