set echo off
set feedback off
set heading off
set pagesize 0

set long 160000
set linesize 1000
set trimspool on
set termout off

-- Ensure clean output and suppress storage parameters.
begin
  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, 'SEGMENT_ATTRIBUTES', false);
end;
/

-- We need to format the output clob column otherwise it will simply wrap at 80 chars no 
-- matter what the linesize is set to.

column ddl_output format a1000

spool tables.txt

select dbms_metadata.get_ddl('TABLE', tab.table_name, user) as ddl_output
  from all_tables tab
 where owner = user
    -- We don't want Index Overflow Tables.
   and tab.table_name not like 'SYS_IOT%'
 order by tab.table_name;

spool sequences.txt

select dbms_metadata.get_ddl('SEQUENCE', objs.object_name, user) as ddl_output
  from all_objects objs
 where object_type = 'SEQUENCE'
   and objs.owner = user
 order by objs.object_name;

spool triggers.txt

select dbms_metadata.get_ddl('TRIGGER', objs.object_name, user) as ddl_output
  from all_objects objs
 where object_type = 'TRIGGER'
   and objs.owner = user
    -- We don't want invalid left-over triggers.
   and objs.object_name not like 'BIN$%'
 order by objs.object_name;

spool views.txt

select dbms_metadata.get_ddl('VIEW', objs.object_name, user) as ddl_output
  from all_objects objs
 where object_type = 'VIEW'
   and objs.owner = user
 order by objs.object_name;

spool packages.txt

select dbms_metadata.get_ddl('PACKAGE', objs.object_name, user) as ddl_output
  from all_objects objs
 where object_type = 'PACKAGE'
   and objs.owner = user
 order by objs.object_name;

spool types.txt

select dbms_metadata.get_ddl('TYPE', objs.object_name, user) as ddl_output
  from all_objects objs
 where object_type = 'TYPE'
   and objs.owner = user
    -- Exclude system-generated PL/SQL types internal to packages.
   and objs.object_name not like 'SYS_PLSQL_%'
 order by objs.object_name;

spool off

exit