Oracle获取数据库中的对象创建语句

使用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;