How to extract the Oracle table definition (Data Defining Language)

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.

Leave a Reply