RE: view re-creation

From: <Joel.Patterson_at_crowley.com>
Date: Fri, 17 Jul 2009 09:43:07 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA030C6D43_at_JAXMSG01.crowley.com>

Try this or combination therof p.s. longchunk is one of the secrets. Then execute your get_ddl  

SET LONG 1000000 SET LONGCHUNK 1000000   EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORA GE',false);

EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETT Y',true);

EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTE RMINATOR',true);

EXECUTE
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_C ONSTRAINTS',false);    

begin  

dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETT Y',TRUE);   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTE RMINATOR',TRUE);   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGME NT_ATTRIBUTES',TRUE);   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORA GE', TRUE);   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLE SPACE',TRUE);   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SPECI FICATION',TRUE);   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'BODY' ,TRUE);   dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONST RAINTS',TRUE); end;

/    

Joel Patterson
Database Administrator
904 727-2546

---

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ram Raman Sent: Thursday, July 16, 2009 7:38 PM
To: ORACLE-L
Subject: view re-creation  

Hi  

We are trying to create a few hunded views whose definitions are very big, longer than 2500 characters. We get the definitions from one database and create in a target database. Extracting the definition via dbms_metadata or selecting text from dba_views

gives an output that is like this:    

SELECT 999 , C.SETID , A.BUSINESS_UNIT , A.DEMAND_SOURCE , A.SOURCE_BUS_UNIT , A
.ORDER_NO , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NO , A.INV_ITEM_ID ,
A.DEMAND_LIN
E_NO , A.CUST_ID , A.SCHED_DATE , A.SCHED_TIME , A.QTY_REQUESTED , MAX(A.QTY_REQ
UESTED - A.QTY_BACKORDER) , MAX(A.QTY_REQUESTED_BASE - A.QTY_BACKORDER_BASE) , A
.QTY_BACKORDER_BASE , A.QTY_BACKORDER , A.UNIT_OF_MEASURE ,
A.PRIORITY_NBR , A.S
HIP_TO_CUST_ID , A.SHIP_CUST_NAME1 , A.LOCATION , A.PARTIAL_QTY_FLAG , A.BCKORDR
.....
   

The lines are cut at the end. The resulting DDL needs lots of editing and it is very time consuming editing the file which has 140,000 lines. Is there a way to extract the view definitions without this problem. I tried adjusting the linesize in sqlplus, but it did not help. Tested in both windows sqlplus and Unix env.  

Thanks.    

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Jul 17 2009 - 08:43:07 CDT