Oracle interview Questions

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

Oracle Concepts and Architecture

Memory Management

Qus.  . What are disadvantages of having raw devices?
Ans: We should depend on export/import utility for backup/recovery (fully reliable)
The tar command cannot be used for physical file backup, instead we can use dd command, which is less flexible and has limited recoveries.

Qus.  . List the factors that can affect the accuracy of the estimations?
Ans: - The space used transaction entries and deleted records, does not become free immediately after completion due to delayed cleanout.
- Trailing nulls and length bytes are not stored.
- Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.

Database Security & Administration

Qus.  . What is user Account in Oracle database?
Ans: A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

Qus.  . How will you enforce security using stored procedures?
Ans: Don't grant user access directly to tables within the application.
Instead grant the ability to access the procedures that access the tables.
When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

Qus.  . What are the dictionary tables used to monitor a database space?
Ans: DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.

SQL*Plus Statements



Qus.  0. What are the types of SQL statement?
Ans: Data Definition Language: CREATE, ALTER, DROP, TRUNCATE, REVOKE, NO AUDIT & COMMIT.
Data Manipulation Language: INSERT, UPDATE, DELETE, LOCK TABLE, EXPLAIN PLAN & SELECT.
Transactional Control: COMMIT & ROLLBACK
Session Control: ALTERSESSION & SET ROLE
System Control: ALTER SYSTEM.

Qus.  1. What is a transaction?
Ans: Transaction is logical unit between two commits and commit and rollback.

Qus.  2. What is difference between TRUNCATE & DELETE?
Ans: TRUNCATE commits after deleting entire table i.e., cannot be rolled back.
Database triggers do not fire on TRUNCATE
DELETE allows the filtered deletion. Deleted records can be rolled back or committed.
Database triggers fire on DELETE.

Qus.  3. What is a join? Explain the different types of joins?
Ans: Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns.
Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

Qus.  4. What is the sub-query?
Ans: Sub-query is a query whose return values are used in filtering conditions of the main query.

Qus.  5. What is correlated sub-query?
Ans: Correlated sub-query is a sub-query, which has reference to the main query.

Qus.  6. Explain CONNECT BY PRIOR?
Ans: Retrieves rows in hierarchical order eg.
select empno, ename from emp where.


1 2 3 4 5 6 7 8 9 10 11 12 13 14 15