How to get table and index DDL in Oracle

How to get table and index DDL in Oracle?

To get all table and indexes for the EDS table, we execute dbms_metadata. get_ddl, select from DUAL, and providing all required parameters.

set heading off;
set echo off;
Set pages 999;
set long 90000;
spool tableddl.sql
select dbms_metadata.get_ddl(‘TABLE’,’ACCT_INFO’,’EDS’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,’ACCT_INFO_PK’,’EDS’) from dual;
spool off;
Now we can modify the syntax to get a whole schema. It us easily done by select dbms_metadata. get_ddl and specifying TABLES_NAME and INDEXES_NAME :

set pagesize 0
set long 90000
set feedback off
set echo off
spool edt_schema.sql
SELECT DBMS_METADATA.GET_DDL(‘TABLE’,p.table_name)
FROM USER_TABLES p;
SELECT DBMS_METADATA.GET_DDL(‘INDEX’,p.index_name)
FROM USER_INDEXES p;
spool off;

 

Leave a Reply

Your email address will not be published. Required fields are marked *