Oracle DBA – A lifelong learning experience

Killing sessions across multiple instances

Posted by John Hallas on March 23, 2016

I am sure every DBA has used a kill session script before, although a lot probably use OEM as much now as individual scripts.

However I had not realised there was an option to add a 3rd parameter, the instance _id to the command

For example to kill any DBSNMP connections across all nodes of a RAC database

select 'alter system kill session ''' || sid || ', ' || serial# || ', @' || inst_id || ''';'

from gv$session where username = DBSNMP' order by logon_time desc;

alter system kill session ‘975, 57707, @2’;
alter system kill session ‘584, 2515, @1’;
alter system kill session ‘589, 4399, @2’;
alter system kill session ‘401, 2081, @1’;
alter system kill session ‘1168, 36153, @2’;
alter system kill session ‘1350, 21961, @1’;

This parameter is only available on RAC databases – which I suppose is quite obvious really.

There is also an option to use the IMMEDIATE keyword as well, this returns control straight back to the user without waiting for the command to complete, which can be quite useful when you have a few iterations of the command to run

alter system kill session '1350, 21961, @1' immediate;


Posted in Oracle | Tagged: , , | 4 Comments »

Greyed out connections in SQL Developer

Posted by John Hallas on January 26, 2016

After upgrading to SQL Developer a number of users noticed that some connections in the left hand pane were greyed out. I had the same experience for myself. There did not seem to be any pattern as to why specific connections were chosen and not others. Once greyed out they remained greyed out permanently – the selection did not change

I have not been able to find out an answer as to why it is happening, other than it is a bug/feature of that release.

However what I do know is that it is fixable Read the rest of this entry »

Posted in Oracle | Tagged: , , , , , | Leave a Comment »

2015 in review

Posted by John Hallas on January 2, 2016

The stats helper monkeys prepared a 2015 annual report for this blog.

Here’s an excerpt:

The Louvre Museum has 8.5 million visitors per year. This blog was viewed about 210,000 times in 2015. If it were an exhibit at the Louvre Museum, it would take about 9 days for that many people to see it.

Click here to see the complete report.

Posted in Oracle | Leave a Comment »

ORAchk is released – what does it offer ?

Posted by John Hallas on December 29, 2015

I have looked at the ORAchk tool before and thought that I must do something with it and I have now had a more detailed review of what it offers.

Firstly the history, it started off as RACchk and then morphed into ORAchk with a sibling called EXAchk. The recent release version is which is available for MoS Doc_Id 1268927.2

It can perform an ever-growing list of checks against database, OS, specific applications (EBS and Peoplesoft being two)and various permutations of all that. It is very customisable, the usage command output runs to 281 lines !!

There is now the ability to run it as part of an Apex collection so that all reports can be centralised and you can then compare the differences between one run and the previous one.

Let me give an example of the ./orachk –b output. This shows the best practises and I have selected some of the output that seemed worthy of comment. Read the rest of this entry »

Posted in 11g new features, Oracle | Tagged: , , , , , , , | Leave a Comment »

Memory advisors – how reliable?

Posted by John Hallas on December 24, 2015

I like the memory advisors that are available from the OEM GUI or the direct database tables themselves.  If I want a quick overview of whether a memory area (shared_pool, cache or the overall memory target itself) needs reviewing look there first. Note that I did not include PGA here specifically, I am well aware that PGA is included with the MAX_MEMORY_TARGET value   – I am not considering that view in my discussion here because it is so subjective to the application and what the users are doing and the code being run.

Here is an example of the OEM advisor for MAX_MEMORY_TARGET for a specific database (Performance/Advisors Home/ Memory Advisors and select advice)


The graph tell us that we have a MAX_MEMORY_TARGET of 4Gb and increasing it will not improve database time (the blue line continues on the same horizontal tangent right through to 8Gb). Read the rest of this entry »

Posted in Oracle | Tagged: , , , , , , , , | 5 Comments »

Recruiting for DBAs

Posted by John Hallas on December 21, 2015

My team has vacancies for several permanent DBAs. We are a supermarket chain based in Bradford, West Yorkshire.

The estate is split between HPUX and Linux (mostly OEL but moving to RH). The oracle versions in production are primarily 11Gr1,11GR2 and 12c with a little 10g, all managed through OEM

The key requirements are knowledge of DataGuard, RMAN and RAC.

It is an interesting site and we do a lot of work looking at new technology alongside ensuring the production systems run as well as possible.

Any experience of managing MySQL would be an advantage

If interested please send your CV in to the address in this advert



Posted in Oracle, Uncategorized | 1 Comment »

Managing plans – identifying which plans have been used

Posted by John Hallas on December 7, 2015

This blog has the aim of answering quetions about how a sql statement has performed and which plan it has used. I have asked the type of questions that we as DBAs normally have to answer and shown how an answer may be arrived at.

We know the SQL_ID and we want to see what has been happening

set long 5000 lines 160
select sql_text from dba_hist_sqltext where sql_id = '7vt1xg0afxkba';

The SQL_ID is unknown but you do know some of the sql statement that will be used

select sql_id from dba_hist_sqltext where sql_text like 'INSERT%WC_INV_ITEM_DAILY_BAL_F%';


When did a sql statement run and what happened – did it use a different plan to normal Read the rest of this entry »

Posted in 11g new features, Oracle | Tagged: , , , | 1 Comment »

Restrictions on the append hint

Posted by John Hallas on November 22, 2015

I wanted to add an append hint to some code but realised that there are known restrictions when either triggers or referential integrity are involved.

That does make sense as potentially rows would be inserted that either did not fire a trigger or broke RI constraints.

I decided to produce a test case to prove that the restrictions did work and were still in place.

Test append with trigger created

set lines 240
set pages 0
create table test1 as select * from dba_objects;

create table test2 as select * from dba_objects;
truncate table test2;
insert into test2 select * from test1 where object_id > 47
select * from table(dbms_xplan.display_cursor);
 | Id  | Operation                | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
 |   0 | INSERT STATEMENT         |       | 19848 |  4012K|    70   (0)| 00:00:01 |
 |   1 |  LOAD TABLE CONVENTIONAL | TEST2 |       |       |            |          |
 |*  2 |   TABLE ACCESS FULL      | TEST1 | 19848 |  4012K|    70   (0)| 00:00:01 |
Predicate Information (identified by operation id):
2 - filter("OBJECT_ID">47)
 - dynamic sampling used for this statement (level=2)

Now lets use the append hint and see what things look like Read the rest of this entry »

Posted in Oracle | Tagged: , , , , , | 1 Comment »

A blog related beer that I am currently enjoying

Posted by John Hallas on November 7, 2015

Currently drinking a bottle of this


I didn’t actually notice the name until I opened it tonight.

Posted in Oracle, Uncategorized | Tagged: , | 1 Comment »


Get every new post delivered to your Inbox.

Join 293 other followers