Database Architecture

ORACLE HISTORY

The name Oracle refers to either:

Oracle database, the famous database management system (DBMS) that is widely used for business applications, and runs on most mainframe, micro, and personal computers.

Oracle Corporation, the name of the company that sells the Oracle Database Management System.

Origin of the word ORACLE

IBM is an abbreviation for International Business Machines and SUN is an acronym for Stanford University Network (or Networking). However, the word ORACLE is neither an abbreviation nor an acronym (see an English dictionary for the meaning of the word oracle). It appears in uppercase because this is the branding style Oracle Corp. chose for itself. The word Oracle means: Prophecy or prediction; answer to a question believed to come from the gods; a statement believed to be infallible and authoritative; a shrine at which these answers are given

So why is it named Oracle?

Legend has it that Larry Ellison and Bob Miner were working on a consulting project for the CIA (Central Intelligence Agency in USA) where the CIA wanted to use this new SQL language that IBM had written a white paper about. The code name for the project was Oracle (the CIA saw this as the system to give all answers to all questions or something such ;-).

The project eventually died (of sorts) but Larry and Bob saw the opportunity to take what they had started and market it. So they used that project's codename of Oracle to name their new RDBMS engine. Funny thing is, that the CIA was one of Oracle's first customers...

Further speculation:

We received a lot of E-mails recently stating that ORACLE stands for Oak Ridge Automatic Computer and Logical Engine, a computer built by Oak Ridge National Laboratory in the 1950's. For some time this was the fastest computer in the world. It reportedly could do 100 man-years of calculations in an eight-hour work shift. However, we don't believe this has anything to do with the Oracle Database or Oracle Corporation. However, if you have information to the contrary, please let us know.

Yet another mail stated that Oracle stands for Orbital Relational Analytical Computing Logical Equation. Absolutely amazing! As expected, a Google search on this returned zero hits.

Some jokingly refer to ORACLE as One Real A$#@%& called Larry Ellison.

What is oracle?

Definition : Oracle is a powerful relational database management system that offers a large feature set. Along with Microsoft SQL Server, Oracle is widely regarded as one of the two most popular full-featured database systems on the market today.

What is oracle architecture?

Oracle architecture consists of Oracle server, oracle instance and Oracle database.

Oracle server :  The Oracle server is the system that manages databases and provides comprehensive and integrated information management.
 An Oracle server consists of an instance and a database.

Oracle instance: The Oracle instance allows you to access the Oracle database. Also an instance is a gateway in order to access the database,  An instance is a combination of different background process and memory structures are called an instance

Background processes + Memory structure = Oracle Instance

Memory structures consisting of essentially of two memory areas:

Memory area allocated to the SGA (System Global Area): allocated to start the Instance and represents a fundamental component of an Oracle instance. It consists of Several memory areas:
•The shared memory area
•The buffer cache of the database
•Buffer logging and other structures for the management of internal and external locks, statistical data, etc ...
•The LARGE POOL memory area
•The Java memory area

Shared pool

The size of shared pool is defined by the parameter called shared_pool_size . Shared pool contains many components but important components are library cache,data dictionary cache and control structure .

Library cache

Library cache consists of shared sql and pl/sql area . SQL and PL/SQL area stored most recently used sql and pl/sql statements . We cannot declare the size of lib cache ,but it is complete based on shared pool size. If the size of lib cache is small ,the statements are continously reload in the lib cache which can effect the performance . It is managed through LRU.

Data Dictionary cache

The data dictionary cache hold information about database objects like

table

index

column

privileges

view

trigger .. etc .

The data dictionary cache also known as row cache ,because it store the information in the form of rows instead of buffers. If the size of DDC is small ,then database has to query database tables repeatedly which degrade the performance.

Control Structure

Locking information will be stored in control structure.

Database buffer cache

The size of database buffer cache is defined by the parameter called db_cache_size. Database buffer cache hold the data which is fetch or read from the database files . The size of each buffer in the database buffer cache  is equal to the size of oracle block size .

Database buffer cache consist of two independent sub caches.

DB_KEEP_CACHE_SIZE

It will retain the block in the memory which are likely to be used.

DB_RECYCLE_CACHE_SIZE

It will eliminate the blocks from memory which are having little chances of being used .

Buffer modes

unused

The buffer is ready to use or available to use ,as it was never used .

Cleaned

The data has been written to database and available for use .

dirty

The data has been modified but not written to the disk .

redo log buffer

The size of redo log buffer is defined by parameter called log_buffer. It sequentially record all the changes made to database. log_buffer is a static parameter.

Java Pool

The size of java_pool is defined by the parameter called java_pool_size. If you want to execute java commands inside the database then java pool will be used. Whenever you run dbca,netca etc the memory is allocated from java pool .

Large pool

The size of large_pool is defined by the parameter called large_pool_size. Whenever a rman session is initiated the memory is allocated from large pool and once finished the memory is de allocated.

It does not follow LRU algorithm.

Memory allocated for the PGA (Program Global Area) is allocated at the start of the process server. It is reserved for each user process that connects to the Oracle database and is released at the end of the process

PGA

The PGA (Program or Process Global Area) is a memory area (RAM) that stores data and control information for a single process. For example, it typically contains a sort area, hash area, session cursor cache, etc.

Auto tuning

PGA areas can be sized manually by setting parameters like hash_area_size, sort_area_size etc.

To allow Oracle to auto tune the PGA areas, set the WORKAREA_SIZE_POLICY parameter to AUTO and the PGA_AGGREGATE_TARGET to the size of memory that can be used for PGA. This feature was introduced in Oracle 9i.

Oracle 11g allows users to tune both PGA and SGA areas with a single parameter, called MEMORY_TARGET.

Monitor

PGA usage statistics:
select * from v$pgastat;

Determine a good setting for pga_aggregate_target:
select * from v$pga_target_advice order by pga_target_for_estimate;

Show the maximum PGA usage per process:
select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

Background process : manage and implement the relationships between physical structures and memory structures.

There are two categories

•Mandatory background processes: DBWN, PMON, CKPT, LGWR, SMON

•Optional background process: ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn

These processes are mandatory and can be found in all typical database environment.

PMON – Process Monitor

Recover failed user process, Releasing resource, Rollback uncommitted Transaction

SMON – System Monitor

Instance recovery, Cleans and releases temporary segments

LGWR – Log Writer (Redo etc.)

Writes redo entries from redo log buffer to disk (Redo log file)

CKPT – Checkpoint

Ensures data consistency and easy database recovery in case of crash by synchronizing all the data file headers and control files with recent checkpoint information.

DBW0…j – DB Writer

Flushing or writing modi?ed dirty data (buffers) from database buffer cache to disks (datafiles). You can configure addition DB writer processes (up to 20) from DBW0-DBW9 and DBWa through DBWj.

RECO – Distributed Recovery

Recoverer is responsible for recovering failed distributed transactions in a distributed database.

MMON & MMNL – Manageability Monitor and Manageability Monitor Lite

MMON performs tasks related to AWR such as taking snapshots, capturing statistics value for recently modified SQL objects and writes when a metric violates its threshold value.

MMNL writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when the ASH buffer is full.

DIAG – Diagnostic Capture

Prior to Oracle database 11g, DIAG was used in RAC environment. Monitors overall health of the instance and performs diagnostic dumps requested by other processes and dumps triggered by process or instance termination.

Followings are optional and used by specific database feature.

ARCn – Archiver Process

Responsible for copying online redo log to archival storage before being reused. Runs only when database is in Archivelog Mode.

CJQ0 – Job Queue Coordinator Process

Job Queue coordinator is responsible for managing scheduled job processes within the database.

Dnnn – Dispatcher Process

Performs network communication in the shared server architecture

MMAN – Memory Manager

Responsible for managing instance memory based on the workloads.

PSP0 – Process Spawner Process

Spawns Oracle background processes whenever needed.

QMNC – AQ (Advanced Queuing) Coordinator Process

Facilitating various background activities required by AQ and Oracle Streams.

These are introduced in Oracle Database 11g. The first three is mandatory and others could be running depending upon the features being used.

DIA0 – Diagnostic

Responsible for detecting hangs and resolving deadlocks

GEN0 – General Task Execution Process

performs required tasks including SQL and DML

VKTM – Virtual Keeper of TiMe Process

Responsible for providing a wall-clock time (updated every second) and reference-time counter (updated every 20 ms) and available only when running at elevated priority.

DBRM – DataBase Resource Manager

Performs resource manager tasks setting resource plans.

FBDA – Flashback Data Archiver Process

Archives historical rows for tracked tables into flashback data archives and manages archive space, organization, and retention

SMCO – Space Management Coordinator

Coordinates the execution of various space management tasks

Wnnn – Space Management Slaves

Performs various background space management tasks, including proactive space allocation and space reclamation

RCBG – Result Cache BackGround

Handles result cache messages.

VKRM – Virtual Scheduler for Resource Manager Process

Schedules Resource Manager Activity. Serves as centralized scheduler for Resource Manager activity. VKRM manages the CPU scheduling for all managed Oracle processes.

User process : The program which interacts with the database by starting a connection. It communicates only with the process server.

Servers process : Represents the program that interact directly with the Oracle server. It responds to all requests and return results. It can be dedicated to a client    or a server shared by many.

Oracle database: An Oracle database consists of operating system files, also known as database files, that provide the actual physical storage for database information. The database files are used to ensure that the data is kept consistent and can be recovered in the event of a failure of the instance.

An Oracle Database :The general purpose of a database is to store and retrieve related information. An Oracle database has a logical and a physical structure. The physical structure of the database is the set of operating system files in the database.

An Oracle database consists of three file types:

Data files containing the actual data in the database

Redo logs containing a record of changes made to the database to enable recovery of the data in case of failures

Control files containing information necessary to maintain and verify database integrity

Other Key File Structures

The Oracle server also uses other files that are not part of the database:

• The parameter file defines the characteristics of an Oracle instance. For example, it contains parameters that size some of the memory structures in the SGA.

• The password file authenticates users privileged to start up and shut down an Oracle instance.

• Archived redo log files are offline copies of the redo log files that may be necessary to recover from media failures.

Physical Structure :

Other key files exist and are required to start up and use a database, for example: parameter files, configuration files, password files and so on. However, the physical structure of an Oracle database includes only three types of files: control files, data files, and redo log files.

Control File : Every Oracle Database has a control file, which is a small binary file that records the physical structure of the database. The control file includes:

The database name

Names and locations of associated datafiles and redo log files

The timestamp of the database creation

The current log sequence number

Checkpoint information

The control file must be available for writing by the Oracle Database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult.

The control file of an Oracle Database is created at the same time as the database. By default, at least one copy of the control file is created during database creation. On some operating systems the default is to create multiple copies. You should create two or more copies of the control file during database creation. You can also create control files later, if you lose control files or want to change particular settings in the control files.

Redo log files : The most crucial structure for recovery operations is the redo log, which consists of two or more preallocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.

Datafile : Database datafiles are physical files stored on disk. These files are used to store data on disk. Database datafiles are only written to by the DBWR processes that we introduced you to earlier (there is an exception or two to this statement, but for now, assume that this point this true).

These database datafiles are associated with Oracle tablespaces, which are logical containers for tables and indexes.

Oracle Parameter file Concepts

The parameter file (sometimes called init.ora) contains configuration information for the database to use at startup time. The parameter file you configure how much RAM the database is going to use, where to find the control files, where to write trace files, and a whole host of other information. In most cases the database will not start without a parameter file. Oracle allows you to have a manual parameter file (called a PFILE) or a server-side parameter file (called a SPFILE).

What is password file?

Password file : If the DBA wants to start up an Oracle instance there must be a way for Oracle to authenticate this DBA. That is if (s)he is allowed to do so. Obviously, his password cannot be stored in the database, because Oracle cannot access the database before the instance is started up. Therefore, the authentication of the DBA must happen outside of the database. There are two distinct mechanisms to authenticate the DBA: using the password file or through the operating system.

The init parameter remote_login_passwordfile specifies if a password file is used to authenticate the DBA or not. If it set either to shared or exclusive a password file will be used.

What is the difference between a PFILE and SPFILE:

A PFILE is a static, client-side text file that must be updated with a standard text editor like "notepad" or "vi". This file normally reside on the server, however, you need a local copy if you want to start Oracle from a remote machine. DBA's commonly refer to this file as the INIT.ORA file.

An SPFILE (Server Parameter File), on the other hand, is a persistent server-side binary file that can only be modified with the "ALTER SYSTEM SET" command. This means you no longer need a local copy of the pfile to start the database from a remote machine. Editing an SPFILE will corrupt it, and you will not be able to start your database anymore.

(FROM 10G)>> PFILE= If the database is running with pfile and we want to change parameter then the parameter will be change in only memory with the current instance not in the pfile.

Show parameter (name of the parameter)

Example: Sql>alter system set db_recovery_dest=’/u01/xyz’

SPFILE= (server parameter file) if the database is running with spfile and we want to change parameter then the parameter will be change in memory plus+ spfile (by default it changes in both memory and spfile).

Example :>sqlplus> alter system set db_recovery_dest=’/u02/oracle/xyz’ >>>>this change in both

alter system set db_recovery_dest=’/u02/oracle/xyz’ >>>>(scope=both,spfileor memory)

What is Schema?

A schema is the set of objects (tables, views, indexes, etc) that belongs to an user account. It is often used as another way to refer to an Oracle account.

The CREATE SCHEMA statement lets one specify (in a single SQL statement) all data and privilege definitions for a new schema. One can also add definitions to the schema later using DDL statements.

Cursor

A cursors is a pointer used to fetchrows from a result set. One can think of a cursor as a data structure that describes the results returned from a SQL SELECT statement. One of the variables in this structure is a pointer to the next record to be fetched from the query results.

Constraint

A constraint is a data rule or restriction that is enforced within the database rather than at application or object level.

There  are five types of constraints. they  are
•primary key
•unique key
•foreign key (references)
•check constraint
•NOT NULL

Coalesce

Coalesce is an SQL function that returns the first expression that is NOT NULL.

Meaning of coalesce (verb)
 to grow together; to merge; to unite

Block

A block can refer to:
•Data block - A data block is the smallest unit of storage in an Oracle database. Every database has a default block size, although blocks in different tablespaces may have different block sizes.
•Forms block - A Forms block is a container for items (eg. fields). Blocks can be related to tables in the database.

Byte

A byte is a series of 8 bits. Also called a character. Computer storage space is measured in bytes.

Unit 

Abbreviation

Value

A kilobyte

1 KB

1024 bytes

A megabyte

1 MB

1024 KB

A gigabyte

1 GB

1024 MB

A terabyte

1 TB

1024 GB

An exabyte

1 EB

1024 TB

Connect string

A connect string is a string used to identify and connect to a remote database. Connect strings are defined in a local TNSNAMES.ORA file, Oracle Names Server or OID directory.

When you connect, specify the username/password@connectstring

Data dictionary

A data dictionary is repository of metadata (information about data), such as its meaning, relationships to other data, origin, usage and format. The dictionary assists company management, database administrators, systems analysts and application programmers in effectively planning, controlling and evaluating the collection, storage and use of data.

Oracle's data dictionary is stored in the SYS schema.

Directory

The term directory can refer to:
•Operating system directory, a container or folder created with the "mkdir" command for organizing and storing files on a computer's hard disk.
•An Oracle directory, database object pointing to an operating system directory on the database server machine for reading and writing files. Also see UTL_FILE.
•Oracle Internet Directory (or OID), a standard LDAP directory server available with the Oracle Application Server

Flashback

Flashback is an Oracle database facility to quickly move an entire database or a table back to a prior state for recovery purposes. It can also be used to allow users to view data in a database "as it was", at a specified point in the past, even though committed transactions have since been applied.

History

Flashback was first introduced with Oracle 9i with Oracle's Flashback Query feature.

Oracle 10g added the following features:
•Flashback Database
•Flashback Drop
•Flashback Table
•Flashback Version Query
•Flashback Transaction Query

Oracle 11g added Flashback Transaction and Total Recall (or Flashback Data Archive).

Enable database flashback
shutdown immediate;
startup mount;
alter database archivelog;
alter database flashback on;
alter database open;
SELECT flashback_on, log_mode FROM v$database;

Function

A function is a block of PL/SQL code named and stored within the database. A function always returns a single value to its caller.

Granule

A granule is a unit of contiguous virtual memory allocated to a process.

Oracle instance

If an Oracle instance starts up, it allocates the SGA (System Global Area) in granule sized memory units. The size of these granules depends on your database version and somtimes even on the operating system port. For example, for Oracle 9i it is typically 4MB if your SGA is smaller than 128MB and 16MB if your SGA is bigger. For later releases it is typically 4MB if your SGA is smaller than 1GB and 16MB if your SGA is bigger. Issue the below query to get your instance's exact granule size:

SQL> SELECT * FROM v$sgainfo WHERE name = 'Granule Size';
NAME                                  BYTES RES
-------------------------------- ---------- ---
Granule Size                        4194304 No

What is index?

An  alphabetical listing of names and topics along with page numbers where they are discussed.

Index

An index is a special database object that lets you quickly locate particular records based on key column values. Indexes are essential for good database performance.

The index creation process requires a temporary segment if the data cannot be sorted in memory. Indexes can be created with the nologging option.

What is view?

In databasetheory, a view consists of a stored query accessible as a virtual table composed of the result set of a query. Unlike ordinary tables (base tables) in a relational database, a view does not form part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database. Changing the data in a table alters the data shown in subsequent invocations of the view.

What is table?

A  set of data arranged in rows and columns.

What is object?

An object in image processing is an identifiable portion of an image that can be interpreted as a single unit.

What is listerner?

The Listener is a named process which runs on the Oracle Server, awaiting requests from Clients to connect to the Instance.By default, the Listener name is (amazingly enough)“Listener” (but you can call it anything you like).It listens for connection requests on a particular port (the default port number in 8.0 and above is 1521, but once again you can set this to any valid port number if you wish).A client knows where to contact the Listener (the machine it’s running on, and the port it’s listening on) because a local configuration file, called “tnsnames.ora”, gives it the necessary information. More advanced configurations can dispense with the tnsnames.ora (for example, you can opt to install a “Names Server”, which does the same job of telling the client where to find the

 Listener).

What is oracle SID?

The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system.

When using RAC, all instances belonging to the same database must have unique SID's.

What is ORACLE BASE?

ORACLE_BASE is an environment variable used as base directory for an OFA(optimal flexible architecture) installation. On Unix systems, this is usually /app/oracle or /u01/app/oracle.

What is ORACLE HOME?

ORACLE_HOME refers to either:

a directory where the Oracle software is installed; or an environment variable pointing to the directory where the Oracle software is installed.

What is data block?

At the finest level of granularity(pieces join to make one component), Oracle stores data in data blocks (also called logical blocks, Oracle blocks, or pages). One data block corresponds to a specific number of bytes of physical database space on disk.

What is extent?

An extent is a contiguous set (side-by-side) of Oracle data blocks allocated to a segment within a tablespace.

The size of an extent is controlled by storage parameters used when you CREATE or ALTER the segment (INITIAL, NEXT and PCT_INCREASE) and tablespace parameters.

What is Segment?

A  segment is a database object that has space allocated to it - for example, a table, index, materialized view, etc. A segment consists of one or more extents allocated within a tablespace.

What is tablespace?

A tablespace is a storage location where the actual data underlying database objects can be kept. It is the physical portion of the database used to allocate storage for all DBMS(database management system) managed segments. ...

What Is a Dirty Block?

Whenever a server process changes or modifies a data block, it becomes a dirty block. Once a server process makes changes to the data block, the user may commit transactions, or transactions may not be committed for quite some time. In either case, the dirty block is not immediately written back to disk.

Writing dirty blocks to disk takes place under the following two conditions:

* When a server process cannot find a clean, reusable buffer after scanning a threshold number of buffers, then the database writer process writes the dirty blocks to disk.

* When the checkpoint takes place, the database writer process writes the dirty blocks to disk.

What is Program Global Area (PGA)

A Program Global Area (PGA) is a memory region that contains data and control information for a server process. It is a non-shared memory region created by Oracle when a server process is started. Access to the PGA is exclusive to that server process and it is read and written only by Oracle code acting on its behalf. It contains a private SQL area and a session memory area.

A private SQL area contains data such as bind information and runtime memory structures. Each session that issues a SQL statement has a private SQL area. Session memory is the memory allocated to hold a session�s variables (logon information) and other information related to the session. For a shared server, the session memory is shared and not private.

LRU

LRU (Least Recently Used) is an algorithm Oracle uses when it needs to make room for new information in the memory space allocated. It replaces the oldest (LRU) data to make room for new data.

 Latch

A latch is an internal Oracle mechanism used to protect data structures in the SGA from simultaneous access. Atomic hardware instructions like TEST-AND-SET are used to implement latches. Latches are more restrictive than locks in that they are always exclusive. Latches are never queued, but will spin or sleep until it obtains a resource or times out. Latches are important for performance tuning.

Lock

Database locks are used to provide concurrency control. Locks are typically acquired at row or table level. Common database lock types are: Shared, eXclusive, Row Share, Row eXclusive, etc.

Common uses of locks are:
•ensure that only one user can modify a record at a time;
•ensure that a table cannot be dropped if another user is querying it;
•ensure that one user cannot delete a record while another is updating it.

Listener

The Oracle Listener is a process listening for incoming database connections. This process is only needed on the database server side. The listener is controlled via the lsnrctl utility. Configuration is done via the listener.ora file.

Logminer

Oracle LogMiner is an Oracle database facility that allows data in redo log files to be extracted and analysed.

LogMiner is typically used to:
•Track database changes for audit purposes
•Undo user errors or logical corruption

After mining the logs, query the V$LOGMNR_CONTENTS view to list all logical change operation conducted on the database. The rows will contain both redo SQL and undo SQL.

Metadata

Metadata is data that is used to describe other data. Data definitions are sometimes referred to as metadata. Examples of metadata include schema, table, index, view and column definitions.

Merge

MERGE (sometimes also called UPSERT) is a SQL command that performs a series of conditional update and/or insert operations. A row is updated if it exists, inserted if it doesn't. Merge was first introduced with Oracle 9i.

Materialized View

A materialized view (MV) is similar to a view but the data is actually stored on disk (view that materializes). Materialized views are often used for summary and pre-joined tables, or just to make a snapshot of a table available on a remote system. A MV must be refreshed when the data in the underlying tables is changed.

MMON

MMON (Memory Monitor) is a background process that gathers memory statistics (snapshots) stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

This process was introduced in Oracle 10g.

MMAN

MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.

This process was introduced in Oracle 10g.

MMNL

MMNL (MMON Lite) is a background process that assists the MMON process. This process will flush the ASH buffer to AWR tables when the buffer is full or a snapshot is taken.

Transaction

Logical unit of work that contains one or more SQL statements. All statements in a transaction are committed or rolled back together.

Trigger

Stored database procedure automatically invoked whenever a table or view ismodified, for example by INSERT, UPDATE, or DELETE operations

view

A view is a custom-tailored presentation of the data in one or more tables. A view can also be thought of as a "stored query." Views do not actually contain or store data; they derive their data from the tables on which they are based. Like tables, views can be queried, updated, inserted into, and deleted from, with some restrictions. All operations performed on a view affect the base tables of the view.

buffer cache

The database buffer cache is the portion of the SGAthat holds copies of data blocks read from data files. All user processes concurrently connected to the instance share access to the database buffer cache.

commit

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.

database buffer

One of several types of memory structures that stores information within the system global area. Database buffers store the most recently used blocks of data.

Database buffer cache

Memory structure in the system global area that stores the most recently used blocks of data.

Database link

A named schema object that describes a path from one database to another. Database links are implicitly used when a reference is made to a global object name in a distributed database.

DBTZ

Database time zone.

SDTZ

Current session time zone.

Dedicated server

A database server configuration in which a server process handles requests for a single user process.

row

Set of attributes or values pertaining to one entity or record in a table. A row is a collection of column information corresponding to a single record.

ROWID

A globally unique identifier for a row in a database. It is created at the time the row is inserted into a table, and destroyed when it is removed from a table.

schema

Collection of database objects, including logical structures such as tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links. A schema has the name of the user who controls it.

synonym

An alias for a table, view, sequence, or program unit.

PCTFREE is a block storage parameter used to specify how much space should be left in a database block for future updates. For example, for PCTFREE=10, Oracle will keep on adding new rows to a block until it is 90% full. This leaves 10% for future updates (row expansion).

When using Oracle Advanced Compression, Oracle will trigger block compression when the PCTFREE is reached. This eliminates holes created by row deletions and maximizes contiguous free space in blocks.

Monitor

See the PCTFREE setting for a table:
SQL> SELECT pct_free FROM user_tables WHERE table_name = 'EMP';
  PCT_FREE
----------
        10

PCTINCREASE

PCTINCREASE refers to the percentage by which each next extent (beginning with the third extend) will grow. The size of each subsequent extent is equal to the size of the previous extent plus this percentage increase.

This parameter is only relevant when using old Dictionary Managed tablespaces.

PCTUSED

PCTUSED is a block storage parameter used to specify when Oracle should consider a database block to be empty enough to be added to the freelist. Oracle will only insert new rows in blocks that is enqueued on the freelist. For example, if PCTUSED=40, Oracle will not add new rows to the block unless sufficient rows are deleted from the block so that it falls below 40% empty.

This parameter is ignored for objects created in locally managed tablespaces with Segment Space Management (ASSM) specified as AUTO.

Package

A package is a collection of procedures and functions stored within the database.

A package usually has a specification and a body stored separately in the database. The specification is the interface to the application and declares types, variables, exceptions, cursors and subprograms. The body implements the specification.

When a procedure or function within the package is referenced, the whole package gets loaded into memory. So when you reference another procedure or function within the package, it is already in memory.

Parse tree

A Parse tree is a parsed representation of the grammar of a computer language. This parsed representation is stored in a tree structure.

For example, the grammar of a SQL statement must be parsed into a parse tree before it can be understood and executed by a computer.

Privilege

A privilege is a special right or permission granted to a user or a role to perform specific actions. Granted privileges can be revoked when necessary. For example, one must grant the CREATE SESSION privilege to a database user before that user is allowed to login. Likewise, the CREATE TABLE privilege is required before a user can create new database tables. •role - grouping of privileges

Procedure

A procedure is a block of PL/SQL code named and stored within the database.

Process

A process is a running program. It consists of a virtual memory address space, state (registers, stack, variables, etc.), program code, and other operating system resources.

Query

A query is a SQL SELECT statement that reads data from tables and views within the database, and returns the result set to the requester. Queries cannot change data within the database, use DML statements if you need to make changes.
•Select - statement used for querying the database.
•Join - query data from several tables.
•Subquery - SELECT statement in the WHERE- or HAVING-clause of another SELECT statement.
•Inline view - SELECT statement in the FROM-clause of another SELECT statement.

ROWID

Every record has a unique ROWID within a database representing the physical location on disk where the record lives.

From Oracle 8 the ROWID format and size changed from 8 to 10 bytes. Note that ROWID's will change when you reorganize or export/import a table. In case of a partitioned table, it also changes if the row migrates from a partition to another one during an UPDATE.

Restore

Restore is the action of getting data back to a prior consistent state from a prior taken backup. After a restore the database may need to recovered to roll it forward in time to a particular point oin time.

RMAN

RMAN (Recovery Manager) is a utility used to backup, restore, recover and clone Oracle databases.

Role

A role is a named list or group of privileges that are collected together and granted to users or other roles.

Rollback Segment

A Rollback Segment is a database object containing before-images of data written to the database.

Rollback segments are used to:
•Undo changes when a transaction is rolled back
•Ensure other transactions do not see uncommitted changes made to the database
•Recover the database to a consistent state in case of failures

Instead of creating Rollback Segments, users running Oracle 10g or greater should use Undo Segments instead.

Row

A row is a component of a relational table. In nonrelational terms, a row is called a record. A row is not named (in contrast to a column). Each row in a table has one value in each column of the table.

Schemas and Schema Objects

A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are the logical structures that directly refer to the database’s data. Schema objects include structures like tables, views, and indexes. (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.)

Tables 

Tables are the basic unit of data storage in an Oracle database. Database tables hold all user-accessible data. Each table has columns and rows. Oracle stores each row of a database table containing data for less than 256 columns as one or more row pieces. A table that has an employee database, for example, can have a column called employee number, and each row in that column is an employee’s number.

PL/SQL Overview

PL/SQL is Oracle’s procedural language extension to 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.When designing a database application, consider the following advantages of using stored PL/SQL:

PL/SQL code can be stored centrally in a database. Network traffic between applications and the database is reduced, so application and system performance increases. Even when PL/SQL is not stored in the database, applications can send blocks of PL/SQL to the database rather than individual SQL statements, thereby reducing network traffic.

Data access can be controlled by stored PL/SQL code. In this case, PL/SQL users can access data only as intended by application developers, unless another access route is granted.

PL/SQL blocks can be sent by an application to a database, running complex operations without excessive network traffic.

The following sections describe the PL/SQL program units that can be defined and stored centrally in a database.

PL/SQL Program Units

Program units are stored procedures, functions, packages, triggers, and anonymous

Transactions.

Procedures and Functions Procedures and functions are sets of SQL and PL/SQL statements grouped together as a unit to solve a specific problem or to perform a set of related tasks. They are created and stored in compiled form in the database and can be run by a user or a database application. Procedures and functions are identical, except that functions always return a single value to the user. Procedures do not return values.

Packages :Packages encapsulate and store related procedures, functions, variables, and other constructs together as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).

Database Triggers: Database triggers are PL/SQL, Java, or C procedures that run implicitly whenever a table or view is modified or when some user actions or database system actions occur. Database triggers can be used in a variety of ways for managing your database. For example, they can automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.

Autonomous Blocks :You can call autonomous transactions from within a PL/SQL block. When an autonomous PL/SQL block is entered, the transaction context of the caller is suspended. This operation ensures that SQL operations performed in this block (or other blocks called from it) have no dependence or effect on the state of the caller’s transaction context.

XML Overview

XML, eXtensible Markup Language, is the standard way to identify and describe data on the Web. It is a human-readable, machine-understandable, general syntax for describing hierarchical data, applicable to a wide range of applications, databases, e-commerce, Java, web development, searching, and so on.

The Oracle server includes the Oracle XML DB, a set of built-in high-performance

XML storage and retrieval technologies. The XML DB fully absorbs the W3C XML data model into the Oracle server and provides new standard access methods for navigating and querying XML. You get all the advantages of relational database technology and XML technology at the same time. Key aspects of the XML database include the following:

Process Architecture

A process is a "thread of control" or a mechanism in an operating system that can run a series of steps. Some operating systems use the terms job or task. A process generally has its own private memory area in which it runs. An Oracle server has two general types of processes: user processes and Oracle processes.

User (Client) Processes

User processes are created and maintained to run the software code of an application program (such as a Pro*C/C++ program) or an Oracle tool (such asEnterprise Manager). User processes also manage communication with the server process through the program interface, which is described in a later section.

Oracle Processes

Oracle processes are invoked by other processes to perform functions on behalf of the invoking process. The different types of Oracle processes and their specific functions are discussed in the following sections.

Server Processes Oracle creates server processes to handle requests from connected user processes. A server process communicates with the user process and interacts with Oracle to carry out requests from the associated user process. For example, if a user queries some data not already in the database buffers of the SGA, then the associated server process reads the proper data blocks from the datafiles into the SGA.

Privileges

A privilege is a right to run a particular type of SQL statement. Some examples of privileges include the right to: Connect to the database (create a session) Create a table in your schema Select rows from someone else’s table Execute someone else’s stored procedure

The privileges of an Oracle database can be divided into two categories: system

privileges and schema object privileges.

System Privileges System privileges allow users to perform a particular system wide action or a particular action on a particular type of schema object. For example, the privileges to create a tablespace or to delete the rows of any table in the database are system privileges. Many system privileges are available only to administrators and application developers because the privileges are very powerful.

Schema Object Privileges Schema object privileges allow users to perform a particular action on a specific schema object. For example, the privilege to delete rows of a specific table is an object privilege. Object privileges are granted (assigned) to users so that they can use a database application to accomplish specific tasks.

Roles

Oracle provides for easy and controlled privilege management through roles. Roles are named groups of related privileges that you grant to users or other roles.

Storage Settings and Quotas

Oracle provides a way to direct and limit the use of disk space allocated to the database for each user, including default and temporary tablespaces and tablespace quotas. Default Tablespace Each user is associated with a default tablespace. When a user creates a table, index, or cluster and no tablespace is specified to physically contain the schema object, the user’s default tablespace is used if the user has the privilege to create the schema object and a quota in the specified default tablespace. The default tablespace feature provides Oracle with information to direct space use in situations where schema object’s location is not specified.

Temporary Tablespace Each user has a temporary tablespace. When a user runs a SQL statement that requires the creation of temporary segments (such as the creation of an index), the user’s temporary tablespace is used. 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. Tablespace Quotas Oracle can limit the collective amount of disk space available to the objects in a schema. Quotas (space limits) can be set for each tablespace available to a user. The tablespace quota security feature permits selective control over the amount of disk space that can be consumed by the objects of specific schemas.

Profiles and Resource Limits

Each user is assigned a profile that specifies limitations on several system resources available to the user, including the following: Number of concurrent sessions the user can establish CPU processing time available for: The user’s session A single call to Oracle made by a SQL statement Amount of logical I/O available for: The user’s session A single call to Oracle made by a SQL statement Amount of idle time available for the user’s session, Amount of connect time available for the user’s session Password restrictions: Account locking after multiple unsuccessful login attempts Password expiration and grace period Password reuse and complexity restrictions

Different profiles can be created and assigned individually to each user of the database. A default profile is present for all users not explicitly assigned a profile.

The resource limit feature prevents excessive consumption of global database system resources.

Transparent Application Failover

Transparent Application Failover (TAF) enables an application user to automatically reconnect to a database if the connection fails. Active transactions rollback, but the new database connection, made by way of a different node, is identical to the original. This is true regardless of how the connection fails.

With Transparent Application Failover, a client notices no loss of connection as long as there is one instance left serving the application. The database administrator controls which applications run on which instances and also creates a failover order for each application.

Introduction to the Data Dictionary

One of the most important parts of an Oracle database is its data dictionary, which is a read-only set of tables that provides information about the database. A data dictionary contains:

_ The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on)

_ How much space has been allocated for, and is currently used by, the schema objects

_ Default values for columns

_ Integrity constraint information

_ The names of Oracle users

_ Privileges and roles each user has been granted

_ Auditing information, such as who has accessed or updated various schema objects

_ Other general database information

The data dictionary is structured in tables and views, just like other database data.

All the data dictionary tables and views for a given database are stored in that database’s SYSTEM tablespace. Not only is the data dictionary central to every Oracle database, it is an important tool for all users, from end users to application designers and database administrators. Use SQL statements to access the data dictionary. Because the data dictionary is read-only, you can issue only queries (SELECT statements) against it’s tables and views.

Structure of the Data Dictionary

The data dictionary consists of the following:

Base Tables

The underlying tables that store information about the associated database. Only Oracle should write to and read these tables. Users rarely access them directly because they are normalized, and most of the data is stored in a cryptic format.

User-Accessible Views

The views that summarize and display the information stored in the base tables of the data dictionary. These views decode the base table data into useful information, such as user or table names, using joins and WHERE clauses to simplify the information. Most users are given access to the views rather than the base tables. SYS, Owner of the Data Dictionary

The Oracle user SYS owns all base tables and user-accessible views of the data

dictionary. No Oracle user should ever alter (UPDATE, DELETE, or INSERT) any rows or schema objects contained in the SYS schema, because such activity can compromise data integrity. The security administrator must keep strict control of this central account.

Caution: Altering or manipulating the data in data dictionary tables can permanently and detrimentally affect the operation of a database.

How the Data Dictionary Is Used

The data dictionary has three primary uses:

Oracle accesses the data dictionary to find information about users, schema objects, and storage structures.

_ Oracle modifies the data dictionary every time that a data definition language (DDL) statement is issued.

_ Any Oracle user can use the data dictionary as a read-only reference for information about the database.

Caution: No data in any data dictionary table should be altered or deleted by any user.

The DUAL Table

The table named DUAL is a small table in the data dictionary that Oracle and user-written programs can reference to guarantee a known result. This table has one column called DUMMY and one row containing the value X.

Transaction Control Statements

Transaction control statements manage the changes made by DML statements and group DML statements into transactions. They enable you to:

_ Make a transaction’s changes permanent (COMMIT)

_ Undo the changes in a transaction, either since the transaction started or since a savepoint (ROLLBACK)

_ Set a point to which you can roll back (SAVEPOINT)

_ Establish properties for a transaction (SET TRANSACTION)

Parsing

Parsing is one stage in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to Oracle. During the parse call, Oracle:

_ Checks the statement for syntactic and semantic validity

_ Determines whether the process issuing the statement has privileges to run it

_ Allocates a private SQL area for the statements