Answers for Oracle Interview Questions

61)What is meant by Partitioning?

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data. Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, enabling these database objects to be managed and accessed at a finer level of granularity.

62)What are local and Global Indexes?

Just like partitioned tables, partitioned indexes improve manageability, availability, performance, and scalability. They can either be partitioned independently (global indexes) or automatically linked to a table's partitioning method (local indexes).

Global Index:  A global index is a one-to-many relationship, allowing one index partition to map to many table partitions.  A global index can only be a range partition!

By default, the following operations on partitions on a heap-organized table mark all global indexes as unusable:

ADD (HASH)
COALESCE (HASH)
DROP
EXCHANGE
MERGE
MOVE
SPLIT
TRUNCATE

These indexes can be maintained by appending the clause UPDATE GLOBAL INDEXES to the SQL statements for the operation. The two advantages to maintaining global indexes:

The index remains available and online throughout the operation. Hence no other applications are affected by this operation. The index doesn't have to be rebuilt after the operation.

GLOBAL NONPARTITIONED INDEXES behave just like a nonpartitioned index. They are commonly used in OLTP environments and offer efficient access to any individual record.

Local Index: A local index is a one-to-one mapping between a index partition and a table partition.  In general, local indexes allow for a cleaner “divide and conquer” approach for generating fast SQL execution plans with partition pruning.A local index can be unique. However, in order for a local index to be unique, the partitioning key of the table must be part of the index's key columns.

You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.

Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.

63)What are the different Types of Partitioning Available?
Range Partitioning
List Partitioning
Hash Partitioning
Composite Partitioning

64)What are the Paritioning methods which are available in Oracle 11G Onwards?
Extended Composite Partitioning
Interval Partitioning
System Partitioning
Reference Partitioning
Virtual Column-Based Partitioning

65)How do you say that partitioning increases the performance of the Queries?

Partitioning can help you improve performance and manageability. Some topics to keep in mind when using partitioning for these reasons are:

Partition Pruning
Partition-wise Joins
Parallel DML

Partition Pruning
The Oracle server explicitly recognizes partitions and subpartitions. It then optimizes SQL statements to mark the partitions or subpartitions that need to be accessed and eliminates (prunes) unnecessary partitions or subpartitions from access by those SQL statements. In other words, partition pruning is the skipping of unnecessary index and data partitions or subpartitions in a query.

Partition-wise Joins
A partition-wise join is a join optimization that you can use when joining two tables that are both partitioned along the join column(s). With partition-wise joins, the join operation is broken into smaller joins that are performed sequentially or in parallel. Another way of looking at partition-wise joins is that they minimize the amount of data exchanged among parallel slaves during the execution of parallel joins by taking into account data distribution.

Parallel DML
Parallel execution dramatically reduces response time for data-intensive operations on large databases typically associated with decision support systems and data warehouses. In addition to conventional tables, you can use parallel query and parallel DML with range- and hash-partitioned tables. By doing so, you can enhance scalability and performance for batch operations.

66)What is meant by Skip Scan Indexes?
When frequently accessing two or more columns in conjunction in the WHERE clause, a concatenated index is often more selective than two single indexes.
In the case of a Concatenated Index, The ability of an Oracle Optimizer to use a Concatenated index without reference to leading edge columns is known as the skip-scan feature.A concatenated index that is used for skip-scanning is more efficient than a full table scan. Index compression is useful for indexes that contain multiple columns where the leading index column value is often repeated.

67)What is meant by composite partitioning?
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash or list method. Composite range-hash partitioning provides the improved manageability of range partitioning and the data placement, striping, and parallelism advantages of hash partitioning. Composite range-list partitioning provides the manageability of range partitioning and the explicit control of list partitioning for the subpartitions.

In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:

*    Range-Hash (available since 8i)
*    Range-List (available since 9i)
*    Range-Range
*    List-Range
*    List-Hash
*    List-List

Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:

*    Interval-Hash
*    Interval-List
*    Interval-Range

68)What are the Different combinations of partitioning possible?

In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:

*    Range-Hash (available since 8i)
*    Range-List (available since 9i)
*    Range-Range
*    List-Range
*    List-Hash
*    List-List

Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:

*    Interval-Hash
*    Interval-List
*    Interval-Range

69)What do you mean by SQL Profiles?

SQL profiles are optionally generated corrections and improvements to statistics. The recommendation (and code) to implement a SQL profile is manifested through the output of the SQL Tuning Advisor. You can manually enable SQL profiles or configure them to be automatically accepted. SQL profiles help the optimizer derive better execution plans.

When working with tuning advice and SQL profiles, ensure that the database account you're using has the ADMINISTER SQL MANAGEMENT OBJECT system privilege granted to it. This privilege contains all of the privileges required to manage tuning tasks and SQL profiles.

How do you know if a SQL profile is being used by the optimizer?
Set AUTOTRACE on and view the execution plan with the profile enabled and then disabled. You should see a lower-cost execution plan being used when the profile is enabled. Additionally, consider inspecting the SQL_PROFILE column of V$SQL.

70)What are the Automatic Tuning tools available in Oracle?
SQL TUNING ADVISOR
SQL ACCESS ADVISOR
SQL PERFORMANCE ANALYZER
ADDM (Automatic Database Diagnostic Monitor) Reports

71)What is meant by a Global Temporary Table?
Applications often use some form of temporary data store for processes that are to complicated to complete in a single pass. Often, these temporary stores are defined as database tables or PL/SQL tables.

72)What is meant by Scalar SubQuery Caching?

It is a subquery in a SQL statement that returns exactly one column and zero rows or one row. That single column can be a complex object type, so it can consist of many attributes, but the subquery returns a single scalar value (or NULL if the subquery returns zero records). A scalar subquery can be used anywhere a literal could have been used.

Oracle makes use of a HASH Table for caching the results of the Scalar subQuery so that it doesn't have to fire the same scalar subQuery each time for the same value passsed to the Scalar sub Query.The HASH Table is capable of storing values from 1..255 (Oracle 10G and 11G) and will look in that hash table slot to see if the answer exists. So Depending on the distinct number of input values passed to a Scalar Sub Query, Oracle tries to fetch the data from the Hash Table instead of firing the Sub Query again but this is limited to the number of entries permitted to be stored in the HASH Table.

73)What is Subquery Factoring?
The SQL WITH clause only works on Oracle 9i release 2 and beyond.Formally, the WITH clause was called subquery factoring.

Depending on the release of Oracle in use, the global temporary tables (GTT) might be a better solution than the WITH clause because indexes can be created on the GTT for faster performance.

Making use of the With Clause , Oracle makes use of a optimization approach where by the Query specified in the With Clause is execued only once and this data is stored in the temporary Tablespace( Similar to creating a Global Temporary Table). So irrespective of the number of times the Query within the WITH CLAUSE is called, it will be Executed only once thereby limiting the number of times the data is accessed from disk thereby reducing the execution time of the Query.

On tiny datasets, the time involved in the temporary table setup can take longer than the original query itself so is not a particularly useful mechanism.

Any subqueries we define must be referenced at least once otherwise Oracle will raise an Exception.

74)What do you mean by a locally managed Tablespace?
Oracle maintains a bitmap in each datafile to track used and free space availability in an LMT. The initial blocks in the datafiles are allocated as File Space Bitmap blocks to maintain the extent allocation information present in the datafile. Each bit stored in the bitmap corresponds to a block or a group of blocks. Whenever the extents are allocated or freed, oracle changes the bitmap values to reflect the new status. Such updates in the bitmap header do not generate any rollback information.

The number of blocks that a bit represents in a bitmap depends on the database block size and the uniform extent size allocated to the tablespace. For example, if the DB_BLOCK_SIZE parameter is set to 8K, and the tablespace is created with uniform extent sizing of 64K, then 1 bit will map to one 64K extent, i.e., 64K (extent size)/8K (block size) = 8 database blocks.

Answers for Oracle Interview Questions

51)Will Oracle make use of a Hint if it is specified in the Query?
Not necessarily use the hint, if the optimizer sees a better optimized path than the one specified by means of the hint.

52)Can we specify more than one Hint in a Query?
yes.Multiple hints are separated with a space. Specifying multiple hints that conflict with each other causes the query to use none of the hints that are conflicting.

53)What are the different Hints that you have come across?
Using the FIRST_ROWS hint to generally force the use of indexes
Using the ALL_ROWS hint to generally force a full table scan
Using the FULL hint to force a full table scan
Using the INDEX hint to force the use of an index
Using the NO_INDEX hint to disallow a specified index from being used
Using the INDEX_JOIN hint to allow the merging of indexes on a single table
Using the INDEX_ASC hint to use an index ordered in ascending order
Using the INDEX_DESC hint to use an index ordered in descending order
Using the AND_EQUAL hint to access multiple b-tree indexes
Using the INDEX_COMBINE hint to access multiple bitmap indexes
Forcing fast full scans with the INDEX_FFS hint
Using the ORDERED hint to specify the driving order of tables
Using the LEADING hint to specify just the first driving table
Using the NO_EXPAND hint to eliminate OR expansion
Queries involving multiple locations and the DRIVING_SITE hint
Using the USE_MERGE hint to change how tables are joined internally
Forcing the subquery to process earlier with PUSH_SUBQ
Using the parallel query option and using PARALLEL and NO_PARALLEL
Using APPEND and NOAPPEND with parallel options
Caching and pinning a table into memory with the CACHE hint
Forcing clustering with the CLUSTER hint
Forcing cluster hashing with the HASH hint
Overriding the CURSOR_SHARING setting with the CURSOR_SHARING_EXACT hint

54)What are the different types of Joins available in Oracle?
Inner join
Outer Join
Cartesian join
Full join


55)Explain where each type of Join can be used w.r.t. Different Sceanarios?
NESTED LOOPS joins are ideal when the driving row source (the records you are looking for) is small and the joined columns of the inner row source are uniquely indexed or have a highly selective non-unique index. NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined. This situation is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched.

If the driving row source (the records retrieved from the driving table or the outer table) is quite large, other join methods may be more efficient.

HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index. As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins.

SORT-MERGE Join - Requires a sort on both tables. It is built for best optimal throughput and does not return the first row until all rows are found.
HASH Join - Can require a large amount of memory for the hash table to be built. Does not return the first rows quickly. Can be extremely slow if it must do the operation on disk. Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. It is usually faster than a SORT-MERGE.

56)What is meant by Reverse Key Indexes?
It is as simple as that -- instead of all inserts hitting one side of the index, producing mass contention on one side of the index -- a reverse key index will tend to distribute the inserts throughout the entire structure more evenly.

It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table).  For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.

Reverse key indexes void the ability of an index range scan.They only work with exact equality.

With a Reverse Key Index, Oracle will reverse the bytes and insert the values sorted by the reverse bytes into the index

57)What and where do you make use of a Reverse Key Index?
For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.

58)Will a Reverse Key Index increase the performance of a Query?
Reverse key indexes void the ability of an index range scan.They only work with exact equality.Also RBO doesn't make use of a Reverse Key Index. So the Large Batch Inserts making use of Sequences benefit from the use of a Reverse Key Index

59)How do you read an Explain Plan?

The Explain plan shows the following

The row source tree is the core of the execution plan. It shows the following information:

*    An ordering of the tables referenced by the statement
*    An access method for each table mentioned in the statement
*    A join method for tables affected by join operations in the statement
*    Data operations like filter, sort, or aggregation

In addition to the row source tree, the plan table contains information about the following:

*    Optimization, such as the cost and cardinality of each operation
*    Partitioning, such as the set of accessed partitions
*    Parallel execution, such as the distribution method of join inputs

Examining an explain plan lets you look for throw-away in cases such as the following:

*    Full scans
*    Unselective range scans
*    Late predicate filters
*    Wrong join order
*    Late filter operations


60)What are the different types of Joins used by the optimizer?
The following are the joins used by the optimizer

NESTED LOOP JOIN
SORT MERGE JOIN
HASH JOIN
ANTI JOIN and
SEMI JOIN

Answers for Oracle Interview Questions

37)What is the optimizer mode which is currently followed?
Cost Based Optimizer since oracle 9i.

38)What are statistics in Oracle?
The term "Oracle statistics" may refer to historical performance statistics that are kept in STATSPACK and AWR, but a more common use of the term "Oracle statistics" is about Oracle optimizer "Metadata statistics" in order to provide the cost-based SQL optimizer with the information about the nature of the tables.

Oracle statistics tell you the size of the tables, the distribution of values within a columns, and other important information so that SQL statements will always generate the "best" execution plans when the optimizer is a cost based one.

39)Will Statistics affect the execution plan for a Query?
Since Oracle 9i, the Optimizer is a cost based optimizer which relies on the metadata statistics of a database object to determine the best execution path when executing a Query.

40)How will you collect statistics on a table?
Statistics on a table can be collected by making use of the ANALYZE Clause.In order to make use of the different options available while collecting statistics on a table, then make use of the DBMS_STATS package provided by oracle.

41)What are the different database objects on which we can collect statistics?
The following procedures are available under the DBMS_STATS Package for collecting statistics

GATHER_DATABASE_STATS Procedures
GATHER_DICTIONARY_STATS Procedure
GATHER_FIXED_OBJECTS_STATS Procedure
GATHER_INDEX_STATS Procedure
GATHER_SCHEMA_STATS Procedures
GATHER_SYSTEM_STATS Procedure
GATHER_TABLE_STATS Procedure


42)How do you check that statistics is upto date?
Statistics must be regularly gathered on database objects as those database objects are modified over time. In order to determine whether or not a given database object needs new database statistics, Oracle provides a table monitoring facility. This monitoring is enabled by default when STATISTICS_LEVEL is set to TYPICAL or ALL. Monitoring tracks the approximate number of INSERTs, UPDATEs, and DELETEs for that table and whether the table has been truncated, since the last time statistics were gathered. The information about changes of tables can be viewed in the USER_TAB_MODIFICATIONS view. Following a data-modification, there may be a few minutes delay while Oracle propagates the information to this view. Use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to immediately reflect the outstanding monitored information kept in the memory.


The LAST_ANALYZED Column on the data dictionary contains information about when was the last time that statistics was collected on the particular database object.

43)Can we Override indexes on a table in determining the execution plan for a Query?
We can by making use of the hints. The use of Hints overrides the Optimizer in choosing the execution plan for a Query.

44)What is meant by Extended Statistics?
Any statistics you collect for expressions and column groups are called "extended statistics".Expression statistics on functions enable the optimizer to obtain a vastly more accurate selectivity value for predicates that involve expressions.

You can issue the following query to find details about expression statistics on a table's columns:

SQL> select extension_name, extension
     from user_stat_extensions
     where table_name='CUSTOMERS';

45)What is the use of Extended Statistics?
The 11g extended statistics are intended to improve the optimizers guesses for the cardinality of combined columns and columns that are modified by a built-in or user-defined function.Gathering extended statistics allows histograms not only on one column, but multiple columns at a time.

46)What is Estimating Statistics?
The purpose of dynamic sampling is to improve server performance by determining more accurate estimates for predicate selectivity and statistics for tables and indexes. The statistics for tables and indexes include table block counts, applicable index block counts, table cardinalities, and relevant join column statistics. These more accurate estimates allow the optimizer to produce better performing plans.

You can use dynamic sampling to:
*    Estimate single-table predicate selectivities when collected statistics cannot be used or are likely to lead to significant errors in estimation.
*    Estimate statistics for tables and relevant indexes without statistics.
*    Estimate statistics for tables and relevant indexes whose statistics are too out of date to trust

The following estimate_percent argument is a new way to allow Oracle’s dbms_stats to automatically estimate the best percentage of a segment to sample when gathering statistics:

estimate_percent => dbms_stats.auto_sample_size

47)What is the use of Exporting and Importing Statistics?
Statistics can be exported and imported from the data dictionary to user-owned tables, enabling you to create multiple versions of statistics for the same schema. You can also copy statistics from one database to another database. You may want to do this to copy the statistics from a production database to a scaled-down test database.

The concept of exporting and importing statistics can be used when you wanna export statistics from the prod database to the test database in order to test the performance of an application Query.

48)How do you Export and Import Statistics in Oracle?
After the table is created, then you can export statistics from the data dictionary into your statistics table using the DBMS_STATS.EXPORT_*_STATS procedures. The statistics can then be imported using the DBMS_STATS.IMPORT_*_STATS procedures.

49)What are Oracle Hints?
You can use hints to specify the following:

The optimization approach for a SQL statement
The goal of the cost-based optimizer for a SQL statement
The access path for a table accessed by the statement
The join order for a join statement
A join operation in a join statement

50)When should you go in for Oracle Hints?
You can use hints to specify the following:

The optimization approach for a SQL statement
The goal of the cost-based optimizer for a SQL statement
The access path for a table accessed by the statement
The join order for a join statement
A join operation in a join statement

Answers for Oracle Interview Questions

23)What is the use of system level triggers?
You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:

System events

Database startup and shutdown
Server error message events

User events

User logon and logoff
DDL statements (CREATE, ALTER, and DROP)
DML statements (INSERT, DELETE, and UPDATE)

24)What is an Exception in Oracle?
An exception is a runtime error or warning condition, which can be predefined or user-defined. Predefined exceptions are raised implicitly (automatically) by the runtime system. User-defined exceptions must be raised explicitly by RAISE statements. To handle raised exceptions, you write separate routines called exception handlers.

25)What is the difference between an error and an Exception?
Error: Any departure from the expected behavior of the system or program, which stops the working of the system is an error.
Exception:Any error or problem which one can handle and continue to work normally.


26)What is the use of an Exception Clause in Oracle?
We make use of the exception block whenever any error or problem which occurs, has to be by passed and continue the normal workflow of the application.


27)Will the use of an Exception clause hinder the performance?
It all depends on the exception routine and the operations performed within the exception routine.

28)What are the different types of Exceptions in Oracle?
System Defined Exceptions
user defined Exceptions
Un-named Exceptions

29)What is the use of the PRAGMA EXCEPTION INIT Clause?
The pragma EXCEPTION_INIT associates an exception name with an Oracle error number. You can intercept any ORA- error and write a specific handler for it instead of using the OTHERS handler.You can use EXCEPTION_INIT in the declarative part of any PL/SQL block, subprogram, or package. The pragma must appear in the same declarative part as its associated exception, somewhere after the exception declaration.

30)What is the Usual error(Number) raised when using a BULK COLLECT Clause?
ORA-24381


31)How can you raise an Exception?
You can raise an exception using the RAISE and RAISE Application Error Clause.

32)What is the use of Re raising an Exception?
Re Raising an exception within the exception block occurs whenever some kind of auditing is required and when the audit information is recorded and the raised exception needs to be handled by the calling block or application.

33)Can we Use Exception clause for Auditing purposes? If so, How?
By making use of the RAISE Application Error Clause within the exception block after recording the audit information.

34)What is the use of SAVE Exceptions clause?
Since Oracle 9i the FORALL statement includes an optional SAVE EXCEPTIONS clause that allows bulk operations to save exception information and continue processing.  Once the operation is complete, the exception information can be retrieved using the SQL%BULK_EXCEPTIONS attribute.  This is a collection of exceptions for the most recently executed FORALL statement, with the following two fields for each exception:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX – Holds the iteration (not the subscript) of the original FORALL statement that raised the exception.  In sparsely populated collections, the exception row must be found by looping through the original collection the correct number of times.

SQL%BULK_EXCEPTIONS(i).ERROR_CODE – Holds the exceptions error code.

The total number of exceptions can be returned using the collections COUNT method, which returns zero if no exceptions were raised.  The save_exceptions.sql script, a modified version of the handled_exception.sql script, demonstrates this functionality.

35)Can we use the SAVE Exceptions clause in a normal sceanario even without using a BULK COLLECT?
No, SAVE Exceptions clause comes along with FORALL Clause.

36)What are the different Optimizer modes available in Oracle?
ALL_ROWS
FIRST_ROWS
FIRST_ROWS_n
CHOOSE

Answers for Oracle Interview Questions

10)What is a View?
A View is a Database Object which hides the complexity of the Query and it invokes the indexes on the base tables. Any DML Operation on the view applies on the base tables on which the view is based on. We make use of a INSTEAD OF TRIGGER on the view so that any DML operation on the view can applied on the base tables where is no definite correlation between the columns on the view and the columns on the base tables.Also when a View is Queried , then the underlying Query is run at that time.

11)What is a materialized View?
A Materialized View Precomputes the data or executes the Query behind the view and has its results precomputed. This is very different from the normal Oracle Views which executes the Query on run time. Since the Query results are already precomputed, Accessing Data from the Materialized View is much faster when compared to Oracle Views. Materialized Views favours the creation of indexes on the precomputed data which is not possible in the case of Oracle Views which uses the indexes on the base tables on which the Oracle View is created.

The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.ENABLE Clause Specify ENABLE to enable the materialized view for query rewrite.

Enabling of query rewrite is subject to the following restrictions:

•You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC.
•You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change).

12)Why should we go for creating a View and a materialized View?
When there is a need to hide the complexity of a Query, then we should go for creating a View for the underlying Query. On the Other hand, when you want the results of the underlying Query to be pre computed , then prolly we should go in for creating a materialized view. Also when there is a need to create indexes on the underlying view apart from the indexes on the underlying tables, we should go in for creating a materialized view.Also Materialized View is a way of replicating data from one database to another.

13)What are the different refresh techniques associated with Materialized Views?
COMPLETE
FAST
FORCE

14)Is there anything like a Parameterized View?
There is nothing like a parameteized view in oracle, but we can replicate the same by making use of Oracle Context.

15)What is Context with respect to Oracle?
There is no concept of a parameterized Views in Oracle as to parameterized Cursors in Oracle. But the Concept of a Parameterized Views can be achived
by way of a context Creation and treat the Attributes in the Context as to parameters which are passed to Parameterized Views. The View is initally created with
the help of the context attribute been used in the where clauses as filter conditions to the data retrieved. Then the calling Program should first set the attribute values
in the context and then make a call to the View.

16)What is a Trigger?
In Order to enforce a business contraint, we make use of a trigger in Oracle.Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database, or they can be written as C callouts.

You can write triggers that fire whenever one of the following operations occurs: DML statements on a particular schema object, DDL statements issued within a schema or database, user logon or logoff events, server errors, database startup, or instance shutdown.Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

17)Will the use of a Trigger hinder the performance of an application?
Yes, because each time when the triggering event occurs, the procedure within the trigger needs to be executed apart from the event which triggered it.The performance hindrance also depends upon whether its a row level or a statement level trigger.

18)What are the different types of Triggers?
Row Triggers and Statement Triggers
BEFORE and AFTER Triggers
INSTEAD OF Triggers
Triggers on System Events and User Events

19)What is meant by an Instead of Trigger?
INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.

20)What are statement level and Row Level Triggers?
A ROW TRIGGER is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

A STATEMENT TRIGGER is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

21)Can we commit inside a Trigger?
Yes we can commit inside a trigger. We can make use of the PRAGMA AUTONOMOUS TRANSACTION inside the trigger to state that the transactions made inside the trigger are independent of the calling block.

22)What is meant by Pragma?
PRAGMA is a compiler directive.Pragma is a keyword in Oracle PL/SQL that is used to provide an instruction to the compiler.

Answers for Oracle Interview Questions

1)What is RDBMS?
A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Most popular databases currently in use are based on the relational database model.

2)What is an index?
An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

4)What are the different types of Indexes?
BTree Index
Bitmap Index
Functional Index
Composite Index
Prefixed Index
Non Prefixed Index
Context Index
Partitioned Index
Clustered Index
Non Clustered Index
Reverse Key Index
Compressed Index
Invisible Index
Bitmap Join Index

5)When should you go in for a Bitmap Index?
With bitmap indexes, the optimizer can efficiently answer queries that include AND, OR, or XOR. (Oracle supports dynamic B-tree-to-bitmap conversion, but it can be inefficient.)

With bitmaps, the optimizer can answer queries when searching or counting for nulls. Null values are also indexed in bitmap indexes (unlike B-tree indexes).

We usually go in for Bitmap Index when the cardinality of the column is very low which is the count of distinct values in the column is low.

6)What is the backend architecture of Bitmap Indexes?
The real power of the bitmap index is seen when a table contains multiple bitmap indexes. With multiple bitmap indexes available, Oracle has the ability to merge the result sets from each of the bitmap indexes to quickly eliminate the unwanted data.

7)What are the different initialization parameters relating to the use of Indexes?
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ


8)What is the Use of a Functional Index?
You can't modify a column that has a function-based index applied to it. You'll have to drop the index, modify the column, and then re-create the index.
This is because the Functional Index is created by making use of a SQL Function and therefore there is a possibility that some of the SQL Functions don't work based on the data stored in the Columns on which the functional index is created.

Any user-created SQL functions must be declared deterministic before they can be used in a function-based index. Deterministic means that for a given set of inputs, the function always returns the same results. You must use the keyword DETERMINISTIC when creating a user-defined function that you want to use in a function-based index

9)What are the different initialization parameters which are considered important?
BACKGROUND_DUMP_DEST
BUFFER_POOL_KEEP
BUFFER_POOL_RECYCLE
COMPATIBLE
CONTROL_FILES
CONTROL_FILE_RECORD_KEEP_TIME
CPU_COUNT
CURSOR_SHARING
DBWR_IO_SLAVES
DB_BLOCK_SIZE
DB_CACHE_ADVICE
DB_CACHE_SIZE
DB_CREATE_FILE_DEST
DB_FILE_MULTIBLOCK_READ_COUNT
DB_KEEP_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_WRITER_PROCESSES
FAST_START_MTTR_TARGET
HASH_AREA_SIZE
HASH_JOIN_ENABLED
LARGE_POOL_SIZE
JOB_QUEUE_PROCESSES
LOG_ARCHIVE_DEST
LOG_FILE_NAME_CONVERT
OPEN_CURSORS
OPTIMIZER_DYNAMIC_SAMPLING
OPTIMIZER_FEATURES_ENABLE
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_MODE
OS_AUTHENT_PREFIX
PGA_AGGREGATE_TARGET
QUERY_REWRITE_ENABLED
QUERY_REWRITE_INTEGRITY
REMOTE_OS_AUTHENT
RESOURCE_LIMIT
RESOURCE_MANAGER_PLAN
SESSION_CACHED_CURSORS
SGA_MAX_SIZE
SHARED_POOL_RESERVED_SIZE
SHARED_POOL_SIZE
SORT_AREA_RETAINED_SIZE
SORT_AREA_SIZE
STATISTICS_LEVEL
TRACE_ENABLED
UNDO_MANAGEMENT
UNDO_RETENTION
UNDO_TABLESPACE
UTL_FILE_DIR
WORKAREA_SIZE_POLICY

Common Oracle Interview Questions

1)What is RDBMS?
2)What is an index?
3)What is the use of an Index?
4)What are the different types of Indexes?
5)When should you go in for a Bitmap Index?
6)What is the back end architecture of Bitmap Indexes?
7)What are the different initialization parameters relating to the use of Indexes?
8)What is the Use of a Functional Index?
9)What are the different initialization parameters which are considered important?
10)What is a View?
11)What is a materialized View?
12)Why should we go for creating a View and a materialized View?
13)What are the different refresh techniques associated with Materialized Views?
14)Is there anything like a Parameterized View?
15)What is Context with respect to Oracle?
16)What is a Trigger?
17)Will the use of a Trigger hinder the performance of an application?
18)What are the different types of Triggers?
19)What is meant by an Instead of Trigger?
20)What are statement level and Row Level Triggers?
21)Can we commit inside a Trigger?
22)What is meant by Pragma?
23)What is the use of system level triggers?
24)What is an Exception in Oracle?
25)What is the difference between an error and an Exception?
26)What is the use of an Exception Clause in Oracle?
27)Will the use of an Exception clause hinder the performance?
28)What are the different types of Exceptions in Oracle?
29)What is the use of the PRAGMA EXCEPTION INIT Clause?
30)What is the Usual error(Number) raised when using a BULK COLLECT Clause?
31)How can you raise an Exception?
32)What is the use of Re raising an Exception?
33)Can we Use Exception clause for Auditing purposes? If so, How?
34)What is the use of SAVE Exceptions clause?
35)Can we use the SAVE Exceptions clause in a normal scenario even without using a BULK COLLECT?
36)What are the different Optimizer modes available in Oracle?
37)What is the optimizer mode which is currently followed?
38)What is statistics in Oracle?
39)Will Statistics affect the execution plan for a Query?
40)How will you collect statistics on a table?
41)What are the different database objects on which we can collect statistics?
42)How do you check that statistics is up to date?
43)Can we Override statistics on a table in determining the execution plan for a Query?
44)What is meant by Extended Statistics?
45)What is the use of Extended Statistics?
46)What is Estimated Stats Collection?
47)What is the use of Exporting and Importing Statistics?
48)How do you Export and Import Statistics in Oracle?
49)What are Oracle Hints?
50)When should you go in for Oracle Hints?
51)Will Oracle make use of a Hint if it is specified in the Query?
52)Can we specify more than one Hint in a Query?
53)What are the different Hints that you have come across?
54)What are the different types of Joins available in Oracle?
55)Explain where each type of Join can be used w.r.t. Different Scenarios?
56)What is meant by Reverse Key Indexes?
57)What and where do you make use of a Reverse Key Index?
58)Will a Reverse Key Index increase the performance of a Query?
59)How do you read an Explain Plan?
60)What are the different types of Joins used by the optimizer?
61)What is meant by Partitioning?
62)What are local and Global Indexes?
63)What are the different Types of Partitioning Available?
64)What are the Partitioning methods which are available in Oracle 11G Onwards?
65)How do you say that partitioning increases the performance of the Queries?
66)What is meant by Skip Scan Indexes?
67)What is meant by composite partitioning?
68)What are the Different combinations of partitioning possible?
69)What do you mean by SQL Profiles?
70)What are the Automatic Tuning tools available in Oracle?
71)What is meant by a Global Temporary Table?
72)What is meant by Scalar Sub Query Caching?
73)What is Subquery Factoring?
74)What do you mean by a locally managed Tablespace?
75)What is the difference that you find with a Locally managed and a Dictionary managed Tablespace?
76)What do you mean by ASSM?
77)How come the performance of an Query increase by setting the Segment Space Management as AUTO?
78)What is the memory architecture of Oracle?
79)What is SGA and PGA?
80)What are the components of the SGA and the PGA?
81)What is meant by sga_target and pga_aggregate_target and memory_target and what are the benefits of the same?
82)What is meant by External Tables in Oracle?
83)Can we fire DML Statements on External Tables?
84)When should you go in for an External Table?
85)What is meant by SQL Profiler?
86)What is the use of SQL Profiler Tool in Oracle?
87)Have you heard of SQL Tuning Advisor and SQL Access Advisor?
89)Have you worked on the above tools?
90)What is the use of the SQL Performance Analyzer?
91)What do you mean by Fragmentation?
92)What is meant by Shared pool Fragmentation?
93)What happens on the Oracle Back end whenever you fire a Query?
94)What is parsing?
95)Can you explain the Parse, Bind , Execute and Fetch Phases?
96)What is a Cursor?
97)What are the different Types of Cursors?
98)What is a parametrized Cursor?
99)What is a reference Cursor?
100)What is meant by Context Switching?
101)Will Context Switching hinder the performance of an application?
102)How will you overcome Context Switching in Oracle?
103)What is meant by BULK COLLECT?
104)What is the use of the FORALL Clause in Oracle?
105)What are the different types of collections available in Oracle?
106)Can you explain the collection types one by one along with their pitfalls?
107)What are the different memory issues encountered when using a BULK COLLECT?
108)What is the use of a LIMIT Clause?
109)What is AWR?
110)What is ADDM?
111)What is the use of ADDM Report and Recommendations?
112)What is SQL Performance Analyzer?
113)When exactly do you go in for a SQL Performance Analyzer?
114)What is ASH?
115)What are the different Views that can be Queried for getting info on ASH?
116)What is meant by Latch?
117)How do you overcome Latches in Oracle?
118)What is the use of Database Replay?
119)How do you accept a Profile Recommended by ADDM?
120)Any idea on the Database Buffer Cache?
121)Can you throw some light on the Data Dictionary and the Library Cache?
122)How do you overcome Data Dictionary and Library Cache Contention?
123)What is the basic Structure of a PLSQL Block?
124)What are the different types of PLSQL Objects that you have come across?
125)What is the use of a Package?
126)When should you go in for a Package?
127)What is the difference between a Procedure and a Function?
128)Can a Function return more than one value?
129)Can a Function have OUT Parameters?
130)What is meant by a Function Based Index?
131)What is benefit of using Functions?
132)Can we create an index on an user defined Functions?
133)What is the use of the RESULT_CACHE Keyword in Functions?
134)What do you mean by a global Parameter?
135)Can a Global parameter be referenced by some other packages or PLSQL?
136)What is the use of an UNDO Tablespace?
137)What is meant by FLASHBACK?
138)How do you flashback a Query?
139)Can we flashback a Table and database?
140)What is Snapshot too Old Error?
141)What is the main use of a Global Temporary Table?
143)What do you mean by Cursor Sharing?
144)What do you mean by Adaptive Cursor Sharing?
145)What are the different values for the Cursor Sharing Parameter?
146)What are the parameters which influence influence the sorting of Query Results?
147)Why and When Sorting Hinders the performance of an application?
148)Whats the use of temporary Tablespace w.r.t. sorting?
149)What are the different operations which involve disk I/O?
150)What is meant by Row chaining?
151)What is meant by Row Migration?
152)When does a row get chained and migrated?
153)How do you verify whether there the rows are chained or migrated in a table?
154)What is meant by Paging?
155)What is meant by swapping?
156)When does this paging and swapping occur?
157)How do you overcome Paging and Swapping?
158)Will a "!" negate the use of Indexes?
159)What happens on the back end when we do a COMMIT statement?
160)How Often do you COMMIT in your application?
161)Will the frequency of COMMIT hinder the performance of an application?
162)Why Should we need to index a foreign Key?
163)How do you identify whether Indexes are used by the application?
164)Whats the use of the QUERY REWRITE in materialized views in Oracle?
165)Any Idea about Histograms?
166)How Does Collecting Histograms influence the performance of an Query?
167)Whats the use of the KEEP and RECYCLE Buffer Pools in Oracle?
168)What is the HIGH WATER MARK LEVEL w.r.t. Oracle?
169)Whats the use of the Bitmap Join Indexes?
170)What are prefixed and non prefixed Indexes?
171)How do you shrink Unused Space?
172)When do you think that a Index needs to be rebuilt?
173)Any idea on Compressed Indexes in Oracle?
174)What is Virtual Column in Oracle 11G?
175)What are the benefits of using Virtual Column in terms of indexing and partitioning?
176)What is the "Maximum number of cursors exceeded" and how do you overcome it?
177)Whats the difference between a scalar sub Query and an Inline View?
178)Can we lock statistics on a Table?
179)Will the use of a DB Link hinder the performance of a Query?
180)What are the things to be looked for in a AWR Report?
181)What is meant by SQL Trace?
182)Any idea on the OPTIMIZER_DYNAMIC_SAMPLING Parameter?
183)What does cost refer to in a cost based optimizer?
184)What do you mean by stored outlines in Oracle?
185)What are the caveats of Bitmap join Indexes?
186)What do you mean by pipe lined table functions?
187)What is Consistent gets in Oracle?
188)What is the Sequence Caching in Oracle?
189)Will Data type conversion hinder the performance of a Query?
190)What are the different values for the STATISTICS_LEVEL parameter?
191)Any idea on the DB_FILE_MULTIBLOCK_READ_COUNT parameter?
192)When does Oracle go in for a FULL Table scan?
193)Will a FULL Table scan always hinder performance?
194)What does "B" in a Btree Index denote?
195)Any idea on the height of an index and what does that denote?
196)What is meant by Invisible Indexes in Oracle 11G?
197)What is meant by a Cartesian join in Oracle?
198)What is meant by partitioning pruning?
199)Any idea on IN MEMORY , ONE PASS AND MULTI PASS SORT?
200)What is the use of the overflow segment while creating an Index Organized tables?
201)What are the disadvantages of IOT?
202)Can we create secondary indexes on IOT's?
203)Whats the use of a reverse key indexes and where do you make use of a reverse key indexes?
204)What are the processes involved in a Database Replay?
205)What are the processes involved in a SQL Performance Analyzer?
206)What do you mean by session cached cursor?
207)Whats the use of a LARGE POOL?
208)What is SCN?
209)Can we flashback a DB on a particular SCN?
210)What is an Oracle Sub Type?
211)What is an Oracle Super Type?
212)What's the difference between an Oracle Type and Oracle Sub Type?
213)What's the use of PLSQL WARNINGS?
214)What's the use of Oracle Referenece Datatypes?
215)What's the use of RECORD in Oracle PLSQL?
216)Does an Oracle Type has a Body?
217)Whats the use of an Oracle Type?
218)Where exactly do you make use of an Oracle Type?
219)Do we need to create an Oracle Collection after creating an Oracle Object?
220)Can we use an Oracle Object without specifying the Collection for the Object created?If So , Then in what scenarios?
221)What do you mean by nested table?
222)Where exactly do you make use of a nested table?
223)What do you mean by CONSTRUCTOR FUNCTION and MEMBER FUNCTION?
224)CAn we ALTER a type which is already created?
225)Can we add an attribute to an Object already Created?What Option do we use? (Hint CASCADE OPTION)
226)What's the use of the FINAL Keyword while defining an Object?
227)Whats the use of USER_PLSQL_OBJECT_SETTINGS like PLSQL_OPTIMIZE_LEVEL , PLSQL_DEBUG etc.
228)What does the word deterministic mean?
229)Whats the use of DBMS_ALERT?
230)What is a pipelined function?
231)Can a function have an OUT Parameter?
232)Can we use any type of file as an external file for loading into a table as an External Table?
233)Can we load the contents of a zip file into an External Table?
234)What is the cost factor on the explain plan denote?
235)Whats the Difference that you find between a Nested Table Collection and an Associative Array Collection.
236)How to Initialize an Oracle Collection?
237)Whats the difference between dense and sparse collections?
238)How do you classify the Nested Tables, Associative Arrays and Varrays as sparse or Dense Collections?
239)Can we Assign Values to a collection before initializing it?
240)Can we Assign one collection to another?
241)Can we scan the contents of a collection from LAST to FIRST?
242)What is a RECORD?
243)Will a Parsing happen when using CURSORS and REFCURSORS?
244)Whats the difference between a Regular CURSORS and REFCURSORS?