Oracle Management Cloud

Introduction to Oracle Management Cloud We have been seeing so many products of Oracle off late. A new suite from Oracle named Oracle Manage...

Thursday, October 1, 2020

Types of Database objects an Oracle DBA should know

I have been working as a Oracle DBA / Oracle Apps DBA for more than 15 years for now. Offlate I have realized that many of the DBAs dont know the basic things such as different object types available in Oracle Database.

When I ask any of the DBAs; what are the different types of objects that they know in Oracle, most people mention about Tables and Indexes and they stop. A few go a little beyond and mention about Views and Packages. A few talk about synonyms. But most of them just know their (object) names but do not know what is its purpose. We will talk about it a little today in this post.

Most Widely used objects that we use everyday as a DBA:

1) Tables -Basic unit of data storage in Database

2) Indexes - Contains an entry for each value that appears in indexed column of the table

3) Synonyms - Synonyms are alias for other objects. Usually we create synonyms to access objects which we have access to; without a prefix.

4) Views - In simple terms, we can define view as a virtual object which does not occupy space, it does not store anything. We can create a view to query specific columns from multiple tables and display.

5) Materialized View - MV is similar to a view where specific columns from multiple tables is queried and it stores data within it. It will have data as of the creation time. To get latest data from tables into MV, we refresh them.

6) Procedure - A procedure is a group of PL/SQL statements that you can call by name. 

7) Function - Similar to Procedure, its a subprogram (group of PL/SQL statements) which computes and must return a value.

8) Package - A package is a schema object that contains definitions for a group of related functionalities. A package includes variables, constants, cursors, exceptions, procedures, functions, and subprograms. It is compiled and stored in the Oracle Database. Typically, a package has a specification and a body.

There are more object types. But the above mentioned are the very basic every DBA must be aware of.

No comments:

Post a Comment

Most Viewed Posts