Re: return all base objects referenced by a cursor?
Date: Fri, 31 Oct 2008 08:06:54 -0700 (PDT)
On Oct 31, 9:17 am, "wode..._at_googlemail.com" <wode..._at_googlemail.com> wrote:
> 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?
> Craig Simpson
Take a look at V$SQL_PLAN:
SQL> DESC V$SQL_PLAN SELECT
SP.OBJECT_OWNER, SP.OBJECT_NAME, SP.OBJECT_TYPE
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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Fri Oct 31 2008 - 10:06:54 CDT