原创作品,出自 “深蓝的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