Hi
I was wondering if there is any way to know when was the last time an index was rebuilt in the database (except for using log-miner).
Similarly, when was the last time a table was analysed?
Thanks
Raminder
Printable View
Hi
I was wondering if there is any way to know when was the last time an index was rebuilt in the database (except for using log-miner).
Similarly, when was the last time a table was analysed?
Thanks
Raminder
look in user_indexes and user_tables for the last_analyzed time
You only would want to rebuild the indices when they are fragmented.Code:
SELECT last_analyzed
FROM dba_tables
WHERE UPPER(OWNER) LIKE '%XYZ%'
AND UPPER(TABLE_NAME) LIKE '%ABC%';
-Sam
Thanks davey and Sambavan.
Is there a way to know when were the indexes last rebuilt?
Raminder
Rebuild gets timestamped in ???_objects.last_ddl_time.
But it will be overwritten by other DDL on index.
...and if moving them to a different tablespace.Quote:
Originally posted by sambavan
You only would want to rebuild the indices when they are fragmented.
-Sam
..and if you made them unusable to load data faster, then rebuilt them after the load was complete.Quote:
Originally posted by grjohnson
...and if moving them to a different tablespace.
Any more?
:cool:Quote:
Originally posted by slimdave
..and if you made them unusable to load data faster, then rebuilt them after the load was complete.
Any more?
... and after alter table exchange partition ... excluding indexes.
... and when the table is moved.
It used to be the case that making any column nullable or non-nullable would make all the bitmap indexes unusable, so there's another one.
I am afraid that's not true:Quote:
Originally posted by TomazZ:
Rebuild gets timestamped in ???_objects.last_ddl_time
SQL> select object_name, object_type, last_ddl_time from user_objects where object_name= 'MCF_EXCEPTION_LOOKUP_1';
OBJECT_NAME OBJECT_TYPE LAST_DDL
------------------------------ --------------- --------
MCF_EXCEPTION_LOOKUP_1 INDEX 03-02-21
SQL> alter index MCF_EXCEPTION_LOOKUP_1 rebuild;
Index altered.
SQL> select object_name, object_type, last_ddl_time from user_objects where obj
ect_name= 'MCF_EXCEPTION_LOOKUP_1';
OBJECT_NAME OBJECT_TYPE LAST_DDL
------------------------------ --------------- --------
MCF_EXCEPTION_LOOKUP_1 INDEX 03-02-21
My envronment is Oracle 8.0.5.2.1, though.
Regards