Saturday, 16 July 2022

Oracle Query to check OS PID from SID

Oracle Query to check OS PID from SID

How to find OS Process ID using Session ID in oracle?

Below query will help you extract the PID on OS using SID. Normally in OS, different processes takes care of different operations and the same goes for Oracle as well. In random scenarios, you could be interested in finding PID for some operation running within database for example, a background job for which you want to track the resource consumption. The below query will help in such scenarios.

Set lines 200
col sid format 99999
col username format a15
col osuser format a15
select a.sid, a.serial#,a.username, a.osuser, b.spid
from v$session a, v$process b
where a.paddr= b.addr
and a.sid='&sid'
order by a.sid;
/

How to find SQL Text From SID?

How to find SQL Text From Session ID?

The below queries would help you find SQL Text running behind some Oracle SID.


col sql_text form a80
set lines 120
select sql_text from gv$sqltext where hash_value=
(select sql_hash_value from gv$session where sid=&1)
order by piece
/

It will prompt you to enter SID for which you want to find running SQL Query, (here i am entering my own SID so it will show you the same query with provided details)

SQL> SQL> 2 3 4
Enter value for 1: 175 old 2: (select sql_hash_value from gv$session where sid=&1)
new 2: (select sql_hash_value from gv$session where sid=175)

SQL_TEXT
--------------------------------------------------------------------------------
select sql_text from gv$sqltext where hash_value= (select sql_ha
sh_value from gv$session where sid=175) order by piece

2 rows selected.

Thursday, 17 February 2022

ORA-15080 ORA-27070 ORA-63999 ORA-01114 ORA-15081 in alert log

ORA-15080: synchronous I/O operation to a disk failed | ORA-27070: async read/write failed | ORA-15081: failed to submit an I/O operation to a disk

These Oracle errors would lead Oracle instance to crash and won't let it start till you fix the issue. The below IO errors shows the issue with Oracle Storage Management when there's some issue while performing write operation on Oracle file. 

Wednesday, 13 January 2021

Windows Batch/Powershell Script to Send EMail with Attachments

Automatic Mail Sending through Batch/Powershell Scripting on Windows

This guide is for those who are looking for the simplest way to trigger mails from their Windows Servers along with attachments (if required). The same mechanism can be configured with Task Scheduler to trigger it on regular intervals OR whenever you want it to run.

The whole concept requires 2 different scripts,

1) BAT file

2) Powershell Script

Monday, 24 August 2020

Difference between Direct Path Load and Conventional Load

Difference between Direct Path Load and Conventional Load

This article will help you quickly understand the concept of direct  path load and conventional load within Oracle Database. 

So to begin, SQL*Loader provides 2 methods for loading data,

  • Conventional Path Load (This is by default used)
  • Direct Path load

Sunday, 1 September 2019

Selecting data from table and Writing to file in Oracle PL/SQL using UTL_FILE (File handling)

Using Oracle supplied package UTL_FILE we can perform file operations like Reading a File, Writing a File, Deleting File & also coping files.
Before you start make sure to create DB directory & to provide required required grants to your user.

Wednesday, 28 August 2019

GET EXACT ERROR LINE NUMBER IN PL/SQL USING FORMAT_ERROR_BACKTRACE


DBMS_UTILITY package provides FORMAT_ERROR_BACKTRACE procedure which returns exact line no of error source along with the error source name.