How to extract the Oracle table creation definition (Data Defining Language) from an Oracle database without digging dictionary views?

With the help of GET_DDL() function of DBMS_METADATA metadata package.

Syntax:

select DBMS_METADATA.GET_DDL(‘TABLE’,’TABLE_NAME’) from DUAL;

SET LONG 1000

SET PAGESIZE 0

select DBMS_METADATA.GET_DDL(‘TABLE’,’SAI_USER_MASTER’) from DUAL;

CREATE TABLE “MILIND”.”SAI_USER_MASTER”
( 
“USER_ID” NUMBER(11,0),
“ADMIN_USERNAME” NVARCHAR2(50),
“ADMIN_PASSWORD” NVARCHAR2(200) NOT NULL ENABLE,
“ADMIN_NAME” NVARCHAR2(50),
“ADMIN_EMAIL” NVARCHAR2(50),
“ADMIN_ROLES” NVARCHAR2(255),
“USER_ADDED” TIMESTAMP (6),
CONSTRAINT “PRIMARY00016” PRIMARY KEY (“USER_ID”)
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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS” 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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE “USERS”
By issuing above you function and package, we can extract whole table definition along with CONSTRAINTS and TABLESPACE.
***********************************************************************
Note: Please don’t hesitate to revert in case of any query OR feedback.

Thanking you.

Have a easy life ahead.