Tag Archives: oracle

Kill blocking session in Oracle

To check which all session are live on an oracle server

select * from v$session

To check blocking sessions

SELECT
O.OBJECT_NAME,
S.SID,
S.SERIAL#,
P.SPID,
S.PROGRAM,
SQ.SQL_FULLTEXT,
S.LOGON_TIME
FROM
V$LOCKED_OBJECT L,
DBA_OBJECTS O,
V$SESSION S,
V$PROCESS P,
V$SQL SQ
WHERE
L.OBJECT_ID = O.OBJECT_ID
AND L.SESSION_ID = S.SID
AND S.PADDR = P.ADDR
AND S.SQL_ADDRESS = SQ.ADDRESS;

To kill a session

alter system kill session ‘SID,SERIAL#’;

Find and delete blocking sessions- Oracle

Following query would give you all the session running in your oracle database with query details

select sesion.sid,
sesion.serial#,
sesion.username,
optimizer_mode,
hash_value,
address,
cpu_time,
elapsed_time,
sql_text
from v$sqlarea sqlarea, v$session sesion
where sesion.sql_hash_value = sqlarea.hash_value
and sesion.sql_address = sqlarea.address
and sesion.username is not null;

Now you might want to kill an unwanted session

ALTER SYSTEM KILL SESSION ‘sid,serial#’ IMMEDIATE;

Handling Null Values in SQL- ISNULL, NVL2, IFNULL, Coalesce

There can be situations when if a value is null in sql query, you want to replace it with something else. Different DBs provide you different options

Oracle- PLSQL
select NVL2(teamid, teamid, deptid) allocationid, empid, name from employee; //If team id is available that is returned as allocation id else deptid

IN MS-SQL we have ISNULL
select ISNULL(teamid, deptid) allocationid, empid, name from employee;

Similarly MySQL has IFNULL

Another option is COALESCE, this works for both Oracle and MS-SQL. This is slightly different from others in implementation that it makes columns to be skipped unless first non-null value is found

select COALESCE(teamid, deptid, organizationid) allocationid, empid, name from employee;

Forgot password for Oracle accounts

I have a oracle installation on my windows machine but that was done a few months back and I could not recollect passwords for default sys and sysadmin records. The following trick helped reset passwords for these accounts

1. Go to Command prompt and type sqlplus /nolog. It should open SQL/> prompt.
2. On SQL/> prompt, type connect /as dba
Now you are logged in as dba, changing password is simple matter of
SQL> alter user sys identified by “newpassword”

How does database (oracle) joins handle filter condition

I was wondering how does database interpret join queries. When we create a join between 2 tables in Oracle, with some additional filter condition on one or both tables, will oracle join the tables first and then filter or will it filter the conditions first and then join.

For example I have 2 simple queries

Query 1:

select e.name, d.name from employee e, department d
where e.dept_id=d.id and e.salary>50000;

Query 2:

select e.name, d.name from
(select * from employee where salary>50000) e, department d
where e.dept_id=d.id;

Now which is a better option?

I posted my doubts on a couple of forums and did some googling, and this is what I found

“Both are same”

An easy way to check this out id to use “explain plan for” clause, which tells you how actually oracle interprets your query after optimization.

explain plan for
select e.name, d.name from employee e, department d
where e.dept_id=d.id and e.salary>50000;
select * from table(dbms_xplan.display());

output was same as

explain plan for
elect e.name, d.name from
(select * from employee where salary>50000) e, department d
where e.dept_id=d.id;