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.