DDL查询

 1, 查询表的DDL, 并导出到c:/table.sql
SQL> set pagesize 0
SQL> set long 2000
SQL> set feedback off
SQL> set echo off
SQL> spool c:/table.sql
SQL> select dbms_metadata.get_ddl('TABLE', u.table_name)
  2  from user_tables u;

  CREATE TABLE "SEASAIL"."TEST"
   (    "A" NUMBER,
        "AGE" NUMBER
   ) 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)
  TABLESPACE "DB10G" ENABLE ROW MOVEMENT

  CREATE TABLE "SEASAIL"."T"
   (    "V" NUMBER
   ) 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)
  TABLESPACE "DB10G"

SQL> spool off

2, 查询索引
SQL> select dbms_metadata.get_ddl('INDEX', u.index_name)
  2  from user_indexes u;

  CREATE UNIQUE INDEX "SEASAIL"."SYS_IL0000052634C00003$$" ON "SEASAIL"."IMAGE"
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"
  PARALLEL (DEGREE 0 INSTANCES 0)

3, 查询存储过程
SQL> select dbms_metadata.get_ddl('PROCEDURE', u.object_name)
  2  from user_objects u
  3  where object_type='PROCEDURE';

  CREATE OR REPLACE PROCEDURE "SEASAIL"."PROC1"
as
begin
for i in 1 .. 10000
loop
execute immediate
'insert into t values(:x)' using i;
end loop;
end;
存储过程DDL还有另一个方式查询,但不完整:
SQL> select text from user_source where name='PROC1';
procedure proc1
as
begin
for i in 1 .. 10000
loop
execute immediate
'insert into t values(:x)' using i;
end loop;
end;

还有一种查询的方式:
select dbms_metadata.get_ddl('Objec_type', 'object_name', 'schema') from dual;
                               对象类型        对象名      用户名/方案名
通过下面的语句查询seasail方案下, 名为TEST的TABLE:
SQL> select dbms_metadata.get_ddl('TABLE','TEST','SEASAIL') from dual;
  CREATE TABLE "SEASAIL"."TEST"
   (    "A" NUMBER,
        "AGE" NUMBER
   ) 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)
  TABLESPACE "DB10G" ENABLE ROW MOVEMENT