Oracle Interview Questions for Freshers

Hello Friends 

I am posting here the most common question which are asked in interviews  to the  candidates who are having experience of 0 to 6 Months.

1. What is Oracle table?

     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?

    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

     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 views?

• 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 types of queries?

     

     The types of queries are :

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

 6. What is the difference between clustered and a 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?

     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?

     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?

     ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

10. Compare and contrast TRUNCATE and DELETE for a table?

       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. What is the usage of SAVEPOINTS?

      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.

12. What is cluster key?

        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.

13.What is DECODE function used for?

      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.

14. What is the difference between Explicit and Implicit Cursors?

An Implicit cursor is one created "automatically" for you by Oracle when you execute a query. It is simpler to code  

An Explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don't care if there are others DBA_DATA_FILES.

15. What are the different index configurations a table can have?

• A table can have one of the following index configurations
• No indexes
• A clustered index
• A clustered index and many nonclustered indexes
• A nonclustered index
• Many nonclustered indexes.

16.What is BCP? When does it used? 

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

17.How to know which index a table is using?

SELECT table_name,index_name FROM user_constraints.

18.  How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

19. What is the maximum number of triggers, can apply to a single table?

     

 12 triggers

20. What are the differences between stored procedures and triggers?

A stored procedures are compiled collection of programs or SQL statements that live in the database. A stored procedure can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. But triggers are event-driven special procedures which are attached to a specific database object.

21. Explain the difference between a data block, an extent and a segment?

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

22. What are ORACLE PRECOMPILERS?

  A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc.The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way.

23.What are the dictionary tables used to monitor a database spaces ? 

• DBA_FREE_SPACE
• DBA_SEGMENTS
• DBA_DATA_FILES