RE: When was an index last rebuilt?

Rama,

As a point of interest, since I don't have an 8.1.7 database, can you do the same test and check the column TIMESTAMP as well? I'd appreciate seeing the results.

Thanks!

Rachel

>From: "Rama Malladi" <rmalladi_at_inteliant.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: When was an index last rebuilt?
>Date: Tue, 02 Jan 2001 18:15:24 -0800
>
>Bruce,
> You are right. In our 8.1.7databases DBA_OBJECTS.LAST_DDL_TIME is getting
>updated after an index rebuild. But not in 8.1.5 databases...
>
> Here is the system O/P
>
>8.1.7 DB
>=======
>
>SQL> select OBJECT_NAME, LAST_DDL_TIME from dba_objects where
>OBJECT_NAME='CUSTOMER_CONTRACTS_1';
>
>OBJECT_NAME LAST_DDL_TIME
>--------------------------------------------- ---------
>CUSTOMER_CONTRACTS_1 07-DEC-00
>
>SQL> alter index CUSTOMER_CONTRACTS_1 rebuild;
>Index altered.
>
>SQL> select OBJECT_NAME, LAST_DDL_TIME from dba_objects where
>OBJECT_NAME='CUSTOMER_CONTRACTS_1';
>
>OBJECT_NAME LAST_DDL_TIME
>--------------------------------------------- ---------
>CUSTOMER_CONTRACTS_1 02-JAN-01
>
>
>
>8.1.5 DB
>=======
>
>18:13:26 SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,
>'dd-mm-yyyy:hh:mi:ss') from dba_objects where
>OBJECT_NAME='CONTRACT_ASSIGNMENTS_HQ_1';
>
>OBJECT_NAME TO_CHAR(LAST_DDL_TI
>---------------------------------------- -------------------
>CONTRACT_ASSIGNMENTS_HQ_1 02-01-2001:03:45:17
>
>18:13:30 SQL> alter index CONTRACT_ASSIGNMENTS_HQ_1 rebuild;
>Index altered.
>
>18:13:44 SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,
>'dd-mm-yyyy:hh:mi:ss') from dba_objects where
>OBJECT_NAME='CONTRACT_ASSIGNMENTS_HQ_1';
>
>OBJECT_NAME TO_CHAR(LAST_DDL_TI
>---------------------------------------- -------------------
>CONTRACT_ASSIGNMENTS_HQ_1 02-01-2001:03:45:17
>
>
>
>Rama
>=========================================================
>-----Original Message-----
>Bruce (CALBBAY)
>Sent: Tuesday, January 02, 2001 4:15 PM
>To: Multiple recipients of list ORACLE-L
>
>
>Hi,
>
>As of 8.1.5 this information is not stored in any of timestamp,
>last_ddl_time nor the created field.
>
>However, this is a bug (1096399) and rebuilding an index should update
>last_ddl_time - see excerpt below from a Metalink forum:
>
>"
>
>Hi. ALTER INDEX ... REBUILD does not update DBA_OBEJCTS.LAST_DDL_TIME in
>Oracle7/8. This is documented in bug 1096399. This bug is fixed in 8.1.7
>where an ALTER INDEX ... REBUILD of a non-partitioned index will update the
>LAST_DDL_TIME column of DBA/USER/ALL_OBJECTS.
>"
>
>The bug details state that the data_object_id field does get updated in
>versions below 817, so if really needed then perhaps this field could be
>used as a flag to indicate index rebuilds.
>
>Regards,
>Bruce Reardon
>mailto:bruce.reardon_at_comalco.riotinto.com.au
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Reardon, Bruce (CALBBAY)
> INET: Bruce.Reardon_at_comalco.riotinto.com.au
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Rama Malladi
> INET: rmalladi_at_inteliant.com
>
>Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
>San Diego, California -- Public Internet access / Mailing Lists
>--------------------------------------------------------------------
>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).