Oracle之查看建表语句

Myth丶恋晨 2023-02-26 12:25 43阅读 0赞

直入主题：

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

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

select dbms\_metadata.get\_ddl('TABLE','a') from dual;   //其中a为表名；

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

![Center][]

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

测试结果如下：

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][https_blog.csdn.net_zt_fucker_article_details_50767019]

[Center]: /images/20230209/5e07b8a91ae249f2845a0283a98af299.png
[https_blog.csdn.net_zt_fucker_article_details_50767019]: https://blog.csdn.net/zt_fucker/article/details/50767019