Oracle database objects


Oracle database objects

<<Previous  Next>>

Explain in brief oracle database objects. 

Oracle database objects - Jan 28, 2009 at 18:00 PM by Amit Satpute

Explain in brief oracle database objects.

1. Tables

Oracle stores information in the form of tables. For eg you can have a table named as climate in which you can store information about the climate of a place in the form of columns which could be the temperature, name of the place, date, humidity, etc.

In the terms of a relational database, one can call a table as an entity and the columns as it attributes.

2. Indexes

Indexing is a concept of listing of keywords accompanied by the location of information of the subject. Indexes are used to speed up the processing, especially searching.


A view is a way of hiding the logic that created the joined table just displayed. For example:

create view AB
select A.x, B.y from A, B where A.x = B.y;
You can query it as select x, y from AB.
Note: AB is the view name, A, B are the Table names with x and y as their column names respectively.
For views, you don’t need to specify the tables as the logic is hidden inside the views.

4. Synonyms 

A synonym is a name assigned to a table or view that may be used refer to it thereafter. If you have an access to another users table, you may create a synonym for it and refer to it by the synonym alone, without entering the users name as a qualifier.
Using synonyms is a good way to implement location transparency.

5. Sequences

Tables usually have a primary key which uniquely identifies a row in a table. A sequence is a unique number generator which can be assigned to the primary keys of the tables.

 Eg create sequence xyz
increment by 1
start with 1;

6. Partitions

Partitioning provides tremendous advantages to applications by improving manageability, performance, and availability.
Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces.
Each piece of database object is called a partition.

Techniques for partitioning tables:

Range Partitioning
List Partitioning
Hash Partitioning
Composite Range-Hash Partitioning
Composite Range-List Partitioning

7. Clusters

A cluster is a schema object that contains data from one or more tables, all of which have one or more columns in common.
All the rows from all the tables that share the same cluster key are stored.
After you create a cluster, you add tables to it. A cluster can contain a maximum of 32 tables.

8. Stored procedures and packages

A procedure is a PL/SQL block alike the functions of the 3rd generation languages. You just have to compile them so as to use them later.
When a procedure is created, it is compiled and stored in the database in the compiled form.
Parameters can be passed to a procedure.
A procedure call is a PL/SQL statement by itself. A procedure is a PL/SQL block with a declarative section, an executable section and an exception handling section.

Packages are PL/SQL constructs that allow related objects to be stored together. A package has two separate parts. Each of them is stored separately in the data dictionary.
A package can include procedures, functions, cursors, types, and variables.

Eg create or replace package XYZ as
procedure p1 (p_id IN % type, …………, ……..)
end XYZ;

9. User-defined data types

User defined data types are PL/SQl types that are based on the existing types. Subtypes are used to gives an alternate name to for a type.

subtype counter is number;
counter a;

10. Table spaces

A table space is an area on disk which comprises of one or more disk files. A tablespace can contain many tables, clusters or indexes.
One or more tablespaces together make a database.
Each table has a single area of diskspace called a segment set aside for it in the table space.
Each segment has an initial area on disk space set aside for it in the table space called the initial extent.
Once it has been used up, another extent is set aside for it.

11. Constraint

Constraints help understand how the tables and columns are related to each other.
The constraint information is accessible under the USER_constraint view.
The constraints include the following columns
Owner - - - of constraint
R_Owner - - owner of the foreign key referenced table.

Also read
Oracle Index

What is an Index?, What are the objects in oracle?, What is INDEX_BY_BINARY_INTEGER?, What is index and explain its purpose...........

Oracle large objects

What is large object in oracle? Explain its purposes, Explain types of large objects in oracle, i.e. BLOB, LLOB,NCLOB and BFILE............

Oracle object datatypes

Object data types are user defined data types. Both column and row can represent an object type................

Oracle transaction

The parser scans the statement and breaks it into logical units such as keywords, identifiers and operators, A query or a sequence tree is built using the units above. This is done to transform the source data into the format required by the result set.............

Oracle sub queries

Explain sub-queries in brief, What are the different operators used in building sub queries?, What are the guidelines for using SUB-QUERIES?, What is correlated sub-query?............

Oracle processes

User process – User process is used in invocation of application software, Data writing process - A database writer process is used to write buffer content into a datafile. They are specifically used to write “dirty block” to data files from the buffer..............

What are the database objects in oracle?Explain them

Table: Composed of rows and column that stores data, View : Represents subset of data from one or more tables............

<<Previous  Next>>

Write your comment - Share Knowledge and Experience


Interview questions
Latest MCQs
» General awareness - Banking » ASP.NET » PL/SQL » Mechanical Engineering
» IAS Prelims GS » Java » Programming Language » Electrical Engineering
» English » C++ » Software Engineering » Electronic Engineering
» Quantitative Aptitude » Oracle » English » Finance
Home | About us | Sitemap | Contact us