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 indexes.txt

select dbms_metadata.get_ddl('INDEX', objs.object_name, user)
as ddl_output
  from all_objects objs
 where object_type = 'INDEX'
   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 grants.txt

select
    'GRANT ' || privilege || ' ON ' || table_name || ' TO ' || grantee ||
    case
      when grantable = 'YES' then ' WITH GRANT OPTION'
      else ''
    end || ';' as grant_statement
  from user_tab_privs_made
    -- Exclude objects moved to the recycle bin.
 where table_name not like 'BIN$%'
 order by table_name, grantee,
          decode(privilege, 'SELECT', 1, 'INSERT', 2, 'UPDATE', 3, 'DELETE', 4, 5);

spool off

exit