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!

Delay when running a query with a specific schema name

Discussion in 'General' started by alexcol, Jan 14, 2018.

  1. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Good afternoon:

    I need your help please, I am new at Oracle

    Im trying to run the below query in order to know what sessions are doing given a username ICONTABLE_SCL'

    Previusly check up if username exists

    Code (SQL):
    SQL> SELECT USERNAME FROM SYS.ALL_USERS
      2  WHERE username LIKE 'ICONTABLE%';

    USERNAME
    ------------------------------
    ICONTABLE_SCL
    Afterward i execute the the view to know if this session is working with this username or schema name but it didn bring any results after some hours;

    Code (SQL):
    SELECT a.sid, a.serial#, b.sql_text
    FROM v$session a, v$sqlarea b
    WHERE a.sql_address=b.address
    AND a.username='ICONTABLE_SCL';
     
    It should bring me sth right away or at least get any error but nothing yields

    Is there any other query to approach this:

    Thanks for your help in advanced
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    "...after some hours" Are you certain you actually executed this query? Or did it simply sit there waiting for a carriage return to get it going? I have no problems getting immediate results from that query on my database so there's nothing wrong with the code.

    You need to capture everything you are doing in a file and post that file here so everyone can see what you're actually doing. I suspect it's that it's waiting for you to hit "Enter" or "Return" to get it going.
     
  3. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Thank you.

    I have captured what i did in an file without getting any result, afterward pressing Enter keypad several times
    File output.txt is empty

    Code (SQL):
    spool output.txt
    SELECT a.sid, a.serial#, b.sql_text
    FROM v$session a, v$sqlarea b
    WHERE a.sql_address=b.address
    AND a.username='ICONTABLE_SCL';
    push Enter
    push Enter
     
    Aditionally i cancel that query afetr 20 minutes and deliberately in username replace whatever it came in mind and it should inmediately have yielded and error the user name does not exist or sthg like that

    Code (SQL):
    spool output.txt
    SELECT a.sid, a.serial#, b.sql_text
    FROM v$session a, v$sqlarea b
    WHERE a.sql_address=b.address
    AND a.username='WHATEVER';
    push enter

     
    But if i run another different query it displays the resulsets

    I would appreciate again your help in advanced or provide me with an another query to display the resulsets
     
  4. alexcol

    alexcol Active Member

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

    Another example: Previusly i run a query to display active sessions, for instance:

    Code (SQL):
      SID    SERIAL# USERNAME                       PROGRAM                                          OSUSER                         STATUS   SCHEMANAME                     LOGON_TIME          MACHINE
    ---------- ---------- ------------------------------ ------------------------------------------------ ------------------------------ -------- ------------------------------ ------------------- ----------------------------------------------------------------
             8      30813 QUETOL                         oracle@prodbtol01 (J002)                         oracle                         ACTIVE   QUETOL                         18/01/2018 00:08:16 prodbtol01
           894      48479 QUETOL                         sqlplus@proscltol01c (TNS V1-V3)                 scheduler                      ACTIVE   SYS                            18/01/2018 00:08:16 proscltol01c
          6559      47503 QUETOL                         oracle@prodbtol01 (J001)                         oracle                         ACTIVE   QUETOL                         18/01/2018 00:08:14 prodbtol01
          5193      36945 SYS                            oracle@prodbtol01 (J000)                         oracle                         ACTIVE   SYS                            18/01/2018 00:08:12 prodbtol01
          3968      50489 TOL                            sqlplus@proscltol01c (TNS V1-V3)                 xptol                          ACTIVE   TOL                            18/01/2018 00:05:08 proscltol01c

     
    So i wanted to know what the sessions are doing with USERNAME = QUETOL but the query i use below is not working for me because after some time it did not get any results even though i push Enter keypad several tiemes

    Code (SQL):
    00:08:17 SQL>
    00:08:41 SQL> SELECT a.sid, a.serial#, b.sql_text
    00:08:41   2  FROM v$session a, v$sqlarea b
    00:08:41   3  WHERE a.sql_address=b.address
    00:08:41   4  AND a.username='QUETOL';
     
  5. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    Besides the only thing weird i see is SID and SERIAL# is changing continuisly, dont know if it has sth to do with this because data is very dynamic?

    Code (SQL):

    SID    SERIAL# USERNAME                       PROGRAM                                          OSUSER                         STATUS   SCHEMANAME                     LOGON_TIME          MACHINE
    ---------- ---------- ------------------------------ ------------------------------------------------ ------------------------------ -------- ------------------------------ ------------------- ----------------------------------------------------------------
          5213      43259 QUETOL                         sqlplus@proscltol01c (TNS V1-V3)                 scheduler                      ACTIVE   SYS                            18/01/2018 02:43:24 proscltol01c

    Code (SQL):
                             oracle@prodbtol01 (J002)                         oracle                         ACTIVE   SYS                            18/01/2018 02:43:23 prodbtol01
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    This is obviously an application user account and it's constantly connecting and disconnecting from the database. SID and SERIAL# do NOT change for a running session.
     
  7. alexcol

    alexcol Active Member

    Messages:
    33
    Likes Received:
    0
    Trophy Points:
    80
    Location:
    Colombia
    So i realized whenever the SID and SERIAL# is the same i can catch up what the session is doing with the above query but if they are different no.

    Is there any other accurate query that given the USERNAME displays what the session is doing regardless SID and SERIAL# fileds is changing or not ?

    Thanks for your help
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No. User activity is tied to the session, identified by SID and SERIAL# and it's possible that one user account is connected multiple times, or in a repeating fashion. A username is NEVER considered a unique identifier; that's what SID and SERIAL# are for.
     
  9. alexcol

    alexcol Active Member

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

    I found session and remain inactive and logon time was since 17th january. it may uses application web logic to use some connectiios pools or sometjhing like that, here this is the session

    Code (SQL):
    19:06:52 SQL> L

      1  SELECT SID, Serial#, UserName, Program, osuser, STATUS, SchemaName, Logon_Time,FLOOR(last_call_et / 60) "Minutes", machine

      2  FROM V$Session

      3  WHERE

      4  --Status='INACTIVE' AND

      5  UserName = 'ICONTABLE_SCL'
    the lasta
      6* ORDER BY LOGON_TIME DESC

    21:08:55 SQL> /


           SID    SERIAL# USERNAME                       PROGRAM                                          OSUSER                         STATUS   SCHEMANAME                     LOGON_TIME             Minutes MACHINE

    ---------- ---------- ------------------------------ ------------------------------------------------ ------------------------------ -------- ------------------------------ ------------------- ---------- ----------------------------------------------------------------

          5686       9507 ICONTABLE_SCL                  oracle@proiuc01c (TNS V1-V3)                     oracle                         INACTIVE ICONTABLE_SCL                  17/01/2018 11:36:51        273 proiuc01c


    But i would like given the field last_call_et does not display in minutes but in another format, for instance 21/01/2018 16:35:01 the last activity.
    Is there anyway to get around this?

    I would appreciate your help in advance to close this issues
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,616
    Likes Received:
    364
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Your statement cannot be true since LAST_CALL_ET is a NUMBER field; there is no possible way it can default to a date/time display format. You are clearly misreading the ouput as the date/time data you mention is from the LOGON_TIME column.