Explain in brief oracle
Oracle database objects - Jan 28, 2009 at 18:00 PM by Amit
Explain in brief oracle database objects.
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.
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,
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
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.
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;
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:
Composite Range-Hash Partitioning
Composite Range-List Partitioning
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
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
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 tablename.id % type, …………, ……..)
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;
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.
Constraints help understand how the tables and columns are related to each
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.
What is an Index?, What are the objects in oracle?, What is
INDEX_BY_BINARY_INTEGER?, What is index and explain its purpose...........
What is large object in oracle? Explain its purposes, Explain types of large
objects in oracle, i.e. BLOB, LLOB,NCLOB and BFILE............
Object data types are user defined data types. Both column and row can represent
an object type................
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.............
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
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..............
Table: Composed of rows and column that stores data, View : Represents subset
of data from one or more tables............