oracle中对象怎么创建,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;

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

获取到的是最完整的对象创建语句。