INTERVIEW QUESTIONS ORACLE ORACLE ARCHITECTURE DETAILS

Question: Explain in brief oracle database objects.

Answer: 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.
3. Views

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.

Package:
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, …………, ……..)
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.

Eg:
declare
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
Constraint_name
Constraint_type
Table_name
Search_condition
R_Owner - - owner of the foreign key referenced table.
R_constraint_name
Delete_rule
Status

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.
3. Views

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.

Package:
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, …………, ……..)
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.

Eg:
declare
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
Constraint_name
Constraint_type
Table_name
Search_condition
R_Owner - - owner of the foreign key referenced table.
R_constraint_name
Delete_rule
Status Source: CoolInterview.com

If you have the better answer, then send it to us. We will display your answer after the approval.

Rules to Post Answers in CoolInterview.com:-

• There should not be any Spelling Mistakes.
• There should not be any Gramatical Errors.
• Answers must not contain any bad words.
• Answers should not be the repeat of same answer, already approved.
• Answer should be complete in itself.

Name :*
Email Id :*
Answer :*
Verification Code	Not readable? Load New Code
Enter the above shown code: *
	Inform me about updated answers to this question