Thanks Conny... this is very helpful. Larry On Feb 22, 2010, at 9:00 AM, Conny Martin wrote:
> Larry, > > you should compare the metatables with the indexes oracle knows. > > If one of these 2 statements gives you any rows, you have messed up your > indexes. > > SELECT name,'pk_idx for T-Table' FROM arschema WHERE schematype = 1 AND NOT > EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IT'||schemaid) > UNION ALL > SELECT name,'pk_idx for H-Table' FROM arschema WHERE schematype = 1 AND NOT > EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IH'||schemaid) > UNION ALL > SELECT name,'pk_idx for B-Table' FROM arschema WHERE schematype = 1 AND NOT > EXISTS (SELECT * FROM user_indexes WHERE index_name = 'IB'||schemaid) > > > SELECT > name,arschema.schemaid,schema_index.numfields,uniqueflag,indexname,f1,f2,f3 > FROM arschema,schema_index WHERE indexname NOT IN (SELECT INDEX_name FROM > user_indexes) AND schema_index.schemaid = arschema.schemaid > > HTH > > Kind Regards Conny > > -----Ursprüngliche Nachricht----- > Von: Action Request System discussion list(ARSList) > [mailto:arsl...@arslist.org] Im Auftrag von L G Robinson > Gesendet: Montag, 22. Februar 2010 14:33 > An: arslist@ARSLIST.ORG > Betreff: Re: Help with Sybase indexes > > Hi Joe, > > Thanks for the reply. I did compare the information in the AR System's > meta-tables between the two systems (schema_index) and it matches up as > expected. However, I believe it is possible for there to be a mismatch > between what ARS thinks and what actually exists in the DB. The situation I > am trying to detect is the one where ARS thinks there is an index, but for > whatever reason, the index no longer exists in the DB. > > I have been using Misi's wonderful RRRchive utility and on occasion, I have > to interrupt a copy operation before it finishes. In this case, I believe > that the target table will be left without its index since RRRchive does not > get the chance to recreate the index on the target table when the program is > killed. I recreate the index(es) when this happens... I just want to make > sure I have not missed any. > > Thanks again. > Larry > > On Feb 21, 2010, at 8:53 AM, Joe D'Souza wrote: > >> Larry, >> >> Why not look this same information up from the AR System's meta information? >> That way it would be easier to compare the two systems indexes if they >> should be identical.. >> >> Joe >> >> -----Original Message----- >> From: Action Request System discussion list(ARSList) >> [mailto:arsl...@arslist.org]on Behalf Of L G Robinson >> Sent: Friday, February 19, 2010 5:47 PM >> To: arslist@ARSLIST.ORG >> Subject: Help with Sybase indexes >> >> >> Hi Folks, >> >> I need some Sybase help regarding indexes. >> >> I am migrating from Sybase to Oracle and I want to compare the two >> systems to make sure that the Oracle system has all of the same >> indexes on the T-tables as currently exist in the Sybase system. I >> don't doubt that they were there originally as a result of the >> workflow import that I did to create the Oracle system. I just want to >> make sure that I have not inadvertently messed any of the indexes up >> while I have been working in the Oracle system and copying data over (Thanks >> Misi!!!). >> >> On the Oracle side, I can do the following: >> >> SELECT s.name as "Form Name", b.column_name, a.table_name, >> a.index_name, a.uniqueness,a.index_type FROM USER_INDEXES a, >> USER_IND_COLUMNS b, arschema s WHERE a.index_name = b.index_name AND >> cast(s.schemaId as varchar2(40)) = >> substr(a.table_name,2) AND substr(a.table_name,1,1) = 'T' >> ORDER by 1,2; >> >> This gives me exactly what I need. However, I have been spelunking >> through the Sybase system tables all day and I can not seem to locate >> all of the information I need. I can see "almost" everything in the >> "sysindexes" table. >> However, I do not see the name or id number of the column that the >> index is built upon. Maybe I'm just missing something obvious but I >> sure don't see it. >> >> Anyone have any insights into how to accomplish the above in Sybase? >> >> Thanks. >> Larry >> >> ARS: 5.1.2 >> Sybase: 12.5.2 >> Oracle: 10.2.0.4.0 >> >> Larry Robinson n...@ncsu.edu >> Office of Information Technology >> NC State University 919-515-5432 Voice >> Raleigh, NC 27695-7109 919-513-0877 FAX >> >> ______________________________________________________________________ >> _________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org >> Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum > Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" > > _______________________________________________________________________________ > UNSUBSCRIBE or access ARSlist Archives at www.arslist.org > Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are" _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor:rmisoluti...@verizon.net ARSlist: "Where the Answers Are"
