Get table/index information without writing complex SQL

While working with Oracle, you frequently need the DDL i.e. definition information of a table or indexes on a table.

Instead of writing complex SQL to access System tables e.g. ALL_TAB_COLUMNS, ALL_IND_COLUMNS etc., Oracle 9i and onwards you can use DBMS_METADATA to get the information you need.

For example, to get the DDL for tables SALES belonging to user SH, log into SQL*Plus and run the following:

set long 20000
spool somefile.txt
select dbms_metadata.get_ddl('TABLE', 'SALES', 'SH')
from dual;
spool off

The 1st parameter 'TABLE' signifies that we want the DDL for the Table.
The 2nd parameter 'SALES' specifies the name of the table we are interested in.
The 3rd parameter 'SH' specifies the schema i.e. the name of the owner of the table.

Now let's suppose you want the definitions for all indexes on table SALES belonging to user SH.

This time, the select statement changes to:

select dbms_metadata.get_ddl('INDEX',
a.index_name, 'SH')
from all_indexes a
where a.table_name = 'SALES';

Since we wanted the DDL for all the indexes, formulating the select this way makes it loop through each index name in turn.

Similarly, if you want the DDLs of all tables belonging to user SH, the select statement should be:

select dbms_metadata.get_ddl('TABLE',
a.table_name, 'SH')
from all_tables a
where owner = 'SH';


One word of warning - this package takes a little while to run - so be patient!