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

From: GS <GS_at_GS.com>
Date: Wed, 21 May 2008 14:09:34 GMT
Message-ID: <y2WYj.3244$Yp.1425@edtnps92>


gazzag wrote:
> On 20 May, 21:10, 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

>
> SCHEMA_NAME_at_DB1> SELECT * FROM V$VERSION;
>
> BANNER
> --------------------------------------------------------------
> Oracle Database 10g Release 10.2.0.1.0 - Production
> PL/SQL Release 10.2.0.1.0 - Production
> CORE 10.2.0.1.0 Production
> TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
> NLSRTL Version 10.2.0.1.0 - Production
>
> SCHEMA_NAME_at_DB1> CREATE TABLE test (
> 2 col1 NUMBER,
> 3 col2 VARCHAR2(10));
>
> Table created.
>
> SCHEMA_NAME_at_DB1> CREATE INDEX idx1 ON test(col1);
>
> Index created.
>
> SCHEMA_NAME_at_DB1> CREATE INDEX idx2 ON test(col1, col2);
>
> Index created.
>
> SCHEMA_NAME_at_DB1> set long 10000000
> SCHEMA_NAME_at_DB1> SELECT
> DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','TEST') FROM DUAL;
>
> DBMS_METADATA.GET_DEPENDENT_DDL('INDEX','TEST')
> --------------------------------------------------------------------------------
>
> CREATE INDEX "SCHEMA_NAME"."IDX1" ON
> "SCHEMA_NAME"."TEST" ("COL1")
> PCTFREE 10 INITRANS 2 MAXTRANS
> 255
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
> 2147483645
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
> DEFAULT)
> TABLESPACE
> "USERS"
>
> CREATE INDEX "SCHEMA_NAME"."IDX2" ON "SCHEMA_NAME"."TEST" ("COL1",
> "COL2")
> PCTFREE 10 INITRANS 2 MAXTRANS
> 255
> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
> 2147483645
> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
> DEFAULT)
> TABLESPACE
> "USERS"
>
>
> HTH
>
> -g

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.. Received on Wed May 21 2008 - 09:09:34 CDT

Original text of this message