Home » SQL & PL/SQL » SQL & PL/SQL » Index creation time (10.2.0.4.0 ,windows)
Index creation time [message #426818] Tue, 20 October 2009 00:03 Go to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member


Hi,

There are 2 indexes A_ind , B_ind in a column .How to find which index is created at last ?

Regards,
Re: Index creation time [message #426819 is a reply to message #426818] Tue, 20 October 2009 00:10 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
SQL> desc dba_objects
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER						    VARCHAR2(30)
 OBJECT_NAME					    VARCHAR2(128)
 SUBOBJECT_NAME 				    VARCHAR2(30)
 OBJECT_ID					    NUMBER
 DATA_OBJECT_ID 				    NUMBER
 OBJECT_TYPE					    VARCHAR2(19)
 CREATED					    DATE
 LAST_DDL_TIME					    DATE
 TIMESTAMP					    VARCHAR2(19)
 STATUS 					    VARCHAR2(7)
 TEMPORARY					    VARCHAR2(1)
 GENERATED					    VARCHAR2(1)
 SECONDARY					    VARCHAR2(1)

Re: Index creation time [message #426820 is a reply to message #426819] Tue, 20 October 2009 00:21 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member



May i know the exact query for this??


Regards,
Re: Index creation time [message #426822 is a reply to message #426818] Tue, 20 October 2009 00:29 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
>There are 2 indexes A_ind , B_ind in a column .
Last 1 is your answer.
Re: Index creation time [message #426823 is a reply to message #426822] Tue, 20 October 2009 00:32 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member


Sorry , i didnt get you.Please be clear with your answer.
Re: Index creation time [message #426824 is a reply to message #426820] Tue, 20 October 2009 00:38 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Mohan10g wrote on Tue, 20 October 2009 07:21

May i know the exact query for this??
Regards,

What is your problem writing the query from the view definition?

Regards
Michel

[Updated on: Tue, 20 October 2009 00:38]

Report message to a moderator

Re: Index creation time [message #426826 is a reply to message #426824] Tue, 20 October 2009 00:42 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

i ran this query based on view definition but i did not get the expected output.

select * from dba_objects where object_type='INDEX' and owner='MOHAN' ;

Please tell me exact query

Regards,
Re: Index creation time [message #426827 is a reply to message #426818] Tue, 20 October 2009 00:42 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

alter session set nls_date_format='DD-MON-YYYY HH24:MI';

select object_name,object_type,last_ddl_time from dba_objects where object_name=upper('&object_name');

Regards

Bala
Re: Index creation time [message #426831 is a reply to message #426827] Tue, 20 October 2009 01:05 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member

Hi bala,

Thanks for your answer.

I ran this query , but still not getting the output.Please correct me if my query is wrong.
select object_name,object_type,last_ddl_time ,created from dba_objects where
object_type='INDEX' and object_name='TABLE_NAME'


Regards,
Re: Index creation time [message #426832 is a reply to message #426831] Tue, 20 October 2009 01:13 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member


Bala,

I got my answer from the TOAD though i was not able to find it from the dba_objects.

alter session set nls_date_format='DD-MON-YYYY HH24:MI';

Could you tell why did you use this command before running the query?

Regards,
Re: Index creation time [message #426833 is a reply to message #426818] Tue, 20 October 2009 01:17 Go to previous messageGo to next message
balakrishnay
Messages: 54
Registered: September 2009
Location: Pune
Member

Hi,

I don't really understand what are you trying to do with this .

I ran this query , but still not getting the output.Please correct me if my query is wrong.
select object_name,object_type,last_ddl_time ,created from dba_objects where
object_type='INDEX' and object_name='TABLE_NAME'


This is your question rite

There are 2 indexes A_ind , B_ind in a column .How to find which index is created at last ?

This is very simple................DAM Simple ........

A_ind & B_ind are two indexes rite.

select object_name,object_type,last_ddl_time ,created from dba_objects where object_name in ('A_IND','B_IND');

Thats it you are making it too complex for very simple one.

Regards

Bala
Re: Index creation time [message #426834 is a reply to message #426826] Tue, 20 October 2009 01:18 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Mohan10g wrote on Tue, 20 October 2009 07:42
i ran this query based on view definition but i did not get the expected output.

You didn't get the expected output ? What was your output ? What do you expect ?

BlackSwan has already provide you with the correct answer to your question.
Re: Index creation time [message #426835 is a reply to message #426833] Tue, 20 October 2009 01:21 Go to previous messageGo to next message
Mohan10g
Messages: 159
Registered: May 2009
Location: INDIA
Senior Member



ok thanks.

Re: Index creation time [message #426860 is a reply to message #426832] Tue, 20 October 2009 02:26 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Mohan10g wrote on Tue, 20 October 2009 08:13

Bala,

I got my answer from the TOAD though i was not able to find it from the dba_objects.

And this is exactly why TOAD is not suitable for learning to work with Oracle. You got your result, but have NO IDEA how TOAD retrieved it.

Quote:

alter session set nls_date_format='DD-MON-YYYY HH24:MI';

Could you tell why did you use this command before running the query?

This was done because he relied on implicit datatype conversion (which is BAD). Instead of doing a select to_char(last_ddl_time, 'dd-mon-yyyy hh24:mi') he altered the way dates are displayed for the complete session.
Previous Topic: to know free and used spaces in a table
Next Topic: Cursor to fetch first day and last day of the quarter
Goto Forum:
  


Current Time: Wed Sep 28 22:53:46 CDT 2016

Total time taken to generate the page: 0.13841 seconds