Re: 9i v$object_usage feature in 8.1.7

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 23 Feb 2002 08:56:16 -0800
Message-ID: <a58hjg013d3@drn.newsguy.com>

In article <3ee16386.0202220917.2413a694_at_posting.google.com>, marlythomas_at_hotmail.com says...
>
>Question:
>
>Has anyone tried to create the object_usage 9i feature in 8.1.7?
>
>We're not going to 9i any time soon but would REALLY LIKE the feature
>to use now. I looked into the create scripts and saw the table and
>view creates, but didn't find anything that would build a proc to use
>it.
>
>I would guess that using a stint on object auditing would do the
>trick.
>

index monitoring is only available with 9i and up. You cannot just create the views in 8i -- there isn't any code in the database to supply the needed values behind the view! The alter index iname monitoring command doesn't exist in 8i.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> alter index t_idx monitoring usage; alter index t_idx monitoring usage

                  *

ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER SNAPSHOT option

auditing won't help you out here either as it doesn't audit accesses to indexes.

If you have my book -- i do describe a way to do this using stored query outlines in a fashion. You can ask the database to store the query outline for all queries executed. From there, you can see in the hints that are stored what indexes are used by which queries.

>Any ideas?
>
>
>Thanks,
>
>Marly

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Sat Feb 23 2002 - 10:56:16 CST