Various Oracle Database Objects

In this tutorial we are going to discuss about a couple of important database objects available in Oracle.

DATABASE:

                Database is a collection many related data which are treated as single unit. Its main purpose it to store, retrieve and manipulate the stored data when required. Oracle is a OORDBMS - Object Oriented Relational Database Management System.

               

SCHEMA / USER:

                Schema and User both are the same in terms of Oracle. All the database objects must belong to any one of the schema. An object between the schemas can communicate if they have process privileges.

TABLESPACE:

                Oracle database is divided into one or more logical storage units which are called as Tablespaces. Tablespaces are further divided into segments, segments are further divided into extends. (i.e X segments group to form a segments. X segments group to form a Tablespace. Where X is a number greater than 1)

               

TABLE:

                Table is the basic location where all the data in the application are being stored. Data are stored as rows and columns. Where each table should have a unique name in the schema. And each table should have unique column names. And each columns should have a valid data type.  The regular tables are also called as Heap Organized Tables.

Note: There is no guarantee that the records will be retrieved in the same order that it was inserted. Need to use ORDER BY clause for it.

CLUSTER:

                Cluster is a combination of more than one table which share the same data blocks, its created for scenario where more than 1 table is joined and queried. Also those tables will have a common column which is shared between them.

               

INDEX:

                Index is a method by which the data of the table can be retrieved fast. Its very similar to Index in a book. In Oracle Index is for a table or cluster. I can have one or more columns. It speeds up the data retrieval in a SQL statement. At the same time if more index are there in a table it will eventually slow the DML operations on the table as every time the index also needs to be rearranged.

               

INDEX ORGANIZED TABLES (IOT):

                IOT's are very similar to an Oracle table, but the main difference is that they all the records are saved in a sorted order. When the table is queried the records are retrieved in the sorted order.

               

VIEW:

                A view in simple terms can be said as a saved SQL query. Whenever a view is called the saved SQL query is ran and the results are fetched. If any WHERE clause is added to view it will be added to the result of the SQL query. A view can also be used as an alternative for complex queries. The view result is generally considered as a virtual table.

               

MATERIALIZED VIEW:

                Materialized view is very similar to the view, but the main difference is that it does not re-query the saved query every time instead once its queried those data are took and saved against the materialized view. The MV can be refreshed based on ADHOC or on timely basis. MV are mainly used to calculate and keep a pre-computed set of results to avoid query long running.

               

TRIGGER:

                Trigger is a stored PL/SQL object, which are used to perform an action when some other actions happen. i.e Make a log entry when a user logs into the database when users login. Make note of the changes of the values when certain table is updated or inserted or deleted.

               

DATABASE LINK:

                Database Links are used to provide a link between more two databases, using which the data from one database can be assessed in the other database.

               

DIRECTORY:

                Directory is a database pointer to file location in the server in which Oracle Database is running. Directories are mainly used to access any files in that specified location. The DBA needs to be make sure that Oracle user have complete access to that directory location.

PROCEDURE:

                Procedure is a stored PL/SQL object, which are used to perform a set of operations either i.e group of PL/SQL statements. The procedure needs to be called each time when it needs to be executed. It doesn't return any value after executing.

               

FUNCTION:

                Function is a stored PL/SQL object, which are used to perform a set of operations either i.e group of PL/SQL statements. The function needs to be called each time when it needs to be executed. It should return one value to the calling environment of any valid data types.

PACKAGE:

                Package is a collection of Procedures, functions. The main advantage of having Package is to group all the related procedures and function into one logical unit. Also when the first procedure, function in the package the whole package is loaded into the memory.

SEQUENCE:

                Sequence is a method provided by Oracle which is used to generate numbers.

               

SYNONYM:

                Synonym is an alternative name of an existing table or view. The main purpose of having a synonym is to hide the identity of the underlying database object.

               

ROLES:

                Role is a database object which has a group of privileges assigned to it, which can granted to another role or user.

Please provide your comments below the blog.