Oracle Interview Questions - 5

What is an aggregate function? Compare with analytic function.

Aggregate function operates against a collection of values, but returns a single value. Analytic functions differ from aggregate functions in that they return multiple rows for each group.

Describe an autonomous transaction.

An autonomous transaction is an independent transaction that is initiated by another transaction, and executes without interfering with the parent transaction. When an autonomous transaction is called, the originating transaction gets suspend. Control is returned when the autonomous transaction does a COMMIT or ROLLBACK.

A procedure can be marked as autonomous by declaring it as PRAGMA AUTONOMOUS_TRANSACTION;. You may need to increase the TRANSACTIONS parameter to allow for the extra concurrent transactions.

What is a bind variable and why to use it?

A variable in a SQL statement that must be replaced with a valid value, or the address of a value, in order for the statement to successfully execute.

Used to minimize hard parses and maximize SQL reuse in the shared pool.

PL/SQL itself takes care of most of the issues to do with bind variables.

The only time you need to consciously decide to use bind variables when working with PL/SQL is when using Dynamic SQL.

Describe BULK COLLECT.

This is a form of array processing inside PL/SQL, which makes possible high-speed retrieval of data.

When data involved is very large, we can use Bulk Collect clause to fetch the data into local PL/SQL variables faster without looping through one record at a time. We can store the result set into either individual collection variables, if we are fetching certain number of columns or collection records, if we are fetching all the columns of the table.

Oracle Database achieves significant performance gains with this statement by greatly reducing the number of context switches between the PL/SQL and SQL statement execution engines.

What is a cluster?

Optional structure for storing table data. Clusters are groups of one or more tables physically stored together because they share common columns and are often used together. Because related rows are physically stored together, disk access time improves.

Describe collections and collection types.

Oracle uses collections in PL/SQL the same way other languages use arrays. Oracle provides three basic collections, each with an assortment of methods.

-Index-By Tables

-Nested Table Collections

-Varray Collections

What is a compiler?

A program to translate source code into code to be executed by a computer (object code)

What is a correlated subquery and how it my effect performance?

A subquery that uses values from the outer query. The Oracle database wants to execute the subquery once and use the results for all the evaluations in the outer query. With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.

What is a data block?

Smallest logical unit of data storage in an Oracle database. Also called logical blocks, Oracle blocks, or pages. One data block corresponds to a specific number of bytes of physical database space on disk.

What is data dictionary?

The central set of tables and views that are used as a read-only reference about a particular database. A data dictionary stores such information as:

-The logical and physical structure of the database

-Valid users of the database

-Information about integrity constraints

-How much space is allocated for a schema object and how much of it is in use

A data dictionary is created when a database is created and is automatically updated when the structure of the database is updated.

What means a Data mining?

The computer-assisted process of digging through and analyzing enormous sets of data and then extracting the meaning of the data. Data mining tools predict behaviors and future trends, allowing businesses to make knowledge-driven decisions. Data mining tools can answer business questions that traditionally were too time consuming to resolve. They search databases for hidden patterns, finding predictive information that experts may miss because it lies outside their expectations.

What is a data type?

The attribute of a field that determines the kind of data the field can contain.

Explain concepts of a data warehouse?

A data warehouse is a relational database that is designed for query and analysis. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine, client analysis tools, and other applications that manage the process of gathering data and delivering it to business users.

Most data warehouses use a staging area to clean and process your operational data before

You may want to customize your warehouse's architecture for different groups within your organization. You can do this by adding data marts, which are systems designed for a particular line of business

Describe a database trigger with some details

A database trigger is a stored procedure that Oracle invokes ("fires") automatically when certain events occur, for example, when a DML operation modifies a certain table. Triggers enforce business rules, prevent incorrect values from being stored, and reduce the need to perform checking and cleanup operations in each application.

Here are some important items to remember about triggers.

-On insert triggers have no :OLD values.

-On delete triggers have no :NEW values

-Triggers do not commit transactions. If a transaction is rolled back, the data changed by the trigger is also rolled back.

-Commits, rollbacks and save points are not allowed in the trigger body. A commit/rollback affects the entire transaction, it is all or none.

-Unhandled exceptions in the trigger will cause a rollback of the entire transaction, not just the trigger.

-If more than one trigger is defined on an event, the order in which they fire is not defined. If the triggers must fire in order, you must create one trigger that executes all the actions in the required order.

-A trigger can cause other events to execute triggers.

-A trigger can not change a table that it has read from. This is the mutating table error issue.

Describe the package DBMS_PIPE.

Package DBMS_PIPE allows different sessions to communicate over named pipes. (A pipe is an area of memory used by one process to pass information to another.) You can use the procedures pack_message and send_message to pack a message into a pipe, then send it to another session in the same instance.