New Document
Computer Science
Computer Catlog
Oracle Catlog

History of Oracle
Introduction to Terms
Oracle Configurations
Roles of Database Administrator
Oracle Architecture
A Brief History of SQL
Dr. Codd's 12 Rules
An Overview of SQL
The SELECT Statement
Expressions, Conditions, and Operators
Clauses in SQL
Joining Tables
Sub Query
Manipulating Data
Building a Database
Views and Indexes
Controlling Transactions
Database Security
Advanced SQL Topics
Stored Procedures
Embedded SQL
SQL Tuning
Using Views in Data Dictionary
Using SQL to Generate SQL Statements

Oracle Terms

    Many different terms and concepts will be used throughout. I've introduced them here to make it easier for you to grasp many of the concepts and lessons to follow.

Ad Hoc Query

    This use of the Latin term means an impromptu, simple query.


    A block is the smallest unit of storage in an Oracle database. The database block contains header information concerning the block itself as well as the data or PL/SQL code. The Oracle block size is configurable with the minimum size being 2KB and the maximum size being 16KB.


    In computer terms, a bottleneck is a system component that limits the performance of the system.


    This term refers to an amount of memory used to store data. A buffer stores data that is about to be used or that has just been used. In many cases, buffers are in-memory copies of data that is also on disk. Buffers can be used as a copy of data for quick read access, they can be modified and written to disk, or they can be created in memory as temporary storage.

    In Oracle, database buffers of the SGA store the most recently used blocks of database data. The set of database block buffers is known as the database buffer cache. The buffers used to temporarily store redo entries until they can be written to disk are known as redo log buffers.


    A cache is a storage area used to provide fast access to data. In hardware terms, the cache is a small (relative to main RAM) amount of memory that is much faster than main memory. This memory is used to reduce the time it takes to reload frequently used data or instructions into the CPU. CPU chips themselves contain small amounts of memory built in as cache.

    In Oracle, the block buffers and shared pool are considered caches because they are used to store data and instructions for quick access. Caching is very effective in reducing the time it takes to retrieve frequently used data.

    Caching usually works using a least recently used algorithm. Data that has not been used for a while is eventually released from the cache to make room for new data. If data is requested and is in the cache (a phenomenon called a cache hit), the data is retrieved from the cache, preventing it from having to be retrieved from memory or disk. After the data has been accessed again, it is marked as recently used and put on the top of the cache list.


    A checkpoint is an operation that forces all changed, in-memory data blocks to be written out to disk. This is a key factor in how long the database takes to recover in the event of a failure. This concept is discussed in depth on Day 2, "Exploring the Oracle Architecture."

Clean Buffer

    A clean buffer is a buffer that has not been modified. Because this buffer has not been changed, it is not necessary for the DBWR to write this buffer to disk.


    This term refers to the capability to perform many functions at the same time. Oracle provides for concurrency by allowing many users to access the database simultaneously.


    A database is a set of data, organized for easy access. The database is the actual data. It is the database that you will be accessing when you need to retrieve data.

Data Dictionary

    The data dictionary is a set of tables Oracle uses to maintain information about the database. The data dictionary contains information about tables, indexes, clusters, and so on.

DBA (Database Administrator)

    The DBA is the person responsible for the operation, configuration, and performance of the database. The DBA is charged with keeping the database operating smoothly, ensuring that backups are done on a regular basis (and that the backups work), and installing new software. Other responsibilities might include planning for future expansion and disk space needs, creating databases and tablespaces, adding users and maintaining security, and monitoring the database and retuning it as necessary. Large installations might have teams of DBAs to keep the system running smoothly; alternatively, the tasks might be segmented among the DBAs.


    The Database Management System is the software and collection of tools that manages the database. Oracle software is the DBMS. A Relational Database Management System is a DBMS that is relational in nature. This means that the internal workings access data in a relational manner. Oracle is an RDBMS.

DDL (Data Definition Language) Commands

    These commands are used in the creation and modification of schema objects. These commands provide the ability to create, alter, and drop objects; grant and revoke privileges and roles; establish auditing options; and add comments to the data dictionary. These commands are related to the management and administration of the Oracle database. Before and after each DDL statement, Oracle implicitly commits the current transaction.

Dirty Buffer

    A dirty buffer is a buffer that has been modified. It is the job of the DBWR to eventually write all dirty block buffers out to disk.

DML (Data Manipulation Language) Commands

    These commands allow you to query and modify data within existing schema objects. Unlike the DDL commands, a commit is not implicit. DML statements consist of DELETE, INSERT, SELECT, and UPDATE statements; EXPLAIN PLAN statements; and LOCK TABLE statements.

Dynamic Performance Tables

    These tables are created at instance startup and used to store information about the performance of the instance. This information includes connection information, I/Os, initialization parameter values, and so on.


    A function is a set of SQL or PL/SQL statements used together to execute a particular function. Procedures and functions are identical except that functions always return a value (procedures do not). By processing the SQL code on the database server, you can reduce the number of instructions sent across the network and returned from the SQL statements.

IM (Information Management)

    This term is usually used to describe the department that handles your corporate data.

IS (Information Systems)

    This term is also used to describe the department that handles your corporate data.

IT (Information Technology)

    This term is used to describe the business of managing information.

Network Computing Architecture (NCA)

    The Network Computing Architecture is a standard for computing over the network. The NCA was developed in conjunction with Oracle.

Physical Memory

    This term refers to the actual hardware RAM (Random Access Memory) available in the computer for use by the operating system and applications.


    A procedure is a set of SQL or PL/SQL statements used together to execute a particular function. Procedures and functions are identical except that functions always return a value (procedures do not). By processing the SQL code on the database server, you can reduce the number of instructions sent across the network and returned from the SQL statements.

Program Unit

    In Oracle, program unit is used to describe a package, a stored procedure, or a sequence.


    A query is a read-only transaction against a database. A query is generated using the SELECT statement. Users generally distinguish between queries and other transaction types because a query does not the change data in the database.


    A schema is a collection of objects associated with the database.

Schema Objects

    Schema objects are abstractions or logical structures that refer to database objects or structures. Schema objects consist of such things as clusters, indexes, packages, sequences, stored procedures, synonyms, tables, views, and so on.

System Global Area (SGA)

    The SGA is a shared-memory region that Oracle uses to store data and control information for one Oracle instance. The SGA is allocated when the Oracle instance starts; it is deallocated when the Oracle instance shuts down. Each Oracle instance that starts has its own SGA. The information in the SGA is made up of the database buffers, the redo log buffer, and the shared pool; each has a fixed size and is created at instance startup.


    A transaction is a logical unit of work consisting of one or more SQL statements, ending in a commit or a rollback. Performance measurements often use the number of transactions per second or per minute as the performance metric.


    A trigger is a mechanism that allows you to write procedures that are automatically executed whenever an INSERT, UPDATE, or DELETE statement is executed on a table or view. Triggers can be used to enforce integrity constraints or automate some other custom function.

Virtual Memory

    This term refers to the memory that can be used for programs in the operating system. To overcome the limitations associated with insufficient physical memory, virtual memory allows programs to run that are larger than the amount of physical memory in the system. When there is not enough physical memory in the system, these programs are copied from RAM to a disk file called a paging or swap file. This arrangement allows small systems to run many programs. You pay a performance penalty when the computer pages or swaps.

Storage Units

    Data is stored in the computer in a binary form. The units used to refer to this binary data are as follows:

Term Definition Comment
bit The smallest unit of data storage A bit is either a 1 or a 0.
nibble. 4 bits This term is not commonly used.
byte 8 bits The most commonly used storage unit.
word This term is architecture On some systems, a word is 16 bits;
dependent on others, a word is 32 or 64 bits.  
kilobyte (KB)   Even though kilo usually means 1,000, a kilobyte in computer terms is actually 1,024 bytes (because we like powers of 2).
megabyte (MB)   The term megabyte denotes 1,024KB or 1,048,576 bytes.
gigabyte (GB)   A gigabyte is 1,024 megabytes or 1,073,741,824 bytes.
terabyte (TB)   A terabyte is 1,024 gigabytes or 1,099,511,627,776 bytes.

    It is not uncommon to hear large data warehousing sites talk in terms of terabytes. In the next few years, you will probably hear of systems using storage in the tens and hundreds of terabytes.