Sunday, September 12, 2010

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.


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

Saturday, September 4, 2010

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).



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


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