Extract data from all tables of all schema into csv file using UTL_DIR

I was working on a metadata comparison project using UTL_DIR to write to file on  database server As having  knowledge on data dictionary I have manipulated dba_* tables to get information and extract it to csv files. I am providing below the list of database metadata object I used to get the information.

1.   DBA_USERS
2.   DBA_TABLES
3.   DBA_TAB_PRIVS
4.   DBA_ROLE_PRIVS
5.   DBA_TAB_COLUMNS
6.   DBA_INDEXES
7.   DBA_PART_TABLES
8.   DBA_VIEWS
9.   DBA_MVIEWS
10. DBA_SOURCE
11. DBA_SEQUENCES
12. DBA_SYNONYMS
13. DBA_LOBS
14. DBA_SCHEDULER_JOBS

For UTL_FILE there are 2 output handler, one for extracting the metadata and another to log the activity.

This extractor will extract all schema objects metadata which is ORACLE_MAINTAINED='N' (Applicable in 12c only).

The script in given below  This script will run on any Oracle 12c environment logged in as SYSTEM or SYS users.


---------------------------------------------------------------------------------------------------------------------------------

set serveroutput on;
declare
refcur_cur SYS_REFCURSOR;
type view_record_name_t is RECORD
(
OWNER dba_VIEWS.OWNER%type,
VIEW_NAME dba_VIEWS.VIEW_NAME%type,
TEXT_LENGTH dba_VIEWS.TEXT_LENGTH%type
);
all_users_record_name dba_users%rowtype;
view_record_name view_record_name_t;
table_record_name dba_tables%rowtype;
--column_record_name column_record_name_t;
column_record_name dba_tab_columns%rowtype;
index_record_name dba_indexes%rowtype;
partition_record_name dba_part_tables%rowtype;
mview_record_name dba_mviews%rowtype;
source_record_name dba_source%rowtype;
sequence_record_name dba_sequences%rowtype;
synonym_record_name dba_synonyms%rowtype;
lobs_record_name dba_lobs%rowtype;
jobs_record_name dba_scheduler_jobs%rowtype;
tab_privs_record_name dba_tab_privs%rowtype;
role_privs_record_name dba_role_privs%rowtype;
users_record_name dba_users%rowtype;
fhandle UTL_FILE.FILE_TYPE;
fhandle_err UTL_FILE.FILE_TYPE;
uname user_users.username%type;
systimes varchar2(15);
rowconut number;
serverHostName varchar2(100);
serverInstanceName varchar2(100);
begin

begin
 select max(username) into uname from user_users;
 EXCEPTION
 when OTHERS then
     uname := 'dummy';
end;

serverHostName:= sys_context('userenv','server_host');
serverInstanceName:= sys_context('userenv','instance_name') ;

fhandle_err := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_InfoAndErrors_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.log' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_users_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select *  FROM DBA_USERS WHERE ORACLE_MAINTAINED='N' order by USERNAME;
utl_file.put_line(fhandle_err,'Start extracting users from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'USERNAME~USER_ID~ACCOUNT_STATUS~LOCK_DATE~EXPIRY_DATE');       
        loop
            fetch refcur_cur into users_record_name;
            utl_file.put_line(fhandle,users_record_name.USERNAME||'~'||users_record_name.USER_ID||'~'||users_record_name.ACCOUNT_STATUS||'~'||users_record_name.LOCK_DATE||'~'||users_record_name.EXPIRY_DATE);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting users from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_tab_privs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select * from dba_tab_privs where GRANTEE in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1,2,3,5;
utl_file.put_line(fhandle_err,'Start extracting table_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'GRANTEE~OWNER~TABLE_NAME~GRANTOR~PRIVILEGE~GRANTABLE~HIERARCHY');       
        loop
            fetch refcur_cur into tab_privs_record_name;
            utl_file.put_line(fhandle,tab_privs_record_name.GRANTEE||'~'||tab_privs_record_name.OWNER||'~'||tab_privs_record_name.TABLE_NAME||'~'||tab_privs_record_name.GRANTOR||'~'||tab_privs_record_name.PRIVILEGE||'~'||tab_privs_record_name.GRANTABLE||'~'||tab_privs_record_name.HIERARCHY);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting table_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_role_privs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select * from dba_role_privs where GRANTEE in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1,2,3,4;
utl_file.put_line(fhandle_err,'Start extracting role_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'GRANTEE~GRANTED_ROLE~ADMIN_OPTION~DEFAULT_ROLE'
                         ) ;       
        loop
            fetch refcur_cur into role_privs_record_name;
            utl_file.put_line(fhandle,role_privs_record_name.GRANTEE||'~'||role_privs_record_name.GRANTED_ROLE||'~'||role_privs_record_name.ADMIN_OPTION||'~'||role_privs_record_name.DEFAULT_ROLE);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting role_privs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
            location =>  'UTL_DIR'     -- File location
            ,filename=> uname||'_tables_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
            ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       

open refcur_cur for select * from dba_tables where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1;
utl_file.put_line(fhandle_err,'Start extracting tables....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~TABLESPACE_NAME~STATUS~NUM_ROWS');       
        loop
            fetch refcur_cur into table_record_name;
            begin
                execute immediate 'select count(*) from '||table_record_name.OWNER||'.'||table_record_name.TABLE_NAME into rowconut;
                EXCEPTION
                    when others then
                    rowconut:=0;               
            end;
            utl_file.put_line(fhandle,table_record_name.OWNER||'~'||table_record_name.TABLE_NAME||'~'||table_record_name.TABLESPACE_NAME||'~'||table_record_name.STATUS||'~'||rowconut);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting tables from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

fhandle := utl_file.fopen(
            location =>  'UTL_DIR'     -- File location
            ,filename=> uname||'_tab_columns_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
            ,open_mode    => 'w', -- Open mode: w = write.
max_linesize => 32767 );       
--nvl(OWNER,' ') OWNER,nvl(TABLE_NAME,' ') TABLE_NAME,nvl(COLUMN_NAME,' ') COLUMN_NAME,nvl(DATA_TYPE,' ') DATA_TYPE,nvl(DATA_LENGTH,0) DATA_LENGTH,nvl(DATA_PRECISION,0) DATA_PRECISION,nvl(DATA_SCALE,0) DATA_SCALE,nvl(NULLABLE,' ') NULLABLE,nvl(COLUMN_ID,0) COLUMN_ID
open refcur_cur for select * from dba_tab_columns where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by table_name,column_id;
utl_file.put_line(fhandle_err,'Start extracting columns....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~COLUMN_NAME~DATA_TYPE~DATA_LENGTH~DATA_PRECISION~DATA_SCALE~NULLABLE~COLUMN_ID'
                         );       
        loop
            fetch refcur_cur into column_record_name;
            utl_file.put_line(fhandle,column_record_name.OWNER||'~'||column_record_name.TABLE_NAME||'~'||column_record_name.COLUMN_NAME||'~'||column_record_name.DATA_TYPE||'~'||column_record_name.DATA_LENGTH||'~'||column_record_name.DATA_PRECISION||'~'||column_record_name.DATA_SCALE||'~'||column_record_name.NULLABLE||'~'||column_record_name.COLUMN_ID);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting columns from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--elsif i.object_type='INDEX' then
        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_indexes_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',
                    max_linesize => 32767 -- Open mode: w = write.
                        );       
        open refcur_cur for
            select * from dba_indexes where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by TABLE_NAME,INDEX_NAME;
utl_file.put_line(fhandle_err,'Start extracting indexes....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~INDEX_NAME~INDEX_TYPE~TABLE_OWNER~TABLE_NAME~TABLE_TYPE~UNIQUENESS~COMPRESSION~TABLESPACE_NAME~DISTINCT_KEYS~STATUS~NUM_ROWS'
                         );       
        loop
            fetch refcur_cur into index_record_name;
            utl_file.put_line(fhandle,  index_record_name.OWNER||'~'||index_record_name.INDEX_NAME||'~'||index_record_name.INDEX_TYPE||'~'||index_record_name.TABLE_OWNER||'~'||index_record_name.TABLE_NAME||'~'||index_record_name.TABLE_TYPE||'~'||index_record_name.UNIQUENESS||'~'||index_record_name.COMPRESSION||'~'||index_record_name.TABLESPACE_NAME||'~'||index_record_name.DISTINCT_KEYS||'~'||index_record_name.STATUS||'~'||index_record_name.NUM_ROWS);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting indexes from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

    --elsif i.object_type='TABLE PARTITION' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_table_partitions_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_part_tables where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 1;

utl_file.put_line(fhandle_err,'Start extracting partitions from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~PARTITIONING_TYPE~SUBPARTITIONING_TYPE~PARTITION_COUNT~PARTITIONING_KEY_COUNT~STATUS~DEF_TABLESPACE_NAME'
                         );       
        loop
            fetch refcur_cur into partition_record_name;
            utl_file.put_line(fhandle,partition_record_name.OWNER||'~'||partition_record_name.TABLE_NAME||'~'||partition_record_name.SUBPARTITIONING_TYPE||'~'||partition_record_name.PARTITION_COUNT||'~'||partition_record_name.PARTITIONING_KEY_COUNT||'~'||partition_record_name.STATUS||'~'||partition_record_name.DEF_TABLESPACE_NAME);
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting partitions from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='VIEW' then
        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_views_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);
        open  refcur_cur for
            select nvl(OWNER,' ') OWNER,nvl(VIEW_NAME,' ') VIEW_NAME,nvl(TEXT_LENGTH,0) TEXT_LENGTH from dba_views  where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by view_name;

utl_file.put_line(fhandle_err,'Start extracting views from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~VIEW_NAME~TEXT_LENGTH');--,TEXT');       
        loop
            BEGIN
                fetch refcur_cur into view_record_name;
                utl_file.put_line(fhandle,view_record_name.OWNER||'~'||view_record_name.VIEW_NAME||'~'||view_record_name.TEXT_LENGTH);--||'~'||view_record_name.TEXT);       
                EXCEPTION
                    when OTHERS then
                    exit;
            END;
            exit when refcur_cur%notfound;
    -- Open mode: w = write.   
    end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting views from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='MATERIALIZED VIEW' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_mviews_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_mviews where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;
utl_file.put_line(fhandle_err,'Start extracting mviews from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~MVIEW_NAME~CONTAINER_NAME~QUERY~QUERY_LEN');       
        loop
            fetch refcur_cur into mview_record_name;
            utl_file.put_line(fhandle, mview_record_name.OWNER||'~'||mview_record_name.MVIEW_NAME||'~'||mview_record_name.CONTAINER_NAME||'~'||mview_record_name.QUERY||'~'||mview_record_name.QUERY_LEN);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting mviews from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--    elsif (i.object_type='FUNCTION'  or i.object_type='TYPE' or i.object_type='PACKAGE BODY' or i.object_type='PACKAGE' or i.object_type='TRIGGER') then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_types_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_source where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by TYPE,NAME,LINE;
utl_file.put_line(fhandle_err,'Start extracting types from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~NAME~TYPE~LINE~TEXT'
                         );       
        loop
            fetch refcur_cur into source_record_name;
            utl_file.put_line(fhandle,source_record_name.OWNER||'~'||source_record_name.NAME||'~'||source_record_name.TYPE||'~'||source_record_name.LINE||'~'||source_record_name.TEXT);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting types from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='SEQUENCE' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_sequences_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_sequences where sequence_owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting sequences from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'SEQUENCE_OWNER~SEQUENCE_NAME~MIN_VALUE~MAX_VALUE~INCREMENT_BY~CYCLE_FLAG~ORDER_FLAG~CACHE_SIZE~LAST_NUMBER');       
        loop
            fetch refcur_cur into sequence_record_name;
            utl_file.put_line(fhandle,sequence_record_name.SEQUENCE_OWNER||'~'||sequence_record_name.SEQUENCE_NAME||'~'||sequence_record_name.MIN_VALUE||'~'||sequence_record_name.MAX_VALUE||'~'||sequence_record_name.INCREMENT_BY||'~'||sequence_record_name.CYCLE_FLAG||'~'||sequence_record_name.ORDER_FLAG||'~'||sequence_record_name.CACHE_SIZE||'~'||sequence_record_name.LAST_NUMBER);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting sequences from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='SYNONYM' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_synonyms_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_synonyms where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;
utl_file.put_line(fhandle_err,'Start extracting synonyms from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~SYNONYM_NAME~TABLE_OWNER~TABLE_NAME~DB_LINK'
                         );       
        loop
            fetch refcur_cur into synonym_record_name;
            utl_file.put_line(fhandle,synonym_record_name.OWNER||'~'||synonym_record_name.SYNONYM_NAME||'~'||synonym_record_name.TABLE_OWNER||'~'||synonym_record_name.TABLE_NAME||'~'||synonym_record_name.DB_LINK);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting synonyms from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
    --elsif i.object_type='INDEX PARTITION' then
    --elsif i.object_type='LOB' then

        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_lobs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w',  -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_lobs where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting lobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~TABLE_NAME~COLUMN_NAME~SEGMENT_NAME~TABLESPACE_NAME~INDEX_NAME~CHUNK~ENCRYPT~FORMAT'
                         );       
        loop
            fetch refcur_cur into lobs_record_name;
            utl_file.put_line(fhandle,lobs_record_name.OWNER||'~'||lobs_record_name.TABLE_NAME||'~'||lobs_record_name.COLUMN_NAME||'~'||lobs_record_name.SEGMENT_NAME||'~'||lobs_record_name.TABLESPACE_NAME||'~'||lobs_record_name.INDEX_NAME||'~'||lobs_record_name.CHUNK||'~'||lobs_record_name.ENCRYPT||'~'||lobs_record_name.FORMAT);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting lobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));

    --elsif i.object_type='JOB' then
        fhandle := utl_file.fopen(
                    location =>  'UTL_DIR'     -- File location
                    ,filename=> uname||'_jobs_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.csv' -- File name
                    ,open_mode    => 'w', -- Open mode: w = write.
                    max_linesize => 32767);       
        open refcur_cur for
            select * from dba_scheduler_jobs where owner in (SELECT USERNAME FROM DBA_USERS WHERE ORACLE_MAINTAINED='N') order by 2;

utl_file.put_line(fhandle_err,'Start extracting jobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
        utl_file.put_line(fhandle,  'OWNER~JOB_NAME~JOB_STYLE~JOB_CREATOR~JOB_TYPE~JOB_ACTION~NUMBER_OF_ARGUMENTS~SCHEDULE_OWNER~SCHEDULE_NAME~SCHEDULE_TYPE~START_DATE~REPEAT_INTERVAL~END_DATE~ENABLED'
                         );       
        loop
            fetch refcur_cur into jobs_record_name;
            utl_file.put_line(fhandle,jobs_record_name.OWNER||'~'||jobs_record_name.JOB_NAME||'~'||jobs_record_name.JOB_STYLE||'~'||jobs_record_name.JOB_CREATOR||'~'||jobs_record_name.JOB_TYPE||'~'||jobs_record_name.JOB_ACTION||'~'||jobs_record_name.NUMBER_OF_ARGUMENTS||'~'||jobs_record_name.SCHEDULE_OWNER||'~'||jobs_record_name.SCHEDULE_NAME||'~'||jobs_record_name.SCHEDULE_TYPE||'~'||jobs_record_name.START_DATE||'~'||jobs_record_name.REPEAT_INTERVAL||'~'||jobs_record_name.END_DATE||'~'||jobs_record_name.ENABLED);       
            exit when refcur_cur%notfound;
        end loop;   
        utl_file.fclose(fhandle);
        close refcur_cur;
utl_file.put_line(fhandle_err,'Finish extracting jobs from '||serverHostName||'..'||serverInstanceName||' at....'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss'));
--    end if;
utl_file.fclose(fhandle_err);
--end loop;

EXCEPTION
 --  WHEN UTL_FILE.INVALID_PATH THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File location is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20000,'File location is invalid.');
   
 --  WHEN UTL_FILE.INVALID_MODE THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The open_mode parameter in FOPEN is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20001,'The open_mode parameter in FOPEN is invalid.');

 --  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File location is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20002,'File handle is invalid.');

 --  WHEN UTL_FILE.INVALID_OPERATION THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'File could not be opened or operated on as requested.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20003,'File could not be opened or operated on as requested.');

 --  WHEN UTL_FILE.READ_ERROR THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Operating system error occurred during the read operation.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20004,'Operating system error occurred during the read operation.');

 --  WHEN UTL_FILE.WRITE_ERROR THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Operating system error occurred during the write operation.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20005,'Operating system error occurred during the write operation.');

 --  WHEN UTL_FILE.INTERNAL_ERROR THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Unspecified PL/SQL error.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20006,'Unspecified PL/SQL error.');

 --  WHEN UTL_FILE.CHARSETMISMATCH THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'A file is opened using FOPEN_NCHAR~but later I/O ' ||
 --                                    'operations use nonchar functions such as PUTF or GET_LINE.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20007,'A file is opened using FOPEN_NCHAR~but later I/O ' ||
 --                                    'operations use nonchar functions such as PUTF or GET_LINE.');

 --  WHEN UTL_FILE.FILE_OPEN THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested operation failed because the file is open.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20008,'The requested operation failed because the file is open.');

 --  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
 --                                    'be within the range 1 to 32767.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20009,'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
 --                                    'be within the range 1 to 32767.');

 --  WHEN UTL_FILE.INVALID_FILENAME THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The filename parameter is invalid.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20010,'The filename parameter is invalid.');

 --  WHEN UTL_FILE.ACCESS_DENIED THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'Permission to access to the file location is denied.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20011,'Permission to access to the file location is denied.');

 --  WHEN UTL_FILE.INVALID_OFFSET THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
 --                                    'it should be greater than 0 and less than the total ' ||
 --                                    'number of bytes in the file.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20012,'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
 --                                    'it should be greater than 0 and less than the total ' ||
 --                                    'number of bytes in the file.');

 --  WHEN UTL_FILE.DELETE_FAILED THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested file delete operation failed.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20013,'The requested file delete operation failed.');

 --  WHEN UTL_FILE.RENAME_FAILED THEN
 --    utl_file.fclose(fhandle);
    -- utl_file.put_line(fhandle_err,'The requested file rename operation failed.');
    -- utl_file.fclose(fhandle_err);
 --    RAISE_APPLICATION_ERROR(-20014,'The requested file rename operation failed.');

  WHEN OTHERS THEN
    utl_file.fclose(fhandle);
    utl_file.fclose(fhandle_err);
    begin
        fhandle_err := utl_file.fopen(
                            location =>  'UTL_DIR'     -- File location
                            ,filename=> uname||'_InfoAndErrors_'||serverHostName||'_'||serverInstanceName||'_'||to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')||'.log' -- File name
                            ,open_mode    => 'a', -- Open mode: w = write.
        max_linesize => 32767 );       
            utl_file.put_line(fhandle_err,sqlcode||' - '||sqlerrm);
            utl_file.fclose(fhandle_err);
            EXCEPTION
                when others then
                utl_file.fclose(fhandle_err);
    end;
    RAISE;
end;
/