Re: View to see when index was made invisible

Presuming that is the last DDL executed on that index it does return the correct date and time it was made invisible:

SQL> alter index logtest_idx invisible;

Index altered.

SQL>
SQL> select object_name, to_char(last_ddl_time, 'DD-MON-RRRR HH24:MI:SS') last_ddl_time
  2  from user_objects
  3  where object_type = 'INDEX'
  4  and object_name = 'LOGTEST_IDX';

OBJECT_NAME                                                                                                              LAST_DDL_TIME
-------------------------------------------------------------------------------------------------------------------------------- -----------
---------
LOGTEST_IDX                                                                                                              23-DEC-2013 13:18:22

SQL>


But, if that wasn't the last DDL applied to that index DBMS_LOGMNR can tell you:

SQL> select substr(sql_redo,1,100) sql_redo, to_char(timestamp,'DD-MON-RRRR HH24:MI:SS') timestamp
  2  from v$logmnr_contents
  3  where seg_owner = upper('bing')
  4  and sql_redo like '%invis%'
  5
SQL> spool did_nolog_work.log
SQL> /

SQL_REDO                                                                                             TIMESTAMP
---------------------------------------------------------------------------------------------------- --------------------
alter index logtest_idx invisible;                                                                   23-DEC-2013 13:18:22

SQL>

 
David Fitzjarrell
 



On Monday, December 23, 2013 12:58 PM, Don Seiler <don_at_seiler.us> wrote:
 
Perhaps the last_ddl_time of the index in dba_objects would possibly indicate? This is assuming that the ALTER INDEX to make it invisible was the last DDL on it. I haven't tested this though.



On Mon, Dec 23, 2013 at 12:49 PM, Mark Bobak <Mark.Bobak_at_proquest.com> wrote:

Unless you had auditing enabled *before* the index was made invisible, I think not.
>
>
>A quick look at DBA_INDEXES definition, and you can see that the visibility status of an index is just a bit in the FLAGS column of IND$.  I can’t imagine that Oracle records date/time that it flipped a bit.
>
>
>-Mark
>
>
>PS  Happy Holidays!/Merry Christmas!
>
>
>From: Sandra Becker <sbecker6925_at_gmail.com>
>Reply-To: "sbecker6925_at_gmail.com" <sbecker6925_at_gmail.com>
>Date: Monday, December 23, 2013 at 12:27 PM
>To: oracle-l <oracle-l_at_freelists.org>
>Subject: View to see when index was made invisible
>
>
>
>Running 11gr3 EE.  Is there a view where I can see when an index was made invisible?
>
>-- 
>Sandy
>GHX
>


-- 
Don Seiler
http://www.seiler.us 

--
http://www.freelists.org/webpage/oracle-l