LogoASPAlliance: Articles, reviews, and samples for .NET Developers
Basics of Oracle
by Abhinash Jena
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 23376/ 122


Oracle is a Relational Database Management System. It was designed to make it possible for allowing simultaneous access to a larger amount of stored data. Normally RDBMS consists of the database (this contains the physical files residing in the system and also the logical data) and the instance. The Oracle database has two layers.

The Physical Layer – This layer consists of files like datafiles, redo log files and control files that reside on the disk. Datafiles consists of information contained in the database. Redo log files are used to hold information which can be used during recovery in the event of a system failure. Control files contain information used to start an instance.

The Logical Layer – This layer consists of one or more tablespaces (tablespace is used to group data together logically) and the database schema (database schema is a collection of logical structure objects) consisting of various items such as tables, clusters, etc.

The database schema consists of:

·         Table – It is the basic storage unit in the Oracle database.

·         View – It acts as a window into one or more tables. A view, like a table, can be updated, deleted and queried.

·         Cluster – It is a group of tables stored together as one table sharing a common column.

·         Index – It is used to retrieve data more quickly and efficiently.

·         Stored Procedure – It is a predefined query that is stored in the data dictionary.

·         Sequence – It is used to generate a unique sequence of numbers in a cache.

·         Database Trigger – It is a procedure that is invoked automatically when an event occurs.

Tables in Oracle

A table is the most visible object in the Oracle RDBMS. It is the structure used by Oracle for storing data and this consists of rows and columns. It is normally created in a table segment which consists of one or more extents. Extents consist of data blocks. It is normally used to minimize the amount of wastage storage. Extents act as the building blocks of segments. When a table grows filling the current extents, a new extent is created for that table. These extents grow in the manner specified by the STORAGE clause used while creating a table which if not included during table creation; the default STORAGE clause defined on the tablespace is used.

Tables can be defined and created by using Enterprise Manager, Schema Manager, or the CREATE TABLE command. I personally like the CREATE TABLE way to create a table as it works as a powerful tool when we need to accomplish some complex tasks. One more advantage is that we can include the STORAGE clause while creating a table using the CREATE TABLE command. And as I have started talking about STORAGE, let me give some more details on STORAGE as this is important for specifying the initial size and characteristics of a tablespace, etc. The DEFAULT STORAGE is used to define the storage parameters on a tablespace which can be used for any table created on the particular tablespace that does not have a STORAGE clause defined during its creation. And if a STORAGE clause is defined which creation of a table in that particular tablespace, it would override the DEFAULT STORAGE clause defined. The STORAGE clause has various parameters such as:

·         INITIAL parameter – for specifying the initial size

·         NEXT parameter – for specifying the size of subsequent extents to be of some number

·         MINEXTENTS parameter – for specifying the minimum number of extents created (during the creation of the schema object)

·         MAXEXTENTS parameter – just the reverse of MINEXTENTS parameter (used for specifying the maximum number)

·         PCTINCREASE parameter – for specifying the size of extends after a second

Though there are many other parameters of STORAGE clause, those mentioned above are the ones normally used.

Listing 1

Name VARCHAR2(30),
Address VARCHAR2(80)

The various important data types that are supported by Oracle are the following.

1.    CHAR – stores fixed length characters

2.    DATE – stores date in the form containing Year, Month, Date, Hour, Minute and Second

3.    NUMBER – stores fixed and floating point numbers

4.    VARCHAR – stores variable length character

5.    VARCHAR2 – Similar to VARCHAR data type

6.    ROWID – stores the data block, the row, and the datafile in the form of a triplet

7.    LONG – stores variable length character data up to 2GB in size

8.    LONG RAW – similar to LONG data type, except that it is not converted, as the LONG data type is converted by the Oracle utilities.

Creating Views on Tables

A view is something like a window into a table. It is a logical structure that derives its data from other tables which can be tables or other views. Views are normally used for making it easy for accessing certain data and for hiding other pieces of data. A view does not store data, rather it shows the data of the table. The command for creating a view on a table is:

Listing 2

SELECT demo_id, demo_name, demo_address
FROM demo
WHERE demo_id >10;

Views can be built from simple queries that select only a subset of a table or they can be built from join query operating on multiple tables. But it is not possible to modifying a view after creating it, though it can be replaced.



Synonyms are database objects that point to another object in the system. They are the aliases for tables, views, sequences, etc. These are used to hide certain details from the end user. There are two forms of synonyms – public synonyms which are owned by the public schema and are available to every user in the database and private synonyms which are present in the schema of the user who created it, and that user controls who can access it. The syntax for creating a synonym is:

Listing 3


In the above syntax, ABC is the system name.

Synonyms are used in distributed systems for simplifying the process of accessing certain data. For example, without using synonyms a SELECT statement may be written as:

Listing 4

SELECT * FROM abc.demo;

But by using synonyms the above statement may be simplified as:

Listing 5


As of now, abc.demo is defined as a demo; the user need not worry for knowing the system name.


We are done!!! I hope you liked my article and I hope this article helped you in understanding Oracle better. I have tried to make it as simple as possible.

By Abhinash Jena

Product Spotlight
Product Spotlight 

©Copyright 1998-2020  |  Page Processed at 2020-01-26 4:28:09 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search