Re: return all base objects referenced by a cursor?
Date: Fri, 31 Oct 2008 08:06:54 -0700 (PDT)
Message-ID: <83120e12-3a47-494b-b0e9-c75d0008ac90@b31g2000prb.googlegroups.com>
On Oct 31, 9:17 am, "wode..._at_googlemail.com" <wode..._at_googlemail.com>
wrote:
> Hi,
>
> Its been a slow day at work, and I had a thought to write a bit of sql
> to return the list of indexes, last time analyzed, and num_rows for a
> given cursor.
>
> I initially thought about using the join and split pl/sql functions
> available on the web to take sql_text and parse it to return the top
> level of objects referenced 1 per row, but i am now curious if there
> is a better view to obtain all the base objects referenced by a
> cursor. Any ideas?
>
> thanks
> Craig Simpson
Take a look at V$SQL_PLAN:
SQL> DESC V$SQL_PLAN
SELECT
SP.OBJECT_OWNER, SP.OBJECT_NAME, SP.OBJECT_TYPE
FROM
V$SQL_PLAN SP
WHERE
SQL_ID = '2xhj49maph0d6'
AND SP.OBJECT_TYPE LIKE 'INDEX%'; OBJECT_OWNER OBJECT_NAME OBJECT_TYPE
------------- ----------- -------------------- TESTUSER SYS_C004588 INDEX (UNIQUE) TESTUSER SYS_C005202 INDEX (UNIQUE)
You could then join the above to DBA_INDEXES to find the date that the index was last analyzed.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Oct 31 2008 - 10:06:54 CDT