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

383次阅读

共计 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" )