Oracle gets the object creation statement in the database

Use the DBMS_METADATA.GET_DDL () function to do this.

Lab Environment: Oracle 11.2.0.4
To get the T1 table under the Jingyu user, for example:

SQL> conn jingyu/jingyuConnected.SQL> select count(1) from t1;  COUNT(1)----------       100SQL> 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,

The result is not complete, set the long again query:

SQL> set long 1000SQL> r  1* select dbms_metadata.get_ddl(‘TABLE‘,‘T1‘,‘JINGYU‘) from dualDBMS_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"

Look uncomfortable, then set the pagesize:

SQL> set pagesize 0SQL> 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"

You can also query the creation statements for objects such as indexes:

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"

Partitioned tables and partitioned indexes can also be obtained by:

  CREATE TABLE T_part (ID number, name VARCHAR2 (a), start_time date, content Varchar2) partition by range (ST Art_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 (' 20 15-01-03 00:00:00 ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian '), tablespace Dbs_d_jingyu); ALTER TABLE T_part AD  D 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 10000sql> Select DBMS_METADATA.GET_DDL (' TABLE ', ' t_part ', ' Jingyu ') from dual; CREATE TABLE "Jingyu". "         T_part "(" ID "number," NAME "VARCHAR2 ()," Start_time "DATE," CONTENT "VARCHAR2 (200), CONSTRAINT "pk_t_part_id" PRIMARY KEY ("Start_time", "ID") USING INDEX PCTFREE Initrans 2 Maxtrans 255 STORAGE (BUFF Er_pool default Flash_cache default Cell_flash_cache default) tablespace "Dbs_i_jingyu" LOCAL (PARTITION "P20150101" PC Tfree Initrans 2 Maxtrans 255 LOGGING STORAGE (buffer_pool default flash_cache default Cell_flash_cache default) TAB Lespace "Dbs_i_jingyu", PARTITION ' P20150102 "PCTFREE Initrans 2 Maxtrans 255 LOGGING STORAGE (Buffer_pool DEFAULT Flash_cache default Cell_flash_cache default) tablespace "Dbs_i_jingyu", PARTITION "P20150103" PCTFREE 2 MA  Xtrans 255 LOGGING STORAGE (buffer_pool default flash_cache default Cell_flash_cache default) tablespace "Dbs_i_jingyu" ) ENABLE) PCTFREE 10 pctused Initrans 1 Maxtrans 255 STORAGE (buffer_pool default flash_cache default Cell_flash_cache default) Tablespa CE "Dbs_d_jingyu" PARTITION by RANGE ("Start_time") (PARTITION "P20150101" VALUES Less THAN (to_date (' 2015-01-01 00:00: xx ', ' syyyy-mm-dd HH24:MI:SS ', ' Nls_calendar=gregorian ')) SEGMENT CREATION DEFERRED PCTFREE pctused Initrans 1 maxt RANS 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_calend Ar=gregorian ') SEGMENT CREATION DEFERRED PCTFREE pctused + initrans 1 Maxtrans 255 nocompress LOGGING STORAGE (BUF Fer_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 pctused INitrans 1 Maxtrans 255 nocompress LOGGING STORAGE (buffer_pool default flash_cache default Cell_flash_cache default) TA  Blespace "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 Initrans 2 Maxtrans 255 STORAGE (buffer_pool DEFAULT flash_cache DEFA  ULT cell_flash_cache DEFAULT) tablespace "Dbs_i_jingyu" LOCAL (PARTITION "P20150101" PCTFREE Initrans 2 Maxtrans 255 LOGGING STORAGE (buffer_pool default flash_cache default Cell_flash_cache default) tablespace "Dbs_i_jingyu", Partiti On "P20150102" PCTFREE Initrans 2 Maxtrans 255 LOGGING STORAGE (buffer_pool default Flash_cache default Cell_flash_c ACHE DEFAULT) tablespace "Dbs_i_jingyu", PARTITION P20150103 "PCTFREE Initrans 2 Maxtrans 255 LOGGING STORAGE (BU Ffer_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 Initrans 2 Maxtrans 255 STORAGE (buffer_pool default Flash_cache default CELL   _flash_cache DEFAULT) tablespace "Dbs_i_jingyu" LOCAL (PARTITION "P20150101" PCTFREE Initrans 2 Maxtrans 255 LOGGING STORAGE (buffer_pool default flash_cache default Cell_flash_cache default) tablespace "Dbs_i_jingyu", PARTITION "P201 50102 "PCTFREE Initrans 2 Maxtrans 255 LOGGING STORAGE (buffer_pool default Flash_cache default Cell_flash_cache DEF Ault) tablespace "Dbs_i_jingyu", PARTITION P20150103 "PCTFREE Initrans 2 Maxtrans 255 LOGGING STORAGE (Buffer_poo L default Flash_cache default Cell_flash_cache default) tablespace "Dbs_i_jingyu")

Gets to the most complete object creation statement.

Oracle gets the object creation statement in the database