批量獲取表定義的方法

SQL> conn an/an
已連線。
SQL> create table ddl_scripts(
  2  object_name varchar2(50),
  3  object_type varchar2(30),
  4  text varchar2(4000));

表已建立。

SQL> select table_name from user_tables;

TABLE_NAME
------------------------------
DDL_SCRIPTS
AN

SQL> declare
  2  a1 varchar2(32767);
  3  str varchar2(32767);
  4  n1 number;
  5  BEGIN
  6  for c1 IN(select table_name from user_tables order by 1) LOOP
  7          select DBMS_LOB.SUBSTR(dbms_metadata.get_ddl('TABLE', c1.table_name), 32767,1) INTO a1 from dual;
  8          insert into ddl_scripts values(c1.table_name, 'TABLE', a1);
  9  END LOOP;
 10  END;
 11  /

PL/SQL 過程已成功完成。

SQL> SELECT TEXT FROM DDL_SCRIPTS;

TEXT
--------------------------------------------------------------------------------

  CREATE TABLE "AN"."AN"
   (    "ID" NUMBER(*,0),
        "NAME" VARCHAR2(2)
   ) 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 DE
FAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"

TEXT
--------------------------------------------------------------------------------

  CREATE TABLE "AN"."DDL_SCRIPTS"
   (    "OBJECT_NAME" VARCHAR2(50),
        "OBJECT_TYPE" VARCHAR2(30),
        "TEXT" 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 DE
FAULT CELL_FLASH_CACHE DEFAULT)

TEXT
--------------------------------------------------------------------------------
  TABLESPACE "USERS"

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/13177610/viewspace-676352/，如需轉載，請註明出處，否則將追究法律責任。