How to Get Table Definition in Oracle?

Written by Vinish Kapoor

Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.

April 26, 2023
PLSQL
1 min read

Use dbms_metadata.get_ddl() function to get the table definition in Oracle. Below is an example:

DBMS_METADATA.GET_DDL() Example

The following SQL query will get the table definition for the EMP table in the current schema of Oracle Database:

select DBMS_METADATA.GET_DDL('TABLE','EMP') from DUAL;

Output:

CREATE TABLE "FJ22CDVLZSYLNS"."EMP" (
  "EMPNO" NUMBER(4, 0) NOT NULL ENABLE,
  "ENAME" VARCHAR2(10),
  "JOB" VARCHAR2(9),
  "MGR" NUMBER(4, 0),
  "HIREDATE" DATE,
  "SAL" NUMBER(7, 2),
  "COMM" NUMBER(7, 2),
  "DEPTNO" NUMBER(2, 0),
  PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 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 "APEX_13295325405752554236" ENABLE,
  FOREIGN KEY ("MGR") REFERENCES "FJ22CDVLZSYLNS"."EMP" ("EMPNO") ENABLE,
  FOREIGN KEY ("DEPTNO") REFERENCES "FJ22CDVLZSYLNS"."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 "APEX_13295325405752554236"

Get Table Definition in Oracle Using DB Tools

To view the table definition using the SQL Developer and Toad, check the following posts: