17

How can I generate a DDL script on an existing table in oracle? I am working on a project where I have to re-create some tables that are present in Oracle table into Hive.

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
VSJ
  • 213
  • 1
  • 2
  • 5

2 Answers2

27

If your SQL client doesn't support this, then you can use the dbms_metadata package to get the source for nearly everything in your database:

For a table use something like this:

select dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME')
from dual;

You can also do this for all tables at once:

select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables;

and spool the output into a SQL script.

More details are in the manual: http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_metada.htm

  • 1
    THANKS! what is "TABLE" and "YOUR_TABLE_NAME"/Table_Name? – VSJ Oct 08 '14 at 06:07
  • Also, what is dual and user_tables? I am new to oracle, please explain. thanks. – VSJ Oct 08 '14 at 06:08
  • @vids: `USER_TABLES`: http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_5490.htm `dual`: http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries009.htm#SQLRF20036 –  Oct 08 '14 at 06:21
  • 7
    Without the third parameter for the schema it may try to look for the table in the SYSTEM schema even if session current_schema is different: `select dbms_metadata.get_ddl('TABLE', 'YOUR_TABLE_NAME', 'YOUR_SCHEMA_NAME') from dual;` – pumpkinthehead May 10 '19 at 22:21
12

Just expanding a bit on @a_horse_with_no_name's answer. Using DBMS_METADATA, you might have to take care of the format in SQL*Plus in order to get the output properly.

For example, I want to get the DDL for SCOTT.EMP table.

SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
  2  from dual;

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),


SQL>

But, that is not what I expected.

So, setting up the format properly, would give me my desired output

SQL> set long 100000
SQL> set head off
SQL> set echo off
SQL> set pagesize 0
SQL> set verify off
SQL> set feedback off
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
  2  from dual;

  CREATE TABLE "SCOTT"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

SQL>
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124