SQL_使用DBMS_METADATA.GET_DDL生成建表语句

原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明以下出处，否则追究版权法律责任。

深蓝的blog：http://blog.csdn.net/huangyanlong/article/details/43988377

今天在群里学来一招，通过DBMS_METADATA.GET_DDL来生成建表或视图的sql语句。

（1）、生成创建表的sql语句
SQL> set long 20000;
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','TEMP','HYL') code FROM DUAL;
--注意三个参数都需要大写

CODE
--------------------------------------------------------------------------------
  CREATE TABLE "HYL"."TEMP"
   ( "EMPLOYEE_ID" NUMBER,
 "EMPLOYEE_NAME" VARCHAR2(20),
 "EMPLOYEE_POSITION" VARCHAR2(20)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "HYL_SYSTEM"

（2）、生成创建视图的sql语句
再来做个视图的实验
SQL> select * from user_users
2 ;

USERNAME                          USER_ID ACCOUNT_STATUS                   LOCK_DATE   EXPIRY_DATE DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE           CREATED     INITIAL_RSRC_CONSUMER_GROUP    EXTERNAL_NAME
------------------------------ ---------- -------------------------------- ----------- ----------- ------------------------------ ------------------------------ ----------- ------------------------------ --------------------------------------------------------------------------------
SYS                                     0 OPEN                                         2015/4/20 2 SYSTEM                         TEMP                           2010/3/30 1 SYS_GROUP

SQL> SET LONG 20000;
SQL> SELECT DBMS_METADATA.GET_DDL('VIEW','USER_USERS','SYS') code FROM DUAL;
--注意三个参数都需要大写，表示sys用户下，叫做“USER_USERS”的视图

CODE
--------------------------------------------------------------------------------
  CREATE OR REPLACE FORCE VIEW "SYS"."USER_USERS" ("USERNAME", "USER_ID", "ACCOU
  select u.name, u.user#,
       m.status,
       decode(u.astatus, 4, u.ltime,
                         5, u.ltime,
                         6, u.ltime,
                         8, u.ltime,
                         9, u.ltime,
                         10, u.ltime, to_date(NULL)),
       decode(u.astatus,
              1, u.exptime,
              2, u.exptime,
              5, u.exptime,
              6, u.exptime,
              9, u.exptime,
              10, u.exptime,
              decode(u.ptime, '', to_date(NULL),
                decode(p.limit#, 2147483647, to_date(NULL),
                 decode(p.limit#, 0,
                   decode(dp.limit#, 2147483647, to_date(NULL), u.ptime +
                     dp.limit#/86400),
                   u.ptime + p.limit#/86400)))),
       dts.name, tts.name, u.ctime,
       nvl(cgm.consumer_group, 'DEFAULT_CONSUMER_GROUP'),
       u.ext_username
from sys.user$ u left outer join sys.resource_group_mapping$ cgm
     on (cgm.attribute = 'ORACLE_USER' and cgm.status = 'ACTIVE' and
         cgm.value = u.name),
     sys.ts$ dts, sys.ts$ tts, sys.user_astatus_map m,
     profile$ p, profile$ dp
where u.datats# = dts.ts#
  and u.tempts# = tts.ts#
  and u.astatus = m.status#
  and u.type# = 1
  and u.user# = userenv('SCHEMAID')
  and u.resource$ = p.profile#
  and dp.profile# = 0
  and dp.type# = 1
  and dp.resource# = 1
  and p.type# = 1
  and p.resource# = 1