Re: View to see when index was made invisible

From: David Fitzjarrell <>
Date: Mon, 23 Dec 2013 12:21:33 -0800 (PST)
Message-ID: <>

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> 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


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%'
SQL> spool did_nolog_work.log
SQL> /

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


David Fitzjarrell

On Monday, December 23, 2013 12:58 PM, Don Seiler <> 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 <> 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.
>PS  Happy Holidays!/Merry Christmas!
>From: Sandra Becker <>
>Reply-To: "" <>
>Date: Monday, December 23, 2013 at 12:27 PM
>To: oracle-l <>
>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?

Don Seiler 
Received on Mon Dec 23 2013 - 21:21:33 CET

Original text of this message