in

How to Get Table Definition in Oracle?

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:

  1. Get Table Structure Using the SQL Developer
  2. Get Table Structure Using the Toad for Oracle

Written by Vinish Kapoor

An Oracle Apex Consultant, Oracle ACE, and founder of foxinfotech.org and orclqa.com a question and answer forum for developers. You can connect with me on Facebook and Twitter.

Leave a Reply

Your email address will not be published. Required fields are marked *