Oracle Database Interview Questions & Answers Set-1

«« Previous
Next »»

1. What is Oracle table?
Ans: A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

2. What are Clusters?
Ans: Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

3. What is an Index?
Ans: An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

4. What are the Advantages of view? 

  • Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
  • Hide data complexity
  • Simplify commands for the user. 
  • Present the data in a different perspective from that of the base table.
  • Store complex queries.

5. What are the Various type of queries?
Ans: The types of queries are :

  • Normal Queries 
  • Sub Queries
  • Co-related queries 
  • Nested queries
  • Compound queries

6. What is the difference between clustered and non- clustered index? 

  • A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
  • A Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. 

7. What is a Tablespace?
Ans: A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together.

8. Why use materialized view instead of a table?
Ans: Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

9. What does ROLLBACK do?
Ans: ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

10. Compare and  contract TRUNCATE and DELETE for a table?
Ans: Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

11. Can we store pictures in the database and if so, how it can be done?
Ans: Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.

12. What is the data type of DUAL table?
Ans: The DUAL table is a one-column table present in oracle database.  The table has a single VARCHAR2(1) column called DUMMY which has a value of ‘X’.

13. What is the difference between $ORACLE_BASE and $ORACLE_HOME?
Ans: Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.

14. What is the parameter mode that can be passed to a procedure?
Ans: IN, OUT and INOUT are the modes of parameters that can be passed to a procedure.

15. What is hash cluster?
Ans: Hash Cluster is a technique used to store the table for faster retrieval. Apply hash value on the table to retrieve the rows from the table.

«« Previous
Next »»