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

On May 21, 11:59 am, GS <G..._at_GS.com> wrote:
> gazzag wrote:
> > On 21 May, 15:09, GS <G..._at_GS.com> wrote:
> >> Thanks - but the schema that owns the table does not have create session
> >> priv's, so I need to run this from system schema, and this examples does
> >> not work..- Hide quoted text -
>
> >> - Show quoted text -
>
> > Simply apend the schema name:
>
> > SELECT DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','TEST',
> > '<schema_name'>) FROM DUAL;
>
> > HTH
>
> > -g
>
> got it, that did the trick
>
> thanks!- Hide quoted text -
>
> - Show quoted text -

First, GS, very nice example.

A couple of general comments about using get_dependend_ddl.

If the table has a PK or UK on it and you tell run it with 'INDEX' you will get create index statements for the index used to support the PK or UK but not the constraint definition itself. The create statements do not have ending semicolons so some editing is required.

If you run the procedure with 'CONSTRAINT' you will get an ALTER TABLE to add the PK/UK constaint with index definition code.

If you run both scripts depending on the order you run the scripts in you can get duplicate index creation errors.

You may also want to extract the grants and check for synonyms for the table.

HTH -- Mark D Powell -- Received on Thu May 22 2008 - 08:37:30 CDT