Appendix B: Glossary


abstract datatypes

New datatypes, usually user-created, that are based on one or more built-in datatypes and can be treated as a unit.

aggregate

A type of function in Oracle SQL that performs a calculation or transformation across multiple rows in a table, rather than just on a single row.

alert log file

A text file that contains entries about significant database events, such as database startup and shutdown, nondefault initialization parameters, and various errors. The alert log file is stored in the directory specified by the system parameter BACKGROUND_DUMP_DEST.

alias

An alternate name for a column, specified right after the column name in a SELECT statement, seen in the results of the query.

associative table

A database table that stores the valid combinations of rows from two other tables and usually enforces a business rule. An associative table resolves a many-to-many relationship.

auditing

Storing information about activities in the database in the SYS.AUD$ table. Auditing is controlled by the DBA.

bitmap index

An index that maintains a binary string of ones and zeros for each distinct value of a column within the index.

branch blocks

Index blocks in the traversal path of a b-tree index that either point to branch blocks at the next level or point to leaf blocks.

b-tree index

A type of index structure that resembles an inverted tree. The branches of a b-tree index are balanced. Traversing the tree for any index value reads the same number of blocks.

buffer cache advisory

A feature of the Oracle9i database that can assist the DBA in determining how large to make the buffer cache. This feature collects statistics on how often a requested database block is found in the buffer cache. The system initialization parameter DB_CACHE_ADVICE controls whether these statistics are collected, and the data dictionary view V$DB_CACHE_ ADVICE contains the estimated number of physical reads that would occur given a number of different cache sizes.

cardinality

The number of distinct values in a column of a table.

Cartesian product

A join between two tables where no join condition is specified, and as a result, every row in the first table is joined with every row in the second table.

CHECK constraint

A constraint that evaluates the condition defined in the constraint and permits the INSERT or UPDATE of the row in the table if the condition is satisfied.

closed backup

See cold backup.

cold backup

A database backup performed while the database is shut down. Also known as a closed backup.

column

The component of a database table that contains all of the data of the same name and type across all rows.

comment

Documentation for SQL statements. Comments are specified by using the pair /* and */ or by using —.

composite index

An index that is created on two or more columns in a table.

concatenation

The process of combining two or more data elements into a single element. In Oracle SQL, concatenation can be accomplished by using the concatenation operator (a pair of vertical bars, ||) or the CONCAT function.

connection identifier

See host string.

constraint

A condition defined against a column or columns on a table in the database to enforce business rules or relationships between tables in the database.

control file

A file that records the physical structure of a database, the database name, and the names and locations of datafiles and redo log files.

correlated subquery

A subquery that contains a reference to a column in the main, or parent, query.

cost-based optimizer

An Oracle optimizer methodology that relies on the characteristics of the tables being queried to determine the method used to run the query. A cost is calculated for estimated CPU, I/O, and sorting for the possible execution paths. The path with the lowest overall cost is used to perform the query.

CTAS

Also known as Create Table As Select, a method for creating a table in the database by using the results from a subquery to both populate the data and specify the datatypes of the columns in the new table.

data dictionary views

Read-only views owned by the user SYS that are created when the database is created and contain information about users, security, and database structures, as well as other persistent information about the database.

data modeling

A process of defining the entities, attributes, and relationships between the entities in preparation for creating the physical database.

database

The collection of all physical files on disk that are associated with a single Oracle instance.

database block

The smallest unit of allocation in an Oracle database. One or more database blocks compose a database extent.

database buffer cache

The memory structure in the SGA that holds the most recently used or written blocks of data.

Database Configuration Assistant (DBCA)

A multiplatform GUI tool that allows a DBA to easily create, modify, and delete databases, as well as manage database templates.

datafiles

Files that contain all of the database data that the users of the database save and retrieve using SELECT and other DML statements. A tablespace comprises one or more datafiles.

date function

A function that performs some kind of transformation on a date literal, a column containing a date, or an expression consisting of date literals and table columns. Date functions return a date or a string containing a portion of the date as the result of the transformation.

DCL (Data Control Language)

Includes statements such as GRANT and REVOKE to provide or deny users or roles system or object privileges.

DDL (Data Definition Language)

Includes statements such as CREATE, ALTER, and DROP to work with objects such as tables. DDL modifies the structure of the objects in a database instead of the contents of the objects.

directory

A database object that stores a reference to a directory on the host operating system’s filesystem.

DML (Data Manipulation Language)

Includes INSERT, UPDATE, DELETE, and MERGE statements that operate specifically on database tables. Occasionally, SELECT statements are included in the SQL DML category.

DUAL

A special table, owned by the Oracle SYS user, that has one row and one column. It is useful for ad-hoc queries that don’t require rows from a specific table.

dynamic performance views

Data dictionary views owned by the user SYS that are continuously updated while a database is open and in use and whose contents relate primarily to performance. These views have the prefix V$ and their contents are lost when the database is shut down.

encapsulation

An object-oriented technique that may hide, or abstract, the inner workings of an object and expose only the relevant characteristics and operations on the object to other objects.

equijoin

A join between two tables where rows are returned if one or more columns in common between the two tables are equal and not NULL.

Explain Plan tool

A GUI-based Oracle tool that details the steps in which a SQL statement is executed, as well as what method Oracle used to access the tables in the query.

explicit conversion

Conversion of one datatype to another in an expression using function calls such as TO_CHAR instead of relying on automatic conversion rules (See implicit conversion).

Export utility (EXP)

An Oracle utility that copies the contents of one or more tables to a binary dump file, along with the DDL needed to create the table and its associated indexes, permissions, and constraints.

extent

A contiguous group of blocks allocated for use as part of a table, index, and so forth.

external table

A table whose definition is stored in the database but whose data is stored externally to the database.

field

The smallest piece of information that can be retrieved by the database query language. A field is found at the intersection of a row and a column in a database table.

flashback query

A feature of the Oracle database that allows a user to view the contents of a table as of a user-specified point in time in the past. How far in the past a flashback query can retrieve rows depends on the size of the undo tablespace and on the setting of the UNDO_RETENTION system parameter.

foreign key

A column (or columns) in a table that draws its values from a primary or unique key column in another table. A foreign key assists in ensuring the data integrity of a table.

FOREIGN KEY constraint

A constraint that establishes a parent-child relationship between two tables via one or more common columns. The foreign key in the child table refers to a primary or unique key in the parent table.

function

A named set of predefined programming language commands that performs a specific task given zero, one, or more arguments and returns a value.

function-based index

A b-tree index that is created based on an expression involving the columns of a table, instead of on a single column or columns in the table.

heading separator

A single character embedded in an iSQL*Plus column alias that indicates where the alias is split to appear on multiple lines in the output. The heading separator itself does not appear in the output.

hierarchical

A table design where one of the foreign keys in the table references the primary key of the same table in a parent-child relationship.

hint

A directive placed between /*+ and */ in a query that overrides an execution method that the Oracle optimizer would normally choose.

host string

A text string that represents a shortcut or reference to a set of parameters that provide the information needed to connect to a database host from the client application.

hot backup

A database backup performed while the database is open and available to users. Also known as an open backup.

implicit conversion

Conversion of one datatype to another that occurs automatically when columns or constants with dissimilar datatypes appear in an expression.

Import utility (IMP)

An Oracle utility that takes as input a binary dump file created by the Export utility and restores one or more database tables, along with any associated indexes, permissions, and constraints.

index

A database object designed to reduce the amount of time it takes to retrieve rows from a table. An index is created based on one or more columns in the table.

index-organized table (IOT)

A b-tree index that stores both the data and the index in the same segment.

inheritance

Acquiring the properties of the parent, or base object, in a new object.

inner join

See equijoin.

instance

The collection of memory structures and Oracle background processes that operates against an Oracle database.

intersection table

See associative table.

JDBC (Java Database Connectivity)

A set of library routines specific to the Java language that allows a Java application to easily connect to and process SQL statements against an Oracle database.

join

To combine two or more tables in a query to produce rows as a result of a comparison between columns in the tables.

leaf blocks

Index blocks at the bottom of a b-tree index that contain ROWIDs to the rows in the table containing the desired index value.

logical structures

Structures in an Oracle database that a database user would see, such as a table, as opposed to the underlying physical structures at the datafile level.

LRU (least recently used) algorithm

An algorithm used to determine when to reuse buffers in the database buffer cache that are not dirty or pinned. The less frequently a block is used, the more likely it is to be replaced with a new database block read from disk.

many-to-many relationship

A relationship type between tables in a relational database where one row of a given table may be related to many rows of another table, and vice versa. Many-to-many relationships are often resolved with an intermediate associative table.

materialized view

A view that stores the results of the query the view is based on, in addition to the SQL join statement of the view itself. Materialized views may be refreshed manually (on demand), on a regular basis, or when there is a change in the underlying tables on which that view is based.

media failure

A type of database failure where a server hardware component fails and the contents of one or more disk files are either unreadable or corrupted.

methods

Operations on an object that are exposed for use by other objects or applications.

multiple-column subquery

A subquery in which more than one column is selected for comparison to the main query using the same number of columns.

multiple-row subquery

A subquery that can return more than one row for comparison to the main, or parent, query using operators such as IN.

nonmedia failure

A type of database failure that is not related to a server disk-related hardware component and is one of several types: statement failure, process failure, instance failure, or user error.

NOT NULL constraint

A constraint that prevents NULL values from being entered into a column of a table.

NULL

A possible value for any Oracle column that indicates the absence of any known value for that column. A NULL is usually used to represent a value that is unknown, not applicable, or not available.

numeric function

A function that operates on numeric literals, columns containing numbers, or an expression containing numeric literals and table columns, returning a number as the result.

numeric literal

A constant that can consist of numeric digits, plus the characters +, -, ., and E.

object privileges

Privileges that allow users to manipulate the contents of database objects in other schemas.

object view

A database construct that overlays an object-oriented structure over an existing relational database table. As a result, the table can be accessed as a relational table or as an object table and make the transition to a fully object-oriented environment easier.

object-relational database

A relational database that includes additional operations and components to support object-oriented data structures and methods.

OCI (Oracle Call Interface)

A set of library routines that allows a C application on virtually any development platform to easily connect to and process SQL statements against an Oracle database. The OCI routines are called as native C library functions; therefore, no preprocessor is necessary when compiling a C application using OCI.

ODBC (Open Database Connectivity)

A set of standards that allow applications that are not dependent on any one specific database to process SQL statements against any database that supports SQL.

ODBC driver

An interface, usually at the operating-system level, that supports the connection of an ODBC-compliant application to a specific database platform.

one-to-many relationship

A relationship type between tables where one row in a given table is related to many other rows in a child table. The reverse condition, however, is not true. A given row in a child table is related to only one row in the parent table.

one-to-one relationship

A relationship type between tables where one row in a given table is related to only one or zero rows in a second table. This relationship type is often used for subtyping. For example, an EMPLOYEE table may hold the information common to all employees, while the FULLTIME, PARTTIME, and CONTRACTOR tables hold information unique to full time employees, part time employees and contractors respectively. These entities would be considered subtypes of an EMPLOYEE and maintain a one-to-one relationship with the EMPLOYEE table.

open backup

See hot backup.

Oracle block

See database block.

Oracle Enterprise Manager (OEM)

A GUI tool that allows access, maintenance, and monitoring of multiple databases or services within a single application.

Oracle Home

A common directory location used to store the associated program files for a specific release of the Oracle database software.

Oracle Universal Installer (OUI)

A GUI-based tool used to install or uninstall Oracle software components and tools.

Oracle’s Tuning Methodology

A tuning method recommended by Oracle Corporation that prioritizes areas in tuning database performance. The six areas, in order of priority, are data design, application design, memory allocation, I/O and physical structures, resource contention, and underlying platform.

outer join

A join between two or more tables returning all the rows in one table whether or not the second table contains a match on the join condition.

partitioned table

A table that stores its rows into smaller and more manageable pieces based on the values of one or more columns of the table.

pattern matching

Comparing a string in a database column to a string containing wildcard characters. These wildcard characters can represent zero, one, or more characters in the database column string.

PFILE

A text file containing the parameters and their values for configuring the database and instance at startup.

physical structures

Structures of an Oracle database, such as datafiles on disk, that are not directly manipulated by users of the database. Physical structures exist at the operating system level.

primary key

A column (or columns) in a table that makes the row in the table distinguishable from every other row in the same table.

PRIMARY KEY constraint

A constraint that uniquely defines each row of a table and prevents NULL values from being specified in the column or combination of columns. Only one PRIMARY KEY constraint may be defined on a table.

privileges

The right to perform a specific action in the database, granted by the DBA or other database users.

process

An executing computer program in memory that performs a specific task.

profile

A set of predefined resource parameters that can be used to monitor and control various database resources, such as CPU time and number of disk reads against the database.

Program Global Area (PGA)

A nonshared area of memory used for storing all connection information, including SQL statement information, in a dedicated server configuration for a user who is connected to the database. In a shared server configuration, a large portion of the memory for each connection is stored in the SGA instead of the PGA.

quota

A numeric limit on the amount of disk space that a user can allocate within a tablespace. The quota can also be specified as UNLIMITED.

read consistency

A feature of the Oracle database that ensures a database reader (in a SELECT statement) will see the same data in a table regardless of changes made to the table by database writers that were initiated after the reader initiated the SELECT statement.

Recovery Manager (RMAN)

A comprehensive set of backup and recovery tools that can streamline the backup and recovery of a database.

redo log buffer

A buffer in the SGA that contains information pertaining to changes in the database.

redo log files

Files that contain a record of all changes made to both the data in tables and indexes, as well as changes to the database structures themselves. These files are used to recover changed data that was in memory at the time of a crash.

referential integrity

A method employed by a relational database system that enforces one-to-many relationships between tables.

relation

A two-dimensional structure used to hold related information, also known as a table.

relational database

A collection of tables that stores data without any assumptions as to how the data is related within the tables or between the tables.

relational table

The most common form of a table in the Oracle database; the default type created with the CREATE TABLE statement. A relational table is permanent and can be partitioned.

reverse key index

A b-tree index whose keys have their byte-order reversed to improve the performance of an application by spreading out the key values for adjacent index values to different leaf blocks.

role

A group of related privileges that is referenced by a single name. Privileges can be assigned to a role, and a role can be assigned to a database user or to another role. Roles ease the maintenance issues with managing privileges for a large number of users who can be grouped into a relatively small number of categories based on job function.

row

A group of one or more data elements in a database table that describes a person, place, or thing.

ROWID

A unique identifier for a row in a table, maintained automatically in the table by the Oracle server. ROWIDs are unique throughout the database.

rule-based optimizer

An Oracle optimizer methodology that relies on a fixed set of rules to determine the method used to run a query, ignoring the cardinality and distribution of data in the column being queried.

schema

A group of related database objects assigned to a database user. A schema contains tables, views, indexes, sequences, and SQL code. The schema name can be used to qualify objects that are not owned by the user referencing the objects.

script

A set of one or more SQL or iSQL*Plus commands that is executed as a group. Scripts may be retrieved from within an iSQL*Plus session, or saved to an operating system file and retrieved later in another session.

segment

A set of extents allocated for a single type of object, such as a table.

self-join

A join of a table to itself where a non-primary key column in the table is related to the primary key column of another row in the same table.

sequence

A database structure that generates a series of numbers typically used to assign primary key values to database tables.

shared pool

An area of memory within the total amount of memory allocated for the Oracle database that can hold recently executed SQL statements, PL/SQL procedures and packages, as well as cached information from the system tables.

SID

A system identifier, which is a unique name assigned to an Oracle instance. A user must supply a SID to connect to an Oracle instance.

single-row function

Functions that may have zero, one, or more arguments, and will return one result for each row returned in a query.

single-row subquery

A subquery that returns a single row and is compared to a single value in the parent query.

Software Code Area

A location in memory where the Oracle application software resides. The Software Code Area can be shared among several Oracle instances.

SPFILE

A parameter file stored in a binary format that gives the DBA more flexibility when changing parameters. Parameters can be changed for the current instance only, can take effect only after the next restart of the instance, or both.

SQL (Structured Query Language)

The industry-standard database language used to query and manipulate the data, structures, and permissions in a relational database.

statistics

Information about tables and indexes stored in the data dictionary used to assist the cost-based optimizer when deciding how to run a given query.

stored function

A sequence of PL/SQL variable declarations and statements that can be called as a unit, passing zero or more arguments and returning a single value of a specified datatype. Built-in stored functions are created when the database software is installed. Customized or user-defined functions are defined by application developers or DBAs.

string function

A function that operates on string literals, columns containing strings, or an expression containing string literals and table columns, returning a string as the result.

string literal

A constant that can consist of any string of letters, digits, and special characters enclosed in single quotation marks.

subquery

A query that is embedded in a main, or parent, query and used to assist in filtering the result set from a query.

substitution variable

A string literal with no embedded spaces, preceded by & or &&, that will prompt the user for a value when an iSQL*Plus script containing one of these variables is executed. A substitution variable preceded by & will not prompt the user for a value if the same substitution variable, preceded by &&, exists earlier in the script.

synonym

An alias assigned to a table, view, or other database structure. Synonyms can be either available to all users (public) or available only to one schema owner (private).

System Global Area (SGA)

A group of shared memory structures for a single Oracle instance.

system privileges

Privileges that allow users to perform a specific action on one or more database objects or users in the database.

system trace file

A text file that pertains to a single background process and contains status, debugging, or error information about that background process. System trace files are stored in the directory specified by the system parameter BACKGROUND_DUMP_DEST.

system variable

A variable maintained in the iSQL*Plus, SQL*Plus, or SQL*Plus Worksheet environment that holds a status or a setting for a particular feature in that environment. PAGESIZE is an example of a system variable in iSQL*Plus.

table

The basic construct of a relational database that contains rows and columns of related data.

tablespace

A logical grouping of database objects, usually to facilitate security, performance, or the availability of database objects such as tables and indexes. A tablespace is composed of one or more datafiles on disk.

temporary table

A table whose definition is persistent and shared by all database users but whose data is local to the session that created the data. When the transaction or session is completed, the data is truncated from the temporary table.

thin client

A workstation or CPU with relatively low-powered components that can use a web interface (or other application with a small footprint) to connect to a middleware or a back-end database server where most of the processing occurs. iSQL*Plus is an example of a web application that runs on a thin client.

tiers

Locations where different components of an enterprise application system reside. In a typical three-tier environment, the client tier runs a thin application such as a web browser, which connects to a middleware server that is running a web server. The web server and its related components typically manage the business rules of the application. The third-tier database platform controls access to the data and manages the data itself. This approach partitions the application so that it is easier to maintain and segregates the tasks into tiers that are best equipped to handle a particular function.

TKPROF

An Oracle utility that reformats a user trace file containing SQL statement statistics into a readable format.

Top SQL tool

A GUI-based Oracle tool that can identify SQL statements that may be consuming too many system resources and therefore may be good candidates for tuning.

transaction

A logical unit of work consisting of one or more SQL statements that must all succeed or all fail to keep the database in a logically consistent state. A transfer of funds from one bank account is a logical transaction, in that both the withdrawal from one account and the deposit to another account must both succeed for the transaction to succeed.

transportable tablespace

A feature of Oracle’s Import and Export utilities that allows a tablespace to be copied to another database. All objects within the tablespace to be copied must be self-contained; in other words, a table in a tablespace to be copied must have its associated indexes in the same tablespace.

UNIQUE constraint

A constraint that prevents duplicate values from being specified in a column or combination of columns in a table. NULL values may be specified for columns that have a UNIQUE constraint defined, as long as the column itself does not have a NOT NULL constraint.

unique index

A b-tree index whose keys are not duplicated.

user trace file

A text file that contains information pertaining to any error conditions triggered by a command in an individual user’s session or SQL statement information for the purposes of tuning and optimization. User trace files are stored in the directory specified by the system parameter USER_DUMP_DEST.

user-defined function

A function that is written by an analyst, user, or database adminstrator and does not come as part of the default installation of the Oracle server software.

username

An Oracle database account identifier that, along with a password, allows a user to connect to the database.

view

A database object that is based on a SELECT statement against one or more tables or other views in the database. A regular view does not store any data in the database; only the definition is stored. Views are also known as stored queries.




Oracle9i DBA JumpStart
Oracle9i DBA JumpStart
ISBN: 0782141897
EAN: 2147483647
Year: 2006
Pages: 138
Authors: Bob Bryla

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net