getting DDL for all indexes on a table (using dbms_metadata.get)

From: GS <GS_at_GS.com>
Date: Tue, 20 May 2008 20:10:44 GMT
Message-ID: <8fGYj.3140$Yp.359@edtnps92>


A table in a database has 146 columns and 76 indexes on it, most of the indexes are unused, I suspect. I'm not sure why, but most of these were (likely) from when developers had access to the database and were adding an index everytime they thought it might do some good. I want see which of these are being used at all, and drop the ones that aren't, but I want to generate the DDL for all the indexes on the table so if need be I can restore them.

I used to use a tool (I think it was called DB Artisan or something like that) that would do this, but don't have it available anymore and in any case would just as soon learn the sql syntax for doing this. I have done some research on dbms_metadata.get and see how I can generate ddl for a specific table, but I want it to generate all the ddl for all indexes on a given table, is this possible?

database is 9.2.0.7

thanks in advance Received on Tue May 20 2008 - 15:10:44 CDT

Original text of this message