Archive for January, 2013

Hello friends

Some of my friends always ask for such scripts through which they can monitor the Instance,Database and all that related to oracle.
So, after a long a research i have came with some handy scripts which you can find very handy to help you in real situations.

Note: Please test the scripts before applying it to the Production database server.and you must have privilege as a sysdba.

Some Monitoring Scripts are as below…….

1. Select Poor Performing sql queries….

SELECT sql_text, executions,
ROUND(disk_reads / executions, 2) reads_per_run,
disk_reads, buffer_gets,
buffer_gets, 2) hit_ratio,
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets-disk_reads) / buffer_gets < 0.80
ORDER by 3 desc ;

2. Find sql query being executed by the os.

First find the Process Id of the The OS via “top” command in linux.
Process ID (PID)
prompt “Enter The Linux Process ID”
set long 999999
set pagesize 50000
set linesize 30000
set long 500000
set head off
s.username su,
substr(sa.sql_text,1,1000) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
3. To Find out the blocked,waiting sessions

col high_event format a40
col wait_event format a40
select s.sid blocker,
s.event high_event,
w.event wait_event,
w.sid blocked
from v$session s, v$session w
where w.blocking_session = s.sid
and w.blocking_session_status=’VALID’;

4. Watch out session

select sid,serail# from v$session;

(After getting the sid and serial# for example– 42,67)

Long operations

select sid,username Username,serial#,opname,target,sofar,totalwork total_work,units,
time_remaining remaning_time,elapsed_seconds elapsed_time,last_update_time last_time_updated
from v$session_longops where sid=42
order by last_update_time desc;

All active sessions
select * from v$session where status=’ACTIVE’
and sid in (42,67)
order by sid;

Find session’s sid or process id by it’s sid or process id

select sid, a.serial#, spid, a.username,
status, taddr, a.program
from v$session a, v$process b
where a.paddr=b.addr and a.username is not null
and (sid=42 or spid=28179)
order by status, sid;

5. Check the size of db

Prompt “Total Size Of Database Including all”

SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size
“total_size in GB”
FROM (SELECT SUM (bytes) / 1024 / 1048756 data_file_size FROM dba_data_files) a,
(SELECT NVL (SUM (bytes), 0) / 1024 / 1048756 temp_file_size
FROM dba_temp_files) b,
(SELECT SUM (bytes) / 1024 / 1048756 redo_log_size FROM sys.v_$log) c,
FROM v$controlfile) d;

6. Sga (System Global Area) free memory

select name,
sgasize/1024/1024 “Allocated (MEG)”,
bytes/1024 “Free (KB)”,
round(bytes/sgasize*100, 2) “% Space Free”
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = ‘free memory’;

7. Archive Generation Monitoring 

Archive log Hourly Generation Monitoring

set pages 2000
select trunc(COMPLETION_TIME,’HH’) Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,’HH’),thread# order by 1 ;

Archive log Weekly Generation Monitoring

SELECT to_date(first_time) DATE,

to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”,
to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’99’) “03”,
to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’99’) “04”,
to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’99’) “05”,
to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’99’) “06”,
to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’99’) “07”,
to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’99’) “08”,
to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’99’) “09”,
to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’99’) “10”,
to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’99’) “11”,
to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’99’) “12”,
to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’99’) “13”,
to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’99’) “14”,
to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’99’) “15”,
to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’99’) “16”,
to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’99’) “17”,
to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’99’) “18”,
to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’99’) “19”,
to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’99’) “20”,
to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’99’) “21”,
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’99’) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’99’) “23”
where to_date(first_time) > sysdate -8
to_char(first_time,’YYYY-MON-DD’), to_date(first_time)
order by to_date(first_time)

Hope it is useful, in a short time I will come with some “Oracle Performance Monitoring Scripts”,
Thanks & Regards
Nimai Karmakar

Read Full Post »

How to Create a PPK file using Putty Gen

Today I will share you some knowledge about my experience in Putty to create a ppk for a server,

Yesterday one of our client ask to create a user in a Ubuntu server and to send him back the ppk file for that user so that he can connect to that user using that ppk. After a long research on Google i have found the way to do that

Note:- why we create ppk file for the user?

When we want to restrict a user to only connect through users with restricted permission so that he/she can do only those jobs or works that he/she is permitted only….

So here we go….

Start puttygen.exe

Click on generate to generate the key for the user. And follow the instruction to generate key

Once the key is generated give the key comment as “user@server”

Now copy the key generated in puttygen

Go to system which you want to connect to, Via any another user.

Open the .ssh/authorized_key file

(for that user whom you are generating the ppk)

Vi  home/username/.ssh/authorized_key

Paste the key here and save it


Now come to puttygen save the private key with the name as you wished.

Connect through the new ppk file.


Give the host name as the server host name.

Go to auth and browse for the newly created ppk file that you have saved. And connect


That it…

Thanks & Regards

Nimai Karmakar

Read Full Post »

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

Ayurveda and Yoga

Site for Ayurveda and Yoga articles


Few adventures in my slow running life...


4 out of 5 dentists recommend this WordPress.com site


Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World

Life Is A Mystery

This Is Wihemdra's Blog...I Share What I Have

ursvenkat's Blog

Oracle Applications DBA Blog