Re: getting DDL for all indexes on a table (using dbms_metadata.get)
From: GS <GS_at_GS.com>
Date: Tue, 20 May 2008 21:00:03 GMT
Message-ID: <nZGYj.3146$Yp.476@edtnps92>
>
> I have never used the routine but look at the dbms_metadata procedure
> GET_DEPENDENT_DDL.
>
> It would be fairly simple using SQL to generate a series of
> dbms_metadata.get_ddl calls where each call generated an index for the
> table_name used in the driving query against dba_indexes.
>
> We wrote our own index code generator years ago that just reads
> dba_indexes and dba_ind_columns that works for regular non-partitioned
> indexes and we have stuck to it. I have written a few scripts that
> use dbms_metadata but not the all indexes for a table yet.
>
> HTH -- Mark D Powell --
Date: Tue, 20 May 2008 21:00:03 GMT
Message-ID: <nZGYj.3146$Yp.476@edtnps92>
Mark D Powell wrote:
> On May 20, 4: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
>
> I have never used the routine but look at the dbms_metadata procedure
> GET_DEPENDENT_DDL.
>
> It would be fairly simple using SQL to generate a series of
> dbms_metadata.get_ddl calls where each call generated an index for the
> table_name used in the driving query against dba_indexes.
>
> We wrote our own index code generator years ago that just reads
> dba_indexes and dba_ind_columns that works for regular non-partitioned
> indexes and we have stuck to it. I have written a few scripts that
> use dbms_metadata but not the all indexes for a table yet.
>
> HTH -- Mark D Powell --
Thanks, your code would likely do the trick as none of these indexes are partitioned anyway, but I'm still trying to figure out the nuances of dbms_metadata and which syntax to use, documentation on which is hard to find so I'l scanning other peoples examples of how they have used it to see if they apply. So far I have tried:
select dbms_metadata.get_ddl('table_owner',index_name) from dba_indexes where table_owner='<OWNER>' and table_name='<TABLENAME>';
That doesn't work but a quick google on GET_DEPENDENT_DDL brought me to the asktom site so hopefully that will be fruitful.. Received on Tue May 20 2008 - 16:00:03 CDT