Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: When was an index last rebuilt?

RE: When was an index last rebuilt?

From: Rama Malladi <rmalladi_at_inteliant.com>
Date: Wed, 3 Jan 2001 09:34:38 -0800
Message-Id: <10730.125602@fatcity.com> 





Rachel,


 Here is what you asked for ...



8.1.7 DB




09:31:24 SQL> select OBJECT_NAME, timestamp, LAST_DDL_TIME from dba_objects
where


09:31:50   2  OBJECT_NAME='USAGE_JURISDICTION_4';



OBJECT_NAME                              TIMESTAMP           LAST_DDL_TIME
---------------------------------------- ------------------- ---------
USAGE_JURISDICTION_4                     2000-12-07:13:05:58       07-DEC-00





09:31:55 SQL> alter index USAGE_JURISDICTION_4 rebuild;



Index altered.



09:32:10 SQL> select OBJECT_NAME, timestamp, LAST_DDL_TIME from dba_objects
where


09:32:14   2  OBJECT_NAME='USAGE_JURISDICTION_4';



OBJECT_NAME                              TIMESTAMP           LAST_DDL_TIME
---------------------------------------- ------------------- ---------
USAGE_JURISDICTION_4                     2000-12-07:13:05:58       03-JAN-01





09:32:18 SQL>



8.1.5 DB





09:34:13 SQL> select OBJECT_NAME, timestamp, LAST_DDL_TIME from dba_objects
where  OBJECT_NAME='USAGE_JURISDICTION_4'


OBJECT_NAME                              TIMESTAMP           LAST_DDL_TIME
---------------------------------------- ------------------- ---------
USAGE_JURISDICTION_4                     2000-04-05:11:31:00       02-JAN-01





09:34:23 SQL> alter index USAGE_JURISDICTION_4 rebuild;
Index altered.



09:34:42 SQL> select OBJECT_NAME, timestamp, LAST_DDL_TIME from dba_objects
where  OBJECT_NAME='USAGE_JURISDICTION_4';


OBJECT_NAME                              TIMESTAMP           LAST_DDL_TIME
---------------------------------------- ------------------- ---------
USAGE_JURISDICTION_4                     2000-04-05:11:31:00       02-JAN-01






Hope it helps...


Rama


-----Original Message-----


From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Rachel
Carmichael


Sent: Tuesday, January 02, 2001 7:11 PM


To: Multiple recipients of list ORACLE-L
Subject: 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).






Get your FREE download of MSN Explorer at http://explorer.msn.com


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
  INET: carmichr_at_hotmail.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


Received on Wed Jan 03 2001 - 11:34:38 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US