What is Oracle Database

The Oracle Database (commonly referred to as Oracle RDBMS or simply Oracle) consists of a relational database management system (RDBMS) produced and marketed by Oracle Corporation. As of 2009[update], Oracle remains a major presence in database computing.[1]

Larry Ellison and his friends and former co-workers Bob Miner and Ed Oates started the consultancy Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software. The name Oracle comes from the code-name of a CIA-funded project Ellison had worked on while previously employed by

Sql

Structured Query Language (SQL) is a specialized language for updating, deleting, and requesting information from databases. SQL is an ANSI and ISO standard, and is the de facto standard database query language. A variety of established database products support SQL, including products from Oracle and Microsoft SQL Server. It is widely used in both industry and academia, often for enormous, complex databases.

In a distributed database system, a program often referred to as the database's "back end" runs constantly on a server, interpreting data files on the server as a standard relational database. Programs on client computers allow users to manipulate that data, using tables, columns, rows, and fields. To do this, client programs send SQL statements to the server. The server then processes these statements and returns replies to the client program.

Create table In Oracle

Sql Syntex

1. SQL Syntax Introduction
2. CREATE TABLE syntax
3. ALTER TABLE syntax
4. DROP TABLE syntax
5. CREATE VIEW syntax
6. DROP VIEW syntax
7. CREATE SEQUENCE syntax
8. DROP SEQUENCE syntax
9. COMPACT TABLE syntax
10. CREATE SCHEMA syntax
11. DROP SCHEMA syntax
12. INSERT syntax
13. DELETE syntax
14. UPDATE syntax
15. SELECT syntax
16. COMMIT and ROLLBACK syntax
17. CREATE USER, ALTER USER and DROP USER syntax
18. GRANT/REVOKE syntax
19. SET syntax
20. DESCRIBE syntax
21. SHOW syntax
22. SHUTDOWN syntax


1. SQL Syntax Introduction

This section is a reference for the SQL grammar that Mckoi SQL Database supports. Mckoi SQL Database supports a subset of entry level ANSI SQL-92. This section is not intended to be a tutorial for learning SQL. For SQL books and online tutorials see the links section on the home page.



2. CREATE TABLE syntax

CREATE TABLE [ IF NOT EXISTS ] table_name
( column_declare1, column_declare2, constraint_declare1, ... )


column_declare ::= column_name type [ DEFAULT expression ]
[ NULL | NOT NULL ] [ INDEX_BLIST | INDEX_NONE ]

type ::= BIT | REAL | CHAR | TEXT | DATE | TIME |
FLOAT | BIGINT | DOUBLE | STRING | BINARY | NUMERIC |
DECIMAL | BOOLEAN | TINYINT | INTEGER | VARCHAR |
SMALLINT | VARBINARY | TIMESTAMP | LONGVARCHAR |
JAVA_OBJECT | LONGVARBINARY

constraint_declare :: = [ CONSTRAINT constraint_name ]
PRIMARY KEY ( col1, col2, ... ) |
FOREIGN KEY ( col1, col2, ... ) REFERENCES f_table [ ( col1, col2, ... ) ]
[ ON UPDATE triggered_action ] [ ON DELETE triggered_action ] |
UNIQUE ( col1, col2, ... ) |
CHECK ( expression )
[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
[ NOT DEFERRABLE | DEFERRABLE ]

triggered_action :: =
NO ACTION | SET NULL | SET DEFAULT | CASCADE


When declaring string or binary column types the maximum size must be specified. The following example declares a string column that can grow to a maximum of 100 characters,

CREATE TABLE Table ( str_col VARCHAR(100) )

When handling strings the database will only allocate as much storage space as the string uses up. If a 10 character string is stored in str_col then only space for 10 characters will be allocated in the database. So if you need a column that can store a string of any size, use an arbitrarily large number when declaring the column. Mckoi SQL Database does not use a fixed size storage mechanism when storing variable length column data.

JAVA_OBJECT is a column type that can contain serializable Java objects. The JAVA_OBJECT type has an optional Java class definition that is used for runtime class constraint checking. The following example demonstrates creating a JAVA_OBJECT column.

CREATE TABLE ObjectTable (
obj_id NUMERIC, obj JAVA_OBJECT(java.awt.Point))

If the Java class is not specified the column defaults to java.lang.Object which effectively means any type of serializable Java object can be kept in the column.

String types may have a COLLATE clause that changes the collation ordering of the string based on a language. For example, the folling statement creates a string that can store and order Japanese text;

CREATE TABLE InternationalTable (
japanese_text VARCHAR(4000) COLLATE 'jaJP')

The 'jaJP' is an ISO localization code for the Japanese language in Japan. Other locale codes can be found in the documentation to java.text.Collate.

Unique, primary/foreign key and check integrity constraints can be defined in the CREATE TABLE statement. The following is an example of defining a table with integrity constraints.

CREATE TABLE Customer (
number VARCHAR(40) NOT NULL,
name VARCHAR(100) NOT NULL,
ssn VARCHAR(50) NOT NULL,
age INTEGER NOT NULL,

CONSTRAINT cust_pk PRIMARY KEY (number),
UNIQUE ( ssn ), // (An anonymous constraint)
CONSTRAINT age_check CHECK (age >= 0 AND age < col_name1 =" expression1," col_name2 =" expression2," col1 =" 10," col2 =" 4" col3 =" CONCAT(col1," col_name1 =" expression1," col_name2 =" expression2," salary =" salary" name =" 'Bob'" id =" id" part =" CONCAT(part,"> 5
ORDER BY number DESC

The ORDER BY and GROUP BY clause may refer to a column, a column alias, or an expression. The HAVING clause is evaluated after the grouping and aggregate columns have been resolved.

For examples of using SELECT with aggregate functions see the 'Internal SQL Functions' section.



16. COMMIT and ROLLBACK syntax

COMMIT
ROLLBACK

Transactional operations for closing a transaction and either committing all the changes made or rolling back and disposing all changes. COMMIT may cause a concurrent transaction conflict exception to be thrown. If a conflict is detected the transaction is automatically rolled back. See the 'Transactions' section of the documentation for further details of how Mckoi handles transactions.



17. CREATE USER, ALTER USER and DROP USER syntax

CREATE USER username SET PASSWORD 'password'
[ SET GROUPS groups_list ]
[ SET ACCOUNT ( LOCK | UNLOCK ) ]

ALTER USER username SET PASSWORD 'password'
[ SET GROUPS groups_list ]
[ SET ACCOUNT ( LOCK | UNLOCK ) ]

DROP USER username

These are user management commands for creating/altering and dropping users in the system. Only members of the 'secure access' group are permitted to perform these operations, which includes the administrator user that is setup when the Mckoi database is created.

The following example creates a user called 'harry' with the password 'cat';

CREATE USER harry SET PASSWORD 'cat'

See the 'JDBC Driver' section for documentation on connecting to a database using a username and password to connect to a database.



18. GRANT/REVOKE syntax

GRANT privileges ON database_object TO ( PUBLIC | user_list )
[ WITH GRANT OPTION ]

REVOKE [ GRANT OPTION FOR ] privileges ON database_object
FROM ( PUBLIC | user_list )

privileges ::= priv_item1, priv_item2, ...

priv_item ::= ALL [ PRIVILEGES ] | SELECT | INSERT | UPDATE |
DELETE | REFERENCES | USAGE

database_object ::= [ TABLE ] table_name | SCHEMA schema_name

user_list ::= PUBLIC | username1, username2, ...

Grants or revokes types of access on a table or view to a user. When a table or view is created the system gives full grant options to the user that created the object. The user is given the option to grant other users selective access to the object through the GRANT and REVOKE syntax. For example, the follow statement shows how a user would grant user 'toby' permission to SELECT from a table called MyTable;

GRANT SELECT ON TABLE MyTable TO toby

The GRANT command allows granting all users access to an object. The following statement makes MyTable globally readable;

GRANT SELECT ON TABLE MyTable TO PUBLIC

If you wish to give a user the option of granting a privilege to another user, add WITH GRANT OPTION to the GRANT statement.



19. SET syntax

SET variable = expression
SET AUTO COMMIT ( ON | OFF )
SET TRANSACTION ISOLATION LEVEL ( SERIALIZABLE )
SET SCHEMA schema_name

Makes a change to the state of the connection. SET AUTO COMMIT is used to switch transaction 'auto commit mode' on or off. When auto commit mode is on the engine commits after every statement. By default, a connection starts with auto commit mode switched on. SET TRANSACTION ISOLATION LEVEL currently only supports the SERIALIZABLE isolation level. See the 'Transactions' section of the documentation for details of how Mckoi handles transactions.

SET SCHEMA is used to change the default schema of a connection.



20. DESCRIBE syntax

DESCRIBE table_name

This command provides information about the columns of the table. It shows the column names, the type / size and scale (if applicable) and other useful information.



21. SHOW syntax

SHOW engine_variable

engine_variable ::= TABLES | SCHEMA | STATUS | CONNECTIONS

Shows internal information about the database system. SHOW TABLES returns a list of tables in the database. SHOW STATUS returns debugging and statistical information about the internal state of the database engine. SHOW CONNECTIONS returns a snapshot of the current connections on the database. SHOW SCHEMA lists all the schema defined.



22. SHUTDOWN syntax

SHUTDOWN

Shuts down the database. If the database is running as a server the database shuts down cleanly and the process is stopped. If the database is embedded in a Java application it is cleanly put into a shut down state.

Only a user with the correct grants may successfully execute this command.

Oracle Architecture

---

New Term: The Oracle Relational Database Management System, or RDBMS, is designed to allow simultaneous access to large amounts of stored information. The RDBMS consists of the database (the information) and the instance (the embodiment of the system). The database contains the physical files that reside on the system and the logical pieces such as the database schema. These database files take various forms, as described in the following section. The instance is the method used to access the data and consists of processes and system memory.

---

NOTE: Object extensions have been added to the RDBMS with Oracle8. The object extension to tables is covered in detail on Day 12, "Working with Tables, Views, and Synonyms." Oracle refers to Oracle8 as an O-RDBMS (Object-Relational Database Management System). In this book, I refer to Oracle as an RDBMS for clarity.

---

The Database

The Oracle database has a logical layer and a physical layer. The physical layer consists of the files that reside on the disk; the components of the logical layer map the data to these physical components.

The Physical Layer

The physical layer of the database consists of three types of files:

• One or more datafiles--Datafiles store the information contained in the database. You can have as few as one datafile or as many as hundreds of datafiles. The information for a single table can span many datafiles or many tables can share a set of datafiles. Spreading tablespaces over many datafiles can have a significant positive effect on performance. The number of datafiles that can be configured is limited by the Oracle parameter MAXDATAFILES.
• Two or more redo log files--Redo log files hold information used for recovery in the event of a system failure. Redo log files, known as the redo log, store a log of all changes made to the database. This information is used in the event of a system failure to reapply changes that have been made and committed but that might not have been made to the datafiles. The redo log files must perform well and be protected against hardware failures (through software or hardware fault tolerance). If redo log information is lost, you cannot recover the system.
• One or more control files--Control files contain information used to start an instance, such as the location of datafiles and redo log files; Oracle needs this information to start the database instance. Control files must be protected. Oracle provides a mechanism for storing multiple copies of control files.

The Logical Layer

The logical layer of the database consists of the following elements:

• One or more tablespaces.

• The database schema, which consists of items such as tables, clusters, indexes, views, stored procedures, database triggers, sequences, and so on.

Tablespaces and Datafiles

New Term: The database is divided into one or more logical pieces known as tablespaces. A tablespace is used to logically group data together. For example, you can create one tablespace for accounting and a separate tablespace for purchasing. Segmenting groups into different tablespaces simplifies the administration of these groups (see Figure 2.1). Tablespaces consist of one or more datafiles. By using more than one datafile per tablespace, you can spread data over many different disks to distribute the I/O load and improve performance.

The relationship between the database, tablespaces, and datafiles.

As part of the process of creating the database, Oracle automatically creates the SYSTEM tablespace for you. Although a small database can fit within the SYSTEM tablespace, it's recommended that you create a separate tablespace for user data. The SYSTEM tablespace is where the data dictionary is kept. The data dictionary contains information about tables, indexes, clusters, and so on.

Datafiles can be operating system files or, in the case of some operating systems, RAW devices. Datafiles and data access methods are described in detail on Day 12.

The Database Schema

New Term: The database schema is a collection of logical-structure objects, known as schema objects, that define how you see the database's data. These schema objects consist of structures such as tables, clusters, indexes, views, stored procedures, database triggers, and sequences.

• Table--A table, which consists of a tablename and rows and columns of data, is the basic logical storage unit in the Oracle database. Columns are defined by name and data type. A table is stored within a tablespace; often, many tables share a tablespace.
• Cluster--A cluster is a set of tables physically stored together as one table that shares a common column. If data in two or more tables is frequently retrieved together based on data in the common column, using a clustered table can be quite efficient. Tables can be accessed separately even though they are part of a clustered table. Because of the structure of the cluster, related data requires much less I/O overhead if accessed simultaneously.
• Index--An index is a structure created to help retrieve data more quickly and efficiently (just as the index in this book allows you to find a particular section more quickly). An index is declared on a column or set of columns. Access to the table based on the value of the indexed column(s) (as in a WHERE clause) will use the index to locate the table data.

---

NOTE: A new feature in Oracle8 is the index-only table. In an index-only table, the data and index are stored together. This is discussed in detail on Day 13, "Using Indexes and Sequences."

---

• View--A view is a window into one or more tables. A view does not store any data; it presents table data. A view can be queried, updated, and deleted as a table without restriction. Views are typically used to simplify the user's perception of data access by providing limited information from one table, or a set of information from several tables transparently. Views can also be used to prevent some data from being accessed by the user or to create a join from multiple tables.
• Stored procedure--A stored procedure is a predefined SQL query that is stored in the data dictionary. Stored procedures are designed to allow more efficient queries. Using stored procedures, you can reduce the amount of information that must be passed to the RDBMS and thus reduce network traffic and improve performance.
• Database trigger--A database trigger is a procedure that is run automatically when an event occurs. This procedure, which is defined by the administrator or developer, triggers, or is run whenever this event occurs. This procedure could be an insert, a deletion, or even a selection of data from a table.
• Sequence--The Oracle sequence generator is used to automatically generate a unique sequence of numbers in cache. By using the sequence generator you can avoid the steps necessary to create this sequence on your own such as locking the record that has the last value of the sequence, generating a new value, and then unlocking the record.

Segments, Extents, and Data Blocks

Within Oracle, the space used to store data is controlled by the use of logical structures. These structures consist of the following:

• Data blocks--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.
• Extents--Extents consist of data blocks.
• Segments--A segment is a set of extents used to store a particular type of data, as shown in Figure 2.2.

Segments, extents, and data blocks.

Segments

An Oracle database can use four types of segments:

• Data segment--Stores user data within the database.
• Index segment--Stores indexes.
• Rollback segment--Stores rollback information used when data must be rolled back.
• Temporary segment--Created when a SQL statement needs a temporary work area; these segments are destroyed when the SQL statement is finished. These segments are used during various database operations, such as sorts.

Extents

Extents are the building blocks of segments; in turn, they consist of data blocks. An extent is used to minimize the amount of wasted (empty) storage. As more and more data is entered into tablespaces in your database, the extents used to store that data can grow or shrink as necessary. In this manner, many tablespaces can share the same storage space without preallocating the divisions between those tablespaces.

At tablespace-creation time, you can specify the minimum number of extents to allocate as well as the number of extents to add at a time when that allocation has been used. This arrangement gives you efficient control over the space used in your database.

Data Blocks

Data blocks are the smallest pieces of an Oracle database; they are physically stored on disk. Although the data block in most systems is 2KB (2,048 bytes), you can change this size for efficiency depending on your application or operating system.

---

NOTE: Oracle blocks do not need to be, and may not be the same as, operating system data blocks. In fact, in most cases they are not.

---

The Oracle Instance

The Oracle instance consists of the Oracle processes and shared memory necessary to access information in the database. The instance is made up of the user processes, the Oracle background processes, and the shared memory used by these processes (see Figure 2.3).

The Oracle Memory Structure

New Term: Oracle uses shared memory for several purposes, including caching of data and indexes as well as storing shared program code. This shared memory is broken into various pieces, or memory structures. The basic memory structures associated with Oracle are the System Global Area (SGA) and the Program Global Area (PGA).

The Oracle instance.

The 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 and deallocated when the Oracle instance shuts down. Each Oracle instance that starts has its own SGA. The information in the SGA consists of the following elements, each of which has a fixed size and is created at instance startup:

The database buffer cache--This stores the most recently used data blocks. These blocks can contain modified data that has not yet been written to disk (sometimes known as dirty blocks), blocks that have not been modified, or blocks that have been written to disk since modification (sometimes known as clean blocks). Because the buffer cache keeps blocks based on a most recently used algorithm, the most active buffers stay in memory to reduce I/O and improve performance.

• The redo log buffer--This stores redo entries, or a log of changes made to the database. The redo log buffers are written to the redo log as quickly and efficiently as possible. Remember that the redo log is used for instance recovery in the event of a system failure.
• The shared pool--This is the area of the SGA that stores shared memory structures such as shared SQL areas in the library cache and internal information in the data dictionary. The shared pool is important because an insufficient amount of memory allocated to the shared pool can cause performance degradation. The shared pool consists of the library cache and the data-dictionary cache.

The Library Cache

The library cache is used to store shared SQL. Here the parse tree and the execution plan for every unique SQL statement are cached. If multiple applications issue the same SQL statement, the shared SQL area can be accessed by each to reduce the amount of memory needed and to reduce the processing time used for parsing and execution planning.

The Data-Dictionary Cache

The data dictionary contains a set of tables and views that Oracle uses as a reference to the database. Oracle stores information here about the logical and physical structure of the database. The data dictionary contains information such as the following:

• User information, such as user privileges
• Integrity constraints defined for tables in the database
• Names and data types of all columns in database tables
• Information on space allocated and used for schema objects

The data dictionary is frequently accessed by Oracle for the parsing of SQL statements. This access is essential to the operation of Oracle; performance bottlenecks in the data dictionary affect all Oracle users. Because of this, you should make sure that the data-dictionary cache is large enough to cache this data. If you do not have enough memory for the data-dictionary cache, you see a severe performance degredation. If you ensure that you have allocated sufficient memory to the shared pool where the data-dictionary cache resides, you should see no performance problems.

The Program Global Area (PGA)

The PGA is a memory area that contains data and control information for the Oracle server processes. The size and content of the PGA depends on the Oracle server options you have installed. This area consists of the following components:

• Stack space--This is the memory that holds the session's variables, arrays, and so on.
• Session information--If you are not running the multithreaded server, the session information is stored in the PGA. If you are running the multithreaded server, the session information is stored in the SGA.
• Private SQL area--This is an area in the PGA where information such as binding variables and runtime buffers is kept.

Processes

New Term: In many operating systems, traditional processes have been replaced by threads or lightweight processes. The term process is used in this book to describe a thread of execution, or a mechanism that can execute a set of code; process refers to the mechanism of execution and can refer to a traditional process or a thread.

The Oracle RDBMS uses two types of processes: user processes and Oracle processes (also known as background processes). In some operating systems (such as Windows NT), these processes are actually threads; for the sake of consistency, I will refer to them as processes.

User Processes

User, or client, processes are the user's connections to the RDBMS system. The user process manipulates the user's input and communicates with the Oracle server process through the Oracle program interface. The user process is also used to display the information requested by the user and, if necessary, can process this information into a more useful form.

Oracle Processes

Oracle processes perform functions for users. Oracle processes can be split into two groups: server processes (which perform functions for the invoking process) and background processes (which perform functions on behalf of the entire RDBMS).

Server Processes (Shadow Processes)

Server processes, also known as shadow processes, communicate with the user and interact with Oracle to carry out the user's requests. For example, if the user process requests a piece of data not already in the SGA, the shadow process is responsible for reading the data blocks from the datafiles into the SGA. There can be a one-to-one correlation between user processes and shadow processes (as in a dedicated server configuration); although one shadow process can connect to multiple user processes (as in a multithreaded server configuration), doing so reduces the utilization of system resources.

Background Processes

Background processes are used to perform various tasks within the RDBMS system. These tasks vary from communicating with other Oracle instances and performing system maintenance and cleanup to writing dirty blocks to disk. Following are brief descriptions of the nine Oracle background processes:

• DBWR (Database Writer)--DBWR is responsible for writing dirty data blocks from the database block buffers to disk. When a transaction changes data in a data block, that data block need not be immediately written to disk. Therefore, the DBWR can write this data to disk in a manner that is more efficient than writing when each transaction completes. The DBWR usually writes only when the database block buffers are needed for data to be read. Data is written in a least recently used fashion. For systems in which asynchronous I/O (AIO) is available, there should be only one DBWR process. For systems in which AIO is not available, performance can be greatly enhanced by adding more DBWR processes.
• LGWR (Log Writer)--The LGWR process is responsible for writing data from the log buffer to the redo log.
• CKPT (Checkpoint)--The CKPT process is responsible for signaling the DBWR process to perform a checkpoint and to update all the datafiles and control files for the database to indicate the most recent checkpoint. A checkpoint is an event in which all modified database buffers are written to the datafiles by the DBWR. The CKPT process is optional. If the CKPT process is not present, the LGWR assumes these responsibilities.
• PMON (Process Monitor)--PMON is responsible for keeping track of database processes and cleaning up if a process prematurely dies (PMON cleans up the cache and frees resources that might still be allocated). PMON is also responsible for restarting any dispatcher processes that might have failed.
• SMON (System Monitor)--SMON performs instance recovery at instance startup. This includes cleaning temporary segments and recovering transactions that have died because of a system crash. The SMON also defragments the database by coalescing free extents within the database.
• RECO (Recovery)--RECO is used to clean transactions that were pending in a distributed database. RECO is responsible for committing or rolling back the local portion of the disputed transactions.
• ARCH (Archiver)--ARCH is responsible for copying the online redo log files to archival storage when they become full. ARCH is active only when the RDBMS is operated in ARCHIVELOG mode. When a system is not operated in ARCHIVELOG mode, it might not be possible to recover after a system failure. It is possible to run in NOARCHIVELOG mode under certain circumstances, but typically should operate in ARCHIVELOG mode.
• LCKn (Parallel Server Lock)--Up to 10 LCK processes are used for interinstance locking when the Oracle Parallel Server option is used.
• Dnnn (Dispatcher)--When the Multithreaded Server option is used, at least one Dispatcher process is used for every communications protocol in use. The Dispatcher process is responsible for routing requests from the user processes to available shared server processes and back.

How Transactions Work

New Term: To give you a better idea how Oracle operates, this section analyzes a sample transaction. Throughout this book, the term transaction is used to describe a logical group of work that can consist of one or many SQL statements and must end with a commit or a rollback. Because this example is of a client/server application, SQL*Net is necessary. The following steps are executed to complete the transaction:

1. The application processes the user input and creates a connection to the server via SQL*Net.

2. The server picks up the connection request and creates a server process on behalf of the user.

3. The user executes a SQL statement or statements. In this example, the user changes the value of a row in a table.

4. The server process checks the shared pool to see whether there is a shared SQL area that has this identical SQL statement. If it finds an identical shared SQL area, the server process checks whether the user has access privileges to the data. If so, the server process uses the shared SQL area to process the request. If a shared SQL area is not found, a new shared SQL area is allocated, and the statement is parsed and executed.

5. The server process finds the data in the SGA (if it is present there) or reads the data from the datafile into the SGA.

6. The server process modifies the data in the SGA. Remember that the server processes can read only from the datafiles. At some later time, the DBWR process writes the modified blocks to permanent storage.

7. The user executes either the COMMIT or ROLLBACK statement. A COMMIT will finalize the transaction, a ROLLBACK will undo the changes. If the transaction is being committed, the LGWR process immediately records the transaction in the redo log file.

8. If the transaction is successful, a completion code is returned across the network to the client process. If a failure has occurred, an error message is returned.

---

NOTE: A transaction is not considered committed until the write to the redo log file is complete. This arrangement ensures that in the event of a system failure, a committed transaction can be recovered. If a transaction has been committed, it is set in stone.

---

While transactions occur, the Oracle background processes do their jobs, keeping the system running smoothly. While this process occurs, hundreds of other users might be performing similar tasks. Oracle's job is to keep the system in a consistent state, to manage contention and locking, and to perform at the necessary rate.

This overview is intended to give you an understanding of the complexity and amount of interaction involved in the Oracle RDBMS. As you look in detail at the tuning of the server processes and applications later in this book, you can use this overview as a reference to the basics of how the Oracle RDBMS operates. Because of the differences in operating systems, minor variances in different environments will be discussed individually.

RDBMS Functionality

If the RDBMS is to operate, you must provide for certain functions, including data integrity, recovery from failure, error handling, and so on. This is accomplished via events such as checkpointing, logging, and archiving. The following sections list and describe some of these functions.

Checkpointing

You know that Oracle uses either the CKPT background process or the LGWR process to signal a checkpoint; but what is a checkpoint and why is it necessary?

Because all modifications to data blocks are done on the block buffers, some changes to data in memory are not necessarily reflected in the blocks on disk. Because caching is done using a least recently used algorithm, a buffer that is constantly modified is always marked as recently used and is therefore unlikely to be written by the DBWR. A checkpoint is used to ensure that these buffers are written to disk by forcing all dirty buffers to be written out on a regular basis. This does not mean that all work stops during a checkpoint; the checkpoint process has two methods of operation: the normal checkpoint and the fast checkpoint.

In the normal checkpoint, the DBWR merely writes a few more buffers every time it is active. This type of checkpoint takes much longer but affects the system less than the fast checkpoint. In the fast checkpoint, the DBWR writes a large number of buffers at the request of the checkpoint each time it is active. This type of checkpoint completes much quicker and is more efficient in terms of I/Os generated, but it has a greater effect on system performance at the time of the checkpoint.

You can use the time between checkpoints to improve instance recovery. Frequent checkpoints reduce the time required to recover in the event of a system failure. A checkpoint automatically occurs at a log switch.

Logging and Archiving

The redo log records all changes made to the Oracle database. The purpose of the redo log is to ensure that in the event of the loss of a datafile as a result of some sort of system failure, the database can be recovered. By restoring the datafiles back to a known good state from backups, the redo log files (including the archive log files) can replay all the transactions to the restored datafile, thus recovering the database to the point of failure.

When a redo log file is filled in normal operation, a log switch occurs and the LGWR process starts writing to a different redo log file. When this switch occurs, the ARCH process copies the filled redo log file to an archive log file. When this archive process has finished copying the entire redo log file to the archive log file, the redo log file is marked as available. It's critical that this archive log file be safely stored because it might be needed for recovery.

Create Tablesapce

Locally vs. Dictionary Managed Tablespaces
Submitted by admin on Tue, 2003-07-01 12:31

* RDBMS Server

When Oracle allocates space to a segment (like a table or index), a group of contiguous free blocks, called an extent, is added to the segment. Metadata regarding extent allocation and unallocated extents are either stored in the data dictionary, or in the tablespace itself. Tablespaces that record extent allocation in the dictionary, are called dictionary managed tablespaces, and tablespaces that record extent allocation in the tablespace header, are called locally managed tablespaces.

SQL> select tablespace_name, extent_management, allocation_type from dba_tablespaces;

TABLESPACE_NAME EXTENT_MAN ALLOCATIO
------------------------------ ---------- ---------
SYSTEM DICTIONARY USER
SYS_UNDOTS LOCAL SYSTEM
TEMP LOCAL UNIFORM

Dictionary Managed Tablespaces (DMT):

Oracle use the data dictionary (tables in the SYS schema) to track allocated and free extents for tablespaces that is in "dictionary managed" mode. Free space is recorded in the SYS.FET$ table, and used space in the SYS.UET$ table. Whenever space is required in one of these tablespaces, the ST (space transaction) enqueue latch must be obtained to do inserts and deletes agianst these tables. As only one process can acquire the ST enque at a given time, this often lead to contention.

Execute the following statement to create a dictionary managed
tablespace:

SQL> CREATE TABLESPACE ts1 DATAFILE '/oradata/ts1_01.dbf' SIZE 50M
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE ( INITIAL 50K NEXT 50K MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 0);

Locally Managed Tablespaces (LMT):

Using LMT, each tablespace manages it's own free and used space within a bitmap structure stored in one of the tablespace's data files. Each bit corresponds to a database block or group of blocks. Execute one of the following statements to create a locally managed

tablespace:

SQL> CREATE TABLESPACE ts2 DATAFILE '/oradata/ts2_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

SQL> CREATE TABLESPACE ts3 DATAFILE '/oradata/ts3_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

Note the difference between AUTOALLOCATE and UNIFORM SIZE:

AUTOALLOCATE specifies that extent sizes are system managed. Oracle will choose "optimal" next extent sizes starting with 64KB. As the segment grows larger extent sizes will increase to 1MB, 8MB, and eventually to 64MB. This is the recommended option for a low or unmanaged environment.

UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes (use K or M to specify the extent size in kilobytes or megabytes). The default size is 1M. The uniform extent size of a locally managed tablespace cannot be overridden when a schema object, such as a table or an index, is created.

Also not, if you specify, LOCAL, you cannot specify DEFAULT STORAGE, MINIMUM EXTENT or TEMPORARY.

Advantages of Locally Managed Tablespaces:

* Eliminates the need for recursive SQL operations against the data dictionary (UET$ and FET$ tables)
* Reduce contention on data dictionary tables (single ST enqueue)
* Locally managed tablespaces eliminate the need to periodically coalesce free space (automatically tracks adjacent free space)
* Changes to the extent bitmaps do not generate rollback information

Locally Managed SYSTEM Tablespace:

From Oracle9i release 9.2 one can change the SYSTEM tablespace to locally managed. Further, if you create a database with DBCA (Database Configuration Assistant), it will have a locally managed SYSTEM tablespace by default. The following restrictions apply:

* No dictionary-managed tablespace in the database can be READ WRITE.
* You cannot create new dictionary managed tablespaces
* You cannot convert any dictionary managed tablespaces to local

Thus, it is best only to convert the SYSTEM tablespace to LMT after
all other tablespaces are migrated to LMT.

Segment Space Management in LMT:

From Oracle 9i, one can not only have bitmap managed tablespaces, but also bitmap managed segments when setting Segment Space Management to AUTO for a tablespace. Look at this example:

SQL> CREATE TABLESPACE ts4 DATAFILE '/oradata/ts4_01.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;

Segment Space Management eliminates the need to specify and tune the PCTUSED, FREELISTS, and FREELISTS GROUPS storage parameters for schema objects. The Automatic Segment Space Management feature improves the performance of concurrent DML operations significantly since different parts of the bitmap can be used simultaneously eliminating serialization for free space lookups against the FREELSITS. This is of particular importance when using RAC, or if "buffer busy waits" are deteted.

Convert between LMT and DMT:

The DBMS_SPACE_ADMIN package allows DBAs to quickly and easily
convert between LMT and DMT mode. Look at these examples:

SQL> exec dbms_space_admin.Tablespace_Migrate_TO_Local('ts1');
PL/SQL procedure successfully completed.

SQL> exec dbms_space_admin.Tablespace_Migrate_FROM_Local('ts2');
PL/SQL procedure successfully completed.