Oracle之查看建表语句

直入主题：

如果有PLSQL的话可以直接在可视化界面查看表的建表语句；

如果没有PLSQL的话也没有关系：如下

select dbms_metadata.get_ddl('TABLE','a') from dual;   //其中a为表名；

如果如下错误:（图是扒的。实在懒得截图）

只需要将表名的小写改为大写即可；

测试结果如下：
 

 CREATE TABLE "CRM3"."BIGDATA_QUALITY_CUSTOM" 
   (	"ID" NUMBER(10,0) NOT NULL ENABLE, 
	"COMPANY_NAME" VARCHAR2(25), 
	"PLATE" VARCHAR2(25), 
	"COLOR" VARCHAR2(25), 
	"CREATE_TIME" VARCHAR2(50), 
	"UPDATE_TIME" VARCHAR2(50), 
	"FLAG" VARCHAR2(2), 
	 PRIMARY KEY ("ID")
  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 "CRM3"  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 "CRM3" 

补充：

获取多个表的建表语句

将表名粘贴到 table_name.txt

AIP_AVG_LINE_OFFSET_CONFIG
AIP_AVG_LINE_OFFSET_STRATEGY
AIP_COST_OPTIMIZATION_INDEX

git bash中执行 

cat table_name.txt | awk -F" " '{print "select to_char(dbms_metadata.get_ddl('\''TABLE'\'','\''"$1"'\'')) as t from dual union"}'

select to_char(dbms_metadata.get_ddl('TABLE','AIP_AVG_LINE_OFFSET_CONFIG')) as t from dual union
select to_char(dbms_metadata.get_ddl('TABLE','AIP_AVG_LINE_OFFSET_STRATEGY')) as t from dual union
select to_char(dbms_metadata.get_ddl('TABLE','AIP_COST_OPTIMIZATION_INDEX')) as t from dual union

去数据库navicat 查询一下，就获取建表语句了

 

转载地址 ：https://blog.csdn.net/zt_fucker/article/details/50767019