共计 6239 个字符,预计需要花费 16 分钟才能阅读完成。
使用 dbms_metadata.get_ddl() 函数可以做到。
实验环境:Oracle 11.2.0.4
以获取 jingyu 用户下的 T1 表为例:
SQL> conn jingyu/jingyu | |
Connected. | |
SQL> select count(1) from t1; | |
COUNT(1) | |
---------- | |
100 | |
SQL> select dbms_metadata.get_ddl('TABLE','T1','JINGYU') from dual; | |
DBMS_METADATA.GET_DDL('TABLE','T1','JINGYU') | |
-------------------------------------------------------------------------------- | |
CREATE TABLE "JINGYU"."T1" | |
("ID" NUMBER NOT NULL ENABLE, | |
"N" NUMBER, |
结果显示不全,设置一下 long 再查询:
SQL> set long 1000 | |
SQL> r | |
1* select dbms_metadata.get_ddl('TABLE','T1','JINGYU') from dual | |
DBMS_METADATA.GET_DDL('TABLE','T1','JINGYU') | |
-------------------------------------------------------------------------------- | |
CREATE TABLE "JINGYU"."T1" | |
("ID" NUMBER NOT NULL ENABLE, | |
"N" NUMBER, | |
"CONTENTS" VARCHAR2(4000) | |
) 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) | |
DBMS_METADATA.GET_DDL('TABLE','T1','JINGYU') | |
-------------------------------------------------------------------------------- | |
TABLESPACE "DBS_D_JINGYU" |
看着不舒服,再设置一下 pagesize:
SQL> set pagesize 0 | |
SQL> r | |
1* select dbms_metadata.get_ddl('TABLE','T1','JINGYU') from dual | |
CREATE TABLE "JINGYU"."T1" | |
("ID" NUMBER NOT NULL ENABLE, | |
"N" NUMBER, | |
"CONTENTS" VARCHAR2(4000) | |
) 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 "DBS_D_JINGYU" |
同样可以查询索引等对象的创建语句:
SQL> select dbms_metadata.get_ddl('INDEX','IDX_T1','JINGYU') from dual; | |
CREATE INDEX "JINGYU"."IDX_T1" ON "JINGYU"."T1" ("ID") | |
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 FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_D_JINGYU" |
分区表和分区索引,同样可以获取到:
create table t_part(id number, | |
name varchar2(20), | |
start_time date, | |
content varchar2(200) | |
)partition by range(start_time) | |
(partition P20150101 values less than (TO_DATE('2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) | |
tablespace dbs_d_jingyu, | |
partition P20150102 values less than (TO_DATE('2015-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) | |
tablespace dbs_d_jingyu, | |
partition P20150103 values less than (TO_DATE('2015-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) | |
tablespace dbs_d_jingyu | |
); | |
alter table t_part add constraint pk_t_part_id primary key(start_time, id) using index local tablespace dbs_i_jingyu; | |
create index idx_t_part on t_part(start_time, id, name) local tablespace dbs_i_jingyu; |
select dbms_metadata.get_ddl(‘TABLE’,’T_PART’,’JINGYU’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,’IDX_T_PART’,’JINGYU’) from dual;
select dbms_metadata.get_ddl(‘INDEX’,’PK_T_PART_ID’,’JINGYU’) from dual;
SQL> set long 10000 | |
SQL> select dbms_metadata.get_ddl('TABLE','T_PART','JINGYU') from dual; | |
CREATE TABLE "JINGYU"."T_PART" | |
("ID" NUMBER, | |
"NAME" VARCHAR2(20), | |
"START_TIME" DATE, | |
"CONTENT" VARCHAR2(200), | |
CONSTRAINT "PK_T_PART_ID" PRIMARY KEY ("START_TIME", "ID") | |
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" LOCAL | |
(PARTITION "P20150101" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" , | |
PARTITION "P20150102" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" , | |
PARTITION "P20150103" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" ) ENABLE | |
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_D_JINGYU" | |
PARTITION BY RANGE ("START_TIME") | |
(PARTITION "P20150101" VALUES LESS THAN (TO_DATE('2015-01-01 00:00:00', 'SYYY | |
Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED | |
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
NOCOMPRESS LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_D_JINGYU" , | |
PARTITION "P20150102" VALUES LESS THAN (TO_DATE('2015-01-02 00:00:00', 'SYYYY | |
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED | |
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
NOCOMPRESS LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_D_JINGYU" , | |
PARTITION "P20150103" VALUES LESS THAN (TO_DATE('2015-01-03 00:00:00', 'SYYYY | |
-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION DEFERRED | |
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 | |
NOCOMPRESS LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_D_JINGYU" ) | |
SQL> select dbms_metadata.get_ddl('INDEX','IDX_T_PART','JINGYU') from dual; | |
CREATE INDEX "JINGYU"."IDX_T_PART" ON "JINGYU"."T_PART" ("START_TIME", "ID", "NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255 | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" LOCAL | |
(PARTITION "P20150101" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" , | |
PARTITION "P20150102" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" , | |
PARTITION "P20150103" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" ) | |
SQL> select dbms_metadata.get_ddl('INDEX','PK_T_PART_ID','JINGYU') from dual; | |
CREATE UNIQUE INDEX "JINGYU"."PK_T_PART_ID" ON "JINGYU"."T_PART" ("START_TIME" | |
, "ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" LOCAL | |
(PARTITION "P20150101" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" , | |
PARTITION "P20150102" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" , | |
PARTITION "P20150103" | |
PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING | |
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) | |
TABLESPACE "DBS_I_JINGYU" ) |
获取到的是最完整的对象创建语句。
更多 Oracle 相关信息见 Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-04/143144.htm
正文完
星哥玩云-微信公众号
