==============================
Auto Commit and Implicit Transactions
==============================
Auto Commit mode
SQL Server immediately commits the change after executing the statement.
Implicit Transactions Mode
Need to manually control the Rollback and the Commit operation.
In this mode a new transaction automatically begins after the commit/Rollback. No need to specify BEGIN TRANSACTION.
Explicit Mode.
Same as Implicit Transactions Mode, only we begin each transaction with BEGIN TRANSACTION statement.
Turning ON/OFF the implicit transactions mode.
You can turn auto commit ON by setting implicit_transactions OFF:
SET IMPLICIT_TRANSACTIONS OFF
In Management Studio: Tools-> Options -> Query Execution -> SQL Server -> ANSI -> uncheck SET IMPLICIT TRANSACTIONS checkbox
SET IMPLICIT_TRANSACTIONS ON
When the setting is ON, it returns to implicit transaction mode.
In implicit transaction mode, each transaction must be manually commited or rolled back.
Auto commit is the default for SQL Server 2000 and up.
For example:
SET IMPLICIT_TRANSACTIONS ON
UPDATE MY_TABLE SET col_a = 'A' WHERE col_b = 2
COMMIT TRANSACTION
SET IMPLICIT_TRANSACTIONS ON
UPDATE MY_TABLE SET col_a = 'A' WHERE col_b = 2
ROLLBACK TRANSACTION
Monday, April 27, 2015
Sunday, April 26, 2015
Handling ORA-00060 Deadlock Detected Error in PL/SQL
==============================
General
==============================
Applicative example of Handling ORA-00060 Deadlock Detected Error in PL/SQL.
Consider following scenario:
There are two independent PL/SQL processes running on scheduler.
It might happen that these two process would update same table.
As a result one of the processes is killed by Oracle, and "ORA-00060 deadlock detected" exception is thrown.
The solution would be to catch the ORA-00060 exception, let the process sleep for 20 seconds, as so the other process would have a chance to commit, then rerun the same code.
If the second run fails as well, rollback the transaction and throw an applicative exception.
First Step - Grant EXECUTE ON SYS.DBMS_LOCK to the user.
As SYSTEM
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO DBA_USER WITH GRANT OPTION;
Grant succeeded.
As DBA_USER
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO MY_USER
Grant succeeded.
Second Step - Define the ORA-00060 Exception in the Package Header
Define the ORA-00060 Exception in the Package Specifications using PRAGMA EXCEPTION_INIT
--CONSTANTS
C_FIRST_RUN CONSTANT INTEGER := 1;
C_SECOND_RUN CONSTANT INTEGER := 2;
--EXCEPTIONS
EXP_ORA_DEADLOCK EXCEPTION;
PRAGMA EXCEPTION_INIT(EXP_ORA_DEADLOCK,-60);
Third Step - Implement the solution in the code.
PROCEDURE markTransactions(pDateOfCall IN VARCHAR2,
pCustomerId IN VARCHAR2,
pRunTime IN NUMBER) IS
vModuleName VARCHAR2(100) := 'markTransactions';
BEGIN
UPDATE MY_TABLE_A MY_TABLE
SET MY_TABLE.is_processed = 1
WHERE date_of_call = pDateOfCall
AND customer_id = pCustomerId;
UTIL.writeTrace(vModuleName, 'Finish update MY_TABLE_A');
UPDATE MY_TABLE_B MY_TABLE
SET MY_TABLE.is_processed = 1
WHERE date_of_call = pDateOfCall
AND customer_id = pCustomerId;
UTIL.writeTrace(vModuleName, 'Finish update MY_TABLE_B');
EXCEPTION
WHEN EXP_ORA_DEADLOCK THEN
IF pRunTime=C_FIRST_RUN THEN
DBMS_LOCK.sleep(20);
Util.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Attempting Rerun');
markTransactions(pDateOfCall, pMinDateOfcall ,pOriginGateId,C_SECOND_RUN);
ELSE
ROLLBACK;
UTIL.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Rerun Failed');
UTIL.writeTrace(vModuleName, SQLERRM);
RAISE Util.E_LOGGED_EXP;
END IF;
WHEN OTHERS THEN
ROLLBACK;
UTIL.writeTrace(vModuleName, SQLERRM);
RAISE Util.E_LOGGED_EXP;
END markTransactions;
General
==============================
Applicative example of Handling ORA-00060 Deadlock Detected Error in PL/SQL.
Consider following scenario:
There are two independent PL/SQL processes running on scheduler.
It might happen that these two process would update same table.
As a result one of the processes is killed by Oracle, and "ORA-00060 deadlock detected" exception is thrown.
The solution would be to catch the ORA-00060 exception, let the process sleep for 20 seconds, as so the other process would have a chance to commit, then rerun the same code.
If the second run fails as well, rollback the transaction and throw an applicative exception.
First Step - Grant EXECUTE ON SYS.DBMS_LOCK to the user.
As SYSTEM
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO DBA_USER WITH GRANT OPTION;
Grant succeeded.
As DBA_USER
SQL> GRANT EXECUTE ON SYS.DBMS_LOCK TO MY_USER
Grant succeeded.
Second Step - Define the ORA-00060 Exception in the Package Header
Define the ORA-00060 Exception in the Package Specifications using PRAGMA EXCEPTION_INIT
--CONSTANTS
C_FIRST_RUN CONSTANT INTEGER := 1;
C_SECOND_RUN CONSTANT INTEGER := 2;
--EXCEPTIONS
EXP_ORA_DEADLOCK EXCEPTION;
PRAGMA EXCEPTION_INIT(EXP_ORA_DEADLOCK,-60);
Third Step - Implement the solution in the code.
PROCEDURE markTransactions(pDateOfCall IN VARCHAR2,
pCustomerId IN VARCHAR2,
pRunTime IN NUMBER) IS
vModuleName VARCHAR2(100) := 'markTransactions';
BEGIN
UPDATE MY_TABLE_A MY_TABLE
SET MY_TABLE.is_processed = 1
WHERE date_of_call = pDateOfCall
AND customer_id = pCustomerId;
UTIL.writeTrace(vModuleName, 'Finish update MY_TABLE_A');
UPDATE MY_TABLE_B MY_TABLE
SET MY_TABLE.is_processed = 1
WHERE date_of_call = pDateOfCall
AND customer_id = pCustomerId;
EXCEPTION
WHEN EXP_ORA_DEADLOCK THEN
IF pRunTime=C_FIRST_RUN THEN
DBMS_LOCK.sleep(20);
Util.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Attempting Rerun');
markTransactions(pDateOfCall, pMinDateOfcall ,pOriginGateId,C_SECOND_RUN);
ELSE
ROLLBACK;
UTIL.writeTrace(vModuleName, 'Encountered ORA-00060: deadlock detected Error. Rerun Failed');
UTIL.writeTrace(vModuleName, SQLERRM);
RAISE Util.E_LOGGED_EXP;
END IF;
WHEN OTHERS THEN
ROLLBACK;
UTIL.writeTrace(vModuleName, SQLERRM);
RAISE Util.E_LOGGED_EXP;
END markTransactions;
PL/SQL Exception Handling
==============================
General
==============================
==============================
Index
==============================
Catch predefined Exception
Catch non-predefined specific ORA-999 Exception
Catch application Exception
Raise new Application Exception
==============================
Catch pre-defined Exception
==============================
There are some ~20 predefined Oracle Exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc.
Example:
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
General
==============================
PL/SQL Exception handling by example.
Index
==============================
Catch predefined Exception
Catch non-predefined specific ORA-999 Exception
Catch application Exception
Raise new Application Exception
==============================
Catch pre-defined Exception
==============================
There are some ~20 predefined Oracle Exceptions, such as NO_DATA_FOUND, TOO_MANY_ROWS, ZERO_DIVIDE, etc.
Example:
EXCEPTION
WHEN NO_DATA_FOUND THEN -- catches all 'no data found' errors
==============================
Catch non-predefined specific ORA-999 Exception
==============================
Option A - with PRAGMA EXCEPTION_INIT
For Anonymous block/Function/Procedure - PRAGMA EXCEPTION_INIT should be under DECLARE section.
For Package - PRAGMA EXCEPTION_INIT should be in Package Specification.
Example:
DECLARE
my_deadlock_ora_exp EXCEPTION;
PRAGMA EXCEPTION_INIT(e_deadlock_ora_exp, -60); --catch ORA-00060
PRAGMA EXCEPTION_INIT(e_resource_busy_ora_exp, -54); --catch ORA-00054
PRAGMA EXCEPTION_INIT(e_resource_busy_ora_exp, -54); --catch ORA-00054
BEGIN
... -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN e_deadlock_ora_exp THEN
-- handle ORA-00060
WHEN e_resource_busy_ora_exp THEN
-- handle ORA-00054
END;
Option B - without PRAGMA EXCEPTION_INIT
Example:
DECLARE
my_deadlock_ora_exp NUMBER := -60;
BEGIN
... -- Some operation that causes an ORA-00060 error
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = my_deadlock_ora_exp THEN
END IF;
END;
==============================
Raise new Application Exception
==============================
General syntax to raise application exceptions:
RAISE_APPLICATION_ERROR(error_number, message[, {TRUE | FALSE}]);
Where
error_number - Any number in the range -20000 .. -20999
message - Free text, up to 2048 bytes long
Third Parameter is optional
If TRUE, the error is placed on the stack of previous errors.
If FALSE (the default), the error replaces all previous errors.
RAISE_APPLICATION_ERROR is part of package DBMS_STANDARD, and as with package STANDARD, you do not need to qualify references to it.
==============================
Catch application Exception
==============================
DECLARE
my_exception_exp EXCEPTION;
C_APP_EXCEPTION_NUM CONSTANT NUMBER := -20100;
v_msg_text VARCHAR2(2048);
C_APP_EXCEPTION_NUM CONSTANT NUMBER := -20100;
v_msg_text VARCHAR2(2048);
BEGIN
IF ... THEN
RAISE my_exception_exp;
END IF;
EXCEPTION
WHEN my_exception_exp THEN
RAISE_APPLICATION_ERROR(C_APP_EXCEPTION_NUM,v_msg_text);
END;
identifier "DBMS_LOCK" must be declared
GRANT EXECUTE ON SYS.DBMS_LOCK to MY_USER;
Sunday, April 19, 2015
ksh and perl by Example: crontab task to find and kill zombie Oracle Jobs.
General.
Once a day, at 12:15 execute a script that would identify and kill Oracle Job processes, that are running longer than 24 hours.
This script is launched on Oracle server, as oracle user.
steps
A. crontab task - that runs ksh script once a day, at 12:15.
B. ksh script that launches perl script
C. perl script that connects to DB to find zombie Linux processes that were launched by Oracle job, and then kills these tasks.
crontab -l
15 12 * * * /backup/ora_exp/kill_zombie_jobs.sh
kill_long_zombie_jobs.sh
#!/bin/bash
. /etc/profile
. /etc/sh/orash/oracle_login.sh igt
perl /backup/ora_exp/kill_zombie_jobs.pl
kill_long_zombie_jobs.pl
#! /usr/bin/perl
use DBI;
use Time::gmtime;
use File::Copy;
####################################################################
##### Subroutine to get the current date #########################
####################################################################
sub getDate
{
use Time::gmtime;
$tm=gmtime;
($second,$minute,$hour,$day,$month,$year) = (gmtime) [0,1,2,3,4,5];
my $locDate=sprintf("%04d%02d%02d_%02d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday,$tm->hour,$tm->min,$tm->sec );
return $locDate;
}
########################
# main start here ######
########################
my $RetCode;
my $myDate=getDate;
my $logLocation="/backup/ora_exp/Logs/kill_zombie_jobs/";
my $logFile=$logLocation."kill_zombie_log_".$myDate.".log";
my $sql;
my $db="cgw_new";
my $db_driver="dbi:Oracle:".$db;
my $dbh=DBI->connect($db_driver,'collector','cgw154igt',{RaiseError =>1,AutoCommit=>0})|| die "$DBI::errstr";
print $DBI::errstr;
print "Log file:".$logFile."\n";
my $sql="SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL, ";
$sql=$sql."PROCESSES.spid, PROCESSES.program, ";
$sql=$sql."SESSIONS.username, SESSIONS.sql_id, SESSIONS.logon_time, SESSIONS.event ";
$sql=$sql."FROM v\$process PROCESSES, V\$SESSION SESSIONS ";
$sql=$sql."WHERE PROCESSES.program like '%J0%' ";
$sql=$sql." AND PROCESSES.addr=SESSIONS.paddr ";
$sql=$sql." AND SESSIONS.schemaname <> 'SYS' ";
$sql=$sql." AND SESSIONS.type = 'USER' ";
$sql=$sql." AND PROCESSES.background IS NULL ";
$sql=$sql." AND SESSIONS.sid NOT IN (SELECT sid FROM DBA_JOBS_RUNNING) ";
$sql=$sql." AND logon_time < SYSDATE-1/4";
$sql=$sql." AND logon_time < SYSDATE-1";
open (MyLog,">>".$logFile);
print MyLog "----I---- Startting kill zombie process on ".$myDate."\n";
print MyLog "Running SQL: "."\n".$sql."\n";
print MyLog "\n\n";
print MyLog "These are the details of the job that were killed : \n";
print MyLog ".........................."."\n";
my $FNsth=$dbh->prepare($sql);
$FNsth->execute();
my @row;
my $kill_cmd;
while (@row=$FNsth->fetchrow())
{
open (MyLog,">>".$logFile);
print MyLog "V$SEESION.sid: ".$row[1]." \n";
print MyLog "PROCESSES.program: ".$row[2]." \n";
print MyLog "SESSIONS.username: ".$row[3]." \n";
print MyLog "SESSIONS.sql_id: ".$row[4]." \n";
print MyLog "SESSIONS.logon_time: ".$row[5]." \n";
print MyLog "SESSIONS.event: ".$row[6]." \n";
print MyLog ".........................."."\n";
$kill_cmd = $row[0];
print MyLog "Running command ".$kill_cmd."..........";
system($kill_cmd);
print MyLog "Done"."\n";
}
$FNsth->finish();
$dbh->disconnect;
print MyLog "\n=========================\n";
print MyLog "Script Finished Successfuly";
print MyLog "\n=========================\n";
close MyLog;
=================================
SQL to identify zombie jobs
=================================
SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL,
PROCESSES.spid,
PROCESSES.program,
SESSIONS.username,
SESSIONS.sql_id,
SESSIONS.logon_time,
SESSIONS.event
FROM V$PROCESS PROCESSES,
V$SESSION SESSIONS,
DBA_JOBS_RUNNING RUNNING_JOBS
WHERE PROCESSES.program like '%J0%'
AND PROCESSES.addr=SESSIONS.paddr
AND SESSIONS.sid = RUNNING_JOBS.sid(+)
AND SESSIONS.schemaname <> 'SYS'
AND SESSIONS.type = 'USER'
AND RUNNING_JOBS.job IS NULL
-- AND SESSIONS.logon_time < SYSDATE-1;
=======================================
profile files, not really important for this example...
=======================================
. /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
pathmunge () {
if ! echo $PATH | /bin/egrep -q "(^|:)$1($|:)" ; then
if [ "$2" = "after" ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
fi
}
# Path manipulation
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin
pathmunge /usr/local/bin
pathmunge /usr/X11R6/bin after
# No core files by default
ulimit -S -c 0 > /dev/null 2>&1
USER="`id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
HOSTNAME=`/bin/hostname`
HISTSIZE=1000
if [ -z "$INPUTRC" -a ! -f "$HOME/.inputrc" ]; then
INPUTRC=/etc/inputrc
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC
for i in /etc/profile.d/*.sh ; do
if [ -r "$i" ]; then
. $i
fi
done
unset i
. /etc/sh/orash/oracle_login.sh orainst
#!/bin/bash
#
# NAME:
# oracle_login.sh
# !!!
#
# ABSTRACT:
# Defining oracle's enironment variables. - Unix version
#
#
# ARGUMENTS:
# [p1] - oracle sid
#
# Note - 1) the parameter can be ommitted
#
#
# SPECIAL FILE:
#
# ENVIRONMENT VAR:
# PROJECT_SID -
# should be defined in the following way:
# globaly - the node run only one oracle instance
# group login - there is more than one instance on the current node
# as a parameter - there is more than one instance per project,
# or for system users.
#
# SPECIAL CCC MACROS:
#
# INPUT FORMAT:
#
# OUTPUT FORMAT:
#
# RETURN/EXIT STATUS:
# 1) PROJECT_SID is not defined
# 2) the database PROJECT_SID does not exist
# 3) PROJECT_SID missing from $ORASH/sid.hosts
# 4) ORA_VER missing from $ORASH/sid.hosts
# 5) charcter missing for current sid from $ORASH/charset.dat
#
# LIMITATIONS:
#
# MODIFICATION HISTORY:
# +-------------+-------------------+-----------------------------------------+
# | Date | Name | Description |
# +-------------+-------------------+-----------------------------------------+
# + + + +
# +-------------+-------------------+-----------------------------------------+
if [ "$ORASH" = "" ]; then
ORASH=$SH_ETC/etc/orash
fi
FACILITY="oracle_login:"
UNAME=`uname | cut -d_ -f1` #=> Linux
if [ "$#" -ge 1 ]; then
PROJECT_SID=$1
else
project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
if [ "$project_sid_def" -eq 0 ]; then
echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
return 1
fi
fi
ORACLE_BASE=/software/oracle
project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
if [ "$project_sid_def" -eq 0 ]; then
echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
return 1
fi
ORACLE_HOME=`grep ^"$PROJECT_SID": /var/opt/oracle/oratab | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$ORACLE_HOME" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in /var/opt/oracle/oratab or /etc/oratab"
return 2
fi
ORA_VER=`grep "$PROJECT_SID" $ORASH/sid.hosts | awk '{if (NR==1) print $0}' |cut -d: -f3`
if [ "$ORA_VER" = "" ]; then
echo "$FACILITY unable to verify ORA_VER for $PROJECT_SID using $ORASH/sid.hosts"
return 4
fi
DEFAULT_SID=$PROJECT_SID
ORACLE_SID=$DEFAULT_SID
TNS_ADMIN=${ORACLE_HOME}/network/admin
#----------------------------------------------------------------#
# the hostname of the DEFAULT_SID appears first in file sid.hosts
#----------------------------------------------------------------#
HOST=`grep ^"$PROJECT_SID": $ORASH/sid.hosts | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$HOST" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $ORASH/sid.hosts"
return 3
fi
#-------------------------------------#
# TWO_TASK is used for a unix clients
#-------------------------------------#
# Disable TWO_TASK feature eliminating the need to change hostname, after disk replication
# NODE=`hostname`
# if [ "$NODE" != "$HOST" ]; then
# TWO_TASK=${DEFAULT_SID}_remote
# fi
#-------------------------------------#
# setting up NLS_LANG
#-------------------------------------#
CHARSET=`grep ^"$PROJECT_SID": $ORASH/charset.dat | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$CHARSET" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $
ORASH/charset.dat"
return 5
fi
NLS_DATE_FORMAT="DD-MON-RR"
ORACLE_LPPROG=lpr
ORACLE_LPARGS="-P la424"
NLS_LANG="AMERICAN_AMERICA."${CHARSET}
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data # for oracle
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data # for oracle8
cleaned_path=$PATH
for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
PATH=$cleaned_path:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/openwin/bin
else
PATH=$cleaned_path$ORACLE_HOME/bin
fi
shlib_def=`set | grep "^LD_LIBRARY_PATH" |wc -l`
if [ "$shlib_def" -eq 0 ]; then
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
else
cleaned_shlib_path=$LD_LIBRARY_PATH
for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
cleaned_shlib_path=`echo -e "${cleaned_shlib_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
LD_LIBRARY_PATH=$cleaned_shlib_path:$ORACLE_HOME/lib:/usr/lib
else
LD_LIBRARY_PATH=$cleaned_shlib_path$ORACLE_HOME/lib:/usr/lib
fi
fi
export PROJECT_SID
export ORACLE_TERM
export ORACLE_HOME
export ORACLE_BASE
export ORACLE_SID
export TNS_ADMIN
export ORACLE_SERVER
export PATH
export LD_LIBRARY_PATH
export NLS_LANG
export ORA_NLS33
unset ORA_NLS32
ORACLE_ENV_DEFINED="yes"
export ORACLE_ENV_DEFINED
Once a day, at 12:15 execute a script that would identify and kill Oracle Job processes, that are running longer than 24 hours.
This script is launched on Oracle server, as oracle user.
steps
A. crontab task - that runs ksh script once a day, at 12:15.
B. ksh script that launches perl script
C. perl script that connects to DB to find zombie Linux processes that were launched by Oracle job, and then kills these tasks.
crontab -l
15 12 * * * /backup/ora_exp/kill_zombie_jobs.sh
kill_long_zombie_jobs.sh
#!/bin/bash
. /etc/profile
. /etc/sh/orash/oracle_login.sh igt
perl /backup/ora_exp/kill_zombie_jobs.pl
kill_long_zombie_jobs.pl
#! /usr/bin/perl
use DBI;
use Time::gmtime;
use File::Copy;
####################################################################
##### Subroutine to get the current date #########################
####################################################################
sub getDate
{
use Time::gmtime;
$tm=gmtime;
($second,$minute,$hour,$day,$month,$year) = (gmtime) [0,1,2,3,4,5];
my $locDate=sprintf("%04d%02d%02d_%02d%02d%02d",$tm->year+1900,($tm->mon)+1,$tm->mday,$tm->hour,$tm->min,$tm->sec );
return $locDate;
}
########################
# main start here ######
########################
my $RetCode;
my $myDate=getDate;
my $logLocation="/backup/ora_exp/Logs/kill_zombie_jobs/";
my $logFile=$logLocation."kill_zombie_log_".$myDate.".log";
my $sql;
my $db="cgw_new";
my $db_driver="dbi:Oracle:".$db;
my $dbh=DBI->connect($db_driver,'collector','cgw154igt',{RaiseError =>1,AutoCommit=>0})|| die "$DBI::errstr";
print $DBI::errstr;
print "Log file:".$logFile."\n";
my $sql="SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL, ";
$sql=$sql."PROCESSES.spid, PROCESSES.program, ";
$sql=$sql."SESSIONS.username, SESSIONS.sql_id, SESSIONS.logon_time, SESSIONS.event ";
$sql=$sql."FROM v\$process PROCESSES, V\$SESSION SESSIONS ";
$sql=$sql."WHERE PROCESSES.program like '%J0%' ";
$sql=$sql." AND PROCESSES.addr=SESSIONS.paddr ";
$sql=$sql." AND SESSIONS.schemaname <> 'SYS' ";
$sql=$sql." AND SESSIONS.type = 'USER' ";
$sql=$sql." AND PROCESSES.background IS NULL ";
$sql=$sql." AND SESSIONS.sid NOT IN (SELECT sid FROM DBA_JOBS_RUNNING) ";
$sql=$sql." AND logon_time < SYSDATE-1/4";
$sql=$sql." AND logon_time < SYSDATE-1";
open (MyLog,">>".$logFile);
print MyLog "----I---- Startting kill zombie process on ".$myDate."\n";
print MyLog "Running SQL: "."\n".$sql."\n";
print MyLog "\n\n";
print MyLog "These are the details of the job that were killed : \n";
print MyLog ".........................."."\n";
my $FNsth=$dbh->prepare($sql);
$FNsth->execute();
my @row;
my $kill_cmd;
while (@row=$FNsth->fetchrow())
{
open (MyLog,">>".$logFile);
print MyLog "V$SEESION.sid: ".$row[1]." \n";
print MyLog "PROCESSES.program: ".$row[2]." \n";
print MyLog "SESSIONS.username: ".$row[3]." \n";
print MyLog "SESSIONS.sql_id: ".$row[4]." \n";
print MyLog "SESSIONS.logon_time: ".$row[5]." \n";
print MyLog "SESSIONS.event: ".$row[6]." \n";
print MyLog ".........................."."\n";
$kill_cmd = $row[0];
print MyLog "Running command ".$kill_cmd."..........";
system($kill_cmd);
print MyLog "Done"."\n";
}
$FNsth->finish();
$dbh->disconnect;
print MyLog "\n=========================\n";
print MyLog "Script Finished Successfuly";
print MyLog "\n=========================\n";
close MyLog;
SQL to identify zombie jobs
=================================
SELECT 'kill -9 '||PROCESSES.spid AS LINUX_KILL,
PROCESSES.spid,
PROCESSES.program,
SESSIONS.username,
SESSIONS.sql_id,
SESSIONS.logon_time,
SESSIONS.event
FROM V$PROCESS PROCESSES,
V$SESSION SESSIONS,
DBA_JOBS_RUNNING RUNNING_JOBS
WHERE PROCESSES.program like '%J0%'
AND PROCESSES.addr=SESSIONS.paddr
AND SESSIONS.sid = RUNNING_JOBS.sid(+)
AND SESSIONS.schemaname <> 'SYS'
AND SESSIONS.type = 'USER'
AND RUNNING_JOBS.job IS NULL
-- AND SESSIONS.logon_time < SYSDATE-1;
=======================================
profile files, not really important for this example...
=======================================
. /etc/profile
# /etc/profile
# System wide environment and startup programs, for login setup
# Functions and aliases go in /etc/bashrc
pathmunge () {
if ! echo $PATH | /bin/egrep -q "(^|:)$1($|:)" ; then
if [ "$2" = "after" ] ; then
PATH=$PATH:$1
else
PATH=$1:$PATH
fi
fi
}
# Path manipulation
pathmunge /sbin
pathmunge /usr/sbin
pathmunge /usr/local/sbin
pathmunge /usr/local/bin
pathmunge /usr/X11R6/bin after
# No core files by default
ulimit -S -c 0 > /dev/null 2>&1
USER="`id -un`"
LOGNAME=$USER
MAIL="/var/spool/mail/$USER"
HOSTNAME=`/bin/hostname`
HISTSIZE=1000
if [ -z "$INPUTRC" -a ! -f "$HOME/.inputrc" ]; then
INPUTRC=/etc/inputrc
fi
export PATH USER LOGNAME MAIL HOSTNAME HISTSIZE INPUTRC
for i in /etc/profile.d/*.sh ; do
if [ -r "$i" ]; then
. $i
fi
done
unset i
#!/bin/bash
#
# NAME:
# oracle_login.sh
# !!!
#
# ABSTRACT:
# Defining oracle's enironment variables. - Unix version
#
#
# ARGUMENTS:
# [p1] - oracle sid
#
# Note - 1) the parameter can be ommitted
#
#
# SPECIAL FILE:
#
# ENVIRONMENT VAR:
# PROJECT_SID -
# should be defined in the following way:
# globaly - the node run only one oracle instance
# group login - there is more than one instance on the current node
# as a parameter - there is more than one instance per project,
# or for system users.
#
# SPECIAL CCC MACROS:
#
# INPUT FORMAT:
#
# OUTPUT FORMAT:
#
# RETURN/EXIT STATUS:
# 1) PROJECT_SID is not defined
# 2) the database PROJECT_SID does not exist
# 3) PROJECT_SID missing from $ORASH/sid.hosts
# 4) ORA_VER missing from $ORASH/sid.hosts
# 5) charcter missing for current sid from $ORASH/charset.dat
#
# LIMITATIONS:
#
# MODIFICATION HISTORY:
# +-------------+-------------------+-----------------------------------------+
# | Date | Name | Description |
# +-------------+-------------------+-----------------------------------------+
# + + + +
# +-------------+-------------------+-----------------------------------------+
if [ "$ORASH" = "" ]; then
ORASH=$SH_ETC/etc/orash
fi
FACILITY="oracle_login:"
UNAME=`uname | cut -d_ -f1` #=> Linux
if [ "$#" -ge 1 ]; then
PROJECT_SID=$1
else
project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
if [ "$project_sid_def" -eq 0 ]; then
echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
return 1
fi
fi
ORACLE_BASE=/software/oracle
project_sid_def=`set | grep "^PROJECT_SID=" |wc -l`
if [ "$project_sid_def" -eq 0 ]; then
echo "<oracle_login> ERROR>> PROJECT_SID is not defined"
return 1
fi
ORACLE_HOME=`grep ^"$PROJECT_SID": /var/opt/oracle/oratab | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$ORACLE_HOME" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in /var/opt/oracle/oratab or /etc/oratab"
return 2
fi
ORA_VER=`grep "$PROJECT_SID" $ORASH/sid.hosts | awk '{if (NR==1) print $0}' |cut -d: -f3`
if [ "$ORA_VER" = "" ]; then
echo "$FACILITY unable to verify ORA_VER for $PROJECT_SID using $ORASH/sid.hosts"
return 4
fi
DEFAULT_SID=$PROJECT_SID
ORACLE_SID=$DEFAULT_SID
TNS_ADMIN=${ORACLE_HOME}/network/admin
#----------------------------------------------------------------#
# the hostname of the DEFAULT_SID appears first in file sid.hosts
#----------------------------------------------------------------#
HOST=`grep ^"$PROJECT_SID": $ORASH/sid.hosts | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$HOST" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $ORASH/sid.hosts"
return 3
fi
#-------------------------------------#
# TWO_TASK is used for a unix clients
#-------------------------------------#
# Disable TWO_TASK feature eliminating the need to change hostname, after disk replication
# NODE=`hostname`
# if [ "$NODE" != "$HOST" ]; then
# TWO_TASK=${DEFAULT_SID}_remote
# fi
#-------------------------------------#
# setting up NLS_LANG
#-------------------------------------#
CHARSET=`grep ^"$PROJECT_SID": $ORASH/charset.dat | awk '{if (NR==1) print $0}' | cut -d: -f2`
if [ "$CHARSET" = "" ]; then
echo "<oracle_login> ERROR>> the database $PROJECT_SID does not exist in $
ORASH/charset.dat"
return 5
fi
NLS_DATE_FORMAT="DD-MON-RR"
ORACLE_LPPROG=lpr
ORACLE_LPARGS="-P la424"
NLS_LANG="AMERICAN_AMERICA."${CHARSET}
ORA_NLS32=$ORACLE_HOME/ocommon/nls/admin/data # for oracle
ORA_NLS33=$ORACLE_HOME/ocommon/nls/admin/data # for oracle8
cleaned_path=$PATH
for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
cleaned_path=`echo -e "${cleaned_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
PATH=$cleaned_path:$ORACLE_HOME/bin:/usr/ccs/bin:/usr/openwin/bin
else
PATH=$cleaned_path$ORACLE_HOME/bin
fi
shlib_def=`set | grep "^LD_LIBRARY_PATH" |wc -l`
if [ "$shlib_def" -eq 0 ]; then
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib
else
cleaned_shlib_path=$LD_LIBRARY_PATH
for ora_path in `grep -v "^#" /var/opt/oracle/oratab | cut -d: -f2 | uniq` ; do
cleaned_shlib_path=`echo -e "${cleaned_shlib_path}\c" | tr ':' '\n' | grep -v "$ora_path" | tr '\n' ':'`
done
if [ $UNAME = 'SunOS' ] ; then
LD_LIBRARY_PATH=$cleaned_shlib_path:$ORACLE_HOME/lib:/usr/lib
else
LD_LIBRARY_PATH=$cleaned_shlib_path$ORACLE_HOME/lib:/usr/lib
fi
fi
export PROJECT_SID
export ORACLE_TERM
export ORACLE_HOME
export ORACLE_BASE
export ORACLE_SID
export TNS_ADMIN
export ORACLE_SERVER
export PATH
export LD_LIBRARY_PATH
export NLS_LANG
export ORA_NLS33
unset ORA_NLS32
ORACLE_ENV_DEFINED="yes"
export ORACLE_ENV_DEFINED
export ORA_VER
Wednesday, April 15, 2015
V$SQL , V$SQLTEXT,V$SQLAREA, V$SQL_PLAN, V$SESSION_LONGOPS
V$SQL ,
V$SQLAREA,
V$SQLTEXT,
V$SESSION_LONGOPS
===============================
V$SQL
===============================
V$SQL lists statistics on shared SQL area.
One row for each child cursor per SQL string.
Statistics displayed in V$SQL are normally updated at the end of query execution.
For long running queries , statistics are updated every 5 seconds.
V$SQL Reference
===============================
V$SQLAREA
===============================
V$SQLAREA lists statistics on shared SQL area.
One row for all child cursors, per SQL string.
It provides statistics on SQL statements that are in memory.
V$SQLAREA Reference
to get sql_fulltext
V$SQLAREA main fields
executions - Total number of executions
disk_reads - Sum of physical disk reads
buffer_gets - Sum of DB block gets (memory+physical)
cpu_time - CPU time in microseconds
parse_calls - Sum of all parse calls. (number of times SQL was re-parsed)
first_load_time - Timestamp of parent cursor creation
===============================
V$SQLTEXT
===============================
V$SQLTEXT holds text of SQL statements from shared SQL cursors in the SGA.
In V$SQLTEXT newlines and other control characters are replaced with whitespaces.
V$SQLTEXT Reference
V$SQLTEXT fields:
address - Used with hash_value to uniquely identify a cached cursor
hash_value - Used with address to uniquely identify a cached cursor
sql_id - SQL identifier of a cached cursor
command_type - Code for the type of SQL statement (SELECT, INSERT, and so on)
piece - Number used to order the pieces of SQL text
sql_text - One piece of the SQL text is only VARCHAR2(64)
===============================
V$SQLTEXT_WITH_NEWLINES
===============================
V$SQLTEXT is same as V$SQLTEXT, only newlines and other control characters are NOT replaced with whitespaces.
===============================
V$SQL_PLAN
===============================
V$SQL_PLAN holds the execution plan information per each child cursor.
V$SQL_PLAN Reference
V$SQL_PLAN fields
address - Address of the handle to the parent for this cursor
hash_value - Hash value of the parent cursor.
sql_id - SQL identifier of the parent cursor.
plan_hash_value - Unique identifier for SQL plan.
child_number - Number of the child cursor.
To get Explain Plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1qqtru155tyz8',2));
Join to V$SQLAREA
with address and hash_value
Join to V$SQL
with address, hash_value, and child_number.
===============================
V$SESSION_LONGOPS
===============================
This table lists long running sessions, with their sql_address, sql_hash_value, sql_id, sql_plan_hash_value, and the time consuming step (full_scan, fast full scan, etc.)
SELECT SQLAREA.sql_text,
LONG.sid,
LONG.serial#,
LONG.time_remaining time_remaining_sec,
LONG.sql_plan_operation,
LONG.sql_plan_options
WHERE LONG.sql_id = SQLAREA.sql_id
AND LONG.start_time > TRUNC(SYSDATE)
AND SQLAREA.sql_text LIKE 'SELECT kuku%'
===============================
Common fields
===============================
sql_id - VARCHAR2(13)
Unique identifier, per SQL TEXT.('1qqtru155tyz8')
address - RAW(4 | 8)
hash_value - NUMBER
address + hash_value Uniquely identify cursor.
child_number - NUMBER
Number of child cursor(0,1,2,3,4...)
===============================
What is Child Cursor?
===============================
Child Cursors are simply cursors that reference the same exact sql_text - but are different in some fashion.
The first child cursor is numbered 0 (the parent), then 1 (first child), then 2 and so on.
In what way do cursors different? - Well, this varies.
For example:
A. Two users have table EMPLOYEES - and both run SELECT * FROM EMPLOYEES;
sql_id - would be same, It is same sql text.
child_cursor would be different.
B. For some reason - same SQLs have different execution plan.
===============================
What is unique SQL identifier?
===============================
address + hash_value + child_number
===============================
How to connect V$SQLAREA with V$SESSION?
===============================
hash_value and address fields uniquely identify the SQL cursor.
They are used to connect to V$SESSION
===============================
Top SQLs from V$SQL and V$SQLAREA
===============================
SELECT *
FROM(
SELECT SESSIONS.sid,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.module,
SQLAREA.executions,
SQLAREA.disk_reads,
SQLAREA.buffer_gets,
ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) AS memory_gets_pct,
ROUND(SQLAREA.cpu_time/1000/1000) AS cpu_time_sec,
ROUND(SQLAREA.user_io_wait_time/1000/1000) AS io_time_sec,
SQLAREA.parse_calls,
SQLAREA.first_load_time,
SQLAREA.sql_text
FROM
V$SQL SQLAREA,
-- V$SQLAREA SQLAREA,
V$SESSION SESSIONS
WHERE SESSIONS.sql_hash_value = SQLAREA.hash_value
AND SESSIONS.sql_address = SQLAREA.address
AND SESSIONS.USERNAME IS NOT NULL
AND SQLAREA.executions > 0
ORDER BY
-- SQLAREA.disk_reads DESC
-- ROUND(SQLAREA.cpu_time/1000) DESC
-- ROUND(SQLAREA.user_io_wait_time/1000) DESC
-- SQLAREA.executions DESC
SQLAREA.parse_calls DESC
-- ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) ASC
)WHERE ROWNUM < 21;
===============================
Get SQL Text from V$SQLTEXT
===============================
SELECT SQLTEXT.sql_text,
SQLTEXT.piece ,
SQLTEXT.address,
SS.sid,
SS.username,
SS.schemaname,
SS.osuser,
SS.process,
SS.machine,
SS.terminal,
SS.program,
SS.type,
SS.module,
SS.logon_time,
SS.event,
SS.service_name,
SS.seconds_in_wait
FROM V$SESSION SS,
V$SQLTEXT SQLTEXT
WHERE SS.sql_address = SQLTEXT.address(+)
AND SS.service_name = 'SYS$USERS'
ORDER BY SQLTEXT.address, SQLTEXT.piece
===============================
Oracle memory structures.
===============================
===============================
Reference
===============================
Ask Tom: What is the diference between V$SQL* views
Ask Tom: On Seeing Double in V$SQL
V$SQLAREA,
V$SQLTEXT,
V$SQLTEXT_WITH_NEWLINES
V$SQL_PLAN, V$SESSION_LONGOPS
===============================
V$SQL
===============================
V$SQL lists statistics on shared SQL area.
One row for each child cursor per SQL string.
Statistics displayed in V$SQL are normally updated at the end of query execution.
For long running queries , statistics are updated every 5 seconds.
V$SQL Reference
===============================
V$SQLAREA
===============================
V$SQLAREA lists statistics on shared SQL area.
One row for all child cursors, per SQL string.
It provides statistics on SQL statements that are in memory.
V$SQLAREA Reference
V$SQL and V$SQLAREA main fields:
sql_id - Id of the parent cursor in the library cache
child_number - Only for V$SQL: Number of this child cursor
version_count- Only for V$SQLAREA: Number of child cursors
hash_value - Hash value of the parent statement in the library cache
address - Address of the handle to the parent for this cursor
sql_fulltext - CLOB - full sql text
sql_text - VARCHAR2(1000) - first 1000 characters.
to get sql_fulltext
V$SQLAREA main fields
executions - Total number of executions
disk_reads - Sum of physical disk reads
buffer_gets - Sum of DB block gets (memory+physical)
cpu_time - CPU time in microseconds
parse_calls - Sum of all parse calls. (number of times SQL was re-parsed)
first_load_time - Timestamp of parent cursor creation
V$SQLTEXT
===============================
V$SQLTEXT holds text of SQL statements from shared SQL cursors in the SGA.
In V$SQLTEXT newlines and other control characters are replaced with whitespaces.
V$SQLTEXT Reference
V$SQLTEXT fields:
address - Used with hash_value to uniquely identify a cached cursor
hash_value - Used with address to uniquely identify a cached cursor
sql_id - SQL identifier of a cached cursor
command_type - Code for the type of SQL statement (SELECT, INSERT, and so on)
piece - Number used to order the pieces of SQL text
sql_text - One piece of the SQL text is only VARCHAR2(64)
===============================
V$SQLTEXT_WITH_NEWLINES
===============================
V$SQLTEXT is same as V$SQLTEXT, only newlines and other control characters are NOT replaced with whitespaces.
===============================
V$SQL_PLAN
===============================
V$SQL_PLAN holds the execution plan information per each child cursor.
V$SQL_PLAN Reference
V$SQL_PLAN fields
address - Address of the handle to the parent for this cursor
hash_value - Hash value of the parent cursor.
sql_id - SQL identifier of the parent cursor.
plan_hash_value - Unique identifier for SQL plan.
child_number - Number of the child cursor.
To get Explain Plan:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('1qqtru155tyz8',2));
with address and hash_value
Join to V$SQL
with address, hash_value, and child_number.
===============================
V$SESSION_LONGOPS
===============================
This table lists long running sessions, with their sql_address, sql_hash_value, sql_id, sql_plan_hash_value, and the time consuming step (full_scan, fast full scan, etc.)
SELECT SQLAREA.sql_text,
LONG.sid,
LONG.serial#,
LONG.time_remaining time_remaining_sec,
LONG.sql_plan_operation,
LONG.sql_plan_options
WHERE LONG.sql_id = SQLAREA.sql_id
AND LONG.start_time > TRUNC(SYSDATE)
AND SQLAREA.sql_text LIKE 'SELECT kuku%'
===============================
Common fields
===============================
sql_id - VARCHAR2(13)
Unique identifier, per SQL TEXT.('1qqtru155tyz8')
address - RAW(4 | 8)
hash_value - NUMBER
address + hash_value Uniquely identify cursor.
child_number - NUMBER
Number of child cursor(0,1,2,3,4...)
===============================
What is Child Cursor?
===============================
Child Cursors are simply cursors that reference the same exact sql_text - but are different in some fashion.
The first child cursor is numbered 0 (the parent), then 1 (first child), then 2 and so on.
In what way do cursors different? - Well, this varies.
For example:
A. Two users have table EMPLOYEES - and both run SELECT * FROM EMPLOYEES;
sql_id - would be same, It is same sql text.
child_cursor would be different.
B. For some reason - same SQLs have different execution plan.
===============================
What is unique SQL identifier?
===============================
address + hash_value + child_number
===============================
How to connect V$SQLAREA with V$SESSION?
===============================
hash_value and address fields uniquely identify the SQL cursor.
They are used to connect to V$SESSION
===============================
Top SQLs from V$SQL and V$SQLAREA
===============================
Get top SQLs with the V$SESSION info.
SELECT *
FROM(
SELECT SESSIONS.sid,
SESSIONS.username,
SESSIONS.osuser,
SESSIONS.machine,
SESSIONS.module,
SQLAREA.executions,
SQLAREA.disk_reads,
SQLAREA.buffer_gets,
ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) AS memory_gets_pct,
ROUND(SQLAREA.cpu_time/1000/1000) AS cpu_time_sec,
ROUND(SQLAREA.user_io_wait_time/1000/1000) AS io_time_sec,
SQLAREA.parse_calls,
SQLAREA.first_load_time,
SQLAREA.sql_text
FROM
V$SQL SQLAREA,
-- V$SQLAREA SQLAREA,
V$SESSION SESSIONS
WHERE SESSIONS.sql_hash_value = SQLAREA.hash_value
AND SESSIONS.sql_address = SQLAREA.address
AND SESSIONS.USERNAME IS NOT NULL
AND SQLAREA.executions > 0
ORDER BY
-- SQLAREA.disk_reads DESC
-- ROUND(SQLAREA.cpu_time/1000) DESC
-- ROUND(SQLAREA.user_io_wait_time/1000) DESC
-- SQLAREA.executions DESC
SQLAREA.parse_calls DESC
-- ROUND((SQLAREA.buffer_gets-SQLAREA.disk_reads)/DECODE(SQLAREA.buffer_gets,0,1,SQLAREA.buffer_gets)*100,2) ASC
)WHERE ROWNUM < 21;
===============================
Get SQL Text from V$SQLTEXT
===============================
SELECT SQLTEXT.sql_text,
SQLTEXT.piece ,
SQLTEXT.address,
SS.sid,
SS.username,
SS.schemaname,
SS.osuser,
SS.process,
SS.machine,
SS.terminal,
SS.program,
SS.type,
SS.module,
SS.logon_time,
SS.event,
SS.service_name,
SS.seconds_in_wait
FROM V$SESSION SS,
V$SQLTEXT SQLTEXT
WHERE SS.sql_address = SQLTEXT.address(+)
AND SS.service_name = 'SYS$USERS'
ORDER BY SQLTEXT.address, SQLTEXT.piece
===============================
Oracle memory structures.
===============================
V$SQL, V$SQLAREA, V$SQLTEXT - All query Shared SQL area.
V$SQL_PLAN - Query Library Cache.
V$SQL_PLAN - Query Library Cache.
===============================
Reference
===============================
Ask Tom: What is the diference between V$SQL* views
Ask Tom: On Seeing Double in V$SQL
Subscribe to:
Posts (Atom)