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). […]
Monthly Archives: January 2018
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 […]
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.
|
1 2 3 4 |
select p.spid, s.username, s.status, s.server, to_char(s.logon_time,'DD-MON-YY HH24:MI') from v$process p, v$session s where p.addr = s.paddr and s.status='ACTIVE' ; |
I have connected through sys user :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[oracle@UAT-MIGR01 ~]$ sqlplus sys/oracle as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Wed Jan 10 14:04:51 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production SQL> select name,open_mode from v$database; NAME OPEN_MODE --------- -------------------- TESTDB READ WRITE SQL> |
Now checking processid using above script.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SQL> select p.spid, s.username, s.status, s.server, to_char(s.logon_time,'DD-MON-YY HH24:MI') from v$process p, v$session s where p.addr = s.paddr and s.status='ACTIVE' and s.username='SYS'; ; 2 3 4 SPID USERNAME STATUS SERVER ------------------------ ------------------------------ -------- --------- TO_CHAR(S.LOGON --------------- 14399 SYS ACTIVE DEDICATED 10-JAN-18 14:04 SQL> |
check this processid 14399 from os […]
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: […]
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 […]
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
SELECT e.owner, e.segment_type, e.segment_name, e.partition_name, c.file#, greatest(e.block_id, c.block#) corr_start_block#, least(e.block_id+e.blocks-1, c.block#+c.blocks-1) corr_end_block#, least(e.block_id+e.blocks-1, c.block#+c.blocks-1)- greatest(e.block_id, c.block#) + 1 blocks_corrupted, null description FROM dba_extents e, v$database_block_corruption c WHERE e.file_id = c.file# AND e.block_id <= c.block# + c.blocks - 1 AND e.block_id + e.blocks - 1 >= c.block# UNION SELECT s.owner, s.segment_type, s.segment_name, s.partition_name, c.file# , header_block corr_start_block# , header_block corr_end_block#, 1 blocks_corrupted , 'Segment Header' description FROM dba_segments s, v$database_block_corruption c WHERE s.header_file = c.file# AND s.header_block between c.block# and c.block# + c.blocks - 1 UNION SELECT null owner, null segment_type, null segment_name, null partition_name, c.file# , greatest(f.block_id, c.block#) corr_start_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) corr_end_block# , least(f.block_id+f.blocks-1, c.block#+c.blocks-1) - greatest(f.block_id, c.block#) + 1 blocks_corrupted , 'Free Block' description FROM dba_free_space f, v$database_block_corruption c WHERE f.file_id = c.file# AND f.block_id <= c.block# + c.blocks - 1 AND block_id + f.blocks - 1 >= c.block# order by file#, corr_start_block#; |
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 […]
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.
|
1 |
select sql_id from v$session where sid = :x |
Step2: Login as SYSTEM (or any other user) at […]
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 […]
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 […]