SQL查询以查找oracle中表的DDL脚本
SQL查询以查找oracle中表的DDL脚本
提问于 2014-08-21 09:26:36
我有以下脚本来为表生成DDL脚本：
select dbms_metadata.get_ddl('TABLE','TRADE','dev_schema') from dual;产出：
CREATE TABLE "BCN_QA"."EXM_MESSAGE"
( "MESSAGE_PK" NUMBER(10,0),
"XML_MESSAGE" "SYS"."XMLTYPE" ,
"MESSAGE_TYPE" VARCHAR2(30),
"ERROR_CODE" VARCHAR2(1000),
"ERROR_DESCRIPTION" VARCHAR2(4000),
"MESSAGE_CREATION_DATE" VARCHAR2(14),
"INSTRUMENT_PK" NUMBER(10,0),
"SECURITY_CODE" VARCHAR2(36),
"ACCOUNT_PK" NUMBER(10,0),
"ACCOUNT_NO" VARCHAR2(46),
"SENDER_SYSTEM_ID" VARCHAR2(10),
"RECIPIENT_COMPONENT" VARCHAR2(3),
"REFERENCE_NUMBER" VARCHAR2(35),
"ERROR_NUMBER" NUMBER(4,0),
"REMARKS" VARCHAR2(400),
"STATUS" VARCHAR2(6),
"ACTION_TAKEN" VARCHAR2(10),
"USER_COMMENT" VARCHAR2(200),
"USER_COMMENT_ENTERY_BY" VARCHAR2(20),
"CANCEL_PK" NUMBER(10,0),
"GROUP_PK" NUMBER(10,0),
"DATA_SOURCE" VARCHAR2(20),
"APP_REGI_DATE" DATE,
"APP_UPD_DATE" DATE,
"CREATED_BY" VARCHAR2(20),
"CREATION_DATE" DATE,
"UPDATED_BY" VARCHAR2(20),
"UPDATE_DATE" DATE,
"SENDER_REFERENCE_NO" VARCHAR2(20),
"SENDER_BIC" VARCHAR2(15),
"NACK_OUT_FLAG" CHAR(1),
"NACK_ELIGIBLE_FLAG" CHAR(1),
"VALUE_DATE" DATE,
"QUALIFIER_STATUS" VARCHAR2(4),
"MESSAGE_STATUS" VARCHAR2(6),
"ORIGINAL_REF_NO" VARCHAR2(35),
"LOCAL_ACCOUNT_NO" VARCHAR2(20),
"MSG_PRIORITY" NUMBER(2,0) DEFAULT 4 CONSTRAINT "NN_6055_EXM_MESSAGE" NOT NULL ENABLE,
CONSTRAINT "PK_625_EXM_MESSAGE" PRIMARY KEY ("MESSAGE_PK")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 327680 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "BCN_QA" ENABLE,
CONSTRAINT "FK_1980_EXM_MESSAGE" FOREIGN KEY ("GROUP_PK")
REFERENCES "BCN_QA"."EXM_GROUP" ("GROUP_PK") ENABLE,
CONSTRAINT "FK_1979_EXM_MESSAGE" FOREIGN KEY ("CANCEL_PK")
REFERENCES "BCN_QA"."EXM_MESSAGE" ("MESSAGE_PK") ENABLE,
CONSTRAINT "FK_1976_EXM_MESSAGE" FOREIGN KEY ("ACCOUNT_PK")
REFERENCES "BCN_QA"."REF_ACCOUNT" ("ACCOUNT_PK") ENABLE,
CONSTRAINT "FK_1977_EXM_MESSAGE" FOREIGN KEY ("RECIPIENT_COMPONENT")
REFERENCES "BCN_QA"."REF_COMPONENT" ("COMPONENT_ID") ENABLE,
CONSTRAINT "FK_1975_EXM_MESSAGE" FOREIGN KEY ("INSTRUMENT_PK")
REFERENCES "BCN_QA"."REF_INSTRUMENT" ("INSTRUMENT_PK") ENABLE,
CONSTRAINT "FK_1987_EXM_MESSAGE" FOREIGN KEY ("MESSAGE_TYPE")
REFERENCES "BCN_QA"."REF_MESSAGE_TYPE" ("MESSAGE_TYPE") 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 "BCN_QA"
XMLTYPE COLUMN "XML_MESSAGE" STORE AS CLOB (
TABLESPACE "BCN_QA" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;但我想要这样的结果：
CREATE TABLE "BCN_QA"."EXM_MESSAGE"
( "MESSAGE_PK" NUMBER(10,0),
"XML_MESSAGE" "SYS"."XMLTYPE" ,
"MESSAGE_TYPE" VARCHAR2(30 BYTE),
"ERROR_CODE" VARCHAR2(1000 BYTE),
"ERROR_DESCRIPTION" VARCHAR2(4000 BYTE),
"MESSAGE_CREATION_DATE" VARCHAR2(14 BYTE),
"INSTRUMENT_PK" NUMBER(10,0),
"SECURITY_CODE" VARCHAR2(36 BYTE),
"ACCOUNT_PK" NUMBER(10,0),
"ACCOUNT_NO" VARCHAR2(46 BYTE),
"SENDER_SYSTEM_ID" VARCHAR2(10 BYTE),
"RECIPIENT_COMPONENT" VARCHAR2(3 BYTE),
"REFERENCE_NUMBER" VARCHAR2(35 BYTE),
"ERROR_NUMBER" NUMBER(4,0),
"REMARKS" VARCHAR2(400 BYTE),
"STATUS" VARCHAR2(6 BYTE),
"ACTION_TAKEN" VARCHAR2(10 BYTE),
"USER_COMMENT" VARCHAR2(200 BYTE),
"USER_COMMENT_ENTERY_BY" VARCHAR2(20 BYTE),
"CANCEL_PK" NUMBER(10,0),
"GROUP_PK" NUMBER(10,0),
"DATA_SOURCE" VARCHAR2(20 BYTE),
"APP_REGI_DATE" DATE,
"APP_UPD_DATE" DATE,
"CREATED_BY" VARCHAR2(20 BYTE),
"CREATION_DATE" DATE,
"UPDATED_BY" VARCHAR2(20 BYTE),
"UPDATE_DATE" DATE,
"SENDER_REFERENCE_NO" VARCHAR2(20 BYTE),
"SENDER_BIC" VARCHAR2(15 BYTE),
"NACK_OUT_FLAG" CHAR(1 BYTE),
"NACK_ELIGIBLE_FLAG" CHAR(1 BYTE),
"VALUE_DATE" DATE,
"QUALIFIER_STATUS" VARCHAR2(4 BYTE),
"MESSAGE_STATUS" VARCHAR2(6 BYTE),
"ORIGINAL_REF_NO" VARCHAR2(35 BYTE),
"LOCAL_ACCOUNT_NO" VARCHAR2(20 BYTE),
"MSG_PRIORITY" NUMBER(2,0);即没有储存
有人能为这个写一个查询吗？
回答 1
Stack Overflow用户
回答已采纳
发布于 2014-08-21 09:36:33
您需要运行以下命令
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE','FALSE');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE','FALSE');
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES','FALSE');然后你就可以跑了
select dbms_metadata.get_ddl('TABLE','TRADE','dev_schema') from dual;这只会给您提供create语句。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/25422768
复制相关文章
相似问题
腾讯云开发者
