To: Shetty Rajan 

> 	Hi Ari,
> 
> 	How r u ?
> 
> 	I wanted to compare 2 databases using datalink for  index order on
> columns ( not names ).
> 	To be more specific if a table has 3 columns A , B & C which has
> been indexed in the order B,C & A
> 	I want to check the order with other database whether columns in
> other database is also indexed in the same order or different order.
> 
> 	Kindly let me know the SQL for the same which I can run on my
> database & compare the index order.
> 
> 	Reply as soon as possible.
> 
> 	Regards
> 
> 	Rajan

Hi,

The view to use is USER_IND_COLUMNS:

desc user_ind_columns
 Name                            Null?    Type
 ------------------------------- -------- ----
 INDEX_NAME                      NOT NULL VARCHAR2(30)
 TABLE_NAME                      NOT NULL VARCHAR2(30)
 COLUMN_NAME                              VARCHAR2(4000)
 COLUMN_POSITION                 NOT NULL NUMBER
 COLUMN_LENGTH                   NOT NULL NUMBER

The order is the COLUMN_POSITION.

So, if you have instance A and B (with the same username) you can issue:

SELECT * FROM USER_IND_COLUMNS
MINUS
SELECT * FROM USER_IND_COLUMNS@B

-or-

SELECT * FROM USER_IND_COLUMNS
MINUS
SELECT * FROM USER_IND_COLUMNS@A

depending on what database you are issuing the query from.

Best regards,

-Ari Kaplan
Independent Oracle DBA Consultant

<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 345+ Oracle tips, visit my Web Page:                      <->
<->                                                               <->
<->             http://www.arikaplan.com                          <->
<->                                                               <->
<->             email: akaplan@interaccess.com                    <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->

Back to Ari Kaplan's Home Page