使用 dbms_metadata.get_ddl() 函数可以做到。
实验环境:Oracle 11.2.0.4 以获取 jingyu 用户下的 T1 表为例:
- SQL> conn jingyu/jingyu
- Connected.
- SQL>select count(1)fromt1;
- COUNT(1)
- ----------100SQL>selectdbms_metadata.get_ddl('TABLE','T1','JINGYU')fromdual;
- DBMS_METADATA.GET_DDL('TABLE','T1','JINGYU')
- --------------------------------------------------------------------------------
- CREATE TABLE"JINGYU"."T1"("ID"NUMBER NOT NULL ENABLE,"N"NUMBER,
结果显示不全,设置一下 long 再查询:
- SQL> set long1000SQL> r1* select dbms_metadata.get_ddl('TABLE','T1','JINGYU') from dual
- DBMS_METADATA.GET_DDL('TABLE','T1','JINGYU')
- --------------------------------------------------------------------------------
- CREATE TABLE"JINGYU"."T1"("ID"NUMBER NOTNULLENABLE,"N"NUMBER,"CONTENTS"VARCHAR2(4000)
- ) SEGMENT CREATION IMMEDIATE
- PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESS LOGGING
- STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELIST GROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- DBMS_METADATA.GET_DDL('TABLE','T1','JINGYU')
- --------------------------------------------------------------------------------
- TABLESPACE"DBS_D_JINGYU"
看着不舒服,再设置一下 pagesize:
- SQL>setpagesize0SQL> r1*selectdbms_metadata.get_ddl('TABLE','T1','JINGYU') from dualCREATE TABLE"JINGYU"."T1"("ID"NUMBERNOTNULL ENABLE,"N"NUMBER,"CONTENTS"VARCHAR2(4000)
- ) SEGMENT CREATION IMMEDIATE
- PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESS LOGGING
- STORAGE(INITIAL65536 NEXT 1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELIST GROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_D_JINGYU"
同样可以查询索引等对象的创建语句:
- SQL>selectdbms_metadata.get_ddl('INDEX','IDX_T1','JINGYU') from dual;CREATE INDEX"JINGYU"."IDX_T1" ON "JINGYU"."T1"("ID")
- PCTFREE10INITRANS2MAXTRANS255COMPUTE STATISTICS
- STORAGE(INITIAL65536 NEXT 1048576MINEXTENTS1MAXEXTENTS2147483645PCTINCREASE0FREELISTS1FREELIST GROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_D_JINGYU"
分区表和分区索引,同样可以获取到:
- create tablet_part(id number,namevarchar2(20),
- start_timedate,contentvarchar2(200)
- )partition by range(start_time)
- (partitionP20150101values less than(TO_DATE(' 2015-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))tablespacedbs_d_jingyu,partitionP20150102values less than(TO_DATE(' 2015-01-02 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))tablespacedbs_d_jingyu,partitionP20150103values less than(TO_DATE(' 2015-01-03 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))tablespacedbs_d_jingyu
- );alter tablet_partadd constraintpk_t_part_id primarykey(start_time,id)using index local tablespacedbs_i_jingyu;create indexidx_t_partont_part(start_time,id,name)local tablespacedbs_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 long10000SQL> 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 PCTFREE10INITRANS2MAXTRANS255STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU"LOCAL
- (PARTITION"P20150101"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU",
- PARTITION"P20150102"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU",
- PARTITION"P20150103"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU") ENABLE
- ) PCTFREE10PCTUSED40INITRANS1MAXTRANS255STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- 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
- PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESS LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- 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
- PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESS LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- 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
- PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESS LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- 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") PCTFREE10INITRANS2MAXTRANS255STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU"LOCAL
- (PARTITION"P20150101"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU",
- PARTITION"P20150102"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU",
- PARTITION"P20150103"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- 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") PCTFREE10INITRANS2MAXTRANS255STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU"LOCAL
- (PARTITION"P20150101"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU",
- PARTITION"P20150102"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU",
- PARTITION"P20150103"PCTFREE10INITRANS2MAXTRANS255LOGGING
- STORAGE(
- BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CACHEDEFAULT)
- TABLESPACE"DBS_I_JINGYU")
获取到的是最完整的对象创建语句。
来源: http://www.linuxidc.com/Linux/2017-04/143145.htm