Scripts to Check Library Cache Lock Contention

One of the best sites for scripts related to checking library cache locks…

Script to Check Total No. of Blocks in Buffer Cache for each User Object

COLUMN object_name FORMAT A40

COLUMN number_of_blocks FORMAT 999,999,999,999

SELECT o.object_name, COUNT(*) number_of_blocks


WHERE o.data_object_id = bh.OBJD

AND o.owner != ‘SYS’

GROUP BY o.object_Name


ASH Script for Finding out Top Wait Events in Oracle

prompt ************************************
prompt ************************************
set lines 999

SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available duration FROM v$active_session_history;

select * from (
select NVL(event,’CPU’) event,count(*),
round((ratio_to_report(sum(1)) over ()*100),1) rr
from gv$active_session_history
WHERE user_id0
AND sample_timetrunc(sysdate-1)
group by event
order by 2 desc
) where rownum<10;

ksvcreate: Process(m000) creation failed. Error in Alert log due to DNS ip change.

Today on one of our 10g (, don’t ask !) production databases  running on AIX 5.3 we saw the following message in the alert log.

ksvcreate: Process(m000) creation failed

The database slowed down to a crawl. We couldn't login even SQLPLUS, even sqlplus -prelim couldn't bring up the instance. On starting the instance we got the below error.

ORA-00445: background process "PMON" did not start after 120 seconds

After googling around, we figured out it could be related to a DNS change. Our old DNS was commissioned and the new ones were not added to the resolv.conf file. After adding the new name servers. The instance started immediately without any issues. I'm still wondering what is the relation between the Oracle instance startup and the DNS ip's. Could this be a bug specific to or is it something more generic.

Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands

For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites

1. Oracle client should be installed on the Unix/Linux Server

2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands

3. All databases should have one common user with a common password (this method is not the safest method
since the password will be in plain text in your Shell script)
Now let us proceed to first create a text file called dbnodes.txt which will have the TNSNAMES for the
database we will connect to..


Above is the entries in the dbnodes.txt file, these are the databases where we will loop the sql commands

Create a sql script with the commands you have to run on all the databases, lets call the file script.sql

set echo on
set linesize 200
set pages 0
select sysdate from dual;
select user from dual;
select instance_name,host_name from gv$instance;
alter system set cpu_count=8 scope=both sid='*';
show parameter cpu_count;
alter session set nls_date_format='DD/MM/YYYY';

I do this to dynamically to cap the cpu_count on some of our databases to contain any resouce hogging.

Now the final shell script which will call the dbnodes.txt and script.sql to loop the above sql commands
through all databases. The shell script is called

cat dbnodes.txt | while read line
sqlplus -s user/user123@$line @/u03/scripts/script.sql

Copy all 3 files dbnodes.txt, script.sql and to one directiry and run it like below; to collect the log of the SQL commands.

$--> > dbrun.log

This will spool the output to a logfile.

So there you see it, one of the easiest ,method to run a set ofcommon sql commands on hundreds of server. I use this script to do basics like checking a certain parameters on all our production databases, to check dataguard status for multiple production DB’s etc. The use cases of this script is unlimited. Hope you enjoyed it 🙂 Keep it Easy and Keep It Oracle !

Active Session History 11g

Even when sessions are inactive they can be executing SQL’s in the background. You have to identify the SQL’s which are executing.

Run this query as sysdba user. If you are using RAC then replace v$active_session_history with gv$active_session_history

ash.SQL_ID ,
sum(decode(ash.session_state,’ON CPU’,1,0)) “CPU”,
sum(decode(ash.session_state,’WAITING’,1,0)) –
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “WAIT” ,
sum(decode(ash.session_state,’WAITING’, decode(en.wait_class, ‘User I/O’,1,0),0)) “IO” ,
sum(decode(ash.session_state,’ON CPU’,1,1)) “TOTAL”
from v$active_session_history ash,v$event_name en
where SQL_ID is not NULL and en.event#=ash.event#
group by sql_id ;

You will see something like

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
8tfvwyvfm5cjn          0          1          0          1
1xpfbutrrzkph          0          1          0          1
aykvshm7zsabd          0          1          0          1
92f47aa2q2rmd          0          1          0          1
92b382ka0qgdt          0          4          0          4
1h50ks4ncswfn          0          3          1          4
f99a23s9aba0z          0          0          1          1
f6cz4n8y72xdc          0          0          1          1
cvn54b7yz0s8u          0          0          1          1
3s58mgk0uy2ws          0          1          0          1
fnk7155mk2jq6          0          1          0          1

SQL_ID               CPU       WAIT         IO      TOTAL
————- ———- ———- ———- ———-
c2p32r5mzv8hb          0          1          0          1
5p6a1yss527ap          0          1          0          1
6gvch1xu9ca3g          0          1          0          1

Now see which statement shows maximum value in Total column. Take the SQL ID  and run below query

SELECT sql_text FROM v$sqlarea WHERE sql_id = ‘1h50ks4ncswfn’;

This will give you an SQL statement. See which table is it running on and what is it doing. Maybe it is a poorly written SQL which is locking the table or looping on the table. Do an explain plan for this statement.

SELECT * FROM table(dbms_xplan.display_awr(‘1h50ks4ncswfn’));

This way you can identify the top consuming SQL’s running inside your Oracle database. This procedure was run on a 11gR2 database but is also valid for 10gR2.