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: