How do I get the DDL used to create function based index?

Question

I am reverse-engineering the schema of an Oracle database. I have a table and I created a function-based index on it.

DROP TABLE Fbi_tab;
CREATE TABLE Fbi_tab (
  a INTEGER, 
  b INTEGER, 
  c INTEGER
);

CREATE INDEX Idx ON Fbi_tab (a+b*(c-1), a, b);

How can I generate the DDL used to create this index?

Answer 1

You can use DBMS_METADATA package.

The DBMS_METADATA package provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.

For details: Oracle Documentation
DBMS_METADATA

SQL> create table fbi_tab(
  2  a number,
  3  b number,
  4  c number
  5  );

Table created.

SQL> create index idx on fbi_tab(a+b*(c-1),a,b);

Index created.


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

DBMS_METADATA.GET_DDL('INDEX','IDX','HR')
--------------------------------------------------------------------------------

  CREATE INDEX "HR"."IDX" ON "HR"."FBI_TAB" ("A"+"B"*("C"-1), "A", "B")
  PCTF


SQL> 

Answer 2

If you don't want to use dbms_metadata, you can collect information about indexes from all_indexes, join it with all_ind_columns and finally join with all_ind_expressions view to get column expression. It may be more difficult, but allows you build statement in your own format.