Prevent SPOOL output from completing
I am trying to generate all DDL for objects in a database using the SPOOL command in SQLPLUS:
SET trimspool ON
SET wrap off
SET heading off
SET linesize 300
SET echo off
SET pages 999
SET long 90000
Col object_type format a10000
Col object_name format a10000
Col owner format a10000
spool export.out
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)
FROM all_OBJECTS
WHERE OWNER = 'DMALM'
and object_type not like '%PARTITION'
and object_type not like '%BODY'
and object_type not like '%LOB';
spool off
quit
But the output file I get is truncated at column # 80. How can I prevent the output file from completing?
source to share
How about using word_wrapped?
SET trimspool ON
SET heading off
SET linesize 300
SET echo off
SET pages 999
SET long 90000
set termout off
column txt format a121 word_wrapped
Col object_type format a10000
Col object_name format a10000
Col owner format a10000
spool export.out
SELECT DBMS_METADATA.GET_DDL(object_type, object_name, owner)txt
FROM all_OBJECTS
WHERE OWNER = 'DMALM'
and object_type not like '%PARTITION'
and object_type not like '%BODY'
and object_type not like '%LOB';
spool off
quit
source to share
You also need to do:
SET longchunksize 90000
As the documentation says :
The default column width for data types is the width of the column in the database. Column width
LONG
,BLOB
,BFILE
,CLOB
,NCLOB
or aXMLType
defaultXMLType
valueSET LONGCHUNKSIZE
orSET LONG
, whichever is less.
You are already setting LONG
, but the LONGCHUNKSIZE
default is still 80, so you need to increase it to fit. You can see all your current settings from show all
.
This preserves the default line breaks and indentation .
source to share
It looks like you can try:
set longchunksize 100
or equivalent. Experiment with number see if it helps.
Source Oracle Docs
source to share