{"id":4179,"date":"2025-06-08T10:55:00","date_gmt":"2025-06-08T10:55:00","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=4179"},"modified":"2025-06-10T16:13:10","modified_gmt":"2025-06-10T16:13:10","slug":"extract-ddl-for-oracle-objects","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-dba-d2d-tasks\/extract-ddl-for-oracle-objects\/","title":{"rendered":"Extract DDL for Oracle Objects"},"content":{"rendered":"\n

This guide shows how to extract the CREATE statements (DDL) for common Oracle objects such as TABLE<\/code>, VIEW<\/code>, PROCEDURE<\/code>, FUNCTION<\/code>, PACKAGE<\/code>, TRIGGER<\/code>, DATABASE LINK<\/code>, INDEX<\/code>, SEQUENCE<\/code>, SYNONYM<\/code>, JOB<\/code>, LOB<\/code>, and MATERIALIZED VIEW<\/code>, with proper formatting using built-in Oracle packages.<\/p>\n\n\n\n

\u2705 DDL for Any Object Using DBMS_METADATA.GET_DDL<\/code><\/strong><\/h3>\n\n\n\n
\n

\ud83d\udd04 Replace 'HR'<\/code> and 'TABLE_NAME'<\/code> with your schema and object name.<\/p>\n<\/blockquote>\n\n\n\n

SET LONG 100000;\nSET PAGESIZE 0;\nSET LINESIZE 200;\nSET FEEDBACK OFF;\nSET ECHO OFF;\n\nSELECT DBMS_METADATA.GET_DDL('TABLE', 'TABLE_NAME', 'HR') FROM DUAL;<\/code><\/code><\/pre>\n\n\n\n
\ud83d\udccc Note:<\/strong> You can change 'TABLE'<\/code> to any of the following:<\/p>\n\n\n\n
    \n
  • 'VIEW'<\/code><\/li>\n\n\n\n
  • 'PROCEDURE'<\/code><\/li>\n\n\n\n
  • 'FUNCTION'<\/code><\/li>\n\n\n\n
  • 'PACKAGE'<\/code><\/li>\n\n\n\n
  • 'PACKAGE BODY'<\/code><\/li>\n\n\n\n
  • 'TRIGGER'<\/code><\/li>\n\n\n\n
  • 'DATABASE LINK'<\/code><\/li>\n\n\n\n
  • 'INDEX'<\/code><\/li>\n\n\n\n
  • 'SEQUENCE'<\/code><\/li>\n\n\n\n
  • 'SYNONYM'<\/code><\/li>\n\n\n\n
  • 'MATERIALIZED VIEW'<\/code><\/li>\n<\/ul>\n\n\n\n
    \u2705 DDL for JOBs (DBMS_JOB)<\/strong><\/h3>\n\n\n\n
    Oracle doesn’t support direct DDL via DBMS_METADATA<\/code> for jobs. Use this query to inspect scheduled jobs.<\/p>\n\n\n\n
    SELECT * FROM DBA_JOBS WHERE SCHEMA_USER = 'HR';<\/code><\/code><\/pre>\n\n\n\n
    \ud83d\udccc Tip:<\/strong> For DBMS_SCHEDULER jobs, use:<\/p>\n\n\n\n
    SELECT DBMS_METADATA.GET_DDL('PROCOBJ', 'JOB_NAME', 'HR') FROM DUAL;<\/code><\/code><\/pre>\n\n\n\n
    \u2705 DDL for LOBs (Inside Tables)<\/strong><\/h3>\n\n\n\n
    LOBs are part of tables. Use this to extract LOB info as part of table DDL:<\/p>\n\n\n\n
    SELECT DBMS_METADATA.GET_DDL('TABLE', 'TABLE_WITH_LOB', 'HR') FROM DUAL;<\/code><\/code><\/pre>\n\n\n\n
    \ud83d\udccc Note:<\/strong> The output includes LOB STORAGE<\/code>, TABLESPACE<\/code>, and CHUNK<\/code> settings if they exist.<\/p>\n\n\n\n
    \ud83d\udce4 Exporting All Object DDLs from a Schema<\/h3>\n\n\n\n
    To export DDLs for all objects of a specific type<\/strong>:<\/p>\n\n\n\n
    SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)\nFROM dba_objects\nWHERE owner = 'HR'\nAND object_type IN (\n  'TABLE', 'VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE',\n  'TRIGGER', 'DATABASE LINK', 'INDEX', 'SEQUENCE',\n  'SYNONYM', 'MATERIALIZED VIEW'\n);<\/code><\/code><\/pre>\n\n\n\n
    \ud83d\udccc Note:<\/strong> Filter by object_type<\/code> as needed. JOB<\/code> and LOB<\/code> need special handling.<\/p>\n\n\n\n
    \ud83e\uddfe Final Tip<\/h3>\n\n\n\n
      \n
    • Always run this in SQL*Plus or SQL Developer<\/strong> for proper formatting.<\/li>\n\n\n\n
    • Use SET LONG<\/code> to avoid truncated DDL output.<\/li>\n\n\n\n
    • Wrap output in a spool file to save for backup or migration purposes.<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"
      This guide shows how to extract the CREATE statements (DDL) for common Oracle objects such as TABLE, VIEW, PROCEDURE, FUNCTION, PACKAGE, TRIGGER, DATABASE LINK, INDEX, SEQUENCE, SYNONYM, JOB, LOB, and MATERIALIZED VIEW, with proper formatting using built-in Oracle packages. \u2705 DDL for Any Object Using DBMS_METADATA.GET_DDL \ud83d\udd04 Replace ‘HR’ and ‘TABLE_NAME’ with your schema and […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,984],"class_list":["post-4179","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-dba-d2d-tasks"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}