SQL Language (MOSC)

MOSC Banner

Metadata for unused index

edited Feb 11, 2016 11:51PM in SQL Language (MOSC) 7 commentsAnswered ✓

For Unused indexes, dbms_metdata generate ddl in following format

1. create index

2. Alter index  .. Unusable;

Is there any way we can directly create DDL as below

create index <index>  .. unusable

Test Case

==========

SQL> create table t11(id number, text varchar2(10));

Table created.

SQL>

SQL>

SQL> create index t11_ix on t11(text) unusable;

Index created.

SQL> exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',true);

PL/SQL procedure successfully completed.

SQL> set pages 0

SQL> set long 1000

SQL> select dbms_metadata.get_ddl('INDEX','T11_IX') from dual;

  CREATE INDEX "SYS"."T11_IX" ON "SYS"."T11" ("TEXT")

  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center