Re: getting DDL for all indexes on a table (using dbms_metadata.get)
Date: Tue, 20 May 2008 14:10:28 -0700 (PDT)
Message-ID: <fc311bd0-6cbe-4ca1-b216-fbf7b96b8abe@q24g2000prf.googlegroups.com>
On May 20, 1:10 pm, GS <G..._at_GS.com> wrote:
> 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
See http://www.orafaq.com/node/807
http://www.psoug.org/reference/dbms_metadata.html
You can also use the exp/imp utilities with the show option to make a script file.
jg
-- @home.com is bogus. http://eight2late.wordpress.com/2008/05/09/an-it-system-tragedy-in-five-limericks/Received on Tue May 20 2008 - 16:10:28 CDT