oracle获取ddl建表语句dbms_metadata.get_ddl(‘TABLE‘,‘TABLE_NAME‘)

项目场景：

例如：本地环境出脚本部署正式环境需要将建表语句导出

查询DDL建表语句sql

select dbms_metadata.get_ddl('TABLE','TABLE_NAME') from dual;

---

问题描述

TABLE_NAME是要查询获取的表名称,TABLE_NAME的表名必须是大写

执行查询示例

--查询获取仓库档案DDL建表语句
select dbms_metadata.get_ddl('TABLE','BD_STORDOC') from dual;

--查询结果
  CREATE TABLE "NCC2105"."BD_STORDOC" 
   (	"CODE" VARCHAR2(40) NOT NULL ENABLE, 
	"CREATIONTIME" CHAR(19), 
	"CREATOR" VARCHAR2(20) DEFAULT '~', 
	"CSFLAG" CHAR(1) DEFAULT 'N', 
	"DATAORIGINFLAG" NUMBER(38,0) DEFAULT 0, 
	"DEF1" VARCHAR2(101), 
	"DEF2" VARCHAR2(101), 
	"DEF3" VARCHAR2(101), 
	"DEF4" VARCHAR2(101), 
	"DEF5" VARCHAR2(101), 
	"DR" NUMBER(10,0) DEFAULT 0, 
	"ENABLESTATE" NUMBER(38,0) DEFAULT 2 NOT NULL ENABLE, 
	"GUBFLAG" CHAR(1) DEFAULT 'N', 
	"ISAGENTSTORE" CHAR(1) DEFAULT 'N' NOT NULL ENABLE, 
	"ISATPAFFECTED" CHAR(1) DEFAULT 'Y' NOT NULL ENABLE, 
	"ISCALCULATEDINVCOST" CHAR(1) DEFAULT 'N', 
	"ISCOMMISSIONOUT" CHAR(1) DEFAULT 'N', 
	"ISDIRECTSTORE" CHAR(1) DEFAULT 'N' NOT NULL ENABLE, 
	"ISKPTAXSTORE" VARCHAR2(1), 
	"ISOBLIGATE" CHAR(1) DEFAULT 'Y', 
	"ISRETAIL" CHAR(1) DEFAULT 'N', 
	"ISSHOPSTORE" CHAR(1) DEFAULT 'N', 
	"ISSTOREONTHEWAY" CHAR(1) DEFAULT 'N' NOT NULL ENABLE, 
	"MEMO" VARCHAR2(300), 
	"MODIFIEDTIME" CHAR(19), 
	"MODIFIER" VARCHAR2(20) DEFAULT '~', 
	"MRPFLAG" CHAR(1) DEFAULT 'Y', 
	"NAME" VARCHAR2(300) NOT NULL ENABLE, 
	"NAME2" VARCHAR2(300), 
	"NAME3" VARCHAR2(300), 
	"NAME4" VARCHAR2(300), 
	"NAME5" VARCHAR2(300), 
	"NAME6" VARCHAR2(300), 
	"OPERATESUPPLIER" VARCHAR2(20) DEFAULT '~', 
	"PHONE" VARCHAR2(30), 
	"PK_ADDRESS" VARCHAR2(20) DEFAULT '~', 
	"PK_GROUP" CHAR(20) NOT NULL ENABLE, 
	"PK_ORG" CHAR(20) NOT NULL ENABLE, 
	"PK_STORDOC" CHAR(20) NOT NULL ENABLE, 
	"PRINCIPALCODE" VARCHAR2(38), 
	"PROFITCENTRE" VARCHAR2(20), 
	"PROFLAG" CHAR(1) DEFAULT 'N', 
	"STORADDR" VARCHAR2(20) DEFAULT '~', 
	"TS" CHAR(19) DEFAULT to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'), 
	 CONSTRAINT "PK_BD_STORDOC" PRIMARY KEY ("PK_STORDOC")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 131072 NEXT 131072 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "NNC_DATA01"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "NNC_DATA01" 

---