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

From: gazzag <gareth_at_jamms.org>
Date: Wed, 21 May 2008 04:09:53 -0700 (PDT)
Message-ID: <4dd9a450-18fc-4c40-b80e-2de1d22ac19d@d1g2000hsg.googlegroups.com>

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