• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to get the DDL for indexes using dbms_metadata

by admin

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;

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to check the status of OMS and Agent in OEM 13cR2
  2. How to find current SQL statement being executed by particular session in Oracle
  3. Connecting PDB in Oracle 12c and 19c
  4. Oracle Interview Questions : Grid Infrastructure Single Client Access Name (SCAN)
  5. How to Rename or Move Datafiles and Logfiles in Oracle Database
  6. Oracle 12c: Unplug/Plug PDB to new CDB using RMAN Active Database Duplication
  7. Oracle 19c New Feature – SQL_DIAGNOSE_AND_REPAIR (New Automatic Diagnostics and Repair Function)
  8. Adding Users to Oracle Passwordfile (Oracle Passwordfile Authentication)
  9. Queries to find out the SQL which is using these temporary tablespace
  10. ORA-12547: TNS:lost Contact (Oracle 12c2)

You May Also Like

Primary Sidebar

Recent Posts

  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found
  • macof: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright