Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    27

    Unanswered: How to retrieve the list of queries that the server is running at one time?

    Hi,

    I'm using ASP with Oracle and i need to know whether can i check the list of queries the oracle server are executing at one time?

    and also, i need to know how do i query all the server variables such as version, query execution response time, and everything that the oracle server offers...

    pls advice, thanks!
    Warm Regards,
    Jacky

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle supports many, many views into the realtime SGA data structures.
    They can be listed by....
    SELECT VIEW_NAME FROM DBA_VIEWS
    WHERE OWNER = 'SYS'
    AND VIEW_NAME LIKE 'V$%';

    and hopefully you'll RTFM
    http://download-west.oracle.com/docs...h3.htm#1109131
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Posts
    27
    hi anacedent,

    thanks for replying, but sadly to tell you that i'm not sure of how to do that. Actually i just want to monitor the database to know that which query is currently executing at a certain time. May you please further your explanation?
    Warm Regards,
    Jacky

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    There might be a better way, but here is an example of how I find what someone is currently running.

    HIH

    Code:
    select a.osuser, sql_text
    from v$session a, v$sqlarea b
    where b.hash_value = a.sql_hash_value
      and a.schemaname != 'SYS'
      and a.status = 'ACTIVE'

  5. #5
    Join Date
    Mar 2004
    Location
    Berkshire, UK
    Posts
    186
    this may also give you some of the info you want:

    select * from v$sql where users_executing > 0
    There are 10 types of people in the world, those that know Binary and those that don't.

  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482
    Try this script. It will show you the current SQL statement Oracle users execute:

    set echo off
    set verify off

    select sid, username, command, lockwait, status, osuser, sql_text
    from v$session s, v$sqlarea q
    where s.sql_hash_value = q.hash_value
    and s.sql_address = q.address
    and s.sid = &usrsid
    /

    HTH,

    clio_usa - OCP 8/8i/9i

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >It will show you the current SQL statement Oracle users execute

    Please define/quantify "current".
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •