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;