Course Reference Material
Oracle9iT Database Administration:
Manage Storage Structures
NETg course 64313
Copyright © 2002 National Education Training Group, Inc.
All rights reserved. No part of the material protected by this copyright may be reproduced or utilized in any form or by any means, electronic or mechanical, including photocopying, recording, broadcasting, or by any information storage and retrieval system, without permission in writing from National Education Training Group, Inc.
Skill Builder is a registered trademark of National Education Training Group, Inc. All other trademarks referenced are the trademark, service mark, or registered trademark of their respective holders. National Education Training Group, Inc. is not affiliated with any company or any other product or vendor mentioned in this course and its ac 22322v214w companying materials.
The software and technology used to implement this course contain trade secrets that NETg considers to be confidential and proprietary information, and your right to use this material is subject to the restrictions in the license agreement under which you obtained it.
Contents
Objectives
Storage Structures
Undo Space Management
Managing Temporary Segments
Managing Indexes
Glossary
Alphabetical List of Terms
Total number of objectives = 51
Note: (ULT) is the unit, lesson, topic ID.
Segments
Database Storage Hierarchy (ULT 112): Identify the correct order of the components in the storage hierarchy.
Segments: Types (ULT 113): Match the segment types with their features.
Extent Management
Extent Allocation Control (ULT 121): Identify the factors that determine the order of precedence for controlling extent allocation.
Free Space: Coalescing (ULT 122): Coalesce free space in a given tablespace by using the Oracle SQL*Plus Worksheet.
Block Space
Data Block Contents (ULT 131): Match the data block contents with their features.
Block Space Utilization Parameters (ULT 132): Match the block space utilization parameters with their uses.
Automatic Segment Free Space Management
Bitmap: Features (ULT 141): Select the features of a BMB.
Auto-Management of Free Space: Benefits (ULT 142): Identify the benefits of auto-management of free space.
Views: Modifications (ULT 143): Match the columns that have been modified to support auto-management of segment free space with the existing views that contain them.
Storage Structure Information
Segment Information (ULT 151): Retrieve the information to determine the number of extents and blocks allocated to a segment by using the Oracle SQL*Plus Worksheet.
Used Extent Information (ULT 152): Retrieve the physical location information for a given segment by using the Oracle SQL*Plus Worksheet.
Free Space Information (ULT 153): Retrieve the free space information for a given tablespace by using the Oracle SQL*Plus Worksheet.
DBMS_SPACE Package (ULT 154): Match the procedures of the DBMS_SPACE package with their uses.
DBMS_REPAIR Package (ULT 155): Match the DBMS_REPAIR package procedures with their descriptions.
Automatic Undo Management: Overview
Undo Segments: Purposes (ULT 212): Identify the purposes of undo segments.
Automatic Undo Management: Features (ULT 213): Select the features of automatic undo management.
Undo Segments: Read Consistency (ULT 214): Sequence the steps that the Oracle server uses to ensure read consistency when a transaction occurs.
Undo Segments: Types (ULT 215): Match the undo segments types with their features.
Undo Retention Control: Features (ULT 216): Select the features of undo retention control.
Automatic Undo Management: Activities
Automatic Undo Mode Configuration: Rules (ULT 221): Identify the rules for automatic undo mode configuration.
Switching Undo Tablespaces: Features (ULT 222): Identify the features of switching undo tablespaces.
Undo Tablespace: Switching (ULT 223): Switch undo tablespaces by using the OEM Console.
Setting Up Rollback Segments
Planning Rollback Segments (ULT 231): Identify the factors to be considered while planning rollback segments.
Rollback Segments Creation: Guidelines (ULT 232): Identify the guidelines for creating a rollback segment.
Rollback Segments: Creating (ULT 233): Create a rollback segment by using the OEM Console.
Rollback Segments: Setting Online (ULT 234): Bring a rollback segment online by using the OEM Console.
Rollback Segments: Acquisition (ULT 235): Identify the steps that an Oracle instance uses to acquire rollback segments when a database opens.
Rollback Segments: Transactions
Rollback Segments: Use by Transactions (ULT 241): Sequence the steps in which transactions use rollback segments.
Rollback Segments: Shrinkage (ULT 242): Identify the conditions under which rollback segments shrink.
Maintaining Rollback Segments
Changing Storage Parameters (ULT 251): Change the rollback segment storage parameters by using the OEM Console.
Shrinking Rollback Segments (ULT 252): Deallocate unused space from a rollback segment by using the OEM Console.
Taking Rollback Segments Offline (ULT 253): Take a rollback segment offline by using the OEM Console.
Removing Rollback Segments (ULT 254): Remove a rollback segment by using the OEM Console.
Troubleshooting Rollback Segments
Insufficient Space for Transactions (ULT 261): Identify the solutions to the problem of insufficient space for transactions.
Read Consistency Errors (ULT 262): Identify the preventive measures for the read consistency error.
Blocking Session (ULT 263): Identify the solutions for the blocking session problem.
Error in Taking a Tablespace Offline (ULT 264): Sequence the steps to resolve an error in taking a tablespace offline.
Undo Segments Information
Undo Statistics: Obtaining (ULT 271): Obtain undo statistics by using the Oracle SQL*Plus Worksheet.
General Rollback Segment Information (ULT 272): Display the general information about rollback segments by using the OEM Console.
Rollback Segment Statistics (ULT 273): Retrieve the statistics about rollback segments by using the Oracle SQL*Plus Worksheet.
Current Rollback Segment Activity (ULT 274): Retrieve information about rollback segments that currently have active transactions by using the Oracle SQL*Plus Worksheet.
Temporary Segment Concepts
Temporary Segments: Types (ULT 312): Identify the features of the various types of temporary segments.
Temporary Segments: Guidelines (ULT 313): Identify the guidelines for using temporary segments.
Temporary Segment Information
Sort Segment Statistics (ULT 321): Retrieve the temporary segment statistics by using the Oracle SQL*Plus Worksheet.
Temporary Segment Activity (ULT 322): Retrieve information about temporary segment activity by using the Oracle SQL*Plus Worksheet.
Maintaining Indexes
Modifying Index Storage Parameters (ULT 412): Modify the index storage parameters by using the OEM Console window.
Index Space: Allocation (ULT 413): Allocate index space by using the Oracle SQL*Plus Worksheet.
Index Space: Deallocation (ULT 414): Deallocate the unused space from an index by using the Oracle SQL*Plus Worksheet.
Index Information (ULT 415): Retrieve index information by using the Oracle SQL*Plus Worksheet.
Monitoring Indexes
Index Space Usage: Monitoring (ULT 421): Monitor index space usage by using the Oracle SQL*Plus Worksheet.
Unused Indexes: Identifying (ULT 422): Identify unused indexes by using the Oracle SQL*Plus Worksheet.
access
To enter or communicate with a particular computer resource, such as an operating system, an Oracle database, or specific files or tables. Various commands or controls are used to set and alter the ability to access, depending on the type of resource.
access (database object)
One of several types of privileges, such as SELECT, ALTER or DROP, for using or altering Oracle database objects such as tables, views, or indexes. Database users can grant other users access via the GRANT statement.
address
A unique network location used to identify a client on a network. Transparent Network Substrate (TNS) addresses have a specific format. Addresses must be unique.
ALERT file
If an error occurs while the Oracle instance is running, the messages are written to the ALERT file. The ALERT file of a database is a chronological log of messages and errors. ALERT files are used to check whether or not an instance is running properly.
alias
A term that refers to an alternative name for an existing network object, such as a host (server) or a set of parameters.
In SQL, a temporary name assigned to a table, view, column or value within a SQL statement, used to refer to that item later in the same statement or in associated SQL*Plus commands.
application
One or more program modules used to achieve a specific result. Applications can be nested within other applications. For example, an application to control a company's inventory could consist of various Form Builder applications for input of data, and various Report Builder applications to produce hard copy output of summary data.
audit
(noun) The result of the auditing activity.
auditing
The process of recording or analyzing operations that have been performed on a body of data.
auto-management of segment free space
A method of managing free space in segments that is introduced in Oracle9i. This method enables the Oracle server to manage segment free space automatically by using bitmaps.
automatic undo management
New feature that is introduced in Oracle9i to manage undo space. This feature enables the Oracle server to automatically manage the sizing of undo tablespaces.
automatic undo mode
A mode of the database in which undo data is stored in a dedicated undo tablespace. Unlike in manual undo management mode, the only undo management that you must perform is the creation of the undo tablespace. All other undo management is performed automatically.
back up (verb)
The creation of a copy (or copies) of existing data, to enable recovery of that data subsequently required.
Backup (noun)
The result of backup activity.
bad file
An output file created by SQL*Loader. It contains the records that were rejected during the load.
batch
Users specify a query to run in batch if they do not want to stop work to wait for the results. The computer decides when to run the query. Users can go back and look at the results of the query later.
BFILE
LOBs are stored in separate segments within the database by using external binary files (BFILES).
bitmap
A bitmap enables automatic management of free space in a segment. Each bitmap keeps track of the free or used status of blocks in that data file. Each bit in the bitmap corresponds to a block or a group of blocks.
block
The basic unit of physical or logical storage. The size of an Oracle block may differ from the block size native to the operating system. Typical Oracle block size is 2048 bytes. The terms page and block are often used synonymously. In PL/SQL, a group of SQL and PL/SQL commands related to each other through procedural logic.
blocking session
A rollback segment problem that occurs when an active transaction is writing to an extent that the Oracle server is trying to access.
BMB
A bitmapped block (BMB) contains bitmaps that store the availability of space in a segment.
bootstrap segment
In the Oracle database, a small segment used during instance startup to populate the dictionary cache.
buffer
A temporary storage area for data during the transfer of that data between the computer and a peripheral, or between parts of a computer. A buffer prevents loss of information due to differences in the speed or timing of the transfer, and speeds up certain functions.
(database) Temporary storage place in the SGA for database blocks currently being accessed and changed by database users. Each block undergoing change must be stored in a buffer. By caching blocks in memory, performance is enhanced (because reading blocks from memory is faster than reading them from disk).
(redo log) Temporary storage place where redo log records are created and held before being written to the redo log file.
In SQL*Plus, an area where the user's SQL statements or PL/SQL blocks are temporarily stored. The SQL buffer is the default buffer, although multiple buffers can be used.
buffer cache
The portion of the SGA that holds copies of Oracle data blocks. All user processes concurrently connected to the instance share access to the buffer cache.
cache (memory)
A temporary storage place for database data that is currently being accessed or changed by users, or for data that Oracle server requires to support users.
clause
A part of a SQL statement that does not constitute the full statement: for example, a "WHERE clause".
cluster
A database structure in which one or more tables are stored together in the same block. A cluster allows tables that contain common information to be accessed concurrently. Tables that are clustered have one or more columns in common and are frequently referenced together in queries (especially joins). In network communication, a configuration in which two or more terminals are connected to a single line or single modem.
cluster index
An index manually created after a cluster of database tables has been created. The index must be created before any DML statements can operate on the clustered tables. The index is created on the cluster key with the SQL statement CREATE INDEX.
column
A vertical space in a database table that represents a particular domain of data. A column has a column name and a specific data type. For example, in a table of employee information, all of the employees' dates of hire would constitute one column. A record group column represents a database column. In the context of CASE, a table is often the implementation of an entity. The columns of the table are used to implement the attributes of the entity and its relationships.
command
An instruction to or request of a program, application, operating system, or other software, to perform a particular task. Commands may be single words or may require additional phrases, variously called arguments, options, parameters, and qualifiers. Unlike statements, commands execute as soon as you enter them.
commit
To make permanent changes to data (inserts, updates, deletes) in the database. Before changes are committed, both the old and new data exist so that changes can be stored or the data can be restored to its prior state. In Form Builder, to store new or changed records from the workspace into a table in the database.
condition
An expression whose value is either true or false, such as X> 100. You add a condition to a query when you want the query to display only those rows that evaluate as true in your expression.
configuration files
Files that are used to identify and characterize the components of a network. Configuration is largely a process of naming network components and identifying relationships among those components.
connect
To access an Oracle or foreign database using a valid username and password. Similar to logging on to an operating system using an account name and password. You must connect if you want to create or modify queries or access a display stored in a database.
connect string
The set of parameters, including a protocol that you use to connect to a specific Oracle instance on the network.
connection
A communication pathway between a user process and Oracle server is called a connection.
consistency (of data)
A database requirement that all related data be updated together in the proper order, and that if there is redundant data that it agree. Consistency ensures that the data a user is viewing or changing is not changed by other users until the user is finished with the data.
create
To bring an object (a table, block, field, etc.) into existence, usually by defining its primary characteristics.
data block
A database data storage unit at the finest level of granularity. One data block corresponds to a specific number of bytes of physical database space on disk. A data block size is specified for every Oracle database when the database is created. A database block is the minimum unit of I/O used by an Oracle server.
data dictionary
A comprehensive set of tables and views automatically created and updated by the Oracle server, which contains administrative information about users, data storage, and privileges. It is installed when Oracle is initially installed and is a central source of information for the Oracle server itself and for all users of Oracle. The tables are automatically maintained by Oracle. It is sometimes referred to as the catalog.
data dictionary cache
The data dictionary cache contains information from the data dictionary, the metadata store.
data dictionary view
A view on the data dictionary tables created to facilitate retrieving information about a database.
data files
Files that contain all the database data. A data file is associated with only one database,. The contents of logical database structures such as tables and indexes is physically stored in the data files allocated for a database. One or more data files form a logical unit of database storage called a tablespace. Files containing the input records to be processed during a load performed using SQL*Loader.
database
A set of operating system files, treated as a unit, in which an Oracle server stores a set of data dictionary tables, user tables, and other objects needed to store and manage data. A database consists of three types of files: database files, redo log files, and control files. The disk space corresponding to this set of files.
database administrator (DBA)
A person responsible for the operation and maintenance of an Oracle server or a database application. The database administrator monitors its use in order to customize it to meet the needs of the local community of users. An Oracle role that has been given the privileges needed to perform database administration functions. Usually the two meanings coincide. There may be more than one DBA per site.
database name
A unique identifier used to name a database. It is assigned in the CREATE DATABASE statement or in the INIT.ORA file.
database object
Something created and stored in a database. Tables, views, synonyms, indexes, sequences, clusters, and columns are all examples of database objects.
DB_BLOCK_SIZE parameter
An initialization parameter that specifies the size of Oracle database blocks in bytes. The value of DB_BLOCK_SIZE parameter is used to determine the size of the blocks during CREATE DATABASE. This parameter is of the integer type.
DB_NAME parameter
An initialization parameter that specifies the name of the database.
DBA role
A role given to a user via the GRANT DBA statement. DBA role should be limited to very few users.
DBA_EXTENTS
DBA_EXTENTS describes the extents comprising of all segments in the database or segments for the current user.
DBA_FREE_SPACE
DBA_FREE_SPACE displays information about free extents in all tablespaces.
DBA_IND_COLUMNS
DBA_IND_COLUMNS describes the columns of indexes on tables. Some columns in this view contains statistics that are generated by the ANALYZE statement.
DBA_INDEXES
DBA_INDEXES describes the indexes on all tables in the database. Some columns in this view contains statistics that are generated by the ANALYZE statement.
DBA_ROLLBACK_SEGS
DBA_ROLLBACK_SEGS stores information about the rollback segments of a database. For example, the name, associated tablespace, and status of a rollback segment.
DBA_SEGMENTS
DBA_SEGMENTS describes the storage allocated for all database segments or for segments for the current user. Some columns in this view contains statistics that are generated by the ANALYZE statement.
DBA_TABLES
DBA_TABLES describes all the relational tables in the database.
DBA_TABLESPACES
DBA_TABLESPACES contains measures of tablespace activity for an Oracle database since the last instance startup.
DBMS_REPAIR
An Oracle-supplied package that provides information that enables a DBA to detect and fix corrupt data blocks.
DBMS_SPACE package
An Oracle-supplied package that provides information about the availability of segment space.
default
A value supplied by the system when a user does not specify a required command parameter or qualifier.
deferred undo segment
An undo segment containing undo entries that could not be applied to a tablespace because that tablespace was since taken off line. As soon as the tablespace is brought back online, all the entries are applied.
define
To specify the properties of an object (a table, block, field, etc.).
delete
To remove a row from a table using the SQL statement DELETE.
dialog box
A partial screen that prompts you to enter some piece(s) of information necessary to complete an operation. Dialog boxes are accessed extensively via the File menu and buttons. A dialog typically requires more information from the user than an alert.
DML (Data Manipulation Language)
The category of SQL statements that changes the database data. Common DML statements are INSERT, UPDATE, and DELETE. Occasionally queries and DCL statements are grouped with DML statements.
dynamic performance views
A set of views that are maintained by the server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use.
error message
A message from a computer program informing you of a potential problem or condition preventing program or command execution.
extent
A specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information. An extent can be allocated when the table or index is created or when the table or index requires additional space.
field
A place in a window within a tool where a value can be entered.
file
A collection of data treated as a unit, such as a list, document, index, note, set of procedures, etc. Generally used to refer to data stored on magnetic tapes or disks.
filename
The name component of a file specification. A filename is assigned by either the user or the system when the file itself is created.
fragmentation
A database with many non-contiguous, small blocks of free space is said to be fragmented.
free extents
Term used to describe database blocks that are not currently allocated to any table or index segment. Free extents are also called free space.
free space
Term used to describe database blocks that are not currently allocated to any table or index segment. Free space is also referred as free extents.
FROM
Required clause of the SELECT statement that identifies the tables or views from which data is selected.
function
Term used to describe a PL/SQL subprogram that executes an operation and returns a value at the completion of the operation. A function can be either built-in or user-named.
Grant
A facility to enable users to have certain access rights to information in a database.
hash function
A function used in a hash cluster to calculate the location of a row.
high water mark
Term used to indicate the last block a segment used for storing data. It is reset only when the segment is truncated.
identification
The process that enables recognition by the system, such as providing a username.
index
An index is a tree-like structure that allows direct access to a row in a table. Indexes can be classified on the basis of either their logical design or their physical implementation.
index partition
When an index is partitioned and spread across several tablespaces, each partition is referred to as an index partition. Each index partition corresponds to a segment.
INDEX_STATS
A view that stores information from the last ANALYZE INDEX command with the VALIDATE STRUCTURE clause.
index-organized table (IOT)
A table that stores all the data within an index structure. Row data in an IOT is ordered on the basis of the primary key of the table.
INIT.ORA file
An operating system file containing a list of parameters that are read when an instance is started. The parameters identify the database and control instance wide resources. By affecting the size of the SGA, they also affect the performance of the instance. In UNIX, the init.ora file.
initialization parameters
The initialization parameters specify the characteristics of the Oracle instance.
INITRANS
The INITRANS parameter specifies the number of Data Modification Language transaction entries for which space is initially reserved in the data block header.
input file
File containing data to be transferred into the computer.
instance
A system global area (SGA) and the Oracle background processes constitute an Oracle instance. There is usually a one-to-one correspondence between an Oracle instance and a system global area (SGA).
instance
The background processes and memory areas required to access an Oracle database. A database system requires one instance and one database. Every Oracle instance has a single system global area (SGA); a multiple-user instance also requires several background processes.
instance recovery
Recovery of an instance in the event of software or hardware failure, so that the database is again available to users. If the instance terminates abnormally, then instance recovery automatically occurs at the next instance startup.
Integrity (of data)
The accuracy, consistency, and security of database data, as maintained by Oracle server throughout all user access.
join
A query that retrieves data from multiple database tables, specifying how rows are to be related using a join condition. Occasionally one table is joined to itself.
kilobyte (Kb)
A unit of computer memory equal to 1024 bytes, often rounded off to 1,000 bytes.
LOB index
The LOB index is used by the Oracle server to retrieve or manipulate LOB data.
LOB segment
Term used to describe a segment that stores large objects (LOBs), such as images in the database.
log file
An output file created by SQL*Loader that contains a record of the load.
log in (or log on)
To perform a sequence of actions at a terminal that establishes a user's communication with the operating system and sets up default characteristics for the user's terminal session.
log off (or log out)
To terminate interactive communication with the operating system, and end a terminal session.
logical database structure
Structure determined by one or more tablespaces, the database's schema objects (e.g., tables, views, indexes, clusters, sequences, stored procedures), and the logical storage structures, including tablespaces, segments, and extents. All these elements dictate how the physical space of a database is used. The schema objects and the relationships among them form the relational design of a database.
MANAGER
At the time of its creation, the SYSTEM user account is identified by the password MANAGER.
MAXEXTENTS
The total number of extents, including the first, that can be allocated for the segment.
MAXTRANS
The MAXTRANS parameter limits the number of transaction entries that can concurrently use data in a data block.
megabyte (M)
A unit of memory equal to 1,048,576 bytes (1024 x 1024). Often rounded to one million bytes.
memory
There are several basic memory structures associated with Oracle: the system global area (which includes the database and redo log buffers, and the shared pool) and the program global areas. Memory structures are used to fulfill several requirements. For example, memory is used to store program code being executed and to store data that is shared among database users.
metadata
Data that describes data and other structures, such as objects, business rules, and processes.
MINEXTENTS
The total number of extents to be allocated when the segment is created.
nested table
A table that has a column whose data type is another table.
non-SYSTEM undo segment
The server uses a non-SYSTEM undo segment to store undo data when the database has multiple tablespaces.
null
The absence of a value for a given item. A null implies nothing more than "a value is not applicable" or "the value is unknown." Nulls are not equal to any other value, even to each other. Comparisons with nulls are always false.
NULL value
The absence of a value.
object
A named structure in the Oracle database, such as a table, index, or form. A group of data in a form that you can copy, move, or delete in a single operation.
operating system
The system software that manages a computer's resources, performing basic tasks such as allocating memory and allowing computer components to communicate.
operations
A clearly defined action that allows users to manipulate the data and structures of a database. The operations on a database must adhere to a predefined set of integrity rules.
optimal (adjective)
Best, possible use of an object, for example an index.
Oracle Enterprise Manager (OEM)
A separate Oracle product that combines a graphical console, agents, common services, and tools to provide an integrated and comprehensive systems management platform for managing Oracle products.
Oracle Management Server
The middle tier and the core of the Oracle Enterprise Manager framework. The Management Server provides administrative user accounts, processes management functions such as jobs and events, and manages the flow of information between the tiers.
Oracle Real Applications Cluster (RAC)
An architecture that allows multiple instances to access a shared database of datafiles. Real Application Clusters is also a software component that provides the necessary cluster database scripts, initialization files, and datafiles needed for the Oracle Enterprise Edition and Real Application Clusters.
parameter
A value specified by a command. A parameter can be a file specification, a symbol value passed to a command procedure, or a word defined by the operating system. In SQL*Plus, a substitution variable consisting of an ampersand followed by a numeral (&1, &2, etc.). A variable that is passed to or from a procedure or a function..
Oracle server
A running Oracle database system. Components of the Oracle server include an Oracle Instance and a database.
Oracle System Identifier (SID)
A unique name for an Oracle instance on a computer that may run multiple Oracle Instances concurrently. To switch between Oracle databases, users must specify the desired SID.
ORACLE_HOME
The top directory in the Oracle directory hierarchy on some directory-based operating systems. On UNIX-based systems, an environment variable specifying the top Oracle directory.
orphan key table
An orphan key table stores index entries that point to corrupt rows.
package
A method of encapsulating and storing related procedures, functions, and other package constructs together as a unit in the database. While packages provide the database administrator or application developer organizational benefits, they also offer increased functionality and database performance.
parameter file
File that contains information to initialize an Oracle instance. A file that defines the command line parameters for a load performed using SQL*Loader.
Partition (disk)
A contiguous range of blocks on a physical disk.
password
A means of authenticating a user. A password is used for data security and known only to its owner. Passwords are entered in conjunction with an operating system login ID, Oracle username, or account name in order to connect to an operating system or software application (such as the Oracle database). Whereas the username or ID is public, the secret password ensures that only the owner of the username can use that name, or access that data.
PCTFREE
A parameter that controls utilization of a block in a table or an index. For a table this value represents the proportion of block space that will be reserved for row growth resulting from updates. For an index, this is the amount of space that will be left free in each block during the creation of an index. In an index, this space can be used for inserting new entries.
PCTINCREASE
A storage parameter associated with a schema object to specify the percent by which the size of the third and subsequent extent is increased over the size of the preceding extent.
PCTUSED
A block utilization parameter that determines when a block, which has been filled up to (100-PCTFREE) will again be available for inserts.
physical block
A standard storage allocation on a disk. The native block size varies across operating systems. The logical Oracle block size may differ from the native operating system block size.
physical database structure
Determined by the operating system files that constitute the database. Each Oracle database is comprised of three types of files: one or more data files, two or more redo log files, and one or more control files. The files of a database provide the actual physical storage for database information.
PL/SQL
The Oracle procedural language extension of SQL. PL/SQL combines the ease and flexibility of SQL with the procedural functionality of a structured programming language, such as IF ...THEN, WHILE, and LOOP.
private undo segment
An undo segment whose name has been specified in the INIT.ORA file for a particular instance, and is thus brought on line by that instance. Contrast with public undo segment.
privilege
A right to successfully execute a particular type of SQL statement. Some examples of privileges include rights to connect to the database (create a session), to create a table in your schema, to select rows from someone else's table, and to execute someone else's stored procedure. The privileges of a user in an Oracle database can be divided into two distinct categories: system privileges and object privileges.
process
A thread of control in an operating system; that is, a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs. An Oracle database system has two general types of processes: user processes and Oracle processes.
public
Visible or available to all users. Though by default all objects are private, many database objects can be made public. While only DBAs can create public synonyms or database links, any user may GRANT PUBLIC access to his own objects.
public undo segment
An undo segment that may be allocated to any instance accessing the database. Once allocated to an instance, an undo segment cannot be allocated to other instances until the first instance is shutdown.
query
A SQL SELECT statement that retrieves data, in any combination, expression, or order. Queries are read operations; they do not change any data, they only retrieve data. Queries are often considered to be DML statements.
quota
A limit on the amount of database storage used by a database user. A DBA can set tablespace quotas for each Oracle username.
read consistency
A feature of all versions of Oracle server that guarantees that the results of any query are a consistent set of data as of the time the query was executed. A read-consistent view of the data can be thought of as a "snapshot" of the data as of a point in time.
record
A synonym for row; one row of data in a database table, having values for one or more columns. A unit of information in a particular panel, corresponding to a row in that panel.
recovery
A process in which Oracle server brings the database and associated files back to a consistent state. Some types of recovery are automatic and some require DBA intervention.
redo log
A sequential log of all changes made to the data. The redo log is written and used in the event that a failure occurs before changes are written to disk; it is used to reapply the changes to disk. The redo log consists of two or more redo log files. When the file currently being written fills, the other file is reused.
redo log file
A file containing records of changes to the databases. These files are used for recovery purposes.
relational operator
A symbol used in search criteria to indicate a comparison between two values, such as the equal sign in "WHERE DEPTNO = 10." Rows in which the comparison results in "true" are returned in the result (fetched), while rows in which the comparison returns "false" are rejected from the result.
repair table
A repair table stores the details and the repair directives to fix the corrupt data blocks.
repository
A mechanism for storing any information to do with the definition of a system at any point in its life-cycle. Repository services would typically be provided for extensibility, recovery, integrity, naming standards, and a wide variety of other management functions.
resource
A logical database object or physical structure that may be required or locked by database users. Resources that users can directly lock are rows and tables; resources that Oracle server can lock are more numerous and include data dictionary tables, caches, and files.
restore
To use previously archived data in order to bring data up to date or to replace corrupted data with data known to be correct. To reactivate data that has been stored in archives.
retrieve
To copy, or fetch, selected rows from one or more tables.
role
A list of user names that can be granted privileges.
roles
Named groups of related privileges that are granted to users or other roles.
roll back (verb)
To discard some or all pending changes made to data in the current transaction, using the SQL statement ROLLBACK. You can roll back a portion of a transaction by rolling back to a savepoint. As a part of recovery, to undo changes made to a database that were never committed by users.
rollback segment
A database object used to record before images when a change is made. It is used to undo changes in the database in the event of transaction rollback (i.e. undoing all the changes done in a current transaction), crash, media recovery or, if necessary, for read consistency. Every instance requires at least one rollback segment.
rollback(noun)
The second half of the recovery procedures. After the roll forward, any changes that were not committed must be undone. After the redo log files have been applied, then the rollback segments are used to identify and undo transactions that were never committed, yet were recorded in the redo log. Oracle completes this step automatically.
row
Synonym for record; one row of data in a database table, having values for one or more columns. Also called tuple. One set of field values in the output of a query.
Row header
The portion of each row's required storage that precedes the data for a row and contains information about row pieces, chaining, columns in the row piece and cluster keys.
ROWID
A unique identifier that Oracle server uses to obtain the physical address of the row. Considered a pseudo-column. ROWIDs can be retrieved in a query using the reserved word ROWID.
schema
A collection of database objects owned by a particular user.
schema objects
A collection of related objects. Schema objects are the logical structures that directly refer to the database's data. Schema objects include such structures as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. There is no relationship between a tablespace and a schema: objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.
segment
The space used by a database object such as a table, index, and cluster. A segment consists of one or more extents.
segment header
The first block in the first extent of a segment, which contains segment overhead information, including a extent map for this segment.
SELECT
To fetch rows from one or more database tables using a query (the SQL statement SELECT).
SELECT list
The list of items that follows the keyword SELECT in a query. These items may include column names, SQL functions, constants, pseudo-columns, calculations on columns, and aliases. The number of columns in the result of the query will match the number of items in the SELECT list.
SELECT statement
A SQL statement that specifies which rows and columns to fetch from one or more tables or views.
server
The provider of services requested by a client. A process providing services to other cooperating processes.
Server
Oracle software that handles the functions required for concurrent, shared data access to an Oracle database. The server portion receives and processes SQL and PL/SQL statements originating from client applications.
session
A session is a specific connection of a user to an Oracle server. The session commences when the user is validated by the Oracle server, and ends when the user logs out or when there is an abnormal termination.
SGA
A shared storage area required by Oracle server that contains information required by server and background processes. Every instance starting a multiple-user database first allocates an SGA in main or virtual memory. The SGA supports communication between server and background processes and tracks resources shared by users, such as cache buffers, database and log buffers, locks, and dictionary caches. May also be called the shared global area.
Shared Global Area (SGA)
A shared storage area required by Oracle server that contains information required by server and background processes. Every instance starting a multiple-user database first allocates an SGA in main or virtual memory. The SGA supports communication between server and background processes and tracks resources shared by users (such as cache buffers, database and log buffers, locks, and dictionary caches). Also referred to as System Global Area.
shared mode
An Oracle instance running in shared mode (parallel mode) can share access to the database with other instances that are part of the same Oracle Parallel Server.
shut down
The process of stopping a running instance in order to make a database unavailable, including closing and dismounting a database if one has been mounted and opened. Contrast with start up.
SHUTDOWN
A command used to shut down an Oracle instance.
SID
System identifier, also known as system_ID.
SMON (System Monitor)
A background process that performs instance recovery at instance startup. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instances that have failed. SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database, to make free space contiguous and easier to allocate.
snapshot
Information stored in rollback segments to provide transaction recovery and read consistency. Rollback segment information can be used to recreate a snapshot of a row before an update. A snapshot is a local copy of table data that originates from one or more remote master tables. A snapshot is periodically refreshed to reflect changes made to the master table.
sort (verb)
To rearrange a set of values into a new sequence, using rules of precedence, as in alphabetizing or putting in numeric order. (noun) A program designed to perform a sort.
SQL (Structured Query Language)
The internationally accepted standard for relational systems, covering not only query but also data definition, manipulation, security and some aspects of referential integrity.
SQL statement
A complete command or statement written in the SQL language. In Form Builder, SQL statements can be invoked by triggers at various points in the processing of forms. Synonymous with statement (SQL).
SQL*Plus
An interactive SQL-based language for data manipulation, data definition, and the definition of access rights for an Oracle database. Often used as an ad-hoc reporting tool.
start up
To start an instance presumably with the intention of mounting and opening a database, in order to make a database system available for use.
STARTUP
The command issued to start an instance (presumably with the intention of mounting and opening a database), and thus make a database system available for use.
statement (SQL)
A SQL statement, and analogous to a complete sentence, as opposed to a phrase. Portions of SQL statements or commands are called statement blocks, expressions, predicates, or clauses.
subprogram
A named PL/SQL construct. Functions and procedures constitute subprograms.
switching undo tablespaces
An activity in the automatic undo management mode. A DBA uses this activity to change the current undo tablespace.
syntax
The orderly system by which commands, qualifiers, and parameters are combined to form valid command strings.
SYS username
One of two standard DBA usernames automatically created with each database (the other is SYSTEM). The Oracle username SYS is created with the password CHANGE_ON_INSTALL. SYS owns the base data dictionary tables and views.
system
A named, defined, and interacting collection of real-world facts, procedures, and processes, along with the organized deployment of people, machines, various mechanisms, and other resources that carry out those procedures and processes.
System Change Number (SCN)
A logical time stamp Oracle server uses to order events within a single instance and across all instances.
System Global Area (SGA)
A shared storage area required by Oracle server that contains information required by server and background processes. Every instance starting a multiple-user database first allocates an SGA in main or virtual memory. The SGA supports communication between server and background processes and tracks resources shared by users, such as cache buffers, database and log buffers, locks, and dictionary caches. May also be called the shared global area.
System Monitor (SMON)
A background process that performs instance recovery at instance startup. In a multiple instance system (one that uses the Parallel Server), SMON of one instance can also perform instance recovery for other instances that have failed.
SMON also cleans up temporary segments that are no longer in use and recovers dead transactions skipped during crash and instance recovery because of file-read or offline errors. These transactions are eventually recovered by SMON when the tablespace or file is brought back online. SMON also coalesces free extents within the database, to make free space contiguous and easier to allocate.
SYSTEM undo segment
The server stores the changes made to the objects in the SYSTEM tablespace in the SYSTEM undo segment.
System-Managed Undo (SMU) mode
Method of managing undo data automatically by using undo segments.
table
The basic unit of storage in a relational database management system. A table represents entities and relationships, and consists of one or more units of information (rows), each of which contains the same kinds of values (columns). Each column is given a column name, a data type (such as CHAR, DATE, or NUMBER), and a width (the width may be predetermined by the data type, as in DATE).
Once a table is created, valid rows of data can be inserted into it. Table information can then be queried, deleted, or updated. To enforce defined business rules on a table's data, integrity constraints and triggers can also be defined for a table. In Form Builder, a block in a form is associated with one base table.
table alias
A temporary substitute name for a table, defined in a query and only good during that query. If used, an alias is set in the FROM clause of a SELECT statement and may appear in the SELECT list.
tablespace
A logical portion of an Oracle database used to allocate storage for table and index data. Each tablespace corresponds to one or more physical database files.
Every Oracle database has a tablespace called SYSTEM and may have additional tablespaces. A tablespace is used to group related logical structures together. For example, tablespaces commonly group all of an application's objects to simplify certain administrative operations.
temporary segment
A segment used by Oracle server for a temporary work area during processing (typically sorting). Temporary segments are implicitly used by the Oracle server on behalf of users when needed. Many temporary segments can exist at any given moment.
temporary tablespace
When a user executes a SQL statement that requires the creation of temporary segments (e.g., the creation of an index), the user's temporary tablespace is used to store intermediate values. By directing all users' temporary segments to a separate tablespace, the temporary tablespace feature can reduce I/O contention among temporary segments and other types of segments. It also minimizes fragmentation of data files used for user objects.
timestamp
A unique date and time denoting a particular event, such as the saving of a file. Timestamps are used for identification purposes, especially to locate the most recent version of a file, data item, or program. In Oracle auditing, timestamps can be stored to record the time when the auditing event occurred.
transaction
A logical unit of work that is comprised of one or more SQL statements executed by a single user. According to the ANSI/ISO SQL standard, with which Oracle is compatible, a transaction begins with the user's first executable SQL statement.
A transaction ends when it is explicitly committed or rolled back by the user.
undo quota
Amount of undo space that is assigned to a group of users.
undo retention
A feature of automatic management of undo space. A DBA uses this feature to specify the retention period for undo data.
undo segment
Segments containing all undo data when the database is run in automatic undo management mode. Undo segments are internally similar to the rollback segments that are used to store undo data in manual undo management mode.
UNDO_RETENTION
An initialisation parameter that you use to specify the duration of time for which undo data is retained in the database.
UPDATE statement
An SQL statement that is used to update an object, such as a table.
used extents
Extents that are currently allocated to a segment, and thus are not available for use by other objects. An used extent may or may not contain data and is not release by the DELETE command. Contrast with free extents.
user
Any person or group of people with some rights to access a computer facility. The same person may be known to a sophisticated system in several ways; for example, as an Oracle User, a user of an operating system, or a user of an electronic mail system.
USER_TABLESPACES
USER_TABLESPACES contains the descriptions of all tablespaces that are accessible to a user.
username
The name by which a user is known to the Oracle server and to other users. Every username is associated with a private password, and both must be entered to connect to an Oracle database.
V$OBJECT_USAGE
V$OBJECT_USAGE contains index usage information produced by the ALTER INDEX command with the MONITORING USAGE clause.
V$ROLLNAME
V$ROLLNAME lists the names of all online rollback segments. It can only be accessed when the database is open.
V$ROLLSTAT
V$ROLLSTAT contains rollback segment statistics.
V$SESSION
V$SESSION stores the session information for each current session view.
V$SORT_SEGMENT
V$SORT_SEGMENT contains information about every sort segment in a given instance. The view is only updated when the tablespace is of the TEMPORARY type.
V$SORT_USAGE
V$SORT_USAGE describes sort usage.
V$TRANSACTION
V$TRANSACTION lists the active transactions in the system.
V$UNDOSTAT
V$UNDOSTAT contains statistics for monitoring and tuning undo space.
|
