V$open Cursor Table Identification
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: V$open Cursor Table Identification

  1. #1
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132

    V$open Cursor Table Identification

    HI

    Thanks in advance

    select * from (select SQL_TEXT,COUNT(sql_text) total from v$OPEN_CURSOR GROUP BY rollup(SQL_TEXT));

    select * from (select SQL_TEXT,COUNT(sql_text) total from v$ 1
    select CREDIT_CARD_ID, CONTROL_NUMBER , REFERENCE_NUMBER , R 252
    select CREDIT_CARD_ID, CONTROL_NUMBER , REFERENCE_NUMBER ,RE 27
    table_4_2000_6c5f_0_0_0 4
    table_4_2000_6c83_0_0_0 26
    table_4_2000_6d68_0_0_0 6
    table_4_200_6c5f_0_0_0 9
    table_4_200_6c83_0_0_0 32

    now what is this table_4_200_6c83_0_0_0 / where it is getting generated from.
    is this name created internally table_4_200_6c83_0_0_0

    Gajanan

  2. #2
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    Always remember to post Oracle version. Is it 10g?
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  3. #3
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    Oracle 8.1.7 linux red hat advance server

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    search for that table in dba_tables, see who owns it

  5. #5
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    I gave SQL Query to search any table name with this table_4_200_6c83_0_0_0 32 from dba_tables but no rows was selected.


    just not getting from where is this coming from if i query v$open_cursor

    table_4_2000_6c5f_0_0_0 4
    table_4_2000_6c83_0_0_0 26
    table_4_2000_6d68_0_0_0 6
    table_4_200_6c5f_0_0_0 9
    table_4_200_6c83_0_0_0 32

  6. #6
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    You must have lobs

    http://www.oracle.com/technology/ora...o52asktom.html
    STRANGE ENTRIES IN V$OPEN_CURSOR

    Why is V$OPEN_CURSOR.SQL_TEXT showing things like "table_4_xxxx_x_x_x_ x"? Most of the entries in V$OPEN_CURSOR look like this. My database clients are JDBC thin drivers against Oracle8i Release 8.1.7 on Sun Solaris 8. For example:


    select user_name, count(1) as count, sql_text
    from sys.v_$open_cursor
    group by user_name, sql_text
    order by count;
    USER_NAME COUNT SQL_TEXT
    --------- ----- ---------------------
    ORACLE 1 select user_name, ...
    SGMASTER 1 SELECT PROPERTY_N...
    SGMASTER 5 INSERT INTO ...
    SGMASTER 7 table_4_200_5ee2_0_0_0

    SGMASTER 12 table_4_2000_5ed8_0...
    SGMASTER 12 table_4_200_5ed8_0_0_0
    SGMASTER 12 table_e_400_5ed8_2_0_0


    These entries are connected to implicit cursors surrounding LOBs. When you have a LOB open for reading/writing, you will see one of these mysterious entries in V$OPEN_ CURSOR. You can see them with nested tables as well (when you create or access them).
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

  7. #7
    Join Date
    Jan 2004
    Location
    Washington , USA
    Posts
    132
    Thanks for your output.

    identifying tables now is ok

    Now i am not able to understand how come it opens so many cursors at one time. ?

    select * from (select SQL_TEXT,COUNT(sql_text) total from v$OPEN_CURSOR GROUP BY rollup(SQL_TEXT)) where total >10;

    table_4_2000_6c83_0_0_0 53
    table_4_200_6c83_0_0_0 57

    how to check this ?

  8. #8
    Join Date
    Apr 2001
    Location
    Bangalore, India
    Posts
    727
    I think any implicit application should be closed by the application after processing. Check the application.
    Thomas Saviour(royxavier@yahoo.com)
    Technical Lead (Databases)
    Thomson Reuters (Markets)

    http://ora600tom.wordpress.com/

Posting Permissions

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



Click Here to Expand Forum to Full Width