ExportDDL4View.sql

/* 导出登陆用户的视图
 * 潘欣磊  2014-04
 */
SET ECHO OFF
SET TRIMSPOOL ON
SET VERIFY OFF
SET FEEDBACK OFF
SET FEED OFF
SET TIMING OFF
SET LINESIZE 4000
SET PAGESIZE 1000
SET LONG 90000
SET NEWPAGE NONE
SET HEADING OFF
SET TERMOUT OFF
SET WRAP ON

BEGIN 
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',TRUE);  
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',TRUE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'CONSTRAINTS',FALSE);
END;
/

COL DDL_STR    FORMAT A30000

SPOOL './DDL4View.sql';
     SELECT RPAD('-', 40, '-' )
||CHR(10)|| '-- File created on '||TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS')||' .'
||CHR(10)|| '-- Total export '||COUNT(DISTINCT VIEW_NAME)||' view(s).'
||CHR(10)|| RPAD('-', 40, '-' )
       FROM USER_VIEWS
;
     SELECT 'set sqlblanklines on'
||CHR(10)|| 'set define off' 
       FROM DUAL
; 
WITH V AS (
    SELECT VIEW_NAME
      FROM USER_VIEWS 
) 
     SELECT TO_CLOB(RPAD('-', LENGTH(V.VIEW_NAME)+18, '-' )) 
||CHR(10)|| TO_CLOB('---- View : ' || V.VIEW_NAME)
||CHR(10)|| TO_CLOB(RPAD('-', LENGTH(V.VIEW_NAME)+18, '-' ))
||CHR(10)|| REPLACE(DBMS_METADATA.GET_DDL( OBJECT_TYPE => 'VIEW'
                                 , NAME => V.VIEW_NAME
                                 , SCHEMA => USER
                                 )
                    , '"'||USER||'".'
                    ,''
                   )    AS  DDL_STR
       FROM V
;
     SELECT 'set define on'
||CHR(10)|| 'quit;' 
       FROM DUAL
; 
SPOOL OFF;
QUIT;