Re: last used time of an index ??

And if we're only talking a limited number of indexes, you can isolate them in separate tablespaces/datafiles and look at v$datafile.

Mogens

Sutton, Reed wrote:

>Prem,
>
>I found this on Google. Some guy named Thomas Kyte....? He sounds like
>maybe he might know what he is talking about.... ;-) Unfortunately I don't
>have his book here with me now so I am not sure exactly what code he is
>talking about, but this should be a good start for you.
>
>
>
>>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.
>
>-----Original Message-----
>Sent: Thursday, January 30, 2003 9:20 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Prem
> That is a 9i feature. One way in 8i is to regularly sample the SQL in the
>buffers, run explain plan on it, and scan the results for that specific
>index name. Another way is to drop the index and see if anyone complains ;-)
>
>Dennis Williams
>DBA, 40%OCP
>Lifetouch, Inc.
>dwilliams_at_lifetouch.com
>
>
>-----Original Message-----
>Sent: Thursday, January 30, 2003 4:50 AM
>To: Multiple recipients of list ORACLE-L
>
>
>guys,
>
>can i find out the time when an index was last used ....in Ver
>8.1.6 ?
>
>Regards,
>Prem Khanna J.
>
>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?=
  INET: mln_at_miracleas.dk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).