ORACLE database structure

ORACLE database structure

ORACLE database structures can be divided into 3 categories.
1.Those that is internal to database (such as tables).
2.Those that is internal to the memory areas.
3.Those that is external to database.

ORACLE database structure in detail:

Let us discuss here one of the three parts of ORACLE database structure. Two parts you can visit at the above links.

ORACLE Internal database structure

The elements that are internal to database include:
  • Tables, columns, constraints and data types (including abstract data types)
  • Users and Schemes
  • Indexes, Clusters and Hash clusters
  • Views
  • Sequences
  • Procedures, Functions, Packages and Triggers
  • Synonyms
  • Privileges and Roles
  • Database Links
  • Segments, Extents and Blocks
  • Rollback Segments

Table, columns and data types

Tables are storage mechanisms for data within an ORACLE database. They contain a fixed set of columns. Each column has a name and specific characteristics. The characteristics of a column are made up of 2 parts: Its data type and its length. The  ORACLE has a set of data type like NUMBER for numeric, VARCHAR for variable length character strings, DATE for storing date in the form of DD—MM-YY etc. In addition to the data types, user can create his own abstract data types. For example, he may create a data type that contains the multiple parts of an address- House-Number,  Main, Cross, Extension, City, State etc- as single data type and can be called by a name address which can be treated as a data type.


A table can have constraints placed upon it; when a constraint is applied to a table, every row in the table must satisfy the conditions specified in the constraint definition.
Constraints can be applied at the time of creating a table.

Create table EMPLOYEE
(emp_no       Number(10)    PRIMARY KEY,
Name           Varchar2 (40)    NOT NULL,
dept_no       Number (2)       DEFAULT 10,
salary            Number (7, 2)   CHECK salary < 1000000
soc_sec_no  Char(9)                UNIQUE);

In the above example, NOT NULL is a constraint applied to a name column. This means that every row that is stored in the table must have a value for that column; it cannot be left NULL.
A COLUMN HAVE A DEFAULT constraint ( In the above example column dept_no). This constraint is used to insert value 10 when row is inserted with a NULL value to the dept_no column.
The CHECK constraint is used to ensure that values in a specified column meet a certain criterion ( in the above example, salary column’s value is less than 1000000).
The UNIQUE, is used to specify uniqueness for columns that should be unique but are not part of the primary key. In the above example, every record in the table must have a unique value for the column soc_sec_no.


Views are makes place upon tables. Views appear to be tables containing columns. Columns in views are found in one or more underlying tables. The view does not use physical storage to store data.


Larger tables can be split into smaller ones; these smaller tables are called partitions.


Users own the database objects. When objects are created in database in support of applications, they are created under user accounts.


A set of objects (such as table and view) owned by a user account is called the User’s Schema.


An index is a database structure used by the server to quickly find a row in a table.


The tables that are frequently accessed together may be physically stored together. To store them together, a cluster is created to hold the tables.


Sequences are used to simplify programming efforts by providing a sequential list of unique numbers.


Procedure is a block of PL/SQL statements that is stored in a data dictionary and is called by applications. Procedures allow you to store frequently used application logic within the database. A Procedure does not return any value to the calling program.


Functions like Procedures, are block of code that are stored in the database. Functions return a value to calling program.


Packages are used to arrange Procedures and functions into logical grouping. This helps to organize related Procedures.


Triggers are Procedures that are executed when a specified data base event takes place against a specified table.


Synonyms are used to provide pointers for tables, Procedures, functions, packages and sequences. They can point to objects within the local database or in remote databases.

Privileges and Roles:

Privileges means Permission. Privileges need to be granted to access an object owned by another account. Privileges need to be granted for non-owners. Roles or groups of privileges can be used to simplify this process. Privileges can be granted to a Role, and the role in turn can be granted to multiple users.

Database links:

Database links are created to specify access path to an object in a remote database.
Segments, Extents and Blocks: Segments are the physical counterparts to logical database objects. Segments store data. Index segments, for example, store the data associated with indexes. A segment is made up of sections called extents – contiguous sets of ORACLE blocks.

Rollback segment:

Rollback means getting previous image or state of the object. ORACLE has a mechanism for reconstructing the “Before image” of data for uncommitted transactions. ORACLE uses rollback segments within the database to accomplish this.

Leave a Reply

Your email address will not be published.