Virtual Index or Fake Index in Oracle

Oracle has come up with a feature called virtual index in oracle 9i. This feature allow us to test an index on a table without actually adding an index on the table. The table will be visible only in our session and will be used by our queries only (if optimizer decide it to use). […]

Read More

Block-Related Oracle Rac Wait Events – Part 1

The block-related wait event statistics indicate that a block was received as either the result of a 2-way or a 3-way message, that is, the block was sent from either the resource master requiring 1 message and 1 transfer, or was forwarded to a third node from which it was sent, requiring 2 messages and […]

Read More

Check os processid of your session from sql prompt.

To check os processid of oracle session , we can check v$process view. SPID column of v$process view shows os processid. We can use following query to check processid, status for specific user.

I have connected through sys user :

Now checking processid using above script.

check this processid 14399 from os […]

Read More

ORA-30032: The Suspended (Resumable) Statement Has Timed Out

PROBLEM: While doing a transaction, it was hung for few seconds and got the below error. SQL> create TABLE TEST_3 as select * from dba_objects; create TABLE TEST_3 as select * from dba_objects * ERROR at line 1: ORA-30032: the suspended (resumable) statement has timed out   CAUSE & SOLUTION: Let’s check the alert log: […]

Read More

Basic on Oracle RAC wait events

Analyzing and interpreting what causes sessions to wait is an important method to determine where time is spent. In Oracle RAC, the wait time is attributed to an event which reflects the exact outcome of a request. For example, when a session on an instance is looking for a block in the global cache, it […]

Read More

Find corrupted objects in Oracle using sql query

Find corrupted objects in Oracle using sql query : When you find block corruption in your database use validate database or validate check logical database command of RMAN, it will populate v$database_block_corruption view with corrupted blocks. Then you can check corrupted segments , its partition name and more details using following script.

   

Read More

What happens in the background in Rebuild Index

I must thank my fellow DBA Franky Weber Faust for his publication in his blog. I’ll show you what happens during an online rebuild of an index. Few DBAs realize this, but during this operation an auxiliary index (among other things) is created to store the transient data while the application continues to run normally. Note that for […]

Read More

Create an Oracle SQL TUNING TASK with DBMS_SQLTUNE

You can create an SQL TUNING TASK manually adhoc with the following simple steps. Step1: Find the sql_id of the oracle session you would like to analyze. Usually the AWR has the top sql_ids. In case this is a current sql running use the v$session.

Step2: Login as SYSTEM (or any other user) at […]

Read More

How to extract SQL from SQLT

SQLTXPLAIN gets installed into separate schema called SQLTXPLAIN. It can be installed on RAC and on any version greater than 9i. When it is installed, it will ask for application schema so make sure that installation schema has SELECT_CATALOG_ROLE privilege. In order to run the script to create SQLTXPLAIN schema, one need to connect as […]

Read More

How to recover database without knowing DBID or DB Name

This article is a bit different from other recovery and backup case studies. The solution to this scenario has given by my fellow DBA Sham. Being a DBA we all are must aware of some different conditions that can occur in different conditions. In this post, we are going to learn about the steps which […]

Read More