Oracle Database Interview Questions & Answers Set-6

«« Previous
Next »»

1. What are the components of physical database structure of Oracle database? 
Ans: Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.

2. What is DECODE function used for?
Ans: DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution.

3. What is the default return value of a function?
Ans: The default return value from a function is int. In other words, unless explicitly specified the default return value by compiler would be integer value from function.

4. What is the difference between oracle,sql and sql server?
  • Oracle is based on RDBMS.
  • SQL is Structured Query Language.
  • SQL Server is another tool for RDBMS provided by MicroSoft.

5. How you will avoid your query from using indexes?
Ans: By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.

6. What is a cluster key?
Ans: The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

7. What is SGA?
Ans: The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.

8. What is a data segment?
Ans: Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

9. What is ROWID?
Ans: ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, row number are the components of ROWID.

10. What is the usage of SAVEPOINTS?
Ans: SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

11. How many memory layers are in the Oracle shared pool?
Ans: Oracle shared pools contains two layers:

  • library cache
  • data dictionary cache

12. What is save point in Oracle database?
Ans: Save points are used to divide a transaction into smaller parts. It allows rolling back of a transaction. Maximum five save points are allowed. It is used to save our data, whenever you encounter an error you can roll back from the point where you save your SAVEPOINT.

13. What is hash cluster in Oracle?
Ans: Hash cluster is a technique to store a data in hash table and improve the performance of data retrieval. Hash function is applied on table row's cluster key value and store in hash cluster.

14. What are the various Oracle database objects?
Tables: This is a set of elements organized in vertical and horizontal fashion.
Tablespaces: This is a logical storage unit in Oracle.
Views: It is virtual table derived from one or more tables.
Indexes: This is a performance tuning method to process the records.
Synonyms: This is a name for tables.

15. What is the difference between pre-select and pre-query?
Ans: A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger you can modify the where clause part dynamically.

Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.

Pre-query trigger fires before Pre-select trigger.

«« Previous
Next »»