
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.