CONN ASTDBA10G/MAREA@PROAPP1;

CREATE OR REPLACE PACKAGE ASTDBA10G.PKG_DOC_VISTAS IS
   PROCEDURE PROC_EXEC(p_directorio varchar2);
   PROCEDURE PROC_VDATABASE(p_directorio varchar2);
   PROCEDURE PROC_VINSTANCE(p_directorio varchar2);
   PROCEDURE PROC_VPARAMETER(p_directorio varchar2);
   PROCEDURE PROC_DBAUSERS(p_directorio varchar2);
   PROCEDURE PROC_VLOG(p_directorio varchar2);
   PROCEDURE PROC_VLOGFILE(p_directorio varchar2);
   PROCEDURE PROC_VLOGHISTORY(p_directorio varchar2);
   PROCEDURE PROC_VARCHIVEDEST(p_directorio varchar2);
   PROCEDURE PROC_VARCHIVEDESTSTATUS(p_directorio varchar2);
   PROCEDURE PROC_VARCHIVEPROCESSES(p_directorio varchar2);
   PROCEDURE PROC_VARCHIVEDLOG(p_directorio varchar2);
   PROCEDURE PROC_VCONTROLFILE(p_directorio varchar2);
   PROCEDURE PROC_VDATAFILE(p_directorio varchar2);
   PROCEDURE PROC_VDATAFILEHEADER(p_directorio varchar2);
   PROCEDURE PROC_DBADATAFILES(p_directorio varchar2);
   PROCEDURE PROC_DBATABLESPACES(p_directorio varchar2);
   PROCEDURE PROC_DBATABLES(p_directorio varchar2);
   PROCEDURE PROC_DBATABCOLUMNS(p_directorio varchar2);
   PROCEDURE PROC_DBAINDEXES(p_directorio varchar2);
   PROCEDURE PROC_DBAINDCOLUMNS(p_directorio varchar2);
   PROCEDURE PROC_DBACONSTRAINTS(p_directorio varchar2);
   PROCEDURE PROC_DBACONSCOLUMNS(p_directorio varchar2);
END PKG_DOC_VISTAS;
/

CREATE OR REPLACE PACKAGE BODY "PKG_DOC_VISTAS" IS
   PROCEDURE PROC_EXEC(p_directorio varchar2) IS
   begin
       PROC_VDATABASE(p_directorio);
       PROC_VINSTANCE(p_directorio);
       PROC_VPARAMETER(p_directorio);
       PROC_DBAUSERS(p_directorio);
       PROC_VLOG(p_directorio);
       PROC_VLOGFILE(p_directorio);
       PROC_VLOGHISTORY(p_directorio);
       PROC_VARCHIVEDEST(p_directorio);
       PROC_VARCHIVEDESTSTATUS(p_directorio);
       PROC_VARCHIVEPROCESSES(p_directorio);
       PROC_VARCHIVEDLOG(p_directorio);
       PROC_VCONTROLFILE(p_directorio);
       PROC_VDATAFILE(p_directorio);
       PROC_VDATAFILEHEADER(p_directorio);
       PROC_DBADATAFILES(p_directorio);
       PROC_DBATABLESPACES(p_directorio);
       PROC_DBATABLES(p_directorio);
       PROC_DBATABCOLUMNS(p_directorio);
       PROC_DBAINDEXES(p_directorio);
       PROC_DBAINDCOLUMNS(p_directorio);
       PROC_DBACONSTRAINTS(p_directorio);
       PROC_DBACONSCOLUMNS(p_directorio);
   end;

   PROCEDURE PROC_VDATABASE(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vdatabase.csv','w');
      utl_file.put_line(v_file, 'DBID;NAME;CREATED;RESETLOGS_CHANGE;RESETLOGS_TIME;PRIOR_RESETLOGS_CHANGE;PRIOR_RESETLOGS_TIME;LOG_MODE;CHECKPOINT_CHANGE;ARCHIVE_CHANGE;CONTROLFILE_TYPE;CONTROLFILE_CREATED;CONTROLFILE_SEQUENCE;CONTROLFILE_CHANGE;CONTROLFILE_TIME;OPEN_RESETLOGS;VERSION_TIME;OPEN_MODE;PROTECTION_MODE;PROTECTION_LEVEL;REMOTE_ARCHIVE;ACTIVATION;DATABASE_ROLE;ARCHIVELOG_CHANGE;SWITCHOVER_STATUS;DATAGUARD_BROKER;GUARD_STATUS;SUPPLEMENTAL_LOG_DATA_MIN;SUPPLEMENTAL_LOG_DATA_PK;SUPPLEMENTAL_LOG_DATA_UI;FORCE_LOGGING');
   
      for c in (select DBID||';'||NAME||';'||CREATED||';'||RESETLOGS_CHANGE#||';'||RESETLOGS_TIME||';'||PRIOR_RESETLOGS_CHANGE#||';'
                       ||PRIOR_RESETLOGS_TIME||';'||LOG_MODE||';'||CHECKPOINT_CHANGE#||';'||ARCHIVE_CHANGE#||';'||CONTROLFILE_TYPE||';'
                       ||CONTROLFILE_CREATED||';'||CONTROLFILE_SEQUENCE#||';'||CONTROLFILE_CHANGE#||';'||CONTROLFILE_TIME||';'
                       ||OPEN_RESETLOGS||';'||VERSION_TIME||';'||OPEN_MODE||';'||PROTECTION_MODE||';'||PROTECTION_LEVEL||';'
                       ||REMOTE_ARCHIVE||';'||ACTIVATION#||';'||DATABASE_ROLE||';'||ARCHIVELOG_CHANGE#||';'||SWITCHOVER_STATUS
                       ||';'||DATAGUARD_BROKER||';'||GUARD_STATUS||';'||SUPPLEMENTAL_LOG_DATA_MIN||';'||SUPPLEMENTAL_LOG_DATA_PK
                       ||';'||SUPPLEMENTAL_LOG_DATA_UI||';'||FORCE_LOGGING as fila from v$database) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VDATABASE;

   PROCEDURE PROC_VINSTANCE(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vinstance.csv','w');
      utl_file.put_line(v_file, 'INSTANCE_NUMBER;INSTANCE_NAME;HOST_NAME;VERSION;STARTUP_TIME;STATUS;PARALLEL;THREAD#;ARCHIVER;LOG_SWITCH_WAIT;LOGINS;SHUTDOWN_PENDING;DATABASE_STATUS;INSTANCE_ROLE;ACTIVE_STATE');
   
      for c in (select INSTANCE_NUMBER||';'||INSTANCE_NAME||';'||HOST_NAME||';'||VERSION||';'||STARTUP_TIME||';'||STATUS||';'
                       ||PARALLEL||';'||THREAD#||';'||ARCHIVER||';'||LOG_SWITCH_WAIT||';'||LOGINS||';'||SHUTDOWN_PENDING||';'
                       ||DATABASE_STATUS||';'||INSTANCE_ROLE||';'||ACTIVE_STATE as fila from v$instance) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VINSTANCE;

   PROCEDURE PROC_VPARAMETER(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vparameter.csv','w');
      utl_file.put_line(v_file, 'NUM;NAME;TYPE;VALUE;ISDEFAULT;ISSES_MODIFIABLE;ISSYS_MODIFIABLE;ISMODIFIED;ISADJUSTED;DESCRIPTION;UPDATE_COMMENT');
   
      for c in (select NUM||';'||NAME||';'||TYPE||';'||VALUE||';'||ISDEFAULT||';'||ISSES_MODIFIABLE||';'||ISSYS_MODIFIABLE||';'||ISMODIFIED||';'||ISADJUSTED||';'||DESCRIPTION||';'||UPDATE_COMMENT as fila from v$parameter order by name) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VPARAMETER;

   PROCEDURE PROC_DBAUSERS(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbausers.csv','w');
      utl_file.put_line(v_file, 'USERNAME;USER_ID;PASSWORD;ACCOUNT_STATUS;LOCK_DATE;EXPIRY_DATE;DEFAULT_TABLESPACE;TEMPORARY_TABLESPACE;CREATED;PROFILE;INITIAL_RSRC_CONSUMER_GROUP;EXTERNAL_NAME');
   
      for c in (select USERNAME||';'||USER_ID||';'||PASSWORD||';'||ACCOUNT_STATUS||';'||LOCK_DATE||';'||EXPIRY_DATE||';'||DEFAULT_TABLESPACE||';'||TEMPORARY_TABLESPACE||';'||CREATED||';'||PROFILE||';'||INITIAL_RSRC_CONSUMER_GROUP||';'||EXTERNAL_NAME as fila from dba_users order by username) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBAUSERS;

   PROCEDURE PROC_VLOG(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vlog.csv','w');
      utl_file.put_line(v_file, 'GROUP#;THREAD#;SEQUENCE#;BYTES;MEMBERS;ARCHIVED;STATUS;FIRST_CHANGE#;FIRST_TIME');
   
      for c in (select GROUP#||';'||THREAD#||';'||SEQUENCE#||';'||BYTES||';'||MEMBERS||';'||ARCHIVED||';'||STATUS||';'||FIRST_CHANGE#||';'||FIRST_TIME as fila from v$log order by group#) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VLOG;

   PROCEDURE PROC_VLOGFILE(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vlog.csv','w');
      utl_file.put_line(v_file, 'GROUP#;STATUS;TYPE;MEMBER');
   
      for c in (select group#||';'||status||';'||type||';'||member as fila from v$logfile order by group#) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VLOGFILE;

   PROCEDURE PROC_VLOGHISTORY(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vloghistory.csv','w');
      utl_file.put_line(v_file, 'RECID;STAMP;THREAD#;SEQUENCE#;FIRST_CHANGE#;FIRST_TIME;NEXT_CHANGE#');
   
      for c in (select recid||';'||stamp||';'||thread#||';'||sequence#||';'||first_change#||';'||first_time||';'||next_change# as fila from v$log_history order by recid) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VLOGHISTORY;

   PROCEDURE PROC_VARCHIVEDEST(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'varchivedest.csv','w');
      utl_file.put_line(v_file, 'DEST_ID;DEST_NAME;STATUS;BINDING;NAME_SPACE;TARGET;ARCHIVER;SCHEDULE;DESTINATION;LOG_SEQUENCE;REOPEN_SECS;DELAY_MINS;NET_TIMEOUT;PROCESS;REGISTER;FAIL_DATE;FAIL_SEQUENCE;FAIL_BLOCK;FAILURE_COUNT;MAX_FAILURE;ERROR;ALTERNATE;DEPENDENCY;REMOTE_TEMPLATE;QUOTA_SIZE;QUOTA_USED;MOUNTID;TRANSMIT_MODE;ASYNC_BLOCKS;AFFIRM;TYPE');
   
      for c in (select dest_id||';'||dest_name||';'||status||';'||binding||';'||name_space||';'||target||';'||archiver||';'||schedule||';'||destination||';'||log_sequence||';'||reopen_secs||';'||delay_mins||';'||net_timeout||';'||process||';'||register||';'||fail_date||';'||fail_sequence||';'||fail_block||';'||failure_count||';'||max_failure||';'||error||';'||alternate||';'||dependency||';'||remote_template||';'||quota_size||';'||quota_used||';'||mountid||';'||transmit_mode||';'||async_blocks||';'||affirm||';'||type as fila from v$archive_dest order by dest_id) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VARCHIVEDEST;

   PROCEDURE PROC_VARCHIVEDESTSTATUS(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'varchivedeststatus.csv','w');
      utl_file.put_line(v_file, 'DEST_ID;DEST_NAME;STATUS;TYPE;DATABASE_MODE;RECOVERY_MODE;PROTECTION_MODE;DESTINATION;STANDBY_LOGFILE_COUNT;STANDBY_LOGFILE_ACTIVE;ARCHIVED_THREAD#;ARCHIVED_SEQ#;APPLIED_THREAD#;APPLIED_SEQ#;ERROR;SRL');
   
      for c in (select dest_id||';'||dest_name||';'||status||';'||type||';'||database_mode||';'||recovery_mode||';'||protection_mode||';'||destination||';'||standby_logfile_count||';'||standby_logfile_active||';'||archived_thread#||';'||archived_seq#||';'||applied_thread#||';'||applied_seq#||';'||error||';'||srl as fila from v$archive_dest_status order by dest_id) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VARCHIVEDESTSTATUS;

   PROCEDURE PROC_VARCHIVEPROCESSES(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'varchiveprocesses.csv','w');
      utl_file.put_line(v_file, 'PROCESS;STATUS;LOG_SEQUENCE;STATE');
   
      for c in (select process||';'||status||';'||log_sequence||';'||state as fila from v$archive_processes order by process) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VARCHIVEPROCESSES;

   PROCEDURE PROC_VARCHIVEDLOG(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'varchivedlog.csv','w');
      utl_file.put_line(v_file, 'RECID;STAMP;NAME;DEST_ID;THREAD#;SEQUENCE#;RESETLOGS_CHANGE#;RESETLOGS_TIME;FIRST_CHANGE#;FIRST_TIME;NEXT_CHANGE#;NEXT_TIME;BLOCKS;BLOCK_SIZE;CREATOR;REGISTRAR;STANDBY_DEST;ARCHIVED;APPLIED;DELETED;STATUS;COMPLETION_TIME;DICTIONARY_BEGIN;DICTIONARY_END;END_OF_REDO;BACKUP_COUNT;ARCHIVAL_THREAD#;ACTIVATION#');
   
      for c in (select recid||';'||stamp||';'||name||';'||dest_id||';'||thread#||';'||sequence#||';'||resetlogs_change#||';'||resetlogs_time||';'||first_change#||';'||first_time||';'||next_change#||';'||next_time||';'||blocks||';'||block_size||';'||creator||';'||registrar||';'||standby_dest||';'||archived||';'||applied||';'||deleted||';'||status||';'||completion_time||';'||dictionary_begin||';'||dictionary_end||';'||end_of_redo||';'||backup_count||';'||archival_thread#||';'||activation# as fila from v$archived_log order by recid) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VARCHIVEDLOG;

   PROCEDURE PROC_VCONTROLFILE(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vcontrolfile.csv','w');
      utl_file.put_line(v_file, 'STATUS;NAME');
   
      for c in (select status||';'||name as fila from v$controlfile) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VCONTROLFILE;

   PROCEDURE PROC_VDATAFILE(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vdatafile.csv','w');
      utl_file.put_line(v_file, 'FILE#;CREATION_CHANGE#;CREATION_TIME;TS#;RFILE#;STATUS;ENABLED;CHECKPOINT_CHANGE#;CHECKPOINT_TIME;UNRECOVERABLE_CHANGE#;UNRECOVERABLE_TIME;LAST_CHANGE#;LAST_TIME;OFFLINE_CHANGE#;ONLINE_CHANGE#;ONLINE_TIME;BYTES;BLOCKS;CREATE_BYTES;BLOCK_SIZE;NAME;PLUGGED_IN;BLOCK1_OFFSET;AUX_NAME');
   
      for c in (select file#||';'||creation_change#||';'||creation_time||';'||ts#||';'||rfile#||';'||status||';'||enabled||';'||checkpoint_change#||';'||checkpoint_time||';'||unrecoverable_change#||';'||unrecoverable_time||';'||last_change#||';'||last_time||';'||offline_change#||';'||online_change#||';'||online_time||';'||bytes||';'||blocks||';'||create_bytes||';'||block_size||';'||name||';'||plugged_in||';'||block1_offset||';'||aux_name as fila from v$datafile order by file#) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VDATAFILE;

   PROCEDURE PROC_VDATAFILEHEADER(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'vdatafileheader.csv','w');
      utl_file.put_line(v_file, 'FILE#;STATUS;ERROR;FORMAT;RECOVER;FUZZY;CREATION_CHANGE#;CREATION_TIME;TABLESPACE_NAME;TS#;RFILE#;RESETLOGS_CHANGE#;RESETLOGS_TIME;CHECKPOINT_CHANGE#;CHECKPOINT_TIME;CHECKPOINT_COUNT;BYTES;BLOCKS;NAME');
   
      for c in (select file#||';'||status||';'||error||';'||format||';'||recover||';'||fuzzy||';'||creation_change#||';'||creation_time||';'||tablespace_name||';'||ts#||';'||rfile#||';'||resetlogs_change#||';'||resetlogs_time||';'||checkpoint_change#||';'||checkpoint_time||';'||checkpoint_count||';'||bytes||';'||blocks||';'||name as fila from v$datafile_header order by file#) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_VDATAFILEHEADER;

   PROCEDURE PROC_DBADATAFILES(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbadatafiles.csv','w');
      utl_file.put_line(v_file, 'FILE_NAME;FILE_ID;TABLESPACE_NAME;BYTES;BLOCKS;STATUS;RELATIVE_FNO;AUTOEXTENSIBLE;MAXBYTES;MAXBLOCKS;INCREMENT_BY;USER_BYTES;USER_BLOCKS');
   
      for c in (select file_name||';'||file_id||';'||tablespace_name||';'||bytes||';'||blocks||';'||status||';'||relative_fno||';'||autoextensible||';'||maxbytes||';'||maxblocks||';'||increment_by||';'||user_bytes||';'||user_blocks as fila from dba_data_files order by file_name) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBADATAFILES;

   PROCEDURE PROC_DBATABLESPACES(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbatablespaces.csv','w');
      utl_file.put_line(v_file, 'TABLESPACE_NAME;BLOCK_SIZE;INITIAL_EXTENT;NEXT_EXTENT;MIN_EXTENTS;MAX_EXTENTS;PCT_INCREASE;MIN_EXTLEN;STATUS;CONTENTS;LOGGING;FORCE_LOGGING;EXTENT_MANAGEMENT;ALLOCATION_TYPE;PLUGGED_IN;SEGMENT_SPACE_MANAGEMENT');
   
      for c in (select tablespace_name||';'||block_size||';'||initial_extent||';'||next_extent||';'||min_extents||';'||max_extents||';'||pct_increase||';'||min_extlen||';'||status||';'||contents||';'||logging||';'||force_logging||';'||extent_management||';'||allocation_type||';'||plugged_in||';'||segment_space_management as fila from dba_tablespaces order by tablespace_name) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBATABLESPACES;

   PROCEDURE PROC_DBATABLES(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbatables.csv','w');
      utl_file.put_line(v_file, 'OWNER;TABLE_NAME;TABLESPACE_NAME;CLUSTER_NAME;IOT_NAME;PCT_FREE;PCT_USED;INI_TRANS;MAX_TRANS;INITIAL_EXTENT;NEXT_EXTENT;MIN_EXTENTS;MAX_EXTENTS;PCT_INCREASE;FREELISTS;FREELIST_GROUPS;LOGGING;BACKED_UP;NUM_ROWS;BLOCKS;EMPTY_BLOCKS;AVG_SPACE;CHAIN_CNT;AVG_ROW_LEN;AVG_SPACE_FREELIST_BLOCKS;NUM_FREELIST_BLOCKS;DEGREE;INSTANCES;CACHE;TABLE_LOCK;SAMPLE_SIZE;LAST_ANALYZED;PARTITIONED;IOT_TYPE;TEMPORARY;SECONDARY;NESTED;BUFFER_POOL;ROW_MOVEMENT;GLOBAL_STATS;USER_STATS;DURATION;SKIP_CORRUPT;MONITORING;CLUSTER_OWNER;DEPENDENCIES');
   
      for c in (select owner||';'||table_name||';'||tablespace_name||';'||cluster_name||';'||iot_name||';'||pct_free||';'||pct_used||';'||ini_trans||';'||max_trans||';'||initial_extent||';'||next_extent||';'||min_extents||';'||max_extents||';'||pct_increase||';'||freelists||';'||freelist_groups||';'||logging||';'||backed_up||';'||num_rows||';'||blocks||';'||empty_blocks||';'||avg_space||';'||chain_cnt||';'||avg_row_len||';'||avg_space_freelist_blocks||';'||num_freelist_blocks||';'||degree||';'||instances||';'||cache||';'||table_lock||';'||sample_size||';'||last_analyzed||';'||partitioned||';'||iot_type||';'||temporary||';'||secondary||';'||nested||';'||buffer_pool||';'||row_movement||';'||global_stats||';'||user_stats||';'||duration||';'||skip_corrupt||';'||monitoring||';'||cluster_owner||';'||dependencies as fila 
                  from dba_tables 
                  where owner in ('SITREM','SITREM_BIZ','SITREM_CONFIG','SITREM_HEGEO','SITREM_AUDIT')
                  order by owner,table_name) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBATABLES;

   PROCEDURE PROC_DBATABCOLUMNS(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbatabcolumns.csv','w');
      utl_file.put_line(v_file, 'OWNER;TABLE_NAME;COLUMN_NAME;DATA_TYPE;DATA_TYPE_MOD;DATA_TYPE_OWNER;DATA_LENGTH;DATA_PRECISION;DATA_SCALE;NULLABLE;COLUMN_ID;DEFAULT_LENGTH;NUM_DISTINCT;LOW_VALUE;HIGH_VALUE;DENSITY;NUM_NULLS;NUM_BUCKETS;LAST_ANALYZED;SAMPLE_SIZE;CHARACTER_SET_NAME;CHAR_COL_DECL_LENGTH;GLOBAL_STATS;USER_STATS;AVG_COL_LEN;CHAR_LENGTH;CHAR_USED;V80_FMT_IMAGE;DATA_UPGRADED');
   
      for c in (select owner||';'||table_name||';'||column_name||';'||data_type||';'||data_type_mod||';'||data_type_owner||';'||data_length||';'||data_precision||';'||data_scale||';'||nullable||';'||column_id||';'||default_length||';'||num_distinct||';'||low_value||';'||high_value||';'||density||';'||num_nulls||';'||num_buckets||';'||last_analyzed||';'||sample_size||';'||character_set_name||';'||char_col_decl_length||';'||global_stats||';'||user_stats||';'||avg_col_len||';'||char_length||';'||char_used||';'||v80_fmt_image||';'||data_upgraded as fila 
                  from dba_tab_columns 
                  where owner in ('SITREM','SITREM_BIZ','SITREM_CONFIG','SITREM_HEGEO','SITREM_AUDIT')
                  order by owner,table_name, column_id) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBATABCOLUMNS;

   PROCEDURE PROC_DBAINDEXES(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbaindexes.csv','w');
      utl_file.put_line(v_file, 'OWNER;INDEX_NAME;INDEX_TYPE;TABLE_OWNER;TABLE_NAME;TABLE_TYPE;UNIQUENESS;COMPRESSION;PREFIX_LENGTH;TABLESPACE_NAME;INI_TRANS;MAX_TRANS;INITIAL_EXTENT;NEXT_EXTENT;MIN_EXTENTS;MAX_EXTENTS;PCT_INCREASE;PCT_THRESHOLD;INCLUDE_COLUMN;FREELISTS;FREELIST_GROUPS;PCT_FREE;LOGGING;BLEVEL;LEAF_BLOCKS;DISTINCT_KEYS;AVG_LEAF_BLOCKS_PER_KEY;AVG_DATA_BLOCKS_PER_KEY;CLUSTERING_FACTOR;STATUS;NUM_ROWS;SAMPLE_SIZE;LAST_ANALYZED;DEGREE;INSTANCES;PARTITIONED;TEMPORARY;GENERATED;SECONDARY;BUFFER_POOL;USER_STATS;DURATION;PCT_DIRECT_ACCESS;ITYP_OWNER;ITYP_NAME;PARAMETERS;GLOBAL_STATS;DOMIDX_STATUS;DOMIDX_OPSTATUS;FUNCIDX_STATUS;JOIN_INDEX');
   
      for c in (select owner||';'||index_name||';'||index_type||';'||table_owner||';'||table_name||';'||table_type||';'||uniqueness||';'||compression||';'||prefix_length||';'||tablespace_name||';'||ini_trans||';'||max_trans||';'||initial_extent||';'||next_extent||';'||min_extents||';'||max_extents||';'||pct_increase||';'||pct_threshold||';'||include_column||';'||freelists||';'||freelist_groups||';'||pct_free||';'||logging||';'||blevel||';'||leaf_blocks||';'||distinct_keys||';'||avg_leaf_blocks_per_key||';'||avg_data_blocks_per_key||';'||clustering_factor||';'||status||';'||num_rows||';'||sample_size||';'||last_analyzed||';'||degree||';'||instances||';'||partitioned||';'||temporary||';'||generated||';'||secondary||';'||buffer_pool||';'||user_stats||';'||duration||';'||pct_direct_access||';'||ityp_owner||';'||ityp_name||';'||parameters||';'||global_stats||';'||domidx_status||';'||domidx_opstatus||';'||funcidx_status||';'||join_index as fila 
                  from dba_indexes 
                  where owner in ('SITREM','SITREM_BIZ','SITREM_CONFIG','SITREM_HEGEO','SITREM_AUDIT')
                  order by owner,index_name) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBAINDEXES;

   PROCEDURE PROC_DBAINDCOLUMNS(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbaindcolumns.csv','w');
      utl_file.put_line(v_file, 'INDEX_OWNER;INDEX_NAME;TABLE_OWNER;TABLE_NAME;COLUMN_NAME;COLUMN_POSITION;COLUMN_LENGTH;CHAR_LENGTH;DESCEND');
   
      for c in (select INDEX_OWNER||';'||INDEX_NAME||';'||TABLE_OWNER||';'||TABLE_NAME||';'||COLUMN_NAME||';'||COLUMN_POSITION||';'||COLUMN_LENGTH||';'||CHAR_LENGTH||';'||DESCEND as fila 
                  from dba_ind_columns
                  where index_owner in ('SITREM','SITREM_BIZ','SITREM_CONFIG','SITREM_HEGEO','SITREM_AUDIT')
                  order by index_owner,index_name,column_position) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBAINDCOLUMNS;

   PROCEDURE PROC_DBACONSTRAINTS(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbaconstraints.csv','w');
      utl_file.put_line(v_file, 'OWNER;CONSTRAINT_NAME;CONSTRAINT_TYPE;TABLE_NAME;R_OWNER;R_CONSTRAINT_NAME;DELETE_RULE;STATUS;DEFERRABLE;DEFERRED;VALIDATED;GENERATED;BAD;RELY;LAST_CHANGE;INDEX_OWNER;INDEX_NAME;INVALID;VIEW_RELATED');
   
      for c in (select owner||';'||constraint_name||';'||constraint_type||';'||table_name||';'||r_owner||';'||r_constraint_name||';'||delete_rule||';'||status||';'||deferrable||';'||deferred||';'||validated||';'||generated||';'||bad||';'||rely||';'||last_change||';'||index_owner||';'||index_name||';'||invalid||';'||view_related as fila 
                  from dba_constraints
                  where owner in ('SITREM','SITREM_BIZ','SITREM_CONFIG','SITREM_HEGEO','SITREM_AUDIT')
                  order by owner,table_name,constraint_name) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBACONSTRAINTS;

   PROCEDURE PROC_DBACONSCOLUMNS(p_directorio varchar2) IS
      v_file utl_file.file_type;
   begin
      v_file := utl_file.fopen(p_directorio,'dbaconscolumns.csv','w');
      utl_file.put_line(v_file, 'OWNER;CONSTRAINT_NAME;TABLE_NAME;COLUMN_NAME;POSITION');
   
      for c in (select owner||';'||constraint_name||';'||table_name||';'||column_name||';'||position as fila 
                  from dba_cons_columns
                  where owner in ('SITREM','SITREM_BIZ','SITREM_CONFIG','SITREM_HEGEO','SITREM_AUDIT')
                  order by owner,table_name,constraint_name) loop
          utl_file.put_line(v_file, c.fila);
      end loop;
      
      utl_file.fclose(v_file);
   end PROC_DBACONSCOLUMNS;
END;
/

DISCONN;