Oracle轻松取得建表和索引的DDL语句

set heading off;

set echo off;

Set pages 999;

set long 90000;

spool get_single.sql

select dbms_metadata.get_ddl('TABLE','SZT_PQSO2','SHQSYS') from dual;

select dbms_metadata.get_ddl('INDEX','INDXX_PQZJYW','SHQSYS') from dual;　

spool off; 

SQL> select dbms_metadata.get_ddl('TABLE','SZT_PQSO2','SHQSYS') from dual;　

CREATE TABLE "SHQSYS"."SZT_PQSO2" 

( "PQBH" VARCHAR2(32) NOT NULL ENABLE, 

"ZJYW" NUMBER(10,0), 

"CGSO" NUMBER(10,0) NOT NULL ENABLE, 

"SOLS" VARCHAR2(17), 

"SORQ" VARCHAR2(8), 

"SOWR" VARCHAR2(8), 

"SOCL" VARCHAR2(6), 

"YWHM" VARCHAR2(10), 

"YWLX" VARCHAR2(6) 

) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 

TABLESPACE "DATA1" 

SQL> select dbms_metadata.get_ddl

('INDEX','INDXX_PQZJYW','SHQSYS') from dual;

CREATE INDEX "SHQSYS"."INDXX_PQZJYW" ON "SHQSYS"."SZT_PQSO2" ("ZJYW") 

PCTFREE 10 INITRANS 2 MAXTRANS 255 

STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 

TABLESPACE "DATA1" 

SQL> 

SQL> spool off; 

set pagesize 0

set long 90000

set feedback off

set echo off 

spool get_schema.sql 

connect shqsys/shqsys@hawk1;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)

FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)

FROM USER_INDEXES u;

spool off;