OracleObjects & schema

Generate DDL for a table and an index

Uses DBMS_METADATA to write the CREATE statements for a table and one of its indexes into ddl_list.sql in your current directory. Prompts for the owner, table and index names.

Prompts for input

Tested on 12c, 19c · verified Sep 2026

ora-get-ddl.sql

1SET VERIFY OFF2set heading off;3set echo off;4Set pages 999;5set long 90000;6 7spool ddl_list.sql8select dbms_metadata.get_ddl('TABLE',UPPER('&&table_name'),UPPER('&&owner')) from dual;9select dbms_metadata.get_ddl('INDEX',UPPER('&index_name'),UPPER('&&owner')) from dual;10spool off;11 12UNDEFINE table_name owner13set heading on;

Save it as ora-get-ddl.sql and run it with SQL> @ora-get-ddl.

Open in denrepo

Helps with

More Oracle scripts: Objects & schema

Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
Recompile invalid objectsRecompile one schema, or everything in the database with Oracle's utlrp script. Run the invalid objects check again afterwards.
Unusable indexes and index partitionsIndexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full…
Foreign keys that are probably not indexedForeign keys whose columns aren't the leading columns of an index. Unindexed foreign keys cause table-level locks when the parent row is updated or…
Disabled constraints and triggersConstraints and triggers someone switched off, often for a data load, and never switched back on.
Objects changed in the last 24 hoursApplication objects with DDL in the last day. Grants also update LAST_DDL_TIME, so not every row is a code change.