oracle 导出建表语句。

时间:2021-07-15 23:16:35

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

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


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 >