Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Oracle: All versions FAQ

Tips and Tricks

Get table/index information without writing complex SQL by ProblemSolver
Posted: 15 Feb 05

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!


Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close