What is autonomous transaction in Oracle?

• An autonomous transaction is an independent transaction that is initiated by another transaction (the parent transaction).
• An autonomous transaction can modify data and commit or rollback independent of the state of the parent transaction. 
• The autonomous transaction must commit or roll back before the autonomous transaction is ended and the parent transaction continues. 
• An autonomous transactions is available from Oracle 8i. 
• An autonomous transaction is defined in the declaration of a pl/sql block. This can be an anonymous block, function, procedure, object method or trigger.
• This is done by adding the statement 'PRAGMA AUTONOMOUS_TRANSACTION;' anywhere in the declaration block. 
• Autonomous transactions can be used for logging in the database independent of the rollback/commit of the parent transaction.

      CREATE OR REPLACE PROCEDURE test

      IS

             PRAGMA AUTONOMOUS_TRANSACTION;

      BEGIN

             insert ....

             commit;

      END;

What are ORACLE PRE-COMPILERS?

• Using ORACLE PRE-COMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. 
• The Precompilers are known as Pro*C,Pro*Cobol,This form of PL/SQL is known as embedded pl/sql,the language in which pl/sql is embedded is known as the host language. 
• The pre-compiler translates the embedded SQL and pl/sql ststements into calls to the precompiler runtime library.The output must be compiled and linked with this library to create an executable.

How you can coalesce free space in oracle?

• SMON coalesces free space (extents) into larger, contiguous extents every 2 hours and even then, only for a short period of time. 
• SMON will not coalesce free space if a tablespace's default storage parameter "pctincrease" is set to 0. 
• With Oracle 7.3 one can manually coalesce a tablespace using the ALTER TABLESPACE tblspace_temp COALESCE; command, until then use 

          SQL> alter session set events 'immediate trace name coalesce level n'

          Where 'n' is the tablespace number.

          SELECT TS#, NAME FROM SYS.TS$;

• You can get status information about this process by selecting from the SYS.DBA_FREE_SPACE_COALESCED dictionary view.

What is the need of rebuilding of an index?

          ANALYZE INDEX  INDEX_NAME VALIDATE STRUCTURE;

• Using above statement we can rebuild a Index.
• Each command creates a single row in the V$INDEX_STATS view. This row is overwritten by the next ANALYZE INDEX command, so copy the contents of the view into a local table after each ANALYZE. 
• The 'badness' of the index can then be identified DEL_LF_ROWS/LF_ROWS.

What is Row Level Lock in Oracle?

• Oracle automatically acquires locks which are row-level locks. A transaction can held no of row locks and oracle does not work on it.
• Row locking provides the lowest level of locking. It provides the best possible transaction concurrency.Readers of data do not wait for writers of the same data rows.  
• A modified row is always locked exclusively so that other users cannot modify the row until the transaction holding the lock is committed or rolled back.
• If a transaction obtains a row lock for a row, the transaction also acquires a table lock for the corresponding table. The table lock prevents conflicting DDL operations that would override data changes in a current transaction.
• A Transaction gets an exclusive DML row level lock for each row modified by any of the following statements: INSERT, UPDATE, DELETE, and SELECT with the FOR UPDATE clause.

What are the options for SHUTDOWN a DATABASE?

SHUTDOWN NORMAL

Normal database shutdown proceeds with the following conditions:

• No new connections are allowed after the statement is issued.
• Before the database is shut down, Oracle waits for all currently connected users to disconnect from the database.
• The next startup of the database will not require any instance recovery procedures.

SHUTDOWN IMMEDIATE

Use immediate database shutdown only in the following situations:

• A power shutdown is going to occur soon.
• The database or one of its applications is functioning irregularly.
• Immediate database shutdown proceeds with the following conditions:
• Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
• Oracle does not wait for users currently connected to the database to disconnect; Oracle implicitly rolls back active transactions and disconnects all connected users.

The SHUTDOWN IMMEDIATE statement disconnects all existing idle connections and shuts down the database. If, however, you have submitted processes (for example, inserts, selects or updates) that are awaiting results, the SHUTDOWN TRANSACTIONAL statement allows the process to complete before disconnecting.

SHUTDOWN TRANSACTIONAL

When you wish to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL option:

• After submitting this statement, no client can start a new transaction on this instance. If clients attempt to start a new transaction, they are disconnected. After all transactions have completed, any client still connected to the instance is disconnected. At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.
• A transactional shutdown prevents clients from losing work, and at the same time, does not require all users to log off. 

SHUTDOWN ABORT

You can shut down a database instantaneously by aborting the database's instance. If possible, perform this type of shutdown only in the following situations:

• The database or one of its applications is functioning irregularly and neither of the other types of shutdown work.
• You need to shut down the database instantaneously (for example, if you know a power shutdown is going to occur in one minute).

You experience problems when starting a database instance.

Aborting an instance shuts down a database and yields the following results:

• Current client SQL statements being processed by Oracle are immediately terminated.
• Uncommitted transactions are not rolled back.
• Oracle does not wait for users currently connected to the database to disconnect; Oracle implicitly disconnects all connected users.

How to switch a NOARCHIVELOG mode database into ARCHIVELOG mode? Please explain.

In order to switch to ARCHIVELOG mode from NOARCHIVELOG mode do the following:
edit your init.ora or spfile with the following information

1. create a archive log directory in a path and specify it in init.ora file.

log_archive_dest="/oradata/archlog"
log_archive_format="%t_%s.dbf"
log_archive_start=true

2. Once specifying the path-shut down the database and follow below steps:

shutdown database;
startup mount;
alter database archivelog;
alter database open;

Now, the database is in archivelog. Check it using
SELECT LOG_MODE FROM SYS.V$DATABASE;

How the Explain Plan Works?

Whenever any read or write data in Oracle, it issuing an SQL statement. One of Oracle's task when it receives such a statement is to build a query execution plan.

An execution plan defines how Oracle finds or writes the data. For example, an important decision that Oracle has to take is if it uses indexes or not. And if there are more indexes, which of these is used. All this is contained in an execution plan.

If one wants to explore such an execution plan, Oracle provides the SQL statement EXPLAIN PLAN to determine this.

The general syntax of EXPLAIN PLAN is:

explain plan for your-precious-sql-statement;

If you do an EXPLAIN PLAN, Oracle will analyze the statment and fill a special table with the Execution plan for that statement. You can indicate which table has to be filled with the following SQL command:

explain plan into table_name for your-precious-sql-statement;

If you omit the INTO TABLE_NAME clause, Oracle fills a table named PLAN_TABLE by default.

The Plan Table

The plan table is the table that Oracle fills when you have it explain an execution plan for an SQL statement. You must make sure such a plan table exists. Oracle ships with the script UTLXPLAN.SQL which creates this table, named PLAN_TABLE (which is the default name used by EXPLAIN PLAN).

UTLXPLAN.SQL located under $ORACLE_HOME\RDBMS\ADMIN.

DBMS_OUTPUT in Oracle

Oracle built in with a set of built-in packaged procedures that provide a wealth of functionality for DBA's and developers.

DBMS_OUTPUT

You can use this standard packaged procedure to write messaged to the buffer area and later retrieve those messages.
One of the remarkable usage of this packaged procedure is it capability to display the buffer to your screen if you are using SQLDBA or SQLPLUS.

DBMS_OUTPUT.PUT_LINE(message varchar2);

This procedure is used to Write a message to the session's buffer. You can invoke DBMS_OUTPUT.PUT_LINE(message). either within a PL/SQL block or directly from SQL prompt.

DBMS_OUTPUT.GET_LINE( Message out Varchar2 , Status out integer)
Once this procedure is executed it will return the buffer line into the (Message) variable and will return the Status into the (Status) variable. If a line of information is found in the buffer, the procedure will return a zero in the Status variable, otherwise status is <> 0

What is ROWID in Oracle?

The ROWID is a unique database-wide physical address for every row on every table. Once assigned (when the row is first inserted into the database), it never changes until the row is deleted or the table is dropped.

The ROWID consists of the following three components, the combination of which uniquely identifies the physical storage location of the row.

* Oracle database file number, which contains the block with the rows
* Oracle block address, which contains the row
* The row within the block (because each block can hold many rows)

The ROWID is used internally in indexes as a quick means of retrieving rows with a particular key value. Application developers also use it in SQL statements as a quick way to access a row once they know the ROWID

Partitioning in Oracle

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a partition. From an Application Development perspective, there is no difference between a partitioned and a non-partitioned table. The application need not be modified to access a partitioned table if that application was initially written on a non partitioned tables.

Oracle introduced partitioning like Range Partitioning, Hash Partitioning, Composite Partitioning, List Partitioning,etc. Each method of partitioning has its own advantages and disadvantages and the decision which one to use will depend on the data and type of application. Also one can MODIFY, RENAME, MOVE, ADD, DROP, TRUNCATE, SPLIT partitions.

Advantages of using Partition’s in Table

1. Smaller and more manageable pieces of data (Partitions)

2. Reduced recovery time

3. Failure impact is less

4. Import / Export can be done at the Partition Level.

5. Faster access of data

6. Partitions work independent of the other partitions.

7. Very easy to use

What is SQL*Loader and why it is used for?

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.

One can load data into an Oracle database by using the sqlldr utility. Invoke the utility without arguments to get a list of available parameters.

Faster SQL performance with dbms_stats

While executing a SQL Query, the execution plan determines the best way to retrieve the data most efficiently. For Oracle, there are many way to retrieve the data from a SQL Query, like which index need to be use, order of joins to be performed and which internal join methods to be use like, hash joins, star joins, and sort merge join, etc. These execution plans are computed by the Oracle’s Cost-Based Optimizer, widely known as CBO.

The choice of executions plans made by the Oracle SQL Optimizer is only as good as the Oracle statistics. Always choose the best execution plan for a SQL query, Oracle depends on information about the tables and indexes in the query.

Using dbms_stats package, Oracle provides a simple way for the Oracle developer to collect statistics for the CBO. The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats results in faster SQL execution plans.

The old-fashioned method is Analyzing a table and using methods of dbms_utility to generating CBO statistics are obsolete and somewhat dangerous to SQL performance because they don't always capture high-quality information about tables and indexes. The CBO determines using the the object statistics to choose the best execution plan for all SQL Queries.

The dbms_stats utility does a far better job in estimating statistics, especially for large partitioned tables, and the better stats result in faster SQL execution plans.