oracle 导出建表语句。

来源：互联网发布：oracle sql列默认值编辑：程序博客网时间：2024/04/28 21:15

dbtool 不支持CLOB类型，做不到，所以要用sqlplus去做。

连接数据库： sqlplus username/password@url

导建表语句：select dbms_metadata.get_ddl('TABLE','tablename','属主') from dual;

要是发现显示不完整。做以下设置：

set heading off;　
set echo off;
set pages 999;　
set long 99999;
set linesize 2000;
即可看到。

例如：

BS15CS:/home/wh#sqlplus gzzw/gzzw@boss20dev

SQL*Plus: Release 10.2.0.2.0 - Production on Thu May 2 15:41:31 2013

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

GZZW@boss20dev > select dbms_metadata.get_ddl('TABLE','SP_LTE_LOG','') from dual;

DBMS_METADATA.GET_DDL('TABLE','SP_LTE_LOG','')
--------------------------------------------------------------------------------

CREATE TABLE "GZZW"."SP_LTE_LOG"
( "ID" NUMBER(15,0) NOT NULL ENABLE,
"

GZZW@boss20dev >set heading off;　
GZZW@boss20dev >set echo off;
GZZW@boss20dev >set pages 999;　
GZZW@boss20dev >set long 99999;
GZZW@boss20dev >set linesize 9000;
GZZW@boss20dev >select dbms_metadata.get_ddl('TABLE','SP_LTE_LOG','') from dual;

CREATE TABLE "GZZW"."SP_LTE_LOG"
   (    "ID" NUMBER(15,0) NOT NULL ENABLE,
        "MOBILENO" VARCHAR2(20) NOT NULL ENABLE,
        "AREACODE" VARCHAR2(10),
        "NETCELLID" NUMBER(5,0),
        "NETCELLTYPE" NUMBER(5,0),
        "SEQS" NUMBER(5,0),
        "CREATTIME" DATE,
        "DEALTIME" DATE,
        "DEALCOUNT" NUMBER(3,0),
        "ISSUCCESS" NUMBER(3,0),
        "DATAS1" VARCHAR2(255),
        "DATAS2" VARCHAR2(255),
        "LOGSEQ" VARCHAR2(30),
        "BIPCODE" VARCHAR2(20),
        "RESOURCES" VARCHAR2(10),
        "WORKFID" VARCHAR2(30),
        "OPERPRI" NUMBER(3,0),
        "NETCELLIDERRCODE" VARCHAR2(20),
        "NETCELLIDRESULT" VARCHAR2(256),
        "NETCELLIDTIME" DATE,
        "REMARK" VARCHAR2(256),
        "RELATID" NUMBER(15,0),
        "STARTTIME" DATE,
         CONSTRAINT "PK_SP_LTE_LOG" PRIMARY KEY ("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)
TABLESPACE "ZW_DATA_01" ENABLE
   ) 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)
TABLESPACE "ZW_DATA_01"

GZZW@boss20dev >