Oracle Interview Questions and Answers - Test & Download!

Explain: Integrity Constraints, Index, Extent

a.) Integrity Constraints
It can be called as a declarative way in order to define a business rule for a table's column

b.) Index
- It can be called as an optional structure which is associated with a table for direct access to the rows
- Index can be created for one or more columns in a table

c.) Extent
- It can be defined as a specific number of contiguous data blocks in single allocation.
- It is used to store a specific type of information.

What is ANALYZE command used for?

ANALYZE command is used to perform various functions on index, table, or cluster, as listed below:
- It helps in dentifying migrated and chained rows of the table or cluster.
- It helps in validating the structure of the object.
- It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
- It helps in deleting statistics used by object from the data dictionary.

a.) List the types of joins used in writing SUBQUERIES.

- Self join
- Outer Join
- Equi-join

b.) List the various Oracle database objects.

- TABLES
- VIEWS
- INDEXES
- SYNONYMS
- SEQUENCES
- TABLESPACES

a. Explain i.) Rename ii.) Alias.

- Rename - It is a permanent name provided to a table or column.
- Alias - It is a temporary name provided to a table or column which gets over after the execution of SQL statement.

b.) What is a view?

- It is virtual table which is defined as a stored procedure based on one or more tables.

a.) What are the varoius components of physical database structure of Oracle database?

Oracle database comprises of three kinds of files:
- Datafiles,
- Redo log files,
- Control files.

b.) List out the components of logical database structure of Oracle database.

- Tablespaces
- Database's schema objects.

a.) What do you mean by a tablespace?

- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.

b.) What is Control File used for?

Control File is used for:
- Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.

a.) What is a synonym? What are its various types?

A synonym can be called as an alias for a table, view, sequence or program unit.

It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.

b.) What are the uses of synonyms?

- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.

What do you mean by a deadlock?

- When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.
- The reasons for it to happen are:
- lack of proper row lock commands.
- Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.

a.) What suggestions do you have to reduce the network traffic?

Following are some of the actions which can be taken to reduce the network traffic:
- Use snapshots to replicate data.
- Use remote procedure calls.
- Replicate data in distributed environment.

b.) What are the various types of snapshots ?

There are two types of snapshots:
- Simple snapshots - Based on a query that does not contain GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.
- Complex snapshots- which contains atleast any one of the above.

What is a sub query? What are its various types?

- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.

There can be two types of subqueries:
a.) Correlated sub query
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .

b.) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query
1 2 3 4 5

Describe Oracle architecture in brief
The Oracle database has: - Logical layer: The components of the logical layer map the data to these physical components - Physical layer: The physical layer consists of the files that reside on the disk.....
What is the function of SMON?
The SMON background process performs all system monitoring functions on the oracle database....
Explain different types of segment
There are four types of segments used in Oracle databases: - data segments - index segments - rollback segments - temporary segments.....
Post your comment
Discussion Board
pls provide a sql get the below result.
ID_NO, MARKS, YR
1 30 2010
1 40 2013
1 50 2014
2 30 2009
2 40 2010

is the input table.

pls let me the know the sql to get the following o/p

id_no year previous yr
1 2014 2013
1 2013 2010
1 2010 null
2 2010 2009
2 2009 null

i should not get more rows than the no of rows input table . year 2014 has two previous years one 2013 and 2010 but i should get only 2013 in o/p

the following sql gives below o/p but its not giving expected result

select a.id_no,a.yr,b.yr from student a left outer join student b on a.id_no = b.id_no
and b.yr < a.yr order by a.id_no,a.yr desc

ID_NO YR YR
---------- ---------- ----------
1 2014 2010
1 2014 2013
1 2013 2010
1 2010
2 2010 2009
2 2009

prasanth 10-25-2014
Oracle Interview questions and answers
What are triggers? What are the events a trigger includes?
The procedures are called as triggers in oracle and it run implicitly when an insert, update or delete statement is being performed on a table. They are same as store procedures but it can include SQL, PL/SQL or Java statements. Triggers are implicit to the application.
It includes the following events such as:
- DML statements for modification of data like INSERT, UPDATE or DELETE
- DDL statements
- System related messages like startup, shutdown, and error messages
- User events like logon or logoff
Rohit Sharma 12-7-2011
Oracle interview questions and answers
Does the After report trigger get fired if report execution fails?
Yes.

Explain PGA.
PGA - Program Global Area - area in memory used by a single Oracle user process.
Ramakutty 12-6-2011
Oracle interview questions and answers
List various levels of Auditing ?
- Statement Auditing,
- Privilege Auditing
- Object Auditing.

List the types of snapshots.
- Simple
- Complex

Ankur 12-6-2011
Oracle interview questions and answers
List the characterstics of data files.
- Data file can be associated with only one database.
- Once it is created, it can't change size.
- One or more database files together form a table space.

When does a Transaction finish ?
- When it is committed
- OR When it is Rolled back.

What is the function of ROLLBACK?
- It retracts the changes occuring from the execution of SQL statements in the transaction.

Rajat 12-6-2011
Oracle interview questions and answers
Explain tablespace.
Logical Storage Unit into which a database is divided.

What is Oracle View?
Oracle View - A virtual table which has a query attached to it.

Do views contain/store data?
No. Views do not contain/ store any data.
Sakshi 12-6-2011