RE: When was an index last rebuilt?

From: Rama Malladi <rmalladi_at_inteliant.com>
Date: Tue, 2 Jan 2001 18:13:48 -0800
Message-Id: <10729.125567@fatcity.com>

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-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Reardon, Bruce (CALBBAY)
Sent: Tuesday, January 02, 2001 4:15 PM
To: Multiple recipients of list ORACLE-L Subject: RE: When was an index last rebuilt?

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

Received on Tue Jan 02 2001 - 20:13:48 CST