• CozWeAreFriends Banner. Nice isn't it?
  • CozWeAreFriends Banner. Nice isn't it?
  • Oracle SQLplus – How to check database version

    Posted on April 30th, 2012 by fath

    Hi All.

    A lot of you might’ve been knowing how to achieve what I mentioned in the title. But sometime, we are clueless and we dig down here and there, but found nothing. Hehe. So here’s the easy way how to get Oracle database version using SQLplus.

    Just login to your SQLplus. You will see it there. 🙂

     

    $ sqlplus ‘/as sysdba’

    SQL*Plus: Release 10.2.0.4.0 – Production on Mon Apr 30 17:19:49 2012

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

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
    With the Partitioning, Real Application Clusters, OLAP, Data Mining
    and Real Application Testing options

     



    UNIX – Shell script to capture SQL query using Oracle sqlplus

    Posted on April 22nd, 2012 by fath

    Hi All,
    In my project, as part of Performance Testing team, I’ve wrote a number of scripts to help us perform our duty in system tunings. Here. I’d like to share one of my shell script that can help to capture SQL query running in the background.Feel free to modify this script to fit your own requirement.

     

    ###############################
    # CAPTURE SQL – by Fath #
    ###############################
    ################# PARAMETERS #################

    ##############################################
    #SH_LOG=”$location/CAPTURE_SQL.log”

    clear
    echo “*****************************************************************”
    echo “PV SQL CAPTURE SCRIPT”
    echo “*****************************************************************”

    echo “LOADING CAPTURING SQL..”

    echo “”
    pros=`ps -ef | grep ora_pmon | awk ‘{print $9}’ | grep ora_pmon_ | awk ‘{print substr($1,10)}’`

    echo “Database running on Oracle Process = $pros”
    echo “Oracle SID = $ORACLE_SID”
    echo “Proceed? [y/n]”
    read option
    echo “”

    if [ “$option” = “y” ]
    then
    echo “Enter sid and serial#”
    echo “SID:”
    read sid
    echo “Serial #:”
    read serial

    result=`sqlplus -s ‘/as sysdba’ << ENDOFSQL
    begin
    dbms_system.set_ev(‘$sid’,’$serial’,10046,12,”);
    end;
    /

    begin
    DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>’$sid’, serial#=>’$serial’,sql_trace=>TRUE);
    end;
    /
    show parameter user_dump_dest
    exit;
    ENDOFSQL`

    #path=”$result”

    echo “”
    echo “——————————-”
    echo “Remarks:”
    echo “Spooled queries will be generated in:”
    #echo “$path” | grep /app | awk ‘{print $3}’

    path=”/app/oracle/admin/NSBLPVT/udump”
    echo “$path”
    echo “——————————-”
    echo “”

    while true
    do
    echo “Stop capturing? [y/n]”
    read option2
    echo “”

    if [ “$option2” = “y” ]
    then
    result2=`sqlplus -s ‘/as sysdba’ << ENDOFSQL
    begin
    DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (sid=>’$sid’, serial#=>’$serial’,sql_trace=>FALSE);
    end;
    /
    exit;
    ENDOFSQL`

    echo “——————————-”
    echo “Results: ”
    echo “SQL captured in: ”
    result3=`ls -lrt $path | tail -n 1 | awk ‘{print $9}’`
    echo “$result3”
    echo “Converting trace file to log…”
    result4=`tkprof $path/$result3 SQL_CAPTURED[$result3].log sys=no waits=yes sort=exeela`
    echo “Converted to SQL_CAPTURED[$result3].log…”
    echo “Completed.”
    echo “——————————-”

    break
    fi
    done
    fi

    echo “”
    echo “SEE YOU AGAIN!!”
    echo “”