The Geek Diary

How to get the DDL for indexes using dbms_metadata

by

Let consider the below example, where we list all the indexes in the database.

sql> select index_name from dba_indexes
where  tablespace_name ='EXAMPLE' and
       index_name like 'E%'
order  by 1 asc;

INDEX_NAME
-----------------
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX

Getting DDL

To get the DDL:

DBMS_METADATA.GET_DDL (
object_type IN VARCHAR2,
name IN VARCHAR2,
schema IN VARCHAR2 DEFAULT NULL,
version IN VARCHAR2 DEFAULT 'COMPATIBLE',
model IN VARCHAR2 DEFAULT 'ORACLE',
transform IN VARCHAR2 DEFAULT 'DDL')
RETURN CLOB;

Example:

sql> select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;

Run the below query to get a formatted output.

set heading off
set echo off
set flush off
set pagesize 9000
set linesize 80
set long 100000
spool rcindscr.sql

select 'select dbms_metadata.get_ddl ("INDEX", "'||index_name||'", "'||owner||'") from dual;' from dba_indexes where tablespace_name = 'EXAMPLE' and index_name like 'E%';

spool off

Result (after converting double quotes to single quotes in a text editor):

select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMP_ID_PK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_DEPARTMENT_IX', 'HR') from dual; 
select dbms_metadata.get_ddl ('INDEX', 'EMP_JOB_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_MANAGER_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_NAME_IX', 'HR') from dual;

Now run:

set heading off
set echo off
set flush off
set pagesize 8000
set linesize 80
set long 3999
set longc 3999
spool indsyntax.sql

select dbms_metadata.get_ddl ('INDEX', 'EMP_EMAIL_UK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_EMP_ID_PK', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_DEPARTMENT_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_JOB_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_MANAGER_IX', 'HR') from dual;
select dbms_metadata.get_ddl ('INDEX', 'EMP_NAME_IX', 'HR') from dual;

spool off

note: the ‘SET’ commands used above are very important here.

Results that will run in SQL*Plus:

CREATE UNIQUE INDEX "HR"."EMP_EMAIL_UK" ON "HR"."EMPLOYEES" ("EMAIL")
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 "EXAMPLE"
/
CREATE UNIQUE INDEX "HR"."EMP_EMP_ID_PK" ON "HR"."EMPLOYEES" ("EMPLOYEE_ID")
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 "EXAMPLE"
/
CREATE INDEX "HR"."EMP_DEPARTMENT_IX" ON "HR"."EMPLOYEES" ("DEPARTMENT_ID")
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 "EXAMPLE"
/
CREATE INDEX "HR"."EMP_JOB_IX" ON "HR"."EMPLOYEES" ("JOB_ID")
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 "EXAMPLE"
/
CREATE INDEX "HR"."EMP_MANAGER_IX" ON "HR"."EMPLOYEES" ("MANAGER_ID")
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 "EXAMPLE"
/
CREATE INDEX "HR"."EMP_NAME_IX" ON "HR"."EMPLOYEES" ("LAST_NAME", "FIRST_NAME")
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 "EXAMPLE"
/

Alternatives

1. Alternative to the above, you could also get the DDL of for instance all indexes of a schema using next sql:

sql> connect [schema]/[password]

set heading off
set echo off
set flush off
set pagesize 9000
set linesize 80
set long 100000
-- spool rcindscr.sql

select dbms_metadata.get_ddl (object_type, object_name)
from   user_objects
where  object_type = 'INDEX'
-- and ...

2. To get all index DDL of an (non-heterogeneous) object like a table, you can use the DBMS_METADATA.GET_DEPENDENT_DDL procedure, e.g.

sql> connect [schema]/[password]

set heading off
set echo off
set flush off
set pagesize 9000
set linesize 80
set long 100000
-- spool rcindscr.sql

SELECT dbms_metadata.get_dependent_ddl('INDEX','','') FROM dual;

You May Also Like