1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Track down a long query by a shell script

Discussion in 'General' started by alexcol, Dec 8, 2017.

  1. alexcol

    alexcol Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Good morning

    I need your help plase

    I am trying to track down an active session whose userid called PROVISIONADOR and OSUSER i s oracle but wherever i run this query yields: no rows selected

    This process is inserting data to a table and is taking more than 12 hours

    This is the oracle user for operating system:

    Code (SQL):
    SCEL:/oracle/oracle/PAQUETES/C40760/Install oracle@prosclbt00c # id
    uid=1000(oracle) gid=26(dba)
    This is the process launched by a shell scrip:

    Code (SQL):
    oracle 33744     1   0 20:44:49 ?           0:00 /bin/ksh ./install_bd.ksh
    After exporting variables by means of a shell script and operating system is oracle

    Code (SQL):
    export ORACLE_SID=SCEL
    export TWO_TASK=SCEL

    this is part of the scripts taht calls sqlplus

    Code (SQL):
    # Nota: Es de responsabilidad de quien aplica el cambio la adecuada definicion
    #       de las VARIABLES de ambiente.
    uid=1000(oracle) gid=26(dba)
    TIME sqlplus /<<EOF

    This is the query;

    SELECT SID, Serial#, UserName, Program, osuser, Status, SchemaName, Logon_Time, machine
    FROM V$Session
    WHERE
    Status='ACTIVE' AND
    UserName = 'OPS$ORACLE'
    --AND OSUSER = 'oracle'
    order by LOGON_TIME desc

    no rows selected

    The log is writing

    Iv got the question

    Given the PID from Operating system ie 33744, schema name = PROVISIONADOR, OSUSER=oracle is it possible to build an accurate query that shows the ongoing session ?

    I appreciate your help in advanced
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    TWO_TASK reports that you are connecting to a remote database, not a local one. If you don't have your query environment set in the same way you'll never see any results.
     
  3. alexcol

    alexcol Active Member

    Messages:
    27
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    ok thanks, but i always export those 2 variables and the excecute DML commands vis a shell script. let me go straight,
    this is the whole process step by step

    Code (SQL):
    export ORACLE_SID=XYZ
    export TWO_TASK=XYZ

    SCEL:/oracle/oracle/PACKAGES/CXYZ/Install oracle@prosclbt00c # id
    uid=1000(oracle) gid=26(dba)

    nohup ./install_bd.ksh &
    Noramally i run the command ./install_bd.ksh and this script internally call other DML commands but now i used an unix command (nohup) in order to not to hang up the process
    But after 12 hours or more this process continue writing both logs consuming so much space

    Code (SQL):
    -rw-------   1 oracle   dba         1.8G Dec  8 12:23 nohup.out

    -rw-r-----   1 oracle   dba         1.8G Dec  8 12:23 install_1_0_SCEL.log
     
    Sadly, we validate with our DBA if there was any session but he said there was neither session with OSUSER oracle nor any other sessions affecting the specified tables.

    So it was likely the process was redirecting the output to both logs but it never connected to the DATABASE, dont know for sure:

    This is the script install_bd.ksh

    Code (SQL):
    more install_bd.ksh
    #!/bin/ksh
    # Descripcion: ASB
    TIME sqlplus /<<EOF
    SET echo ON verify ON feedback 1 timing ON TIME ON pages 10000 LINES 132 trimspool ON tab off FLUSH off
    /
    WHENEVER SQLERROR CONTINUE
    SET echo ON verify ON feedback 1 timing ON TIME ON pages 10000 LINES 132 trimspool ON tab off FLUSH off

    spool install_1_0$1_$ORACLE_SID.log

    @install_1_0.SQL

    spool off
    disconnect
    EOF

    resultado=$(egrep "ORA-|PLS-|SP2-" sclsql_$1_$ORACLE_SID.log |grep -v "ORA-00001" | grep -v "ORA-01430" |grep -v "ORA-01921" | grep -v "ORA-01920" |sort -u > final.log)

    hay_error=`wc -l final.log | awk '{ print $1 }'`

    ls -ltr *.log
    cat final.log
    rm final.log
    echo "ERRORES ENCONTRADOS "$hay_error
    exit
    I would appreciate your help in advanced
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,567
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Without seeing what install_1_0.SQL is doing there isn't much anyone can do here to help you. What do YOU see when you use the environment you posted and run sqlplus? Which database do YOU connect to? We're not there so YOU need to do some work before we can give assistance.