# DCdba.ctl:201:Collects Oracle RDBMS Information
# $Id: DCdba.ctl,v 1.21 2015/08/13 20:29:37 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/DCdba.ctl,v 1.21 2015/08/13 20:29:37 RDA Exp $
#
# Change History
# 20150813  KRA  Eliminate Perl 5.22 warnings.

=head1 NAME

DB:DCdba - Collects Oracle RDBMS Information

=head1 DESCRIPTION

This module collects the basic Oracle RDBMS-related diagnostic information.

The following reports can be generated and are regrouped under C<RDBMS>:

=cut

if !${I_DBC/E}
 return

echo tput('bold'),'Processing DB.DBA module ...',tput('off')

# Initialization
var $LOCAL       = ${B_LOCAL/P}
var $LOCAL_DB    = ${I_DATABASE/P}
var $ORACLE_HOME = ${D_ORACLE_HOME/P:''}
var $ORACLE_SID  = ${T_ORACLE_SID/P}

var $TOC = '%TOC%'
var $TOP = '[[#Top][Back to top]]'
var @TXT
toc '1:RDBMS'

# Load the common macros
run DB:DBinfo()

=head2 versions - Product Versions

Gets product versions and determines which version of the database is
installed.

=cut

debug ' Inside DBA module, processing product versions'
report versions
write '---+!! Product Versions from V$Version'
set $sql
{SELECT '|' || v.banner || '|'
"  FROM v$version v;
}
if loadSql($sql)
{prefix
  write '|*Banner*|'
 call writeLastSql()
 unprefix
}
else
{write "Cannot connect to the database (",getSqlMessage(),')'
}
write '---'

# Determine which version the database says we are dealing
var ($ver) = grepLastSql('Oracle','f')
var $ins = 'install'
var $tzf = false
var $rgs = $LOCAL
if match($ver,'^\|Oracle7\s')
{var $ORACLE_VERSION = '7'
 var $ins = 'orainst'
 var $prt = true
}
elsif match($ver,'^\|Oracle8\s')
{var $ORACLE_VERSION = '80'
 var $ins = 'orainst'
 var $prt = true
}
elsif match($ver,'^\|Oracle8i\s')
{var $ORACLE_VERSION = '81'
 var $prt = false
 # Time zone was not available until 8.1.7
 var $tzf = match($ver,'\s8\.1\.7')
}
elsif match($ver,'^\|Oracle9i\s')
{if match($ver,'\s9\.2\.')
 {var $ORACLE_VERSION = '92'
 }
 else
 {var $ORACLE_VERSION = '90'
 }
 var $tzf = true
 var $rgs = false
}
elsif match($ver,'^\|Oracle\s')
{var $ORACLE_VERSION = check($ver,'\s12\.','12',\
                                  '\s11\.','11',\
                                  '\s10\.2\.','102',\
                                  '\s10\.1\.','101',\
                                  '10')
 var $tzf = true
 var $rgs = false
}
else
{# keep searching
 var $prt = false
}

# Merge the operating system and database versions
if or(isWindows(),isCygwin())
 var $nam = 'nt.rgs'
else
 var $nam = 'unix.rgs'

if $rgs
{var $fil = catFile($ORACLE_HOME,$ins,$nam)
 write '---+ From ',encode($fil)
 if !?testFile('r',$fil)
 {write 'Could not read: ',encode($fil),'%BR%'
  var $fil = '/var/opt/oracle'
  if ?testDir('d',$fil)
  {write 'Look in ',encode($fil)
   write '---+ List of files in ',encode($fil)
   call statDir('n',$fil)
   write 'User: ',id(),'%BR%'
  }
 }
 elsif $prt
 {var @tbl = grepFile($fil,'"','f')
  var @tbl = split('"',$tbl[0])
  write '|',$tbl[5],' |',$tbl[3],' |',$tbl[7],' |'
 }
 else
 {call writeFile($fil)
 }
}
elsif $LOCAL
{write 'The content in ',encode($nam),' in this version is no longer useful.\
        %BR%Contents not being displayed.'
}
toc '2:[[',getFile(),'][rda_report][Product Versions]]'

=head2 init_ora - INIT.ORA

Determines if C<spfile> is in use by connecting to the database and looking up
the C<spfile> parameter. If a value is found, then RDA does not search
INIT.ORA. A select for C<v$spparameter> is executed to determine whether to
skip the search for INIT.ORA.

=cut

if $LOCAL_DB
{debug ' Inside DBA module, processing INIT.ORA'
 report init_ora
 set $sql
 {SELECT '|' || p.name || '|' || p.value || '|'
 " FROM v$parameter p
 " WHERE p.name = 'spfile'
 "   AND p.value IS NOT NULL;
 }
 var $SPFile_In_Use = grepSql($sql,'\|spfile\|','f')

 if $SPFile_In_Use
 {write 'SPFile appears to be in use on this server%BR%\
         Review "Database Parameters" and "Database SPFile Parameters" links \
         for parameter settings.'
  # Turn both of these flags off. If pfile local is turned on but the database
  # is using the spfile, the INIT.ORA does us no good. Make sure the rest of the
  # code does not try to dig thru the INIT.ORA if there is an INIT.ORA file.
  call $LOCAL_DB->set_value('B_PFILE_GOOD', false,'Is the PFILE good?')
  call $LOCAL_DB->set_value('B_PFILE_LOCAL',false,'Is the PFILE local?')
 }
 elsif ${B_PFILE_LOCAL/P}
 {var $fil = ${F_PFILE_LOCATION/P}
  var %fil = ()
  prefix
  {write '---+!! INIT.ORA Information'
   write $TOC
   write '---+ Contents of INIT.ORA ',encode($fil)
  }
  if writeFile($fil)
  {call $LOCAL_DB->set_value('B_PFILE_GOOD',true,'Is the PFILE good?')
   write $TOP
   var $fil{$fil} = 1
   # Extract the IFILE, ignoring comments in the Oracle supplied INIT.ORA
   while value(grepFile($fil,'^[^#]*ifile','fi'))
   {var $fil = trim(last)
    var $fil = replace($fil,"'",'',true)
    var $fil = replace($fil,'\?',$ORACLE_HOME)
    var $fil = replace($fil,'\$ORACLE_HOME',$ORACLE_HOME)
    var $fil = replace($fil,'\$\{ORACLE_HOME\}',$ORACLE_HOME)
    var $fil = replace($fil,'%ORACLE_HOME%',$ORACLE_HOME)
    break exists($fil{$fil})
    write '---+ Contents of IFILE ',encode($fil)
    call writeFile($fil)
    write $TOP
    var $fil{$fil} = 1
   }
  }
  else
  {call $LOCAL_DB->set_value('B_PFILE_GOOD',false,'Is the PFILE good?')
   unprefix
   write encode($fil),' is not readable or does not exist.%BR%\
          File permissions are as follows:%BR%'
   call statFile('b',$fil)
   write 'User: ',id(),'%BR%\
          Review Database Parameters output in RDBMS section \
          for parameter settings.'
  }
 }
 else
 {write 'INIT.ORA not located on this server.%BR%\
         Review Database Parameter output in RDBMS section for parameter \
         settings.'
 }
 toc '2:[[',getFile(),'][rda_report][INIT.ORA]]'
}

=head2 vparameters - Database Parameters

Gets C<V$Parameter> information.

=cut

debug ' Inside DBA module, processing V$Parameter'
report vparameters
var $TTL = '---+!! Database Parameters'
var $TTL[1] = '---+ Nondefault Parameters'
var $TTL[2] = '---+ All Parameters'
if match($ORACLE_VERSION,"^7$")
{var $HDR[1] = '|*Parameter Name*|*Value*|'
 var $HDR[2] = '|*Parameter Name*|*Value*|*Default*|'
 set $sql
 {SELECT '|' ||
 "       SUBSTR(name, 0, 512) || '|' ||
 "       NVL(SUBSTR(value, 0, 512), '%NULL%') || '|'
 " FROM v$parameter
 " WHERE isdefault = 'FALSE'
 " ORDER BY name;
 }
 set $sql2
 {SELECT '|' ||
 "       SUBSTR(name, 0, 512) || '|' ||
 "       NVL(SUBSTR(value, 0, 512), '%NULL%') || '|' ||
 "       isdefault || '|'
 " FROM v$parameter
 " ORDER BY name;
 }
}
else
{var $HDR[1] = '|*Parameter Name*|*Value*|*Modified*|'
 var $HDR[2] = '|*Parameter Name*|*Value*|*Default*|*Modified*|'
 set $sql
 {SELECT '|' ||
 "       SUBSTR(name, 0, 512) || '|' ||
 "       NVL(SUBSTR(value, 0, 512), '%NULL%') || '|'  ||
 "       ismodified || '|'
 " FROM v$parameter
 " WHERE isdefault = 'FALSE'
 " ORDER BY name;
 }
 set $sql2
 {SELECT '|' ||
 "       SUBSTR(name, 0, 512) || '|' ||
 "       NVL(SUBSTR(value, 0, 512), '%NULL%') || '|'  ||
 "       isdefault || '|' ||
 "       ismodified || '|'
 " FROM v$parameter
 " ORDER BY name;
 }
}
call separator(1)
call writeSql($sql)
if getSqlMessage()
 write last,'%BR%'
call separator(2)
if loadSql($sql2)
 call writeLastSql()
if ${B_SYSDBA/P}
{var $TTL[3] = '---+ Hidden Parameters'
 var $HDR[3] = '|*Parameter Name*|*Value*|*Default*|*Description*|'
 set $sql
 {PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       SUBSTR(a.ksppinm, 0, 512) ||'|'||
 "       NVL(REPLACE(REPLACE(SUBSTR(b.ksppstvl,0,512),'|','&#124;'),
 "                   CHR(10),'%BR%'),'%NULL%') || '|' ||
 "       b.ksppstdf || '|' ||
 "       a.ksppdesc || '|'
 " FROM x$ksppi a,x$ksppsv b
 " WHERE a.indx = b.indx
 "   AND a.ksppinm LIKE '\_%' ESCAPE '\'
 " ORDER BY a.ksppinm;
 }
 call separator(3)
 call writeSql($sql)
}
call separator(0,'Database Parameters')

# No need to resolve ORACLE_HOME for AUM, but this parameter may not exist
var $AUM = get_param('\|undo_management\|',true)
if !$AUM
 var $AUM = 'manual'

=for stopwords SPFile

=head2 vspparameters - Database SPFile Parameters

Gets C<V$SPParameter> information.
(C<spfiles> are available in Oracle 9i and later only.)

=cut

debug ' Inside DBA module, processing V$SPParameter'
debug '  ORACLE_VERSION is ',$ORACLE_VERSION
debug '  SPFile_In_Use is ',$SPFile_In_Use
report vspparameters
if match($ORACLE_VERSION,'^(9|10|11|12)')
{if $SPFile_In_Use
 {write '---+!! SPFile Parameter Values'
  write '|*SID*|*Name*|*Value*|*Is Specified?*| *Ordinal*|*Update Comment*|'
  set $sql
  {SELECT '|' ||
  "       s.sid || '|' ||
  "       s.name || '|' ||
  "       NVL(s.value, ' ') || '|' ||
  "       s.isspecified || '| ' ||
  "       s.ordinal || '|' ||
  "       NVL(s.update_comment, ' ') || '|'
  " FROM v$spparameter s
  " ORDER BY s.name, s.sid;
  }
  call writeSql($sql)
  if getSqlMessage()
   write last,'%BR%'
 }
 else
 {write '---+!! SPFile Parameter Values'
  write 'This database instance is not using an spfile.'
 }
}
elsif $ORACLE_VERSION
{write 'This database version does not support spfiles.'
}
if isCreated()
 toc '2:[[',getFile(),'][rda_report][Database SPFile Parameters]]'

=head2 voption - Database Options

Gets C<V$Option> information.

=cut

debug ' Inside DBA module, processing V$Option'
report voption
prefix
{write '---+!! Database Options'
 write '|*Parameter*|*Value*|'
}
set $sql
{SELECT '|' ||
"       o.parameter || '|' ||
"       o.value || '|'
" FROM v$option o
" ORDER BY o.parameter;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][Database Options]]'

=head2 dba_registry - Database Registry

Gets C<DBA_Registry> information (Oracle 9i and later).

=cut

if match($ORACLE_VERSION,'^(92$|10|11|12)')
{debug ' Inside DBA module, processing DBA_Registry'
 report dba_registry
 prefix
 {write '---+!! Database Registry'
  write '|*Component ID*|*Component Name*|*Status*|*Version*|'
 }
 set $sql
 {SELECT '|' ||
 "       comp_id   || '|' ||
 "       comp_name || '|' ||
 "       status    || '|' ||
 "       version   || '|'
 " FROM dba_registry
 " ORDER BY comp_id;
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Database Registry]]'
}

=head2 app_registry - Application Registry

Gets application registry information (Oracle 9i and later).

=cut

if match($ORACLE_VERSION,'^(92$|10|11|12)')
{debug ' Inside DBA module, processing app_registry'
 var ($hdr,$col) = getSqlColumns('RDA','','APP_REGISTRY',\
                                 'COMP_ID','COMP_NAME','STATUS','VERSION')
 call clearSqlColumns('RDA')
 if $col
 {report app_registry
  prefix
  {write '---+!! Application Registry'
   write replace($hdr,'Comp ','Component ',true)
  }
  set $sql
  {SELECT :1
  " FROM app_registry
  " ORDER BY comp_id;
  }
  call writeSql(bindSql($sql,$col))
  if getSqlMessage()
   write last
  if isCreated(true)
   toc '2:[[',getFile(),'][rda_report][Application Registry]]'
 }
}

=head2 sga_info - SGA Information

Gets SGA information.

=cut

debug ' Inside DBA module, processing SGA information'
report sga_info
var $TTL = '---+!! SGA Information '

var @TTL = ('',\
 '---+ V$Sga',\
 '---+ V$SgaStat',\
 '---+ V$Sga_Resize_Ops',\
 '---+ Minimum and Maximum Component Final Size')
var @HDR = ('',\
 '|*Name*| *Value*|'\
)
set $sql
{SELECT '|' || s.name || '| ' || s.value || '|'
" FROM v$sga s
" ORDER BY s.name;
"PROMPT ___Macro_separator(2)___
}
if match($ORACLE_VERSION,"^7$")
{call push(@HDR,'|*Name*| *Value*|')
 append $sql
 {SELECT '|' || v.name || '| ' || v.bytes || '|'
 " FROM v$sgastat v
 " ORDER BY v.name;
 }
}
else
{call push(@HDR,'|*Pool*|*Name*| *Bytes*|')
 append $sql
 {SELECT '|' ||
 "       NVL(v.pool, '%NULL%') || '|' ||
 "       v.name || '| ' ||
 "       v.bytes || '|'
 " FROM v$sgastat v
 " ORDER BY v.name;
 }
}
if match($ORACLE_VERSION,'^(92$|10|11|12)')
{call push(@HDR,\
   '|*Component*|*Parameter*| *Initial*| *Final*|*Status*|*Changed At*|',\
   '|*Component*| *Lowest*| *Low (MiB)*| *Highest*| *High (MiB)*|')
 append $sql
 {PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       component || ' |' ||
 "       parameter || ' | ' ||
 "       initial_size || '| ' ||
 "       final_size || '|' ||
 "       status || ' |' ||
 "       TO_CHAR(end_time,'DD-Mon-YYYY HH24:MI:SS') || ' |'
 " FROM v$sga_resize_ops
 " ORDER BY end_time,component;
 "PROMPT ___Macro_separator(4)___
 "SELECT '|' ||
 "       component || ' | ' ||
 "       MIN(final_size) || '| ' ||
 "       MIN(final_size / 1048576) || '| ' ||
 "       MAX(final_size) || '| ' ||
 "       MAX(final_size / 1048576) || '|'
 " FROM v$sga_resize_ops
 " GROUP BY component
 " ORDER BY component;
 }
}
call separator(1)
call writeSql($sql,4)
call separator(0,'SGA Information')

=head2 mts - Multi-Threaded Server / Shared Server

Collects multi-threaded server (Oracle Database 8i) or shared server (Oracle
Database 9i and later) information.

=cut

if match($ORACLE_VERSION,'^(81$|9|10|11|12)')
{debug ' Inside DBA module, processing MTS/shared server information'
 report mts
 var @TTL = ('',\
             '---+ V$Dispatcher',\
             '---+ V$Shared_Server',\
             '---+ V$Circuit')
 var @HDR = ('',\
             '|*Name*|*Network*|*Paddr*|*Status*|*Accept*| *Messages*| \
               *Bytes*| *Breaks*| *Owned*| *Created*| *Idle*| *Busy*| \
               *Listener*| *Conf_indx*|',\
             '|*Name*|*Paddr*|*Status*| *Messages*| *Bytes*| *Breaks*|\
               *Circuit*| *Idle*| *Busy*| *Requests*|')
 set $sql
 {SELECT '|' ||
 "       name || '|' ||
 "       network || '|' ||
 "       paddr || '|' ||
 "       status || '|' ||
 "       accept || '| ' ||
 "       messages || '| ' ||
 "       bytes || '| ' ||
 "       breaks || '| ' ||
 "       owned || '| ' ||
 "       created || '| ' ||
 "       idle || '| ' ||
 "       busy || '| ' ||
 "       listener || '| ' ||
 "       conf_indx || '|'
 " FROM v$dispatcher;
 "PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       name || '|' ||
 "       paddr || '|' ||
 "       status || '| ' ||
 "       messages || '| ' ||
 "       bytes || '| ' ||
 "       breaks || '|' ||
 "       circuit || '| ' ||
 "       idle || '| ' ||
 "       busy || '| ' ||
 "       requests || '|'
 " FROM v$shared_server;
 "PROMPT ___Macro_separator(3)___
 }
 if match($ORACLE_VERSION,'^81')
 {var $TTL = '---+!! Multi-Threaded Server Information'
  var $ttl = 'Multi-Threaded Server Information'
  var $HDR[3] = '|*Circuit*|*Dispatcher*|*Server*|*Waiter*|*Saddr*|*Status*|\
                  *Queue*| *Message0*| *Message1*| *Message2*| *Message3*| \
                  *Messages*| *Bytes*| *Breaks*|*Presentation*|'
  append $sql
  {SELECT '|' ||
  "       circuit || '|' ||
  "       dispatcher || '|' ||
  "       server || '|' ||
  "       waiter || '|' ||
  "       saddr || '|' ||
  "       status || '|' ||
  "       queue || '| ' ||
  "       message0 || '| ' ||
  "       message1 || '| ' ||
  "       message2 || '| ' ||
  "       message3 || '| ' ||
  "       messages || '| ' ||
  "       bytes || '| ' ||
  "       breaks || '|' ||
  "       presentation || '|'
  " FROM v$circuit;
  }
 }
 else
 {var $TTL = '---+!! Shared Server Information'
  var $ttl = 'Shared Server Information'
  var $HDR[3] = '|*Circuit*|*Dispatcher*|*Server*|*Waiter*|*Saddr*|*Status*|\
                  *Queue*| *Message0*| *Message1*| *Message2*| *Message3*| \
                  *Messages*| *Bytes*| *Breaks*|*Presentation*|*Pcircuit*|'
  append $sql
  {SELECT '|' ||
  "       circuit || '|' ||
  "       dispatcher || '|' ||
  "       server || '|' ||
  "       waiter || '|' ||
  "       saddr || '|' ||
  "       status || '|' ||
  "       queue || '| ' ||
  "       message0 || '| ' ||
  "       message1 || '| ' ||
  "       message2 || '| ' ||
  "       message3 || '| ' ||
  "       messages || '| ' ||
  "       bytes || '| ' ||
  "       breaks || '|' ||
  "       presentation || '|' ||
  "       pcircuit || '|'
  " FROM v$circuit;
  }
 }
 call separator(1)
 call writeSql($sql)
 call separator(0,$ttl)
}

=head2 ses_procs - Sessions and Processes

Lists sessions and processes.

=cut

debug ' Inside DBA module, gathering sessions and processes'
report ses_procs
prefix
{write '---+!! Sessions and Processes'
 write '|*Thread ID*|*Background Process*|*User Name*|*OS User*|*Status*|\
         *Session ID*|*OS Program*| *Max PGA Size*|'
}
set $sql
{SELECT '|' ||
"       p.spid  || '|' ||
"       NVL(b.name, '%NULL%') || '|' ||
"       NVL(s.username, '%NULL%') || '|' ||
"       NVL(s.osuser, '%NULL%') || '|' ||
"       s.status || '|' ||
"       s.sid || '|' ||
"       NVL(s.program, '%NULL%') || '| ' ||
"       t.value || '|'
" FROM v$process p, v$bgprocess b, v$session s, v$sesstat t, v$statname n
" WHERE s.paddr = p.addr
"   AND b.paddr(+) = p.addr
"   AND t.sid = s.sid
"   AND n.name = 'session pga memory max'
"   AND t.statistic# = n.statistic#
" ORDER BY t.value DESC;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][Sessions and Processes]]'

=head2 vlicense - V$License Information

Gets C<V$License> information.

=cut

debug ' Inside DBA module, processing V$License'
report vlicense
prefix
 write '---+!! V$License'
set $sql
{SELECT '|*Max Sessions*| ' || l.sessions_max || '|' || CHR(10) ||
"       '|*Warning Sessions*| ' || l.sessions_warning || '|' || CHR(10) ||
"       '|*Current Sessions*| ' || l.sessions_current || '|' || CHR(10) ||
"       '|*Highwater Sessions*| ' || l.sessions_highwater || '|' || CHR(10) ||
"       '|*Max Users*| ' || l.users_max || '|'
" FROM v$license l;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][V$License Information]]'

=head2 vcompatibility - V$Compatibility Information

Gets C<V$Compatibility> information.

=cut

debug ' Inside DBA module, processing V$Compatibility'
report vcompatibility

if match($ORACLE_VERSION,'^(10|11|12)')
{write '---+!! V$Compatibility'
 write 'V$Compatibility does not exist in this database version.'
}
else
{prefix
 {write '---+!! V$Compatibility'
  write '|*Type ID*|*Release*|*Description*|'
 }
 set $sql
 {SELECT '|' ||
 "       c.type_id || '|' ||
 "       c.release || '|' ||
 "       c.description || '|'
 " FROM v$compatibility c
 " ORDER BY c.type_id;
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
}
if isCreated()
 toc '2:[[',getFile(),'][rda_report][V$Compatibility Information]]'

=head2 nls_parms - NLS Information

Gets NLS Parameters. Time zone did not exist until Oracle Database version
8.1.7.

=cut

debug ' Inside DBA module, processing nls parms'
debug '  TimeZone=',$tzf
report nls_parms

# Prepare the NLS parameters sections
var $TTL = '---+!! NLS Database Settings'
var @TTL = ('',\
 '---+ NLS Database Parameters',\
 '---+ NLS Instance Parameters',\
 '---+ NLS Session Parameters'\
)
var @HDR = ('',\
 '|*Parameter Name*|*Value*|',\
 '|*Parameter Name*|*Value*|',\
 '|*Parameter Name*|*Value*|'\
 )
set $sql
{SELECT '|' || n.parameter || '|' || n.value || '|'
" FROM nls_database_parameters n
" ORDER BY n.parameter;
"PROMPT ___Macro_separator(2)___
"SELECT '|' || n.parameter || '|' || NVL(n.value, '%NULL%') || '|'
" FROM nls_instance_parameters n
" ORDER BY n.parameter;
"PROMPT ___Macro_separator(3)___
"SELECT '|' || n.parameter || '|' || n.value || '|'
" FROM nls_session_parameters n
" ORDER BY n.parameter;
"PROMPT ___Macro_separator(4)___
}

# Prepare the time zone offset sections
if $tzf
{call push(@TTL,\
   '---+ Session Time Zone',\
   '---+ Database Time Zone',\
   '---+ Time Zone Definition')
 call push(@HDR,\
   '|*Time Zone*|',\
   '|*Time Zone*|',\
   '|*Version*|')
 append $sql
 {SELECT '|' || sessiontimezone || '|'
 " FROM sys.dual;
 "PROMPT ___Macro_separator(5)___
 "SELECT '|' || dbtimezone || '|'
 " FROM sys.dual;
 }
 if match($ORACLE_VERSION,'^(10|11|12)')
 {append $sql
  {PROMPT ___Macro_separator(6)___
  "SELECT '| ' || version || '|'
  " FROM v$timezone_file;
  }
 }
 elsif match($ORACLE_VERSION,'^9')
 {append $sql
  {PROMPT ___Macro_separator(6)___
  "SELECT '| ' ||
  "       CASE COUNT(DISTINCT(tzname))
  "        WHEN 183 THEN 1
  "        WHEN 355 THEN 1
  "        WHEN 347 THEN 1
  "        WHEN 377 THEN 2
  "        WHEN 186 THEN CASE COUNT(tzname)
  "                       WHEN 636 THEN 2
  "                       WHEN 626 THEN 3
  "                       ELSE 0
  "                      END
  "        WHEN 185 THEN 3
  "        WHEN 386 THEN 3
  "        WHEN 387 THEN CASE COUNT(tzname)
  "                       WHEN 1438 THEN 3
  "                       ELSE 0
  "                      END
  "        WHEN 391 THEN CASE COUNT(tzname)
  "                       WHEN 1457 THEN 4
  "                       ELSE 0
  "                      END
  "        WHEN 392 THEN CASE COUNT(tzname)
  "                       WHEN 1458 THEN 4
  "                       ELSE 0
  "                      END
  "        WHEN 188 THEN CASE COUNT(tzname)
  "                       WHEN 637 THEN 4
  "                       ELSE 0
  "                      END
  "        WHEN 189 THEN CASE COUNT(tzname)
  "                       WHEN 638 THEN 4
  "                       ELSE 0
  "                      END
  "        ELSE 0
  "       END || '|'
  " FROM v$timezone_names;
  }
 }
}
else
{call push(@TTL,\
   '---+!! Time Zone Values do not Exist in this Database Version')
 append $sql
 {PROMPT
 }
}

# Get the information
call separator(1)
call writeSql($sql)
call separator(0,'NLS Information')

=head2 vfeatureusage - Feature Usage Statistics

Gets C<DBA_Feature_Usage_Statistics> information.
(Only available for Oracle Database 10g and later.)

=head2 vfeatureinfo - Feature Information

Collects rows that have feature information from
C<DBA_Feature_Usage_Statistics>.
(Only available for Oracle Database 10g and later.)

=cut

if match($ORACLE_VERSION,'^(10|11|12)')
{debug ' Inside DBA module, processing DBA_Feature_Usage_Statistics'
 report vfeatureusage
 prefix
 {write '---+!! Database Feature Usage Statistics'
  write '|*DB ID*|*Name*|*Version*| *Detected Usages*| *Total Samples*|\
          *Currently Used*|*First Usage Date*|*Last Usage Date*| *Aux Count*|\
          *Last Sample Date*|*Last Sample Period*|*Sample Interval*|'
 }
 set $sql
 { SELECT '|' ||
 "       s.dbid || '|' ||
 "       s.name || '|' ||
 "       s.version || '| ' ||
 "       s.detected_usages || '| ' ||
 "       s.total_samples || '|' ||
 "       s.currently_used || ' |' ||
 "       TO_CHAR(s.first_usage_date,'DD-Mon-YYYY') || ' |' ||
 "       TO_CHAR(s.last_usage_date,'DD-Mon-YYYY') || ' | ' ||
 "       s.aux_count || '|' ||
 "       TO_CHAR(s.last_sample_date,'DD-Mon-YYYY') || ' | ' ||
 "       s.last_sample_period || '| ' ||
 "       s.sample_interval || '|'
 " FROM dba_feature_usage_statistics s
 " WHERE s.version = (SELECT MAX(t.version)
 "                     FROM dba_feature_usage_statistics t
 "                     WHERE t.name = s.name);
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Feature Usage Statistics]]'

 debug ' Inside DBA module, getting Feature Information'
 report vfeatureinfo
 prefix
 {write '---+!! Database Feature Information'
  write '|*DB ID*|*Name*|*Version*|*Information*|'
 }
 set $sql
 { SET long 65536
 " SELECT '[[[' || CHR(10) || '|' ||
 "       s.dbid || '|' ||
 "       s.name || '|' ||
 "       s.version || '|' ||
 "       REPLACE(REPLACE(REPLACE(LTRIM(s.feature_info),
 "         '|', '&#124;'),
 "         '<', '&lt;'),
 "         '>', '&gt;') || '|' ||
 "       CHR(10) || ']]]'
 " FROM dba_feature_usage_statistics s
 " WHERE s.feature_info IS NOT NULL
 "   AND s.version = (SELECT MAX(t.version)
 "                     FROM dba_feature_usage_statistics t
 "                     WHERE t.name = s.name);
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Feature Information]]'
}

=for stopwords HighWaterMark

=head2 vHWM_Statistic - HighWaterMark Statistics

Gets C<DBA_Highwater_Mark_Statistics> information.
(Only available for Oracle Database 10g and later.)

=cut

if match($ORACLE_VERSION,'^(10|11|12)')
{debug ' Inside DBA module, processing DBA_High_Water_Mark_Statistics'
 report vHWM_Statistic
 prefix
 {write '---+!! Database HighWaterMark Statistics'
  write '|*DB ID*|*Name*|*Version*| *Highwater*| *Last Value*|'
 }
 set $sql
 {SELECT '|' ||
 "       s.dbid || '|' ||
 "       s.name || '|' ||
 "       s.version || '| ' ||
 "       s.highwater || '| ' ||
 "       s.last_value || '|'
 " FROM dba_high_water_mark_statistics s
 " WHERE s.version = (SELECT MAX(t.version)
 "                     FROM dba_high_water_mark_statistics t
 "                     WHERE t.name=s.name);
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][HighWaterMark Statistics]]'
}

=head2 CPU_Statistic - CPU Usage Statistics

Gets C<DBA_CPU_Usage_Statistics> information.
(Only available for Oracle Database 10g R2 and later.)

=cut

if match($ORACLE_VERSION,'^(102|11|12)')
{debug ' Inside DBA module, processing DBA_CPU_Usage_Statistics'
 report CPU_Statistic
 prefix
 {write '---+!! Database CPU Usage Statistics'
  write '|*DB ID*|*Version*|*Timestamp*| *CPU Count*| *CPU Core Count*| \
         *CPU Socket Count*|'
 }
 set $sql
 {SELECT '|' ||
 "       s.dbid || '|' ||
 "       s.version || '|' ||
 "       TO_CHAR(s.timestamp,'DD-Mon-YYYY') || '| ' ||
 "       s.cpu_count || '| ' ||
 "       s.cpu_core_count || '| ' ||
 "       s.cpu_socket_count || '|'
 " FROM dba_cpu_usage_statistics s;
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][CPU Usage Statistics]]'
}

=head2 jvm_info - Java Information

Gets JVM information.
(JVM information available for Oracle Database 8i and later only.)

=cut

report jvm_info
if !match($ORACLE_VERSION,'^(7|80)$')
{var $TTL = '---+!! Java Specific Information'
 var @TTL = ('',\
  '---+ Java Objects',\
  '---+ Java Roles',\
  '---+ Oracle Supplied Java Users'\
  )
 var @HDR = ('',\
  '|*Owner*| *Total Objects*| *Invalid Objects*|',\
  '|*Role Name*|',\
  '|*User Name*|*User ID*|*Creation Date*|'\
  )
 set $sql
 {SELECT '|' ||
 "       o.owner || '| ' ||
 "       o.objects || '| ' ||
 "       NVL(i.invalids, 0) || '|'
 " FROM (SELECT owner, COUNT(*) objects
 "        FROM dba_objects
 "        WHERE object_type like 'JAVA%'
 "        GROUP by owner) o,
 "       (SELECT owner, COUNT(*) invalids
 "        FROM dba_objects
 "        WHERE object_type like 'JAVA%'
 "          AND status = 'INVALID'
 "        GROUP by owner) i
 " WHERE o.owner = i.owner(+);
 "PROMPT ___Macro_separator(2)___
 "SELECT '|' || r.role || '|'
 " FROM dba_roles r
 " WHERE role LIKE 'JAVA%';
 "PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       u.username || '|' ||
 "       u.user_id || '|' ||
 "       TO_CHAR(created, 'DD-Mon-YYYY HH24:MI:SS') || '|'
 " FROM dba_users u
 " WHERE u.username like 'OSE%'
 "    OR u.username like 'AURORA%';
 }
 call separator(1)
 call writeSql($sql)
 call separator(0,'Java Information')
}

=head2 vcontrolfile - Control File Information

Gets C<V$ControlFile> information.

=cut

debug ' Inside DBA module, processing V$ControlFile'
report vcontrolfile
prefix
{write '---+!! V$ControlFile'
 write '|*Name*|*Status*|'
}
set $sql
{SELECT '|' || v.name || ' |' || NVL(v.status, '%NULL%') || '|'
" FROM v$controlfile v;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][Control File Information]]'

=head2 log_info - Log Information

Gets C<V$Logfile> and C<V$Log> information.

=cut

debug ' Inside DBA module, processing log information'
report log_info
var $TTL = '---+!! Information from V$Logfile and V$Log'
var @TTL = ('',\
 '---+ V$Logfile',\
 '---+ V$Log'\
 )
if match($ORACLE_VERSION,'^(10|11|12)')
{var @HDR = ('',\
  '| *Group #*|*Status*|*Type*|*Member*|',\
  '| *Group #*| *Thread #*| *Sequence #*| *Bytes*| *Members*| *Archived* |\
    *Status*| *First Change #*|*First Time*|'\
  )
 set $sql
 {SELECT '| ' ||
 "       l.group# || '|' ||
 "       NVL(l.status,'%NULL%') || ' |' ||
 "       l.type || ' |' ||
 "       l.member || ' |'
 " FROM v$logfile l
 " ORDER BY l.group#, l.member;
 "PROMPT ___Macro_separator(2)___
 }
}
else
{var @HDR = ('',\
  '| *Group #*|*Status*|*Member*|',\
  '| *Group #*| *Thread #*| *Sequence #*| *Bytes*| *Members*| *Archived* |\
    *Status*| *First Change #*|*First Time*|'\
  )
 set $sql
 {SELECT '| ' ||
 "       l.group# || '|' ||
 "       NVL(l.status,'%NULL%') || '|' ||
 "       l.member || '|'
 " FROM v$logfile l
 " ORDER BY l.group#, l.member;
 "PROMPT ___Macro_separator(2)___
 }
}
if match($ORACLE_VERSION,"^7$")
{#  In Oracle 7, 'first time' is a VARCHAR2 field. We must not try
 #  to format it, otherwise it throws and invalid number error.
 append $sql
 {SELECT '| ' ||
 "       l.group# || '| ' ||
 "       l.thread# || '| ' ||
 "       l.sequence# || '| ' ||
 "       l.bytes || '| ' ||
 "       l.members || '| ' ||
 "       l.archived || ' |' ||
 "       NVL(l.status, '%NULL%') || '| ' ||
 "       l.first_change# || '|' ||
 "       l.first_time || '|'
 " FROM v$log l
 " ORDER BY l.group#, l.thread#;
 }
}
else
{append $sql
 {SELECT '| ' ||
 "       l.group# || '| ' ||
 "       l.thread# || '| ' ||
 "       l.sequence# || '| ' ||
 "       l.bytes || '| ' ||
 "       l.members || '| ' ||
 "       l.archived || ' |' ||
 "       NVL(l.status, '%NULL%') || '| ' ||
 "       l.first_change# || '|' ||
 "       TO_CHAR(l.first_time, 'DD-Mon-YYYY HH24:MI:SS') || '|'
 " FROM v$log l
 " ORDER BY l.group#, l.thread#;
 }
}
call separator(1)
call writeSql($sql)
call separator(0,'Log Information')

=head2 rollback_info - Rollback Information / undo_info - Undo Information

Gets rollback information.

=cut

if match($AUM,'^manual$',true)
{debug ' Inside DBA module, processing V$RollStat'
 report rollback_info
 var $toc = 'Rollback Information'
 var $TTL = '---+!! Information from V$RollStat and DBA_Rollback_Segs'
 var $TTL[1] = '---+ V$RollStat'
 var $HDR[1] = '| *Undo Segment Number*| *Extents*| *Segment Size*| \
                 *Active Xacts*| *Optimal*| *High Water Mark*| *Shrinks*| \
                 *Wraps*| *Extends*| *Status*| *Current Extent*| \
                 *Current Block*|'
 var $TTL[2] = '---+ DBA_Rollback_Segs'
 set $sql
 {SELECT '| ' ||
 "       v.usn || '| ' ||
 "       v.extents || '| ' ||
 "       v.rssize || '| ' ||
 "       v.xacts || '| ' ||
 "       NVL(v.optsize, 0)  || '| ' ||
 "       v.hwmsize || '| ' ||
 "       v.shrinks || '| ' ||
 "       v.wraps || '| ' ||
 "       v.extends || '|' ||
 "       v.status || '| ' ||
 "       v.curext || '| ' ||
 "       v.curblk || '|'
 " FROM v$rollstat v;
 "PROMPT ___Macro_separator(2)___
 }
 if match($ORACLE_VERSION,'^7$')
 {var $HDR[2] = \
   '|*Segment Name*| *Segment ID*|*Owner*|*Tablespace Name*| *File ID*| \
     *Block ID*| *Initial*| *Next*| *Min Extents*| *Max Extents*| \
     *PCT Increase*|*Status*| *Instance Number*|'
  append $sql
  {SELECT '|' ||
  "       v.segment_name || '| ' ||
  "       v.segment_id || '|' ||
  "       v.owner || '|' ||
  "       v.tablespace_name || '| ' ||
  "       v.file_id || '| ' ||
  "       v.block_id || '| ' ||
  "       v.initial_extent || '| ' ||
  "       v.next_extent || '| ' ||
  "       v.min_extents || '| ' ||
  "       v.max_extents || '| ' ||
  "       v.pct_increase || '|' ||
  "       v.status || '| ' ||
  "       NVL(v.instance_num, '%NULL%') || '|'
  " FROM dba_rollback_segs v;
  }
 }
 else
 {var $HDR[2] = \
   '|*Segment Name*| *Segment ID*|*Owner*|*Tablespace Name*| *File ID*| \
     *Block ID*| *Initial*| *Next*| *Min Extents*| *Max Extents*| \
     *PCT Increase*|*Status*| *Instance Number*| *Relative File Number*|'
  append $sql
  {SELECT '|' ||
  "       v.segment_name || '| ' ||
  "       v.segment_id || '|' ||
  "       v.owner || '|' ||
  "       v.tablespace_name || '| ' ||
  "       v.file_id || '| ' ||
  "       v.block_id || '| ' ||
  "       v.initial_extent || '| ' ||
  "       v.next_extent || '| ' ||
  "       v.min_extents || '| ' ||
  "       v.max_extents || '| ' ||
  "       v.pct_increase || '| ' ||
  "       v.status || '| ' ||
  "       NVL(v.instance_num, '%NULL%') || '| ' ||
  "       v.relative_fno || '|'
  " FROM dba_rollback_segs v;
  }
 }
}
else
{# We're running AUM, so look for undostat and dba_undo_extents
 debug ' Inside DBA module, processing V$UndoStat'
 report undo_info
 var $toc = 'Undo Information'
 var $TTL = '---+!! Information from V$UndoStat and DBA_Undo_Extents'
 var $TTL[1] = '---+ V$UndoStat'
 var $HDR[1] = '|*Begin Time*|*End Time*| *Undo Tablespace Number*| \
                 *Undo Blocks Used*| *Xact Count*| *Max Query Length*| \
                 *Max Concurrency*| *Unexpired Steal Count*| \
                 *Unexpired Block Release Count*| \
                 *Unexpired Block Reuse Count*| *Expired Steal Count*| \
                 *Expired Block Release Count*| *Expired Block Reuse Count*| \
                 *Snapshot Too Old Errors Count*| *No Space Errors Count*|'
 var $TTL[2] = '---+ DBA_Undo_Extents'
 var $HDR[2] = '|*Owner*|*Segment Name*|*Tablespace Name*| *Extent ID*| \
                 *File ID*| *Block ID*| *Bytes*| *Blocks*| \
                 *Relative File Number*|*Commit Time (Julian)*|\
                 *Commit Time (Wall Clock)*|*Status*|'
 set $sql
 {SELECT '|' ||
 "       TO_CHAR(v.begin_time, 'DD-Mon-YYYY HH24:MI:SS') || '|' ||
 "       TO_CHAR(v.end_time, 'DD-Mon-YYYY HH24:MI:SS') || '| ' ||
 "       v.undotsn || '| ' ||
 "       v.undoblks || '| ' ||
 "       v.txncount || '| ' ||
 "       v.maxquerylen || '| ' ||
 "       v.maxconcurrency || '| ' ||
 "       v.unxpstealcnt || '| ' ||
 "       v.unxpblkrelcnt || '| ' ||
 "       v.unxpblkreucnt || '| ' ||
 "       v.expstealcnt || '| ' ||
 "       v.expblkrelcnt || '| ' ||
 "       v.expblkreucnt || '| ' ||
 "       v.ssolderrcnt || '| ' ||
 "       v.nospaceerrcnt || '|'
 " FROM v$undostat v;
 "PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       v.owner || '|' ||
 "       v.segment_name || '|' ||
 "       v.tablespace_name || '| ' ||
 "       v.extent_id || '| ' ||
 "       v.file_id || '| ' ||
 "       v.block_id || '| ' ||
 "       v.bytes || '| ' ||
 "       v.blocks || '| ' ||
 "       v.relative_fno || '|' ||
 "       NVL(TO_CHAR(v.commit_jtime), '%NULL%') || '|' ||
 "       NVL(v.commit_wtime, '%NULL%') || '|' ||
 "       v.status || '|'
 " FROM dba_undo_extents v;
 }
}
call separator(1)
call writeSql($sql)
call separator(0,$toc)

=head2 database_properties - Database Properties

Gets database properties.
(Only for Oracle Database 9i and later.)

=cut

if match($ORACLE_VERSION,'^(9|10|11|12)')
{debug ' Inside DBA module, processing Database_Properties'
 report database_properties
 prefix
 {write '---+!! Database Properties'
  write '|*Property Name*|*Property Value*|'
 }
 set $sql
 {SELECT '|' || p.property_name || '|' || p.property_value || ' |'
 " FROM database_properties p;
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Database Properties]]'
}

=head2 vsystem_event - V$System_Event

Gets C<V$System_Event> information.

=cut

debug ' Inside DBA module, processing V$System_Event'
report vsystem_event
prefix
{write '---+!! V$System_Event'
 write '|*Wait Event*| *Waits*| *Timeouts*| *Time Waited*| *Average Wait*|'
}
set $sql
{SELECT '|' ||
"       s.event || '| ' ||
"       s.total_waits || '| ' ||
"       s.total_timeouts || '| ' ||
"       s.time_waited || '| ' ||
"       ROUND(s.average_wait,2) || '|'
" FROM v$system_event s
" ORDER BY s.time_waited desc, s.event;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][V$System_Event]]'

=head2 vresource_limit - V$Resource_Limit

Gets C<V$Resource_Limit> information.

=cut

debug ' Inside DBA module, processing V$Resource_Limit'
report vresource_limit
prefix
{write '---+!! V$Resource_Limit'
 write '|*Resource Name*| *Current Utilization*| *Max Utilization*| \
         *Initial Allocation*| *Limit Value*|'
}
set $sql
{SELECT '|' ||
"       l.resource_name || '| ' ||
"       l.current_utilization || '| ' ||
"       l.max_utilization || '| ' ||
"       l.initial_allocation || '| ' ||
"       l.limit_value || '|'
" FROM v$resource_limit l;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][V$Resource_Limit]]'

=head2 vsession_wait - V$Session_Wait

Gets C<V$Session_Wait> information.

=cut

debug ' Inside DBA module, processing V$Session_Wait'
report vsession_wait
prefix
{write '---+!! Summary of Sessions Currently Waiting (from V$Session_Wait)'
 write '|*Wait Event*| *Total Sessions Waiting*|'
}
set $sql
{SELECT '|' || w.event || '| ' || COUNT(*) || '|'
" FROM v$session_wait w
" WHERE w.wait_time = 0
" GROUP BY w.event
" ORDER BY COUNT(*) DESC;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][V$Session_Wait]]'

=head2 latch_info - Latch Information

Gets C<V$Latch> and C<V$LatchHolder> information.

=cut

debug ' Inside DBA module, processing latch information'
report latch_info
var $TTL = '---+!! Latch Information'
var @TTL = ('',\
 '---+ V$Latch',\
 '---+ V$LatchHolder'\
 )
var @HDR = ('',\
 '| *Latch #*|*Name*| *Gets*| *Misses*| *Sleeps*| \
         *Immediate Gets*| *Immediate Misses*|',\
 '| *PID #*|*SID*|*Latch Address*|*Name*|'\
 )
set $sql
{SELECT '|' ||
"       l.latch# || '|' ||
"       l.name || '| ' ||
"       l.gets || '| ' ||
"       l.misses || '| ' ||
"       l.sleeps || '| ' ||
"       l.immediate_gets || '| ' ||
"       l.immediate_misses || '|'
" FROM v$latch l
" ORDER BY l.sleeps DESC;
"PROMPT ___Macro_separator(2)___
"SELECT '| ' ||
"       l.pid|| '|' ||
"       l.sid || '|' ||
"       l.laddr || '|' ||
"       l.name || '|'
" FROM v$latchholder l;
}
call separator(1)
call writeSql($sql)
call separator(0,'Latch Information')

=head2 tablespace - Tablespaces

Gets tablespace information.

=cut

report tablespace

if match($ORACLE_VERSION,'^7$')
{# V7 Tablespaces
 debug ' Inside DBA module, processing tablespaces for V7'
 prefix
 {write '---+!! Tablespaces'
  write '|*Status*|*Name*|*Type*| *Size (MiB)*| *Used (MiB)*| *Used (%)*| \
          *Initial Extent*| *Next Extent*| *Largest Free Extent*| \
          *Minimum Extents*| *Maximum Extents*| *Increase (%)*|'
 }
 set $sql
 {SELECT '|' ||
 "       d.status || '|' ||
 "       d.tablespace_name || '|' ||
 "       d.contents || '| ' ||
 "       TO_CHAR(NVL(a.bytes / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1048576 ,
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
 "               '990D00') || '| ' ||
 "       d.initial_extent || '| ' ||
 "       NVL(d.next_extent, 0) || '| ' ||
 "       TO_CHAR(NVL(f.largest_free / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       d.min_extents || '| ' ||
 "       d.max_extents || '| ' ||
 "       d.pct_increase || '|'
 " FROM sys.dba_tablespaces d,
 "      (SELECT tablespace_name,
 "              SUM(bytes) bytes
 "        FROM dba_data_files
 "        GROUP BY tablespace_name
 "      ) a,
 "      (SELECT tablespace_name,
 "              SUM(bytes) bytes,
 "              MAX(bytes) largest_free
 "        FROM dba_free_space
 "        GROUP BY tablespace_name
 "      ) f
 " WHERE d.tablespace_name = a.tablespace_name
 "   AND d.tablespace_name = f.tablespace_name(+);
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Tablespaces]]'
}
elsif match($ORACLE_VERSION,'^80$')
{# V8.0 Tablespaces
 debug ' Inside DBA module, processing tablespaces for V8.0'
 prefix
 {write '---+!! Tablespaces'
  write '|*Status*|*Name*|*Type*| *Size (MiB)*| *Used (MiB)*| *Used (%)*| \
          *Initial Extent*| *Next Extent*| *Largest Free Extent*| \
          *Minimum Extents*| *Maximum Extents*| *Minimum Extent Length*| \
          *Increase (%)*|'
 }
 set $sql
 {SELECT '|' ||
 "       d.status || '|' ||
 "       d.tablespace_name || '|' ||
 "       d.contents || '| ' ||
 "       TO_CHAR(NVL(a.bytes / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1048576,
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
 "                '990D00') || '| ' ||
 "       d.initial_extent || '| ' ||
 "       NVL(d.next_extent, 0) || '| ' ||
 "       TO_CHAR(NVL(f.largest_free / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       d.min_extents || '| ' ||
 "       d.max_extents || '| ' ||
 "       NVL(TO_CHAR(d.min_extlen,
 "                   '99G999G999G999'), '%NULL%') || '| ' ||
 "       pct_increase || '|'
 " FROM sys.dba_tablespaces d,
 "      (SELECT tablespace_name, SUM(bytes) bytes
 "        FROM dba_data_files
 "        GROUP BY tablespace_name
 "      ) a,
 "      (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest_free
 "        FROM dba_free_space
 "        GROUP BY tablespace_name
 "      ) f
 " WHERE d.tablespace_name = a.tablespace_name
 "   AND d.tablespace_name = f.tablespace_name(+);
 }
 call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][Tablespaces]]'
}
elsif match($ORACLE_VERSION,'^81$')
{# V8.1 Tablespaces
 debug ' Inside DBA module, processing tablespaces for V8.1'
 var $TTL = '---+!! Tablespaces'
 var @TTL = ('',\
  '---+ Permanent Tablespaces',\
  '---+ Locally Managed Temporary Tablespaces')
 var @HDR = ('',\
  '|*Status*|*Name*|*Type*|*Extent Management*| *Size (MiB)*| \
    *Used (MiB)*| *Used (%)*| *Initial Extent*| *Next Extent*| \
    *Largest Free Extent*| *Minimum Extents*| *Maximum Extents*| \
    *Minimum Extent Length*| *Increase (%)*|',\
  '|*Status*|*Name*| *Size (MiB)*| *Minimum Extents*| *Maximum Extents*| \
    *Minimum Extent Length*| *Increase (%)*|')
 set $sql
 {SELECT '|' ||
 "       d.status || '|' ||
 "       d.tablespace_name || '|' ||
 "       d.contents || '|' ||
 "       d.extent_management || '| ' ||
 "       TO_CHAR(NVL(a.bytes / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1048576 ,
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
 "               '990D00') || '| ' ||
 "       d.initial_extent || '| ' ||
 "       NVL(d.next_extent, 0) || '| ' ||
 "       TO_CHAR(NVL(f.largest_free / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       d.min_extents || '| ' ||
 "       NVL(TO_CHAR(d.max_extents,
 "                   '99G999G999G999'), '%NULL%') || '| ' ||
 "       NVL(TO_CHAR(d.min_extlen,
 "                   '99G999G999G999'), '%NULL%') || '| ' ||
 "       NVL(TO_CHAR(pct_increase,
 "                   '999'),'%NULL%') || '|'
 " FROM sys.dba_tablespaces d,
 "      (SELECT tablespace_name, SUM(bytes) bytes
 "        FROM dba_data_files
 "        GROUP BY tablespace_name
 "      ) a,
 "      (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest_free
 "        FROM dba_free_space
 "        GROUP BY tablespace_name
 "      ) f
 " WHERE d.tablespace_name = a.tablespace_name
 "   AND d.tablespace_name = f.tablespace_name(+);
 "PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       d.status || '|' ||
 "       d.tablespace_name || '|' ||
 "       TO_CHAR(NVL(a.bytes / 1048576, 0), '99G999G990D900')|| '|' ||
 "       d.min_extents || '|' ||
 "       NVL(TO_CHAR(d.max_extents, '99G999G999G999'), '%NULL%') || '|' ||
 "       NVL(TO_CHAR(d.min_extlen, '99G999G999G999'), '%NULL%') || '|' ||
 "       NVL(TO_CHAR(pct_increase, '999'), '%NULL%') || '|'
 " FROM sys.dba_tablespaces d,
 "      (SELECT tablespace_name, SUM(bytes) bytes
 "        FROM dba_temp_files
 "        GROUP BY tablespace_name
 "      ) a
 " WHERE d.tablespace_name = a.tablespace_name;
 }
 call separator(1)
 call writeSql($sql)
 call separator(0,'Tablespaces')
}
elsif match($ORACLE_VERSION,'^(9|10|11|12)')
{# V9/10/11/12 Tablespaces
 debug ' Inside DBA module, processing tablespaces for 9i and later'
 var $TTL = '---+!! Tablespaces'
 var @TTL = ('',\
  '---+ Permanent Tablespaces',\
  '---+ Locally Managed Temporary Tablespaces',\
  '---+ Tablespace Groups')
 var @HDR = ('',\
  '|*Status*|*Name*|*Type*|*Extent Management*| *Segment Space Management*| \
    *Size (MiB)*| *Used (MiB)*| *Used (%)*| *Initial Extent*| *Next Extent*| \
    *Largest Free Extent*| *Minimum Extents*| *Maximum Extents*| \
    *Minimum Extent Length*| *Increase (%)*|',\
  '|*Status*|*Name*| *Size (MiB)*| *Minimum Extents*| *Maximum Extents*| \
    *Minimum Extent Length*| *Increase (%)*|',\
  '|*Group Name*|*Tablespace Name*|')
 set $sql
 {SELECT '|' ||
 "       d.status || '|' ||
 "       d.tablespace_name || '|' ||
 "       d.contents || '|' ||
 "       d.extent_management || '|' ||
 "       d.segment_space_management || '| ' ||
 "       TO_CHAR(NVL(a.bytes / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL(a.bytes - NVL(f.bytes, 0), 0) / 1048576 ,
 "               '99G999G990D900') || '| ' ||
 "       TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0),
 "               '990D00') || '| ' ||
 "       d.initial_extent || '| ' ||
 "       NVL(d.next_extent, 0) || '| ' ||
 "       TO_CHAR(NVL(f.largest_free / 1048576, 0),
 "               '99G999G990D900') || '| ' ||
 "       d.min_extents || '| ' ||
 "       NVL(TO_CHAR(d.max_extents,
 "                   '99G999G999G999'), '%NULL%') || '| ' ||
 "       NVL(TO_CHAR(d.min_extlen,
 "                   '99G999G999G999'), '%NULL%') || '| ' ||
 "       NVL(TO_CHAR(pct_increase,
 "                   '999'),'%NULL%') || '|'
 " FROM sys.dba_tablespaces d,
 "      (SELECT tablespace_name, SUM(bytes) bytes
 "        FROM dba_data_files
 "        GROUP BY tablespace_name
 "      ) a,
 "      (SELECT tablespace_name, SUM(bytes) bytes, MAX(bytes) largest_free
 "        FROM dba_free_space
 "        GROUP BY tablespace_name
 "      ) f
 " WHERE d.tablespace_name = a.tablespace_name
 "   AND d.tablespace_name = f.tablespace_name(+);
 "PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       d.status || '|' ||
 "       d.tablespace_name || '|' ||
 "       TO_CHAR(NVL(a.bytes / 1048576, 0), '99G999G990D900')|| '|' ||
 "       d.min_extents || '|' ||
 "       NVL(TO_CHAR(d.max_extents, '99G999G999G999'), '%NULL%') || '|' ||
 "       NVL(TO_CHAR(d.min_extlen, '99G999G999G999'), '%NULL%') || '|' ||
 "       NVL(TO_CHAR(pct_increase, '999'), '%NULL%') || '|'
 " FROM sys.dba_tablespaces d,
 "      (SELECT tablespace_name, SUM(bytes) bytes
 "        FROM dba_temp_files
 "        GROUP BY tablespace_name
 "      ) a
 " WHERE d.tablespace_name = a.tablespace_name;
 }
 if match($ORACLE_VERSION,'^(10|11|12)')
 {append $sql
  {PROMPT ___Macro_separator(3)___
  "SELECT '|' ||
  "       group_name || ' |' ||
  "       tablespace_name || ' |'
  " FROM sys.dba_tablespace_groups;
  }
 }
 call separator(1)
 call writeSql($sql)
 call separator(0,'Tablespaces')
}

=head2 datafile - Database Files

Gets data file information.

=cut

report datafile

if match($ORACLE_VERSION,'^7$')
{# V7 Datafiles
 debug ' Inside DBA module, processing datafiles for V7'
 write '---+!! Datafile Information'
 write '|*Status*|*Name*|*Tablespace*| *File Number*| *Size (MiB)*| \
         *Used (MiB)*| *Used (%)*|*Autoextensible*|'
 set $sql
 {SELECT '|' ||
 "       v.status || '|' ||
 "       d.file_name || '|' ||
 "       d.tablespace_name || '| ' ||
 "       d.file_id || '|' ||
 "       TO_CHAR(d.bytes / 1048576,
 "               '99999990D000') || '|' ||
 "       TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0),
 "               '99999990D000') || '|' ||
 "       TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100),
 "               '990D00') || '|' ||
 "       NVL(f.autoextensible, 'NO') || '|'
 " FROM sys.dba_data_files d,
 "      v$datafile v,
 "      (SELECT file_id,
 "              SUM(bytes) bytes
 "        FROM sys.dba_free_space
 "        GROUP BY file_id
 "      ) s,
 "      (select file#,
 "              'YES' autoextensible
 "        from sys.filext$
 "      ) f
 " WHERE s.file_id(+) = d.file_id
 "   AND d.file_name = v.name
 "   AND f.file#(+) = d.file_id;
 }
 call writeSql($sql,2)
 if getSqlMessage()
  write last
}
elsif match($ORACLE_VERSION,'^80$')
{# V8.0 Datafiles
 debug ' Inside DBA module, processing datafiles for V8.0'
 write '---+!! Datafile Information'
 write '|*Status*|*Name*|*Tablespace*| *File Number*| *Relative File Number*| \
         *Size (MiB)*| *Used (MiB)*| *Used (%)*|*Autoextensible*| *Max Blocks*|'
 set $sql
 {SELECT '|' ||
 "       v.status || '|' ||
 "       d.file_name || '|' ||
 "       d.tablespace_name || '| ' ||
 "       d.file_id || '| ' ||
 "       d.relative_fno || '| ' ||
 "       TO_CHAR((d.bytes / 1048576),
 "               '99999990D000') || '| ' ||
 "       TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0),
 "               '99999990D000') || '| ' ||
 "       TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100),
 "               '990D00') || '|' ||
 "       NVL(d.autoextensible, 'NO') || '| ' ||
 "       d.maxblocks || '|'
 " FROM sys.dba_data_files d,
 "      v$datafile v,
 "      (SELECT file_id, SUM(bytes) bytes
 "        FROM sys.dba_free_space
 "        GROUP BY file_id
 "      ) s
 " WHERE s.file_id(+) = d.file_id
 "   AND d.file_name = v.name;
 }
 call writeSql($sql,2)
 if getSqlMessage()
  write last
}
elsif match($ORACLE_VERSION,'^(81$|9|10|11|12)')
{# 8i/9i/10g/11g/12c Datafiles
 debug ' Inside DBA module, processing datafiles from 8i and later'
 write '---+!! Datafile Information'
 write $TOC

 write '---+ Database Files'
 write '|*Status*|*Name*|*Tablespace*|*File Number*| *Relative File Number*| \
         *Size (MiB)*| *Used (MiB)*| *Used (%)*|*Autoextensible*| *Max Blocks*|'
 var @sep1 = (\
  $TOP,\
  '---+ TempFiles',\
  '|*Status*|*Name*|*Tablespace*| *File Number*| *Relative File Number*| \
    *Size (MiB)*| *Max Blocks*|'\
  )
 set $sql
 {SELECT '|' ||
 "       v.status || '|' ||
 "       d.file_name || '|' ||
 "       d.tablespace_name || '| ' ||
 "       d.file_id || '| ' ||
 "       d.relative_fno || '| ' ||
 "       TO_CHAR((d.bytes / 1048576),
 "               '99999990D000') || '| ' ||
 "       TO_CHAR(NVL((d.bytes - NVL(s.bytes, 0)) / 1048576, 0),
 "               '99999990D000') || '| ' ||
 "       TO_CHAR((NVL(d.bytes - s.bytes, d.bytes) / d.bytes * 100),
 "               '990D00') || '|' ||
 "       NVL(d.autoextensible, 'NO') || '| ' ||
 "       d.maxblocks || '|'
 " FROM sys.dba_data_files d,
 "      v$datafile v,
 "      (SELECT file_id, SUM(bytes) bytes
 "        FROM sys.dba_free_space
 "        GROUP BY file_id
 "      ) s
 " WHERE s.file_id(+) = d.file_id
 "   AND d.file_name = v.name;
 "PROMPT ___Separator(sep1)___
 "SELECT '|' ||
 "       v.status || '|' ||
 "       d.file_name || '|' ||
 "       d.tablespace_name || '| ' ||
 "       d.file_id || '| ' ||
 "       d.relative_fno || '| ' ||
 "       TO_CHAR((d.bytes / 1048576), '99999990D000') || '| ' ||
 "       d.maxblocks || '|'
 " FROM sys.dba_temp_files d,
 "      v$tempfile v
 " WHERE (d.file_name = v.name);
 }
 call writeSql($sql,4)
 if getSqlMessage()
  write last,'%BR%'
 write $TOP
}
if isCreated()
 toc '2:[[',getFile(),'][rda_report][Database Files]]'

=head2 replication - Replication Information

Gets replication data.

=cut

debug ' Inside DBA module, processing replication data'
report replication
var $TTL = '---+!! Replication Information'

var @TTL = ('',\
 '---+ DBA_RepSites Information',\
 '---+ DBA_RepGroup Information',\
 '---+ DBA_RepObject Information',\
 '---+ DBA_RepCatlog Information',\
 '---+ Replication Environment Transaction Counts'\
 )
var @HDR = ('',\
 '|*Group Name*|*DB Link*|*Master Site (Y/N)*|*Master Definition DB Link*|',\
 '|*Schema Name*|*Master Site (Y/N)*|*Status*|*Group Name*|',\
 '|*Schema Name*|*Object Name*|*Status*|*Generation Status*|*Group Name*|',\
 '|*Status*|*ID*|*Object Name*|*Object Type Status*|*Error Number*|'\
 )
set $sql
{SELECT '|' ||
"       r.gname || '|' ||
"       r.dblink || '|' ||
"       r.master || '|' ||
"       r.masterdef || '|'
" FROM sys.dba_repsites r;
"PROMPT ___Macro_separator(2)___
"SELECT '|' ||
"       r.sname || '|' ||
"       r.master || '|' ||
"       r.status || '|' ||
"       r.gname || '|'
" FROM sys.dba_repgroup r;
"PROMPT ___Macro_separator(3)___
"SELECT '|' ||
"       r.sname || '|' ||
"       r.oname || '|' ||
"       r.status || '|' ||
"       r.generation_status || '|' ||
"       r.gname || '|'
" FROM sys.dba_repobject r;
"PROMPT ___Macro_separator(4)___
"SELECT '|' ||
"       r.status || '|' ||
"       r.id || '|' ||
"       r.oname || '|' ||
"       r.type || '|' ||
"       r.errnum || '|'
" FROM sys.dba_repcatlog r;
"PROMPT ___Macro_separator(5)___
"SELECT '|*DefCall Count*| ' || COUNT(*) || '|'
" FROM sys.defcall;
"SELECT '|*DefTran Count*| ' || COUNT(*) || '|'
" FROM sys.deftran;
"SELECT '|*DefCallDest Count*| ' || COUNT(*) || '|'
" FROM sys.defcalldest;
"SELECT '|*DefTranDest Count*| ' || COUNT(*) || '|'
 FROM sys.deftrandest;
"SELECT '|*DefError Count*| ' || COUNT(*) || '|'
" FROM sys.deferror;
}
call separator(1)
call writeSql($sql)
call separator(0,'Replication Information')

=head2 jobs - DBA Jobs Information

Gets C<DBA_Jobs> information.

=cut

debug ' Inside DBA module, gathering dba_jobs data'
report jobs
prefix
{write '---+!! DBA_Jobs'
 write '|*Job Number*|*Logged In User*|*Privilege User*|*Schema User*|\
         *Date Last Run*|*Date Next Run*|*Broken(Y/N)*|*Failures*|*Interval*|\
         *What*|'
}
set $sql
{SELECT '|' ||
"       j.job || '|' ||
"       j.log_user || '|' ||
"       j.priv_user || '|' ||
"       j.schema_user || '|' ||
"       NVL(TO_CHAR(j.last_date, 'DD-Mon-YYYY HH24:MI:SS'),
"           '''''never''''') || '|' ||
"       TO_CHAR(j.next_date, 'DD-Mon-YYYY HH24:MI:SS') || '|' ||
"       j.broken || '|' ||
"       j.failures || '|' ||
"       j.interval || '|' ||
"       REPLACE(REPLACE(j.what, '|', '&#124;'), CHR(10), '%BR%') || '|'
" FROM sys.dba_jobs j;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][DBA Jobs Information]]'

=head2 services - DBA Services Information

Gets C<DBA_Services> information.

=cut

debug ' Inside DBA module, gathering dba_services data'
report services
if match($ORACLE_VERSION,'^101$')
{prefix
 {write '---+!! DBA_Services'
  write '| *Service Identifier*|*Name*|*Network Name*|*Failover Method*|\
           *Failover Type*| *Failover Retries*|'
 }
 set $sql
 {SELECT '| ' ||
 "       service_id || '|' ||
 "       name || ' |' ||
 "       network_name || ' |' ||
 "       failover_method || ' |' ||
 "       failover_type || ' | ' ||
 "       failover_retries || '|'
 " FROM dba_services;
 }
}
elsif match($ORACLE_VERSION,'^(102|11|12)$')
{prefix
 {write '---+!! DBA_Services'
  write '| *Service Identifier*|*Name*|*Network Name*|*Failover Method*|\
           *Failover Type*| *Failover Retries*|*Goal*|\
           *Distributed Transaction Processing*|*Enabled*|\
           *AQ HA Notifications*|*Connection Load Balancing Goal*|'
 }
 set $sql
 {SELECT '| ' ||
 "       service_id || '|' ||
 "       name || ' |' ||
 "       network_name || ' |' ||
 "       failover_method || ' |' ||
 "       failover_type || ' | ' ||
 "       failover_retries || '|' ||
 "       goal || ' |' ||
 "       dtp || ' |' ||
 "       enabled || ' |' ||
 "       aq_ha_notifications || ' |' ||
 "       clb_goal || ' |'
 " FROM dba_services;
 }
}
else
 var $sql = undef
if $sql
{call writeSql($sql)
 if getSqlMessage()
  write last
 if isCreated(true)
  toc '2:[[',getFile(),'][rda_report][DBA Services Information]]'
}

=head2 sequences - DBA Sequences Information

Gets C<DBA_Sequences> information.

=cut

debug ' Inside DBA module, gathering dba_sequences data'
report sequences
prefix
{write '---+!! DBA_Sequences'
 write '|*Sequence Owner*|*Sequence Name*| *Increment By*|*Order Flag*| \
         *Cache Size*|'
}
set $sql
{SELECT '|' ||
"       sequence_owner || ' |' ||
"       sequence_name || ' | ' ||
"       increment_by || '|' ||
"       order_flag || ' | ' ||
"       cache_size || '|'
" FROM dba_sequences
" WHERE sequence_owner NOT LIKE '%SYS'
"   AND sequence_owner NOT LIKE 'SYS%'
"   AND sequence_owner NOT IN ('XDB','DBSNMP','WK_TEST')
" ORDER BY sequence_owner, cache_size;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][DBA Sequences Information]]'

=head2 security - Auditing and Password Information

Gets database data for security/passwords/auditing.

=cut

report security
debug ' Inside DBA module, gathering security data'
var $TTL = '---+!! Information Related to Passwords and Auditing'
var @TTL = ('',\
 '---+ V$PWFile_Users Information',\
 '---+ DBA_Obj_Audit_Opts Information',\
 '---+ DBA_Stmt_Audit_Opts Information',\
 '---+ DBA_Priv_Audit_Opts Information',\
 '---+ All_Def_Audit_Opts Information',\
 '---+ DBA_Profiles Information',\
 '---+ DBA_Users Information'\
 )
var @HDR = ('',\
 '|*User Name*|*SYSDBA Privs*|*SYSOPER Privs (Y/N)*|',\
 '',\
 '',\
 '',\
 '|*Alter*|*Audit*|*Comment*|*Delete*|*Grant*|*Index*|*Insert*|*Lock*|\
   *Rename*|*Select*|*Update*|*Reference*|*Execute*|',\
 '|*Profile*|*Resource Name*|*Limit*|',\
 '|*User Name*|*Lock Date*|*Expiry Date*|*Profile*|'\
 )
set $sql
{SELECT '|' ||
"       p.username || '|' ||
"       NVL(p.sysdba, '%NULL%') || '|' ||
"       NVL(p.sysoper, '%NULL%') || '|'
" FROM v$pwfile_users p
" ORDER BY p.username;
"PROMPT ___Macro_separator(2)___
}
if match($ORACLE_VERSION,'^(80|81|90)$')
{var $HDR[2] = '|*Owner*|*Object Name*|*Object Type*|*Alter*|*Audit*|\
                 *Comment*|*Delete*|*Grant*|*Index*|*Insert*|*Lock*|\
                 *Rename*|*Select*|*Update*|*Reference*|*Create Name*|\
                 *Read Name*|*Write Name*|*Execute*|'
 append $sql
 {SELECT '|' ||
 "       NVL(o.owner, '%NULL%') || '|' ||
 "       NVL(o.object_name, '%NULL%') || '|' ||
 "       NVL(o.object_type, '%NULL%') || '|' ||
 "       NVL(o.alt, '%NULL%') || '|' ||
 "       NVL(o.aud, '%NULL%') || '|' ||
 "       NVL(o.com, '%NULL%') || '|' ||
 "       NVL(o.del, '%NULL%') || '|' ||
 "       NVL(o.gra, '%NULL%') || '|' ||
 "       NVL(o.ind, '%NULL%') || '|' ||
 "       NVL(o.ins, '%NULL%') || '|' ||
 "       NVL(o.loc, '%NULL%') || '|' ||
 "       NVL(o.ren, '%NULL%') || '|' ||
 "       NVL(o.sel, '%NULL%') || '|' ||
 "       NVL(o.upd, '%NULL%') || '|' ||
 "       NVL(o.ref, '%NULL%') || '|' ||
 "       NVL(o.cre, '%NULL%') || '|' ||
 "       NVL(o.rea, '%NULL%') || '|' ||
 "       NVL(o.wri, '%NULL%') || '|' ||
 "       NVL(o.exe, '%NULL%') || '|'
 " FROM sys.dba_obj_audit_opts o
 " WHERE (o.alt NOT IN ('-/-', '/  ')
 "     OR o.aud NOT IN ('-/-', '/  ')
 "     OR o.com NOT IN ('-/-', '/  ')
 "     OR o.del NOT IN ('-/-', '/  ')
 "     OR o.gra NOT IN ('-/-', '/  ')
 "     OR o.ind NOT IN ('-/-', '/  ')
 "     OR o.ins NOT IN ('-/-', '/  ')
 "     OR o.loc NOT IN ('-/-', '/  ')
 "     OR o.ren NOT IN ('-/-', '/  ')
 "     OR o.sel NOT IN ('-/-', '/  ')
 "     OR o.upd NOT IN ('-/-', '/  ')
 "     OR o.ref NOT IN ('-/-', '/  ')
 "     OR o.cre NOT IN ('-/-', '/  ')
 "     OR o.rea NOT IN ('-/-', '/  ')
 "     OR o.wri NOT IN ('-/-', '/  ')
 "     OR o.exe NOT IN ('-/-', '/  '))
 "   AND object_name NOT LIKE 'SYS_IOT_OVER%';
 }
}
elsif match($ORACLE_VERSION,'^(92$|10|11|12)')
{var $HDR[2] = '|*Owner*|*Object Name*|*Object Type*|*Alter*|*Audit*|\
                 *Comment*|*Delete*|*Grant*|*Index*|*Insert*|*Lock*|\
                 *Rename*|*Select*|*Update*|*Reference*|*Read Name*|\
                 *Execute*|'
 append $sql
 {SELECT '|' ||
 "       NVL(o.owner, '%NULL%') || '|' ||
 "       NVL(o.object_name, '%NULL%') || '|' ||
 "       NVL(o.object_type, '%NULL%') || '|' ||
 "       NVL(o.alt, '%NULL%') || '|' ||
 "       NVL(o.aud, '%NULL%') || '|' ||
 "       NVL(o.com, '%NULL%') || '|' ||
 "       NVL(o.del, '%NULL%') || '|' ||
 "       NVL(o.gra, '%NULL%') || '|' ||
 "       NVL(o.ind, '%NULL%') || '|' ||
 "       NVL(o.ins, '%NULL%') || '|' ||
 "       NVL(o.loc, '%NULL%') || '|' ||
 "       NVL(o.ren, '%NULL%') || '|' ||
 "       NVL(o.sel, '%NULL%') || '|' ||
 "       NVL(o.upd, '%NULL%') || '|' ||
 "       NVL(o.ref, '%NULL%') || '|' ||
 "       NVL(o.rea, '%NULL%') || '|' ||
 "       NVL(o.exe, '%NULL%') || '|'
 " FROM sys.dba_obj_audit_opts o
 " WHERE (o.alt NOT IN ('-/-', '/  ')
 "     OR o.aud NOT IN ('-/-', '/  ')
 "     OR o.com NOT IN ('-/-', '/  ')
 "     OR o.del NOT IN ('-/-', '/  ')
 "     OR o.gra NOT IN ('-/-', '/  ')
 "     OR o.ind NOT IN ('-/-', '/  ')
 "     OR o.ins NOT IN ('-/-', '/  ')
 "     OR o.loc NOT IN ('-/-', '/  ')
 "     OR o.ren NOT IN ('-/-', '/  ')
 "     OR o.sel NOT IN ('-/-', '/  ')
 "     OR o.upd NOT IN ('-/-', '/  ')
 "     OR o.ref NOT IN ('-/-', '/  ')
 "     OR o.rea NOT IN ('-/-', '/  ')
 "     OR o.exe NOT IN ('-/-', '/  '))
 "   AND object_name NOT LIKE 'SYS_IOT_OVER%';
 }
}
else
{var $HDR[2] = '|*Owner*|*Object Name*|*Object Type*|*Alter*|*Audit*|\
                 *Comment*|*Delete*|*Grant*|*Index*|*Insert*|*Lock*|\
                 *Rename*|*Select*|*Update*|*Reference*|*Execute*|'
 append $sql
 {SELECT '|' ||
 "       NVL(o.owner, '%NULL%') || '|' ||
 "       NVL(o.object_name, '%NULL%') || '|' ||
 "       NVL(o.object_type, '%NULL%') || '|' ||
 "       NVL(o.alt, '%NULL%') || '|' ||
 "       NVL(o.aud, '%NULL%') || '|' ||
 "       NVL(o.com, '%NULL%') || '|' ||
 "       NVL(o.del, '%NULL%') || '|' ||
 "       NVL(o.gra, '%NULL%') || '|' ||
 "       NVL(o.ind, '%NULL%') || '|' ||
 "       NVL(o.ins, '%NULL%') || '|' ||
 "       NVL(o.loc, '%NULL%') || '|' ||
 "       NVL(o.ren, '%NULL%') || '|' ||
 "       NVL(o.sel, '%NULL%') || '|' ||
 "       NVL(o.upd, '%NULL%') || '|' ||
 "       NVL(o.ref, '%NULL%') || '|' ||
 "       NVL(o.exe, '%NULL%') || '|'
 " FROM sys.dba_obj_audit_opts o
 " WHERE (o.alt NOT IN ('-/-', '/  ')
 "     OR o.aud NOT IN ('-/-', '/  ')
 "     OR o.com NOT IN ('-/-', '/  ')
 "     OR o.del NOT IN ('-/-', '/  ')
 "     OR o.gra NOT IN ('-/-', '/  ')
 "     OR o.ind NOT IN ('-/-', '/  ')
 "     OR o.ins NOT IN ('-/-', '/  ')
 "     OR o.loc NOT IN ('-/-', '/  ')
 "     OR o.ren NOT IN ('-/-', '/  ')
 "     OR o.sel NOT IN ('-/-', '/  ')
 "     OR o.upd NOT IN ('-/-', '/  ')
 "     OR o.ref NOT IN ('-/-', '/  ')
 "     OR o.exe NOT IN ('-/-', '/  '))
 "   AND object_name NOT LIKE 'SYS_IOT_OVER%';
 }
}
if match($ORACLE_VERSION,'^(81$|9|10|11|12)')
{var $HDR[3] = '|*User Name*|*Proxy Name*|*Audit Option*|*Success*|*Failure*|'
 var $HDR[4] = '|*User Name*|*Proxy Name*|*Privilege*|*Success*|*Failure*|'
 append $sql
 {PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       NVL(s.user_name, '%NULL%') || '|' ||
 "       NVL(s.proxy_name, '%NULL%') || '|' ||
 "       s.audit_option || '|' ||
 "       NVL(s.success, '%NULL%') || '|' ||
 "       NVL(s.failure, '%NULL%') || '|'
 " FROM sys.dba_stmt_audit_opts s;
 "PROMPT ___Macro_separator(4)___
 "SELECT '|' ||
 "       NVL(p.user_name, '%NULL%') || '|' ||
 "       NVL(p.proxy_name, '%NULL%') || '|' ||
 "       p.privilege || '|' ||
 "       NVL(p.success, '%NULL%') || '|' ||
 "       NVL(p.failure, '%NULL%') || '|'
 " FROM sys.dba_priv_audit_opts p;
 }
}
else
{var $HDR[3] = '|*User Name*|*Audit Option*|*Success*|*Failure*|'
 var $HDR[4] = '|*User Name*|*Privilege*|*Success*|*Failure*|'
 append $sql
 {PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       NVL(s.user_name, '%NULL%') || '|' ||
 "       s.audit_option || '|' ||
 "       NVL(s.success, '%NULL%') || '|' ||
 "       NVL(s.failure, '%NULL%') || '|'
 " FROM sys.dba_stmt_audit_opts s;
 "PROMPT ___Macro_separator(4)___
 "SELECT '|' ||
 "        NVL(p.user_name, '%NULL%') || '|' ||
 "        p.privilege || '|' ||
 "        NVL(p.success, '%NULL%') || '|' ||
 "        NVL(p.failure, '%NULL%') || '|'
 " FROM sys.dba_priv_audit_opts p;
 }
}
append $sql
{PROMPT ___Macro_separator(5)___
"SELECT '|' ||
"       NVL(a.alt, '%NULL%') || '|' ||
"       NVL(a.aud, '%NULL%') || '|' ||
"       NVL(a.com, '%NULL%') || '|' ||
"       NVL(a.del, '%NULL%') || '|' ||
"       NVL(a.gra, '%NULL%') || '|' ||
"       NVL(a.ind, '%NULL%') || '|' ||
"       NVL(a.ins, '%NULL%') || '|' ||
"       NVL(a.loc, '%NULL%') || '|' ||
"       NVL(a.ren, '%NULL%') || '|' ||
"       NVL(a.sel, '%NULL%') || '|' ||
"       NVL(a.upd, '%NULL%') || '|' ||
"       NVL(a.ref, '%NULL%') || '|' ||
"       NVL(a.exe, '%NULL%') || '|'
" FROM sys.all_def_audit_opts a;
}
if match($ORACLE_VERSION,'^(8|9|10|11|12)')
{append $sql
 {PROMPT ___Macro_separator(6)___
 "SELECT '|' ||
 "       p.profile || '|' ||
 "       p.resource_name || '|' ||
 "       NVL(p.limit,'%NULL%') || '|'
 " FROM sys.dba_profiles p
 " WHERE p.resource_type = 'PASSWORD'
 " ORDER BY p.profile;
 "PROMPT ___Macro_separator(7)___
 "SELECT '|' ||
 "       u.username || '|' ||
 "       NVL(TO_CHAR(u.lock_date,'DD-Mon-YYYY HH24:MI:SS'),'%NULL%') || '|' ||
 "       NVL(TO_CHAR(u.expiry_date,'DD-Mon-YYYY HH24:MI:SS'),'%NULL%') || '|' ||
 "       u.profile || '|'
 " FROM sys.dba_users u
 " WHERE u.lock_date IS NOT NULL
 "    OR u.expiry_date IS NOT NULL
 " ORDER BY u.username;
 }
}
call separator(1)
call writeSql($sql)
if hasOutput()
 write 'NOTE: Only display users who have expiration date set.%BR%'
call separator(0,'Auditing and Password Information')

=head2 invalids - Invalid Objects

Gets invalid objects.

=cut

debug ' Inside DBA module, processing invalid objects'
report invalids
prefix
{write '---+!! Invalid Objects for ',$ORACLE_SID
 write '|*Owner*|*Object_Name*|*Object_Type*|*Created*|*Last_DDL_Time*|\
         *Status*|'
}
set $sql
{SELECT '|' ||
"       o.owner || '|' ||
"       o.object_name || '|' ||
"       o.object_type || '|' ||
"       TO_CHAR(o.created,'DD-Mon-YYYY HH24:MI:SS') || '|' ||
"       TO_CHAR(o.last_ddl_time,'DD-Mon-YYYY HH24:MI:SS') || '|' ||
"       o.status || '|'
" FROM sys.dba_objects o
" WHERE status != 'VALID'
" ORDER BY o.owner, o.object_name;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][Invalid Objects]]'

=head2 all_errors - All Errors

Gets DBA_Errors information.

=cut

debug ' Inside DBA module, processing dba_errors'
report all_errors
prefix
{write '---+!! Errors for Invalid Objects for ',$ORACLE_SID
 write '|*Owner*|*Object Name*|*Line/Pos*|*Error Text*|'
}
set $sql
{SELECT '|' ||
"       e.owner || '|' ||
"       e.name || '|' ||
"       TO_CHAR(e.line) || '/' || TO_CHAR(e.position) || '|' ||
"       REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(e.text),
"        '|','&#124;'),
"        '<','&lt;'),
"        '>','&gt;'),
"        CHR(10),'%BR%') || '|'
" FROM dba_errors e
" ORDER BY e.owner, e.name, e.sequence;
}
call writeSql($sql)
if getSqlMessage()
 write last
if isCreated(true)
 toc '2:[[',getFile(),'][rda_report][All Errors]]'

=head2 security_files - Security Related Directory Listings and File Contents

Lists the data for O/S authentication and password files. It is performed only
for databases associated with the current Oracle home.

=cut

if $LOCAL
{debug ' Inside DBA module, doing cat of files to check O/S authentication'
 report security_files
 write '---+!! File Content and \
               Directory Listings Related to Security Configuration'
 write $TOC

 if or(isUnix(),isVms())
 {define ($osn,$dba,$opr) = (getOsName())
  if compare('eq',$osn,'solaris')
  {write '---+ Group Related Data from $ORACLE_HOME/rdbms/lib/config.s'
   if loadFile(catFile($ORACLE_HOME,'rdbms','lib','config.s'))
   {write '<verbatim>'
    write 'Group data is:'
    var ($str) = grepLastFile('(ascii|string)','f')
    write $str
    var $dba = field('"',1,$str)
    write '</verbatim>'
   }
   else
    write 'Could not read $ORACLE_HOME/rdbms/lib/config.s%BR%'
  }
  elsif compare('eq',$osn,'aix')
  {write '---+ Group Related Data from $ORACLE_HOME/rdbms/lib/config.s'
   if loadFile(catFile($ORACLE_HOME,'rdbms','lib','config.s'))
   {write '<verbatim>'
    write 'Group data is:'
    var ($str) = grepLastFile('string','f')
    write $str
    var $dba = field('"',1,$str)
    write '</verbatim>'
   }
   else
    write 'Could not read $ORACLE_HOME/rdbms/lib/config.c%BR%'
  }
  else # dec_osf, hpux, dynixptx
  {var $cfg = cond(isVms(),'config.s','config.c')
   write '---+ Group Related Data from $ORACLE_HOME/rdbms/lib/',$cfg
   if loadFile(catFile($ORACLE_HOME,'rdbms','lib',$cfg))
   {write '<verbatim>'
    write 'Group data is:'
    var ($str) = grepLastFile('#define.*SS_DBA_GRP','f')
    write $str
    var $dba = field('"',1,$str)
    var ($str) = grepLastFile('#define.*SS_OPER_GRP','f')
    write $str
    var $opr = field('"',1,$str)
    write '</verbatim>'
   }
   else
    write 'Could not read $ORACLE_HOME/rdbms/lib/',$cfg,'%BR%'
  }
  write $TOP

  write '---+ /etc/group Oracle Group Settings'
  write '<verbatim>'
  if !$dba
   write 'Missing configuration data to perform this search'
  elsif !loadFile('/etc/group')
   write 'Could not read /etc/group'
  else
  {write 'dba Group record from /etc/group:'
   write grepLastFile(concat('^',$dba,':'),'f')
   if compare('ne',$dba,$opr)
   {write 'oper Group group from /etc/group:'
    write grepLastFile(concat('^',$opr,':'),'f')
   }
   else
    write 'Note:  dba and oper groups are the same'
  }
  write '</verbatim>'
  write $TOP
 }

 write '---+ $ORACLE_HOME/dbs Directory Listing'
 if !statDir('n',catDir($ORACLE_HOME,'dbs'))
  write 'Could not read $ORACLE_HOME/dbs'
 write $TOP

 if isUnix()
 {prefix
   write '---+ UNIX File Permissions on /etc/passwd'
  call statFile('b','/etc/passwd')
  if hasOutput(true)
   write $TOP
 }
 toc '2:[[',getFile(),\
  '][rda_report][Security Related Directory Listings and File Contents]]'
}

=head2 spatial - Spatial Information

Gets spatial information. (Only available for Oracle Database 8i and later.)

=cut

report spatial
var $TTL = '---+!! Spatial Information'
if match($ORACLE_VERSION,'^(92$|10|11|12)')
{# Version 9.2.x,10.x,11.x Spatial queries
 debug ' Inside DBA module, gathering Spatial data'
 var @TTL = ('',\
             '---+ Spatial Version and Status',\
             '---+ Number of Installed Spatial Objects',\
             '---+ List of Invalid Spatial Objects',\
             '---+ Spatial Objects Owned by MDSYS and SYS',\
             '---+ Spatial Table Metadata',\
             '---+ Spatial LRS Metadata',\
             '---+ Invalid Domain Indexes',\
             '---+ Spatial Index Metadata',\
             '---+ Verify SDO_ROOT_MBR Column being the Last Column',\
             '---+ Spatial Network Metadata',\
             '---+ Topology Metadata',\
             '---+ Georaster Metadata')
 var @TXT = ()
 var @HDR = ('',\
             '| *Version*|*Status*|',\
             '| *Spatial Objects*|',\
             '|*Object Name*|*Object Type*|*Status*|',\
             '|*Object Name*|*Object Type*|',\
             '|*Owner*|*Table Name*|*Column Name*| *SRID*|',\
             '|*Owner*|*Table Name*|*Column Name*| *Dim Pos*|',\
             '|*Owner*|*Index Name*|*Domain Index Status*|\
               *Domain Index Operation Status*|*Functional Index Status*|\
               *Status*|',\
             '|*SDO Index Owner*|*SDO Index Name*|*SDO Column Name*|\
               *SDO Index Type*| *DML Batch Size*|*Geodetic ?*| \
               *Index Version*|',\
             '|*Name*|*Type*|',\
             '|*Owner*|*Network Name*|*Node Table Name*|*Link Table Name*|\
               *Path Table Name*|',\
             '|*Owner*|*Topology*|*Table Name*|*Column Name*|',\
             '|*Owner*|*Table Name*|*Column Name*|*RDT Table Name*|')
 var $TXT[4] = '**Remove the objects from SYS.**'
 set $sql
 {SELECT '| ' ||
 "       version || '|' ||
 "       status || '|'
 " FROM dba_registry
 " WHERE comp_id = 'SDO';
 "PROMPT ___Macro_separator(2)___
 "SELECT '| ' ||
 "       COUNT(*) || '|'
 " FROM dba_objects
 " WHERE owner = 'MDSYS';
 "PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       o.object_name || ' |' ||
 "       o.object_type || ' |' ||
 "       o.status || ' |'
 " FROM dba_objects o
 " WHERE owner = 'MDSYS'
 "   AND status <> 'VALID'
 " ORDER BY object_type;
 "PROMPT ___Macro_separator(4)___
 "SELECT '|' ||
 "       a.object_name || ' |' ||
 "       a.object_type || ' |'
 " FROM dba_objects a,dba_objects b
 " WHERE a.object_name = b.object_name
 "   AND a.object_type = b.object_type
 "   AND a.owner = 'SYS'
 "   AND b.owner = 'MDSYS';
 "PROMPT ___Macro_separator(5)___
 "SELECT '|' ||
 "       m.owner || ' |' ||
 "       m.table_name || ' |' ||
 "       m.column_name || ' | ' ||
 "       m.srid || '|'
 " FROM all_sdo_geom_metadata m;
 "PROMPT ___Macro_separator(6)___
 "SELECT '|' ||
 "       m.owner || ' |' ||
 "       m.table_name || ' |' ||
 "       m.column_name || ' | ' ||
 "       m.dim_pos || '|'
 " FROM all_sdo_lrs_metadata m;
 "PROMPT ___Macro_separator(7)___
 "SELECT '|' ||
 "       owner || ' |' ||
 "       index_name || ' |' ||
 "       domidx_status || ' |' ||
 "       domidx_opstatus || ' |' ||
 "       funcidx_status || ' |' ||
 "       status || ' |'
 " FROM dba_indexes
 " WHERE domidx_opstatus != 'VALID'
 "    OR domidx_status != 'VALID';
 }
 if match($ORACLE_VERSION,'^92$')
 {var $HDR[8] = '|*SDO Index Owner*|*SDO Index Name*|*SDO Column Name*|\
                  *SDO Index Type*|*Geodetic ?*| *Indexversion*|'
  append $sql
  {PROMPT ___Macro_separator(8)___
  "SELECT '|' ||
  "       m.sdo_index_owner || ' |' ||
  "       m.sdo_index_name || ' |' ||
  "       m.sdo_column_name || ' |' ||
  "       m.sdo_index_type || ' |' ||
  "       m.sdo_index_geodetic || ' | ' ||
  "       m.sdo_index_version || '|'
  " FROM all_sdo_index_metadata m;
  }
 }
 else
 {var $TXT[9] = '**See %MOS_DOC:342280.1%**'
  append $sql
  {PROMPT ___Macro_separator(8)___
  "SELECT '|' ||
  "       m.sdo_index_owner || ' |' ||
  "       m.sdo_index_name || ' |' ||
  "       m.sdo_column_name || ' |' ||
  "       m.sdo_index_type || ' | ' ||
  "       m.sdo_dml_batch_size || '|' ||
  "       m.sdo_index_geodetic || ' | ' ||
  "       m.sdo_index_version || '|'
  " FROM all_sdo_index_metadata m;
  "PROMPT ___Macro_separator(9)___
  " SELECT '|' ||
  "        c.column_name || ' |' ||
  "        c.data_type || ' |'
  " FROM dba_tab_columns c
  " WHERE table_name = 'SDO_INDEX_METADATA_TABLE'
  "   AND owner = 'MDSYS'
  "   AND column_id > 33
  " ORDER BY column_id;
  "PROMPT ___Macro_separator(10)___
  "SELECT '|' ||
  "       owner || ' |' ||
  "       network || ' |' ||
  "       node_table_name || ' |' ||
  "       link_table_name || ' |' ||
  "       path_table_name || ' |'
  " FROM all_sdo_network_metadata;
  "PROMPT ___Macro_separator(11)___
  "SELECT '|' ||
  "       owner || ' |' ||
  "       topology || ' |' ||
  "       table_name || ' |' ||
  "       column_name || ' |'
  " FROM all_sdo_topo_metadata;
  "PROMPT ___Macro_separator(12)___
  "SELECT '|' ||
  "       owner || ' |' ||
  "       table_name || ' |' ||
  "       column_name || ' |' ||
  "       rdt_table_name || ' |'
  " FROM all_sdo_geor_sysdata;
  }
 }
 if match($ORACLE_VERSION,'^(11|12)$')
 {call push(@TTL,\
            '---+ Semantic Models Defined in the Database',\
            '---+ Rulebases',\
            '---+ Database Objects Used in Rules Indexes',\
            '---+ Rules Indexes',\
            '---+ SEM_INDEXTYPE Indexes')
  call push(@HDR,\
            '|*Owner*| *Model ID*|*Model Name*|*Table Name*|*Column Name*|',\
            '|*Owner*|*RuleBase Name*|*RuleBase View Name*|*Status*|',\
            '|*Index Name*|*Data Type*|*Data Name*|',\
            '|*Owner*|*Index Name*|*Index View Name*|*Status*| \
              *Model Count*| *RuleBase Count*|',\
            '|*Owner*|*Index Name*|')
  var (undef,$col) = getSqlColumns('RDA','MDSYS','SEM_MODEL$')
  call clearSqlColumns('RDA')
  if $col
  {append $sql
   {PROMPT ___Macro_separator(13)___
   "SELECT '|' ||
   "       owner || ' | ' ||
   "       model_id || '|' ||
   "       model_name || ' |' ||
   "       table_name || ' |' ||
   "       column_name || ' |'
   " FROM mdsys.sem_model$
   " ORDER BY owner;
   "PROMPT ___Macro_separator(14)___
   "SELECT '|' ||
   "       owner || ' |' ||
   "       rulebase_name || ' |' ||
   "       rulebase_view_name || ' |' ||
   "       status || ' |'
   " FROM mdsys.sem_rulebase_info
   " ORDER BY owner;
   "PROMPT ___Macro_separator(15)___
   "SELECT '|' ||
   "       index_name || ' |' ||
   "       data_type || ' |' ||
   "       data_name || ' |'
   " FROM mdsys.sem_rules_index_datasets
   " ORDER BY index_name,data_type;
   "PROMPT ___Macro_separator(16)___
   "SELECT '|' ||
   "       owner || ' |' ||
   "       index_name || ' |' ||
   "       index_view_name || ' |' ||
   "       status || ' | ' ||
   "       model_count || '| ' ||
   "       rulebase_count || '|'
   " FROM mdsys.sem_rules_index_info
   " ORDER BY owner;
   }
  }
  append $sql
  {PROMPT ___Macro_separator(17)___
  "SELECT '|' ||
  "       owner || ' |' ||
  "       index_name || ' |'
  " FROM dba_indexes
  " WHERE ityp_name = 'SEM_INDEXTYPE'
  " ORDER BY owner;
  }
 }
 call separator(1)
 call writeSql($sql)
 call separator(0,'Spatial Information')
 var @TXT = ()
}
elsif match($ORACLE_VERSION,'^(81|90|92)$')
{# Version 8.1,9.x Spatial queries
 debug ' Inside DBA module, gathering Spatial data'
 var @TTL = ('',\
  '---+ Spatial Version',\
  '---+ List and Status of installed Spatial Objects',\
  '---+ Privileges on Spatial Objects',\
  '---+ Privileges owned by MDSYS',\
  '---+ All Spatial Table Metadata',\
  '---+ All Spatial Index Metadata'\
  )
 var @HDR = ('',\
  '|*SDO Version*|',\
  '|*Object Name*|*Object Type*|*Status*|',\
  '|*Grantor*|*Grantee*|*Table_name*|*Privilege*|*Grantable*|',\
  '|*Privilege*|*Admin Option*|',\
  '|*Owner*|*Table Name*|*Column Name*|*SRID*|',\
  '|*SDO Index Owner*|*SDO Index Name*|*SDO Column Name*|*SDO Index Type*|'\
  )
 if match($ORACLE_VERSION,'^8')
 {set $sql
  {SELECT '|' || sdo_admin.sdo_version || '|'
  " FROM dual;
  }
 }
 else
 {set $sql
  {SELECT '|' || mdsys.sdo_version || '|'
  " FROM dual;
  }
 }
 append $sql
 {PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       o.object_name || '|' ||
 "       o.object_type || '|' ||
 "       o.status || '|'
 " FROM dba_objects o
 " WHERE owner = 'MDSYS'
 " ORDER BY object_name;
 "PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       p.grantor || '|' ||
 "       p.grantee || '|' ||
 "       p.table_name || '|' ||
 "       p.privilege || '|' ||
 "       p.grantable || '|'
 " FROM all_tab_privs p
 " WHERE table_schema = 'MDSYS'
 " ORDER BY table_name, privilege;
 "PROMPT ___Macro_separator(4)___
 "SELECT '|' ||
 "       p.privilege || '|' ||
 "       p.admin_option || '|'
 " FROM dba_sys_privs p
 " WHERE grantee='MDSYS'
 " ORDER BY privilege;
 "PROMPT ___Macro_separator(5)___
 "SELECT '|' ||
 "       m.owner || ' |' ||
 "       m.table_name || ' |' ||
 "       m.column_name || ' |' ||
 "       m.srid || ' |'
 " FROM all_sdo_geom_metadata m;
 "PROMPT ___Macro_separator(6)___
 "SELECT '|' ||
 "       m.sdo_index_owner || '|' ||
 "       m.sdo_index_name || '|' ||
 "       m.sdo_column_name || '|' ||
 "       m.sdo_index_type || '|'
 " FROM all_sdo_index_metadata m;
 }
 call separator(1)
 call writeSql($sql)
 call separator(0,'Spatial Information')
}

=head2 aq_data - Advanced Queuing Information

Collects Advanced Queuing (AQ) information. AQ is available in 8.0 and later.
It can be manually installed in 8.0 and 8i, and is automatically installed in
Oracle Database 9i. For the manual installations, a quick check is performed
on a predetermined item to see if the product is installed. If it is, then
data is gathered; if not, then a message is displayed to indicate that AQ is not
installed.

=cut

report aq_data
debug ' Inside DBA module, about to gather AQ data'
var $TTL = '---+!! Advanced Queuing Data'
if match($ORACLE_VERSION,'^7')
{# AQ was not part of V7
 write $TTL
 write 'Advanced Queuing was not available in this release'
}
if match($ORACLE_VERSION,'^(8|9|10|11|12)')
{var @TTL = ('',\
  '---+ Table Privileges for AQ Roles',\
  '---+ Queue Privileges Data',\
  '---+ DBA Queue Schedules Data',\
  '---+ AQ$ Queue Schedules Data',\
  '---+ Queue Tables Data',\
  '---+ Queue Status Data',\
  '---+ AQ$ Queue Tables Data',\
  '---+ AQ$ Queues Data',\
  '---+ DBA Rules Data',\
  '---+ DBA Rule Sets Data',\
  '---+ DBA Rule Set Rules Data',\
  '---+ DBA Evaluation Contexts Data',\
  '---+ Invalid Rows Data',\
  '---+ Queue Statistics',\
  '---+ Pending Message Counts',\
  '---+ AQ$ Message Counts'\
  )
 var @HDR = ('',\
  '',\
  '|*Grantee*|*Owner*|*Name*|*Grantor*|*Enqueue Privilege*|\
    *Dequeue Privilege*|',\
  '|*Schema*|*Queue Name*|*Destination*|*Last Run Date*|*Next Run Date*|\
    *Schedule Disabled*|*Session ID*|*Total Number*|*Failures*|\
    *Last Error Date*|*Last Error Message*|',\
  '|*OID*|*Job Number*|*Destination ID*|',\
  '|*Owner*|*Queue Table*|*Type*|*Object Type*|*Sort Order*|\
    *Recipients*|*Message Grouping*|',\
  '|*Owner*|*Name*|*Waiting*|*Ready*|*Expired*|',\
  '|*Schema*|*Name*|*Object Number*|',\
  '|*Table Object Number*|*Name*|*Event ID*|',\
  '|*Rule Owner*|*Rule Name*|*Rule Evaluation Context Owner*|\
    *Rule Evaluation Context Name*|',\
  '|*Rule Set Owner*|*Rule Set Name*|*Rule Set Evaluation Context Owner*|\
    *Rule Set Evaluation Context Name*|',\
  '|*Rule Set Owner*|*Rule Set Name*|*Rule Owner*|*Rule Name*|*Rule Enabled*|\
    *Rule Set Evaluation Context Owner*|*Rule Set Evaluation Context Name*|',\
  '|*Evaluation Context Owner*|*Evaluation Context Name*|',\
  '|*Queue ID*|*Destination*|*Sequence*|*Status*|',\
  '|*Name*|*Ready*|*Waiting*|*Expired*|*Total Wait*|',\
  '|*Count*|',\
  '|*Count*|'\
  )
 if match($ORACLE_VERSION,'^(80|81)$')
 {var $HDR[1] = '|*Grantee*|*Owner*|*Table Name*|*Grantor*|*Privilege*|\
                  *Grantable*|'
  set $sql
  {SELECT '|' ||
  "       p.grantee || '|' ||
  "       p.owner || '|' ||
  "       p.table_name || '|' ||
  "       p.grantor || '|' ||
  "       p.privilege || '|' ||
  "       p.grantable || '|'
  " FROM dba_tab_privs p
  " WHERE p.grantee IN ('AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE')
  " ORDER BY grantee ;
  }
 }
 else
 {var $HDR[1] = '|*Grantee*|*Owner*|*Table Name*|*Grantor*|*Privilege*|\
                  *Grantable*|*Hierarchy*|'
  set $sql
  {SELECT '|' ||
  "       p.grantee || '|' ||
  "       p.owner || '|' ||
  "       p.table_name || '|' ||
  "       p.grantor || '|' ||
  "       p.privilege || '|' ||
  "       p.grantable || '|' ||
  "       p.hierarchy || '|'
  " FROM dba_tab_privs p
  " WHERE p.grantee IN ('AQ_ADMINISTRATOR_ROLE', 'AQ_USER_ROLE')
  " ORDER BY grantee ;
  }
 }
 append $sql
 {PROMPT ___Macro_separator(2)___
 "SELECT '|' ||
 "       p.grantee || '|' ||
 "       p.owner || '|' ||
 "       p.name || '|' ||
 "       p.grantor || '|' ||
 "       p.enqueue_privilege || '|' ||
 "       p.dequeue_privilege || '|'
 " FROM queue_privileges p;
 "PROMPT ___Macro_separator(3)___
 "SELECT '|' ||
 "       s.schema || '|' ||
 "       s.qname || '|' ||
 "       s.destination || '|' ||
 "       TO_CHAR(s.last_run_date, 'DD-Mon-YYYY HH24:MI:SS') || '|' ||
 "       TO_CHAR(s.next_run_date, 'DD-Mon-YYYY HH24:MI:SS') || '|' ||
 "       s.schedule_disabled || '|' ||
 "       s.session_id || '|' ||
 "       s.total_number || '|' ||
 "       s.failures || '|' ||
 "       TO_CHAR(s.last_error_date, 'DD-Mon-YYYY HH24:MI:SS') || '|' ||
 "       s.last_error_msg || '|'
 " FROM dba_queue_schedules s
 " ORDER BY s.schema;
 "PROMPT ___Macro_separator(4)___
 "SELECT '|' ||
 "       s.oid || '|' ||
 "       s.jobno || '|' ||
 "       s.destination || '|'
 " FROM aq$_schedules s;
 "PROMPT ___Macro_separator(5)___
 "SELECT '|' ||
 "       t.owner || '|' ||
 "       t.queue_table || '|' ||
 "       t.type || '|' ||
 "       NVL(t.object_type,'%NULL%') || '|' ||
 "       t.sort_order || '|' ||
 "       t.recipients || '|' ||
 "       t.message_grouping || '|'
 " FROM dba_queue_tables t;
 "PROMPT ___Macro_separator(6)___
 "SELECT '|' ||
 "       q.owner || '|' ||
 "       q.name || '|' ||
 "       a.waiting || '|' ||
 "       a.ready || '|' ||
 "       a.expired || '|'
 " FROM dba_queues q, v$aq a
 " WHERE q.qid = a.qid;
 "PROMPT ___Macro_separator(7)___
 "SELECT '|' ||
 "       t.schema || '|' ||
 "       t.name || '|' ||
 "       t.objno || '|'
 " FROM system.aq$_queue_tables t;
 "PROMPT ___Macro_separator(8)___
 }
 if match($ORACLE_VERSION,'^92$')
 {append $sql
  {SELECT '|' ||
  "       q.table_objno || '|' ||
  "       q.name || '|' ||
  "       q.eventid || '|'
  " FROM system.aq$_queues q;
  "PROMPT ___Macro_separator(9)___
  "SELECT '|' ||
  "       r.rule_owner || '|' ||
  "       r.rule_name || '|' ||
  "       r.rule_evaluation_context_owner || '|' ||
  "       r.rule_evaluation_context_name || '|'
  " FROM dba_rules r;
  "PROMPT ___Macro_separator(10)___
  "SELECT '|' ||
  "       s.rule_set_owner || '|' ||
  "       s.rule_set_name || '|' ||
  "       s.rule_set_eval_context_owner || '|' ||
  "       s.rule_set_eval_context_name || '|'
  " FROM dba_rule_sets s;
  "PROMPT ___Macro_separator(11)___
  "SELECT '|' ||
  "       r.rule_set_owner || '|' ||
  "       r.rule_set_name || '|' ||
  "       r.rule_owner || '|' ||
  "       r.rule_name || '|' ||
  "       r.rule_set_rule_enabled || '|' ||
  "       r.rule_set_rule_eval_ctx_owner || '|' ||
  "       r.rule_set_rule_eval_ctx_name || '|'
  " FROM dba_rule_set_rules r;
  "PROMPT ___Macro_separator(12)___
  "SELECT '|' ||
  "       c.evaluation_context_owner || '|' ||
  "       c.evaluation_context_name || '|'
  " FROM dba_evaluation_contexts c;
  "PROMPT ___Macro_separator(13)___
  "SELECT '|' ||
  "       s.queue_id || '|' ||
  "       s.destination || '|' ||
  "       s.sequence || '|' ||
  "       s.status || '|'
  " FROM sys.aq$_propagation_status s
  " WHERE NOT EXISTS (SELECT NULL
  "                    FROM dba_queues q
  "                    WHERE q.qid = s.queue_id);
  "PROMPT ___Macro_separator(14)___
  "SELECT '|' ||
  "       name || '|' ||
  "       ready || '|' ||
  "       waiting || '|' ||
  "       expired || '|' ||
  "       total_wait || '|'
  " FROM dba_queues q, gv$aq a
  " WHERE a.qid = q.qid;
  "PROMPT ___Macro_separator(15)___
  "SELECT '|' || COUNT(*) || '|'
  " FROM sys.aq$_pending_messages;
  "PROMPT ___Macro_separator(16)___
  "SELECT '|' || COUNT(*) || '|'
  " FROM sys.aq$_schedules s
  " WHERE NOT EXISTS (SELECT NULL
  "                    FROM system.aq$_queues q
  "                    WHERE s.oid = q.oid);
  }
 }
}
call separator(1)
call writeSql($sql)
call separator(0,'Advanced Queuing Information')

=head2 mgw_files_data - Messaging Gateway Files Data

First, RDA determines if and what version of the product is installed. Gateway
is new to Oracle Database 9i and is manually setup. Because it is a manual
setup, RDA performs a quick check for the C<MGW_ADMINISTRATOR_ROLE> role to see
if the product is installed.

When the role is found, it gets Message Gateway-related files.

=head2 mgw_db_data - Messaging Gateway Database Data

Gets next Message Gateway database information.

=cut

debug ' Inside DBA module, about to gather message gateway data'
if match($ORACLE_VERSION,'^(9|10|11|12)')
{# First, let's check to see if message gateway is installed.....
 set $sql
 {SELECT role
 " from dba_roles
 " where role = 'MGW_ADMINISTRATOR_ROLE';
 }
 var $str = grepSql($sql,'MGW_ADMINISTRATOR_ROLE','f')

 # If installed, collect information
 if $str
 {# OK, we assume it's installed, let's go get everything
  if $LOCAL
  {report mgw_files_data
   var $TTL = '---+!! Messaging Gateway Database Data'
   var @TTL = ('',\
    '---+ MGW Initialization File',\
    '---+ MGW Logging Information'\
    )
   var @HDR = ()

   call separator(1)
   var $fil = catFile($ORACLE_HOME,'mgw','admin','mgw.ora')
   var $dft = catDir($ORACLE_HOME,'mgw','log')
   if writeFile($fil)
   {var $log = value(grepFile($fil,'^[^#]*log_directory','fi'))
    var $log = replace($log,"'",'',true)
    if $log
    {var $log = replace($log,'\?',$ORACLE_HOME)
     var $log = replace($log,'\$ORACLE_HOME',$ORACLE_HOME)
     var $log = replace($log,'\$\{ORACLE_HOME\}',$ORACLE_HOME)
     var $log = replace($log,'%ORACLE_HOME%',$ORACLE_HOME)
    }
    else
     var $log = $dft
   }
   else
   {write 'No readable mgw.ora file found.%BR%\
           May be file permission problems or file does not exist.%BR%\
           Permissions are:%BR%'
    call statFile('p',$fil)
    write 'User: ',id(),'%BR%'
    var $log = $dft
   }

   call separator(2)
   var @tbl = grepDir($log,'^oramgw.*\.log$','it')
   if @tbl
   {write
    prefix
     # Empty prefix
    var $cnt = 3
    loop $fil (@tbl)
    {var $TTL[3] = concat('---++ ',encode($fil))
     var $fil = catFile($log,$fil)
     call separator(3)
     if !writeTail($fil,500)
     {write "Cannot read the log file found.%BR%\
             Permissions are:%BR%"
      call statFile('p',$fil)
      write 'User: ',id(),'%BR%'
     }
     decr $cnt
     break !$cnt
    }
   }
   elsif ?testDir('d',$log)
   {write 'No log files found.%BR%\
           Directory contains:%BR%'
    call statDir('n',$log)
   }
   call separator(0,'Messaging Gateway Files Data')
  }

  #  Now let's get data from the database
  report mgw_db_data
  var $TTL = '---+!! Messaging Gateway Database Data'
  var @TTL = ('',\
   '---+ MGW Gateway Data',\
   '---+ MGW Links Data',\
   '---+ MGW MQSeries_Links Data',\
   '---+ MGW Foreign_Queues Data',\
   '---+ MGW Subscribers Data',\
   '---+ MGW Schedules Data'\
   )
  var @HDR = ('',\
   '|*Agent Status*|*Agent Ping*|*Agent Job*|*Agent User*|*Agent Database*|\
     *Last Error Date*|*Last Error Time*|*Last Error Message*| \
     *Max Connections*| *Max Memory*|',\
   '|*Link Name*|*Link Type*|',\
   '|*Link Name*|*Queue Manager*|*Channel*|*Hostname*|',\
   '|*Name*|*Link Name*|*Provider Queue*|',\
   '|*Subscriber ID*|*Queue Name*|*Propagated Messages*|\
     *Exception Queue Messages*|',\
   '|*Schedule ID*|*Schedule Disabled*|'\
   )
  set $sql
  {SELECT '|' ||
  "       agent_status || '|' ||
  "       NVL(agent_ping,'%NULL%') || '|' ||
  "       NVL(TO_CHAR(agent_job),'%NULL%') || '|' ||
  "       NVL(agent_user,'%NULL%') || '|' ||
  "       NVL(agent_database,'%NULL%') || '|' ||
  "       NVL(TO_CHAR(last_error_date, 'DD-Mon-YYYY HH24:MI:SS'),
  "           '%NULL%') || '|' ||
  "       NVL(last_error_time,'%NULL%') || '|' ||
  "       NVL(last_error_msg, '%NULL%') || '| ' ||
  "       max_connections || '| ' ||
  "       max_memory || '|'
  " FROM mgw_gateway;
  "PROMPT ___Macro_separator(2)___
  "SELECT '|' ||
  "       link_name || '|' ||
  "       link_type || '|'
  " FROM mgw_links;
  "PROMPT ___Macro_separator(3)___
  "SELECT '|' ||
  "       link_name || '|' ||
  "       queue_manager || '|' ||
  "       channel || '|' ||
  "       hostname || '|'
  " FROM mgw_mqseries_links;
  "PROMPT ___Macro_separator(4)___
  "SELECT '|' ||
  "       name || '|' ||
  "       link_name || '|' ||
  "       provider_queue || '|'
  " FROM mgw_foreign_queues;
  "PROMPT ___Macro_separator(5)___
  "SELECT '|' ||
  "       subscriber_id || '|' ||
  "       queue_name || '|' ||
  "       propagated_msgs || '|' ||
  "       exceptionq_msgs || '|'
  " FROM mgw_subscribers;
  "PROMPT ___Macro_separator(6)___
  "SELECT '|' ||
  "       schedule_id || '|' ||
  "       schedule_disabled || '|'
  " FROM mgw_schedules;
  }
  call separator(1)
  call writeSql($sql)
  call separator(0,'Messaging Gateway Database Data')
 }
}

=head2 partition_data - Partitioned Object Data

Collects partitioning data. Partitioning is a new option to Oracle Database 8i.

=cut

report partition_data
debug ' Inside DBA module, about to gather partitioning data'
if match($ORACLE_VERSION,'^(8|9|10|11|12)')
{# Check if installed
 set $sql
 {SELECT 'X'
 " FROM v$option
 " WHERE parameter LIKE 'Part%' AND
 "       value = 'TRUE';
 }
 if grepSql($sql,'X','if')
 {# Check if partitioning is in use
  set $sql
  {SELECT table_name
  " FROM dba_tables
  " WHERE partitioned = 'YES' AND
  "       ROWNUM < 2;
  }
  if grepSql($sql,'\w')
  {var $TTL = '---+!! Partitioned Object Data'
   var @TTL = ('',\
    '---+ Partitioned Tables from DBA_Tables',\
    '---+ DBA_Part_Tables Data',\
    '---+ DBA_Tab_Partitions Data',\
    '---+ DBA_Tab_Subpartitions Data',\
    '---+ DBA_Indexes Data',\
    '---+ DBA_Part_Indexes Data',\
    '---+ DBA_Ind_Partitions Data',\
    '---+ DBA_Ind_Subpartitions Data')
   var @HDR = ('',\
    '|*Table Owner*|*Table Name*|',\
    '|*Table Owner*|*Table Name*|*Partitioning Type*|*Subpartitioning Type*| \
      *Partition Count*| *Partitioning Key Count*| *Subpartitioning Key Count*|\
      *Default Tablespace Name*|*Default Logging*|*Default Buffer Pool*|',\
    '|*Table Owner*|*Table Name*|*Composite*|*Partition Name*| \
      *Subpartition Count*| *Partition Position*|*Tablespace Name*|*Logging*| \
      *Number of Rows*|*Buffer Pool*|',\
    '|*Table Owner*|*Table Name*|*Partition Name*|*Subpartition Name*| \
      *Subpartition Position*|*Tablespace Name*|*Logging*| *Number of Rows*|\
      *Buffer Pool*|',\
    '|*Index Owner*|*Index Name*|*Index Type*|*Table Owner*|*Table Name*|',\
    '|*Index Owner*|*Index Name*|*Table Name*|*Partitioning Type*|\
      *Subpartitioning Type*| *Partition Count*| \
      *Partitioning Key Count*| *Subpartitioning Key Count*|*Locality*|\
      *Alignment*|*Default Tablespace Name*|*Default Logging*|\
      *Default Buffer Pool*|',\
    '|*Index Owner*|*Index Name*|*Composite*|*Partition Name*| \
      *Subpartition Count*| *Partition Position*|*Status*|*Tablespace Name*|\
      *Logging*| *Number of Rows*|*Buffer Pool*|',\
    '|*Index Owner*|*Index Name*|*Partition Name*|*Subpartition Name*| \
      *Subpartition Position*|*Status*|*Tablespace Name*|*Logging*| \
      *Number of Rows*|*Buffer Pool*|')
   set $sql
   {SELECT '|' ||
   "       owner || '|' ||
   "       table_name || '|'
   " FROM dba_tables
   " WHERE partitioned = 'YES';
   "PROMPT ___Macro_separator(2)___
   "SELECT '|' ||
   "       owner || '|' ||
   "       table_name || '|' ||
   "       partitioning_type || '|' ||
   "       subpartitioning_type || '| ' ||
   "       partition_count || '| ' ||
   "       partitioning_key_count || '| ' ||
   "       subpartitioning_key_count || '|' ||
   "       NVL(def_tablespace_name,'%NULL%') || '|' ||
   "       def_logging || '|' ||
   "       def_buffer_pool || '|'
   " FROM dba_part_tables;
   "PROMPT ___Macro_separator(3)___
   "SELECT '|' ||
   "       table_owner || '|' ||
   "       table_name || '|' ||
   "       composite || '|' ||
   "       partition_name || '| ' ||
   "       subpartition_count || '| ' ||
   "       partition_position || '|' ||
   "       NVL(tablespace_name,'%NULL%') || '|' ||
   "       logging || '| ' ||
   "       NVL(TO_CHAR(num_rows),'%NULL%') || '| ' ||
   "       NVL(buffer_pool,'%NULL%') || '|'
   " FROM dba_tab_partitions;
   "PROMPT ___Macro_separator(4)___
   "SELECT '|' ||
   "       table_owner || '|' ||
   "       table_name || '|' ||
   "       partition_name || '|' ||
   "       subpartition_name || '| ' ||
   "       subpartition_position || '|' ||
   "       tablespace_name || '|' ||
   "       logging || '| ' ||
   "       NVL(TO_CHAR(num_rows),'%NULL%') || '|' ||
   "       buffer_pool || '|'
   " FROM dba_tab_subpartitions;
   "PROMPT ___Macro_separator(5)___
   "SELECT '|' ||
   "       owner || '|' ||
   "       index_name || '|' ||
   "       index_type || '|' ||
   "       table_owner || '|' ||
   "       table_name || '|'
   " FROM dba_indexes
   " WHERE partitioned = 'YES';
   "PROMPT ___Macro_separator(6)___
   "SELECT '|' ||
   "       owner || '|' ||
   "       index_name || '|' ||
   "       table_name || '|' ||
   "       partitioning_type || '|' ||
   "       subpartitioning_type || '| ' ||
   "       partition_count || '| ' ||
   "       partitioning_key_count || '| ' ||
   "       subpartitioning_key_count || '|' ||
   "       locality || '|' ||
   "       alignment || '|' ||
   "       NVL(def_tablespace_name,'%NULL%') || '|' ||
   "       def_logging || '|' ||
   "       def_buffer_pool || '|'
   " FROM dba_part_indexes;
   "PROMPT ___Macro_separator(7)___
   "SELECT '|' ||
   "       index_owner || '|' ||
   "       index_name || '|' ||
   "       composite || '|' ||
   "       partition_name || '| ' ||
   "       subpartition_count || '| ' ||
   "       partition_position || '|' ||
   "       status || '|' ||
   "       tablespace_name || '|' ||
   "       logging || '| ' ||
   "       NVL(TO_CHAR(num_rows),'%NULL%') || '|' ||
   "       buffer_pool || '|'
   " FROM dba_ind_partitions;
   "PROMPT ___Macro_separator(8)___
   "SELECT '|' ||
   "       index_owner || '|' ||
   "       index_name || '|' ||
   "       partition_name || '|' ||
   "       subpartition_name || '| ' ||
   "       subpartition_position || '|' ||
   "       status || '|' ||
   "       tablespace_name || '|' ||
   "       logging || '| ' ||
   "       NVL(TO_CHAR(num_rows),'%NULL%') || '|' ||
   "       buffer_pool || '|'
   " FROM dba_ind_subpartitions;
   }
   call separator(1)
   call writeSql($sql)
   call separator(0,'Partitioned Object Data')
  }
  else
  {write '---+!! Partitioned Object Data'
   write 'Partitioning Option is installed but no partitioned tables found.'
   toc '2:[[',getFile(),'][rda_report][Partitioned Object Data]]'
  }
 }
}

=head2 ctxsys - CTXSYS Information

Gets CTXSYS information.

=cut

debug ' Inside DBA module, about to gather CTXSYS data'
report text
var ($TTL,@TXT) = ('---+!! CTXSYS Information')
var @TTL = ('',\
            '---+ Instances',\
            '---+ Classes',\
            '---+ CTXSYS Version',\
            '---+ CTXSYS Schema Object Summary Count',\
            '---+ CTXSYS Libraries',\
            '---+ CTXSYS Objects Under The SYS Schema',\
            '---+ CTXSYS Index Errors',\
            '---+ CTXSYS Ten (10) Most Recent Text Index Errors \
                  (CTX_INDEX_ERRORS)',\
            '---+ CTXSYS Pending Rows',\
            '---+ CTXSYS Waiting Rows (Index)',\
            '---+ CTXSYS Waiting Rows (User)',\
            '---+ Text Indexes'\
           )
var @HDR = ('',\
            '|*Name*|*Object*|*Class*|',\
            '|*Class*|',\
            '',\
            '|*Object Type*| *Count*|',\
            '|*Library Name*|*File Spec.*|*Dynamic*|*Status*|',\
            '|*Object Owner*|*Object Name*|*Object Type*|Object Status*|',\
            '| *Count*|*Index Owner*|*Index Name*|',\
            '|*Index Owner*|*Index Name*|*Timestamp*|*Rowid*|*Error Message*|',\
            '| *Count*|*Index Owner*|*Index Name*|',\
            '|*Index Owner*|*Index Name*| *Count*|',\
            '|*User Name*|*Index Name*|',\
            '|*Index Owner*|*Index Name*|*Table Owner*|*Table Name*|\
              *Column Name*|*Index Type*|*Status*|*DOMIDX Status*|\
              *DOMIDX OPStatus*|'\
           )
var $TXT[6] = 'When objects are listed, review the following notes:%BR%\
               %MOS_DOC:1313273.1% - Invalid SYS-owned text objects / How to \
               remove text objects from the SYS schema when text is \
               installed/in use?%BR%\
               %MOS_DOC:558894.1% - Invalid Oracle text object under user SYS \
               even when Oracle text is not Installed%BR%\
               When Oracle text is invalid, open a Service Request.'
set $sql
{SELECT '|' ||
"       u.name || '|' ||
"       u.object || '|' ||
"       u.class || '|'
" FROM (SELECT LOWER(pre_name) name,
"              LOWER(pre_object) object,
"              LOWER(pre_class) class
"        FROM ctx_preferences
"        WHERE pre_owner = 'CTXSYS'
"       UNION ALL
"       SELECT LOWER(spl_name) name,
"              LOWER(spl_type) object,
"              'stoplist' class
"        FROM ctx_stoplists
"        WHERE spl_owner = 'CTXSYS'
"       UNION ALL
"       SELECT LOWER(sgp_name) name,
"              LOWER(sgp_type) object,
"              'section_group' class
"        FROM ctx_section_groups
"        WHERE sgp_owner = 'CTXSYS'
"       UNION ALL
"       SELECT LOWER(ixs_name) name,
"              'basic_index_set' object,
"              'index_set' class
"        FROM ctx_index_sets
"        WHERE ixs_owner = 'CTXSYS'
"        ORDER BY 3,2,1) u;
"PROMPT ___Macro_separator(2)___
"SELECT '|' || LOWER(cla_name) || '|'
" FROM ctx_classes
" ORDER BY cla_name;
"PROMPT ___Macro_separator(3)___
}
if match($ORACLE_VERSION,'^8')
{var $HDR[3] = '| *Ver Dict.*| *DRI Version*|'
 append $sql
 {SELECT '| ' ||
 "       ver_dict || '| ' ||
 "       SUBSTR(ctxsys.dri_version,1,10) || '|'
 " FROM ctxsys.ctx_version;
 }
}
else
{var $HDR[3] = '| *Ver Dict.*| *Ver Code.*| *DRI Version*|'
 append $sql
 {SELECT '| ' ||
 "       ver_dict || '| ' ||
 "       ver_code || '| ' ||
 "       SUBSTR(ctxsys.dri_version,1,10) || '|'
 " FROM ctxsys.ctx_version;
 }
}
append $sql
{PROMPT ___Macro_separator(4)___
"SELECT '|' ||
"       object_type || ' | ' ||
"       COUNT(*) || '|'
" FROM dba_objects
" WHERE owner = 'CTXSYS'
" GROUP BY object_type
" ORDER BY 1;
"PROMPT ___Macro_separator(5)___
"SELECT '|' ||
"       library_name || ' |' ||
"       file_spec || ' |' ||
"       dynamic || ' |' ||
"       status || ' |'
" FROM all_libraries
" WHERE owner = 'CTXSYS';
"PROMPT ___Macro_separator(6)___
"SELECT '|' ||
"       owner || ' |' ||
"       object_name || ' |' ||
"       object_type || ' |' ||
"       status || ' |'
" FROM dba_objects
" WHERE owner = 'SYS'
"   AND (object_name LIKE 'CTX_%' OR object_name LIKE 'DRI%');
"PROMPT ___Macro_separator(7)___
"SELECT '| ' ||
"       COUNT(*) || '|' ||
"       err_index_owner || '|' ||
"       err_index_name || '|'
" FROM ctxsys.ctx_index_errors
" GROUP BY err_index_owner, err_index_name;
"PROMPT ___Macro_separator(8)___
"SELECT '|' ||
"       err_index_owner || ' |' ||
"       err_index_name || ' |' ||
"       err_timestamp || ' |' ||
"       err_textkey || ' |' ||
"       err_text || ' |'
" FROM (SELECT err_index_owner,
"              err_index_name,
"              err_timestamp,
"              err_textkey,
"              err_text
"        FROM ctxsys.ctx_index_errors
"        ORDER BY err_timestamp DESC,err_index_owner,err_index_name)
" WHERE rownum <= 10;
"PROMPT ___Macro_separator(9)___
"SELECT '| ' ||
"       COUNT(*) || '|' ||
"       pnd_index_owner || '|' ||
"       pnd_index_name || '|'
" FROM  ctxsys.ctx_pending
" GROUP BY pnd_index_owner, pnd_index_name
" ORDER BY COUNT(*) DESC;
"PROMPT ___Macro_separator(10)___
"SELECT '|' ||
"       wtg_index_owner || ' |' ||
"       wtg_index_name || ' | ' ||
"       COUNT(*)  || '|'
" FROM (SELECT /*+ ORDERED USE_NL(i p) */
"              u.name wtg_index_owner,
"              idx_name wtg_index_name,
"              ixp_name wtg_partition_name,
"              wtg_rowid
"        FROM ctxsys.dr$waiting,ctxsys.dr$index i,
"             ctxsys.dr$index_partition p,sys.user$ u
"        WHERE idx_owner# = u.user#
"          AND idx_id = ixp_idx_id
"          AND wtg_pid = ixp_id
"          AND wtg_pid != 0
"          AND wtg_cid = idx_id
"       UNION ALL
"       SELECT /*+ ORDERED USE_NL(i) */
"              u.name wtg_index_owner,
"              idx_name wtg_index_name,
"              null wtg_partition_name,
"              wtg_rowid
"        FROM ctxsys.dr$waiting,ctxsys.dr$index i,sys.user$ u
"        WHERE idx_owner# = u.user#
"          AND wtg_pid = 0
"          AND wtg_cid = idx_id
"      ) ctx_waiting
" GROUP BY wtg_index_owner,wtg_index_name
" ORDER BY COUNT(*) DESC;
"PROMPT ___Macro_separator(11)___
"SELECT '|' ||
"       u.username || ' |' ||
"       i.idx_name || '|'
" FROM ctxsys.dr$index i,dba_users u
" WHERE u.user_id = i.idx_owner#
"   AND idx_id IN (SELECT wtg_cid FROM ctxsys.dr$waiting);
"PROMPT ___Macro_separator(12)___
"SELECT '|' ||
"       c.idx_owner || ' |' ||
"       c.idx_name || ' |' ||
"       c.idx_table_owner || ' |' ||
"       c.idx_table || ' |' ||
"       c.idx_key_name || ' |' ||
"       c.idx_type || ' |' ||
"       i.status || ' |' ||
"       i.domidx_status || ' |' ||
"       i.domidx_opstatus || ' |'
" FROM ctxsys.ctx_indexes c,dba_indexes i
" WHERE c.idx_owner = i.owner
"   AND c.idx_name = i.index_name
" ORDER BY c.idx_name,c.idx_table_owner;
}
call separator(1)
call writeSql($sql)
call separator(0,'CTXSYS Information')

=head2 scn_check - SCN Health Check

Provides a snapshot of the SCN health at a given point in time.

=cut

debug ' Inside DBA module, executing the SCN check'
set $sql
{SELECT 'ver=' || version || CHR(10) ||
"       'dat=' || date_time || CHR(10) ||
"       'scn=' || dbms_flashback.get_system_change_number || CHR(10) ||
"       'ind=' || indicator
" FROM (
"  SELECT version,
"         TO_CHAR(SYSDATE,'DD-Mon-YYYY HH24:MI:SS') date_time,
"         ((((
"          ((to_number(to_char(sysdate,'YYYY'))-1988)*12*31*24*60*60) +
"          ((to_number(to_char(sysdate,'MM'))-1)*31*24*60*60) +
"          (((to_number(to_char(sysdate,'DD'))-1))*24*60*60) +
"          (to_number(to_char(sysdate,'HH24'))*60*60) +
"          (to_number(to_char(sysdate,'MI'))*60) +
"          (to_number(to_char(sysdate,'SS')))
"          ) * (16*1024)) - dbms_flashback.get_system_change_number)
"         / (16*1024*60*60*24)
"         ) indicator
"   FROM v$instance );
}
if loadSql($sql)
{var $dat = value(grepLastSql('^dat=','f'))
 var $ind = value(grepLastSql('^ind=','f'))
 var $scn = value(grepLastSql('^scn=','f'))
 var $ver = value(grepLastSql('^ver=','f'))
 var ($low,$mid,$end,$txt) = \
   (10,62,'%BR%For further information, review %MOS_DOC:1393363.1%')
 report scn_check
 write '---+ SCN Health Check Information'
 write '|*Current Date*|',$dat,' |'
 write '|*Current SCN*|',$scn,' |'
 write '|*SCN Headroom*|',cond(defined($ind),sprintf("%.2f",$ind)),' |'
 write '|*Version*|',$ver,' |'
 if compare('newer',$ver,'10.2.0.5.0')
 {if expr('>',$ind,$mid)
  {if compare('older',$ver,'11.2.0.2')
    var $txt = ' and set _external_scn_rejection_threshold_hours=24 after apply'
   write '|*Result*|A - SCN Headroom is good%BR%\
                    Apply the latest recommended patches based on your \
                    maintenance schedule',$txt,'.',$end,' |'
  }
  elsif expr('>',$ind,$low)
  {if compare('older',$ver,'11.2.0.2')
    var $txt = ' and set _external_scn_rejection_threshold_hours=24 after apply'
   write '|*Result*|B - SCN Headroom is low%BR%\
                    If you have not already done so apply the latest \
                    recommended patches right now',$txt,'.',$end,' |'
  }
  else
  {if compare('older',$ver,'11.2.0.2')
    var $txt = ', set _external_scn_rejection_threshold_hours=24 after apply,'
   write '|*Result*|C - SCN Headroom is low%BR%\
                    If you have not already done so apply the latest \
                    recommended patches right now',$txt,\
                    ' and contact Oracle support immediately.',$end,' |'
  }
 }
 else
 {if expr('>',$ind,$mid)
  {if compare('valid',$ver,'10.1.0.5.0')
    var $txt = ' and set _external_scn_rejection_threshold_hours=24 after apply'
   write '|*Result*|A - SCN Headroom is good%BR%\
                    Apply the latest recommended patches based on your \
                    maintenance schedule',$txt,'.',$end,' |'
  }
  else
  {if compare('valid',$ver,'10.1.0.5.0')
    var $txt = ', set _external_scn_rejection_threshold_hours=24 after apply,'
   write '|*Result*|C - SCN Headroom is low%BR%\
                    If you have not already done so apply the latest \
                    recommended patches right now',$txt,\
                    ' and contact Oracle support immediately.',$end,' |'
  }
 }
 toc '2:[[',getFile(),'][rda_report][SCN Health Check]]'
}

=head2 HCVE

When requested and available, performs the preinstallation checks.

=cut

if and($LOCAL,${B_HCVE_CHECKS})
{# Confirm the database version
 if ?getComponentVersion($ORACLE_HOME,'oracle.rdbms')
 {var $ver = last
  if match($ver,'^12\.')
   var $ver = '12'
  elsif match($ver,'^11\.2')
   var $ver = '112'
  elsif match($ver,'^11\.1')
   var $ver = '111'
  elsif match($ver,'^10\.2')
   var $ver = '102'
  elsif match($ver,'^10\.1')
   var $ver = '101'
  else
   var $ver = 'x'
 }
 else
  var $ver = $ORACLE_VERSION

 if match($ver,'^(1[0-2])')
 {# Determine the rule set
  var $rul = undef
  if match($ver,'^101')
   var $rul = check(getOsName(),\
     'aix',    'DB:Adb10r1_aix',\
     'darwin', 'DB:Adb10r1_mac',\
     'dec_osf','DB:Adb10r1_osf',\
     'hpux',   'DB:Adb10r1_hp',\
     'linux',  'DB:Adb10r1_lin',\
     'solaris','DB:Adb10r1_sol')
  elsif match($ver,'^102')
   var $rul = check(getOsName(),\
     'aix',    'DB:Adb10r2_aix',\
     'hpux',   'DB:Adb10r2_hp',\
     'linux',  'DB:Adb10r2_lin',\
     'solaris','DB:Adb10r2_sol')
  elsif match($ver,'^111')
   var $rul = check(getOsName(),\
     'aix',    'DB:Adb11r1_aix',\
     'hpux',   'DB:Adb11r1_hp',\
     'linux',  'DB:Adb11r1_lin',\
     'solaris','DB:Adb11r1_sol')
  elsif match($ver,'^112')
   var $rul = check(getOsName(),\
     'aix',           'DB:Adb11r2_aix',\
     'hpux',          'DB:Adb11r2_hp',\
     'linux',         'DB:Adb11r2_lin',\
     'solaris',       'DB:Adb11r2_sol',\
     cond(isCygwin(), 'DB:Adb11r2_win',\
          isWindows(),'DB:Adb11r2_win'))
  elsif match($ver,'^12')
   var $rul = check(getOsName(),\
     'aix',           'DB:Adb12r1_aix',\
     'hpux',          'DB:Adb12r1_hp',\
     'linux',         'DB:Adb12r1_lin',\
     'solaris',       'DB:Adb12r1_sol',\
     cond(isCygwin(), 'DB:Adb12r1_win',\
          isWindows(),'DB:Adb12r1_win'))

  # Only execute the checks for the supported platforms
  if $rul
  {debug ' Inside DBA module, executing the preinstallation checks'
   test TOOL:TLhcve($rul)
  }
 }
}

# Disable the group title in next index
if isTocCreated()
 toc '-:RDBMS'

=head1 SEE ALSO

L<DB:DBinfo|collect::DB:DBinfo>,
L<DB:DCdb|collect::DB:DCdb>,
L<TOOL:TLhcve|collect::TOOL:TLhcve>

=begin credits

=over 10

=item RDA 4.1:  GP Gongloor, Noriyuki Kamei.

=item RDA 4.7:  Wolfgang Haeckl, Emiel Ramakers, Wes Root.

=item RDA 4.8:  GP Gongloor.

=item RDA 4.11: Jorge Barba, Ken Robinson.

=item RDA 4.15: Mihai Alistar, Grant Hayden.

=item RDA 4.16: Jaime Alcoreza, Hagen Herbst, Scott Jesse, Rick Pulliam,
Sanjay Singh.

=item RDA 4.17: Jaime Alcoreza, John OConnor.

=item RDA 4.23: Savitha Gowda.

=item RDA 4.24: Bogdan Zaman.

=item RDA 4.27: Jurgen Schelfhout.

=item RDA 4.31: Grant Hayden.

=item RDA 8.00: Francois Lange, Terri Nasshan.

=item RDA 8.02: Takeyoshi Sasaki.

=item RDA 8.06: Beate Guldenschuh-Bihannic.

=item RDA 8.07: Harm ten Napel.

=back

=end credits

=head1 COPYRIGHT NOTICE

Copyright (c) 2002, 2016, Oracle and/or its affiliates. All rights reserved.

=head1 TRADEMARK NOTICE

Oracle and Java are registered trademarks of Oracle and/or its
affiliates. Other names may be trademarks of their respective owners.

=cut