Explain Row level and statement level trigger.
Answer
Row Level Trigger
Row Level Trigger is fired each time row is affected by Insert, Update or
Delete command. If statement doesn’t affect any row, no trigger action happens.
Statement Level Trigger
This kind of trigger fires when a SQL statement affects the rows of the table.
The trigger activates and performs its activity irrespective of number of rows
affected due to SQL statement.
What is a JOIN? Explain types of JOIN in oracle.
Answer
A join is a query that extracts data from two or more tables, views or
snapshots.
Types of JOIN
EQUI-JOIN
This is represented by (=) sign. This join retrieves information by using
equality condition.
NON-EQUI JOIN
If sign other than =, then it is non-equi join.
SELF JOIN
Self join is a join type between a row of a table to another row of the same
table.
OUTER JOIN
This type fetches the row that matches the join condition and rows that don’t
match the join condition.
What is object data type in oracle?
Answer
Object data type is created using object type in oracle. The object
data type can only be created using SQL data type such as Number, Varchar 2,
Date etc. PL/SQL types can’t be used to create object data type.
Example
Create or Replace Type EMP as object
(
Id Number (5)
Name varchar2(30)
);
Explain the attributes of implicit cursor.
Answer
There are four attributes of implicit cursor in oracle.
SQL%IsOPEN
Implicit cursor always returns FALSE as it gets closed automatically.
SQL%FOUND
Returns true when DML statement has affected at least one row else returns
false.
SQL%NOTFOUND
Returns TRUE even one row of the table is affected.
SQL%ROWCOUNT
Returns number of rows affected by DML command.
Explain the attributes of explicit cursor.
Answer
There are four attributes of explicit cursor in oracle.
%IsOPEN
This evaluates TRUE when cursor is open else FALSE.
%FOUND
This evaluates TRUE if last fetch succeeded.
%NOTFOUND
Evaluates TRUE if last fetch failed.
%ROWCOUNT
This returns number of record fetched from active set.
Describe ref cursor in Oracle.
Answer
Cursor is a reference type in oracle. We can allocate different storage
locations to the cursor when the program runs.
Syntax
Type <type_name> is REF CURSOR
RETURN <return type>
Return_type – Name of new reference type.
Return Type – This
represents select list type that will be returned by the query.
What is locking, advantages of locking and types of locking in oracle?
Answer
Locking protect table when several users are accessing the same table.
Locking is a concurrency control technique in oracle. It helps in data
integrity while allowing maximum concurrency access to data. Oracle offers
automatic locking whenever situation requires. This is called implicit locking.
Types of locking
Shared Lock
This type is placed on a record when the record is being viewed.
Exclusive lock
This is placed when Insert, Update or Delete command is performed. There can be
only one exclusive lock on a record at a time.
What are background processes in oracle?
Answer
Oracle uses background process to increase performance.
Database writer, DBWn
Log Writer, LGWR
Checkpoint, CKPT
System Monitor, SMON
Process Monitor, PMON
Archiver, ARCn
What is SQL loader? What are the files used by SQL Loader? Explain the method
of loading data.
Answer
SQL Loader loads data from external files into tables in oracle. SQL
loader uses following files
Method for loading data
Connectional path load
Direct Path load
Explain oracle memory structures.
Answer
Two memory area.
System global area(SGA)
Program Global Area(PGA)
SGA consist memory structure such as
Shared Pool
Database buffer cache
Redo log buffer
large Pool
Java Pool
|
Oracle Interview - July 21, 2008, 17:00 pm by Nishant Kumar
Describe in brief about snapshot in oracle.
Answer
A recent copy of a table or a subset of rows or cols of a table is
called as snapshot in oracle. A snapshot is more useful in distributed
computing environment. We can create snapshot in oracle using command create
snapshot. We can specify refresh interval while creating snapshot.
What is a synonym?
Answer
Synonym simplifies the use of the table, the table for which synonym
is created can be referred by synonym name.
Example
create synonym Emp
for Employees;
In the above example Emp is the synonym created for the employees table.
Employees now can also be referred by Emp.
What is a schema?
Answer
Schema represents structure of the database. Database has two main
types of schemas partitioned.
Physical schema: Describes the database design at the physical level.
Logical Schema: Describes the database design at the logical level.
Logical Schema is important from a programmer’s point of view since it is used
extensively during software development.
What is a cursor? What are its types?
Answer
Oracle engine uses private working area to process queries. This work
area is called as cursor. The data that is stored in the cursor is called as
Active Data Set.
The main types of Cursors are:
Implicit cursors (predefined cursor): - The oracle engine implicitly opens a
cursor on the server to process each SQL statement. Implicit cursors are
managed by the Oracle engine itself.
Explicit Cursors (user defined cursor): - Explicit Cursors are the cursors
created by the user inside a PL/SQL block. The declare section of a PL/SQL
block and used within its executable Section.
What is an Archiver?
Answer
Archiving is the process of removing of old data and unused data from
the main databases. This process keeps databases smaller, more manageable and
thus acquires performance gain. To archive data, you can either delete data
from the database or copy the data to a history table and delete that data from
the database.
Define a sequence in terms of oracle.
Answer
A Sequence is a user created database object. A sequence can be shared
by multiple users to generate unique integers. This object is used to create a
primary key value. The sequence is generated and incremented by internal Oracle
routine. This can be time-saving object because it can reduce the amount of
application code needed to write a sequence generating routine.
Syntax
Create sequence seq_name
Increment by n
Start with n
Maxvalue n | NoMaxvalue
Minvalue n | NoMinvalue
Cycle | NoCycle
Cache n | NoCache ;
|
Explain how to grant DML privilege on a table.
Explain how save point works.
Explain difference between SQL and PL/SQL.
Explain User defined exceptions in oracle.
What is composite data type? Explain its types. Record, Table, Varrays
Posted on July 20th, 2008 at 8:47 am by Nishant Kumar
Related Links
-
Fundamental
ODP.NET Classes
To retrieve data from an Oracle database using ODP.NET, we need to work with a
few of the ODP.NET classes.....
-
Retrieving Data Using OracleDataReader
OracleDataReader is simply a read-only and forward-only result set. It works
only if the database connection is open and it makes sure that the connection
is open while you are retrieving data. As the data that it retrieves is
read-only, it is a bit faster than any other method to retrieve data from
Oracle.......
-
Working with Data Tables and Data Sets
The OracleDataAdapter class is mainly used to populate data sets or data tables
for offline use. The OracleDataAdapter simply connects to the database,
retrieves the information, populates that information into datasets or data
tables, and finally disconnects the connection to the database.......
-
Working
with DataTableReader
DataTableReader is complementary to a DataTable object, and is mainly used as a
type of Data Reader in the disconnected mode................
-
Presenting Master-Detail Information Using a Dataset
A DataSet object can have its own relations between data tables existing
in it. We can add these relations dynamically at the client side (within an
application), to represent master-detail (or hierarchical) information......
-
About
the OracleCommand Object
OracleCommand is not simply meant for OracleDataReader. It has got a lot of
functionality for itself. Let us see few of the most commonly used features of
OracleCommand in this section.......
-
Techniques to Improve Performance while Retrieving Data
Performance tuning is a great subject in Oracle. Volumes of books would not be
enough to cover every aspect of performance tuning in Oracle.........
|