# S201DBA.def: Collects RDBMS Information # $Id: S201DBA.def,v 1.37 2008/02/25 13:27:21 mschenke Exp $ # ARCS: $Header: /home/cvs/repository/RDA_4/src/scripting/lib/modules/S201DBA.def,v 1.37 2008/02/25 13:27:21 mschenke Exp $ # # Change History # 20080225 MSC Improve the documentation. =head1 NAME S201DBA - Collects RDBMS Information =head1 DESCRIPTION This module collects the basic RDBMS-related diagnostic information. The following reports can be generated and are regrouped under C: =cut if !getSetting('DATABASE_INSTALLED') return echo tput('bold'),'Processing RDBMS module ...',tput('off') # Initialization var $LOCAL = getSetting('DATABASE_LOCAL') var $MACHINE_NAME = getSetting('MACHINE_NAME') var $ORACLE_HOME = getSetting('ORACLE_HOME') var $ORACLE_SID = getSetting('ORACLE_SID') var $TOC = '%TOC%' var $TOP = '[[#Top][Back to top]]' var @TXT toc '1:RDBMS' # Load the common macros run DBinfo() =head2 versions - Product Versions Gets product versions and determines which version of the database is installed. =cut debug ' Inside RDBMS 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 # Timezone 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,'\s11\.','11',\ '\s10\.2\.','102',\ '\s10\.1\.','101',\ '10') var $tzf = true var $rgs = false } else {# keep searching var $prt = false } echo 'VERSION:',$ORACLE_VERSION # Merge the operating system and database versions if isWindows() var $nam = 'nt.rgs' else var $nam = 'unix.rgs' if $rgs {var $fil = catFile($ORACLE_HOME,$ins,$nam) write '---+ From ',$fil if !testFile('r',$fil) {write 'Could not read: ',$fil,'%BR%' var $fil = '/var/opt/oracle' if testDir('d',$fil) {write 'Look in ',$fil write '---+ List of files in ',$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 ',$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 spfile is in use by connecting to the database and looking up the spfile parameter. If a value is found, then RDA does not search INIT.ORA. A select for v$spparameter is executed to determine whether to skip the search for INIT.ORA. =cut debug ' Inside RDBMS 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 setSetting($PFILE_GOOD,false) call setSetting($PFILE_LOCAL,false) } elsif getSetting('PFILE_LOCAL') {var $fil = getSetting('PFILE_LOCATION') var %treated = () prefix {write '---+!! INIT.ORA Information' write $TOC write '---+ Contents of INIT.ORA ',$fil } if writeFile($fil) {call setSetting('PFILE_GOOD',true) write $TOP var $treated{$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($treated{$fil}) write '---+ Contents of IFILE ',$fil call writeFile($fil) write $TOP var $treated{$fil} = 1 } } else {call setSetting('PFILE_GOOD',false) unprefix write $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 information. =cut debug ' Inside RDBMS module, processing V$Parameter' report vparameters var $TTL = '---+!! Database Parameters' var $TTL[1] = '---+ Non-Default 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) call loadSql($sql2) call writeLastSql() if getSetting('SQL_SYSDBA') {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(SUBSTR(b.ksppstvl, 0, 512), '%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') # Define a macro to extract a parameter value macro get_param {import $ORACLE_HOME var $str = trim(field('\|',2,grepLastSql($arg[0],'f'))) var $str = replace($str,"'",'',true) if $arg[1] {var $str = replace($str,'\?',$ORACLE_HOME) var $str = replace($str,'\$ORACLE_HOME',$ORACLE_HOME) var $str = replace($str,'\${ORACLE_HOME}',$ORACLE_HOME) var $str = replace($str,'%ORACLE_HOME%',$ORACLE_HOME) } return $str } # Get undo_management # There is no need to resolve ORACLE_HOME for AUM, but this parameter may # not exist. debug ' Inside RDBMS module, found V$Parameter output, get some parameters' var $AUM_Setting = get_param('\|undo_management\|',false) if !$AUM_Setting {var $AUM_Setting='manual' } =head2 vspparameters - Database SPFile Parameters Gets C information. (Spfiles are available only in Oracle 9i and later) =cut debug ' Inside RDBMS 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)') {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 information. =cut debug ' Inside RDBMS 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 information (Oracle 9i and later). =cut if match($ORACLE_VERSION,'^(92$|10|11)') {debug ' Inside RDBMS 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)') {debug ' Inside RDBMS 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 RDBMS 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$") {var @HDR = (@HDR,'|*Name*| *Value*|') append $sql {SELECT '|' || v.name || '| ' || v.bytes || '|' " FROM v$sgastat v " ORDER BY v.name; } } else {var @HDR = (@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)') {var @HDR = (@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) call separator(0,'SGA Information') =head2 mts - Multi-Threaded Server / Shared Server Collects multi-threaded server (Oracle 8i) or shared server (Oracle 9i and later) information. =cut if match($ORACLE_VERSION,'^(81$|9|10|11)') {debug ' Inside RDBMS 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 RDBMS 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 information. =cut debug ' Inside RDBMS 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 information. =cut debug ' Inside RDBMS module, processing V$Compatibility' report vcompatibility if match($ORACLE_VERSION,'^(10|11)') {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. Timezone did not exist until Oracle version 8.1.7. =cut debug ' Inside RDBMS 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 Timezone offset sections if $tzf {var @TTL = (@TTL,\ '---+ Session TimeZone',\ '---+ Database TimeZone',\ '---+ TimeZone Definition'\ ) var @HDR = (@HDR,\ '|*TimeZone*|',\ '|*TimeZone*|',\ '|*Version*|'\ ) append $sql {SELECT '|' || sessiontimezone || '|' " FROM sys.dual; "PROMPT ___Macro_separator(5)___ "SELECT '|' || dbtimezone || '|' " FROM sys.dual; "PROMPT ___Macro_separator(6)___ "SELECT '| ' || version || '|' " FROM v$timezone_file; } } else {var @TTL = (@TTL,\ '---+!! TimeZone 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 information. (Only available for Oracle Database 10g and later) =head2 vfeatureinfo - Feature Information Collects rows that have feature information from C. (Only available for Oracle Database 10g and later) =cut if match($ORACLE_VERSION,'^(10|11)') {debug ' Inside RDBMS module, processing DBA_Feature_Usage_Statistics' report vfeatureusage prefix {write '---+!! Database Feature Usage Statistics' write '|*Dbid*|*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 RDBMS module, getting Feature Information' report vfeatureinfo prefix {write '---+!! Database Feature Information' write '|*Dbid*|*Name*|*Version*|*Information*|' } set $sql { SET long 65536 " SELECT '[[[' || CHR(10) || '|' || " s.dbid || '|' || " s.name || '|' || " s.version || '|' || " REPLACE(REPLACE(REPLACE(LTRIM(s.feature_info), " '|', '|'), " '<', '<'), " '>', '>') || '|' || " 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]]' } =head2 vHWM_Statistic - HighWaterMark Statistics Gets C information. (Only available for Oracle Database 10g and later) =cut if match($ORACLE_VERSION,'^(10|11)') {debug ' Inside RDBMS module, processing DBA_High_Water_Mark_Statistics' report vHWM_Statistic prefix {write '---+!! Database HighWaterMark Statistics' write '|*Dbid*|*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 information. (Only available for Oracle Database 10g R2 and later) =cut if match($ORACLE_VERSION,'^(102|11)') {debug ' Inside RDBMS module, processing DBA_CPU_Usage_Statistics' report CPU_Statistic prefix {write '---+!! Database CPU Usage Statistics' write '|*Dbid*|*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 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 HH: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 information. =cut debug ' Inside RDBMS 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 and C information. =cut debug ' Inside RDBMS module, processing log information' report log_info var $TTL = '---+!! Information from V$Logfile and V$Log' var @TTL = ('',\ '---+ V$Logfile',\ '---+ V$Log'\ ) 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 need to 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_Setting,'^manual$',true) {debug ' Inside RDBMS 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 RDBMS 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 in Oracle 9i and later) =cut if match($ORACLE_VERSION,'^(9|10|11)') {debug ' Inside RDBMS 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 information. =cut debug ' Inside RDBMS 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 information. =cut debug ' Inside RDBMS 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 information. =cut debug ' Inside RDBMS 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 and C information. =cut debug ' Inside RDBMS module, processing latch information' report latch_info var $TTL = '---+!! Information from V$Latch and V$LatchHolder' 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 RDBMS module, processing tablespaces for V7' 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 } elsif match($ORACLE_VERSION,'^80$') {# V8.0 Tablespaces debug ' Inside RDBMS module, processing tablespaces for V8.0' 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 } elsif match($ORACLE_VERSION,'^81$') {# V8.1 Tablespaces debug ' Inside RDBMS module, processing tablespaces for V8.1' write '---+!! Tablespaces' write $TOC write '---+ Permanent Tablespaces' write '|*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 (%)*|' var @sep1 = (\ $TOP,\ '---+ Locally Managed Temporary Tablespaces',\ '|*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 ___Separator(sep1)___ "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 writeSql($sql) if getSqlMessage() write last,'%BR%' write $TOP } elsif match($ORACLE_VERSION,'^(9|10|11)') {# V9 or V10 Tablespaces debug ' Inside RDBMS module, processing tablespaces for 9i and higher' write '---+!! Tablespaces' write $TOC write '---+ Permanent Tablespaces' write '|*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 (%)*|' var @sep1 = (\ $TOP,\ '---+ Locally Managed Temporary Tablespaces',\ '|*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 || '|' || " 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 ___Separator(sep1)___ "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 writeSql($sql) if getSqlMessage() write last,'%BR%' write $TOP } if isCreated() toc '2:[[',getFile(),'][rda_report][Tablespaces]]' =head2 datafile - Database Files Gets data file information. =cut report datafile if match($ORACLE_VERSION,'^7$') {# V7 Datafiles debug ' Inside RDBMS module, processing datafiles for V7' write '---+!! Datafile Information' write '|*Status*|*Name*|*Tablespace*| *File Number*| *Size*| *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 RDBMS module, processing datafiles for V8.0' write '---+!! Datafile Information' write '|*Status*|*Name*|*Tablespace*| *File Number*| *Relative File Number*| \ *Size*| *Used (MiB)*| *Used (%)*|*Autoextensible*|' 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') || '|' " 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)') {# 8i/9i/10g/11g Datafiles debug ' Inside RDBMS module, processing datafiles for 8i and higher' write '---+!! Datafile Information' write $TOC write '---+ Database Files' write '|*Status*|*Name*|*Tablespace*|*File Number*| *Relative File Number*| \ *Size*| *Used (MiB)*| *Used (%)*|*Autoextensible*|' var @sep1 = (\ $TOP,\ '---+ TempFiles',\ '|*Status*|*Name*|*Tablespace*| *File Number*| *Relative File Number*| \ *Size (MiB)*|'\ ) 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') || '|' " 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') || '|' " FROM sys.dba_temp_files d, " v$tempfile v " WHERE (d.file_name = v.name); } call writeSql($sql,2) 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 RDBMS 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 information. =cut debug ' Inside RDBMS 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, '|', '|'), 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 security - Auditing and Password Information Gets database data for security/passwords/auditing. =cut report security debug ' Inside RDBMS 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)') {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)') {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)') {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 RDBMS 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 RDBMS 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), " '|','|'), " '<','<'), " '>','>'), " 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 RDBMS 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 var $os = getOsName() var ($dba,$oper) = () if compare('eq',$os,'solaris') {var $ext = 's' write '---+ Group Related Data from $ORACLE_HOME/rdbms/lib/config.s' if loadFile(catFile($ORACLE_HOME,'rdbms','lib','config.s')) {write '' write 'Group data is:' var ($str) = grepLastFile('ascii','f') write $str var $dba = field('"',1,$str) write '' } else write 'Could not read $ORACLE_HOME/rdbms/lib/config.s%BR%' } elsif compare('eq',$os,'aix') {var $ext = 's' write '---+ Group Related Data from $ORACLE_HOME/rdbms/lib/config.s' if loadFile(catFile($ORACLE_HOME,'rdbms','lib','config.s')) {write '' write 'Group data is:' var ($str) = grepLastFile('string','f') write $str var $dba = field('"',1,$str) write '' } else write 'Could not read $ORACLE_HOME/rdbms/lib/config.c%BR%' } else # dec_osf, hpux, dynixptx {var $ext = 'c' write '---+ Group Related Data from $ORACLE_HOME/rdbms/lib/config.c' if loadFile(catFile($ORACLE_HOME,'rdbms','lib','config.c')) {write '' 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 $oper = field('"',1,$str) write '' } else write 'Could not read $ORACLE_HOME/rdbms/lib/config.c%BR%' } write $TOP write '---+ /etc/group Oracle Group Settings' write '' 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,$oper) {write 'oper Group group from /etc/group:' write grepLastFile(concat('^',$oper,':'),'f') } else write 'Note: dba and oper groups are the same' } write '' write $TOP write '---+ $ORACLE_HOME/dbs Directory Listing' if !statDir('n',catDir($ORACLE_HOME,'dbs')) write 'Could not read $ORACLE_HOME/dbs' write $TOP 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 8i and later). =cut report spatial var $TTL = '---+!! Spatial Information' if match($ORACLE_VERSION,'^(92$|10|11)') {# Version 9.2.x,10.x,11.x Spatial queries debug ' Inside RDBMS 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',\ '---+ 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*|',\ '|*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] = '**Please 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; } if match($ORACLE_VERSION,'^92$') {var $HDR[7] = '|*SDO Index Owner*|*SDO Index Name*|*SDO Column Name*|\ *SDO Index Type*|*Geodetic ?*| *Indexversion*|' append $sql {PROMPT ___Macro_separator(7)___ "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[8] = '**See note 342280.1**' append $sql {PROMPT ___Macro_separator(7)___ "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(8)___ " 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(9)___ "SELECT '|' || " owner || ' |' || " network || ' |' || " node_table_name || ' |' || " link_table_name || ' |' || " path_table_name || ' |' " FROM all_sdo_network_metadata; "PROMPT ___Macro_separator(10)___ "SELECT '|' || " owner || ' |' || " topology || ' |' || " table_name || ' |' || " column_name || ' |' " FROM all_sdo_topo_metadata; "PROMPT ___Macro_separator(11)___ "SELECT '|' || " owner || ' |' || " table_name || ' |' || " column_name || ' |' || " rdt_table_name || ' |' " FROM all_sdo_geor_sysdata; } } if match($ORACLE_VERSION,'^11$') {var @TTL = (@TTL,\ '---+ Semantic Models Defined in the Database',\ '---+ Rulebases',\ '---+ Database Objects Used in Rules Indexes',\ '---+ Rules Indexes',\ '---+ SEM_INDEXTYPE Indexes') var @HDR = (@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*|') append $sql {PROMPT ___Macro_separator(12)___ "SELECT '|' || " owner || ' | ' || " model_id || '|' || " model_name || ' |' || " table_name || ' |' || " column_name || ' |' " FROM mdsys.sem_model$ " ORDER BY owner; "PROMPT ___Macro_separator(13)___ "SELECT '|' || " owner || ' |' || " rulebase_name || ' |' || " rulebase_view_name || ' |' || " status || ' |' " FROM mdsys.sem_rulebase_info " ORDER BY owner; "PROMPT ___Macro_separator(14)___ "SELECT '|' || " index_name || ' |' || " data_type || ' |' || " data_name || ' |' " FROM mdsys.sem_rules_index_datasets " ORDER BY index_name,data_type; "PROMPT ___Macro_separator(15)___ "SELECT '|' || " owner || ' |' || " index_name || ' |' || " index_view_name || ' |' || " status || ' | ' || " model_count || '| ' || " rulebase_count || '|' " FROM mdsys.sem_rules_index_info " ORDER BY owner; "PROMPT ___Macro_separator(16)___ "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|91)$') {# Version 8.1,9.x Spatial queries debug ' Inside RDBMS 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 Queueing Information Collects Advanced Queuing 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 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 RDBMS module, about to gather AQ data' var $TTL = '---+!! Advanced Queueing Data' if match($ORACLE_VERSION,'^7') {# AQ was not part of V7 write $TTL write 'Advanced Queueing was not available in this release' } if match($ORACLE_VERSION,'^(8|9|10|11)') {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 Queueing 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 9i and is manually setup. Because it is a manual setup, RDA performs a quick check for the C 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 RDBMS module, about to gather message gateway data' if match($ORACLE_VERSION,'^(9|10|11)') {# 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('---++ ',$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 8. =cut report partition_data debug ' Inside RDBMS module, about to gather partitioning data' if match($ORACLE_VERSION,'^(8|9|10|11)') {# 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 RDBMS module, about to gather CTXSYS data' report text var $TTL = '---+!! CTXSYS Information' var @TTL = ('',\ '---+ Instances',\ '---+ Classes',\ '---+ CTXSYS Version',\ '---+ CTXSYS Libraries',\ '---+ CTXSYS Index Errors',\ '---+ CTXSYS Pending Rows',\ '---+ CTXSYS Waiting Rows'\ ) var @HDR = ('',\ '|*Name*|*Object*|*Class*|',\ '|*Class*|',\ '',\ '|*Library Name*|*File Spec.*|*Dynamic*|*Status*|',\ '| *Count*|*Index Owner*|*Index Name*|',\ '| *Count*|*Index Owner*|*Index Name*|',\ '| *Count*|*wtg_cid*|'\ ) 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 '|' || " library_name || ' |' || " file_spec || ' |' || " dynamic || ' |' || " status || ' |' " FROM all_libraries " WHERE owner = 'CTXSYS'; "PROMPT ___Macro_separator(5)___ "SELECT '| ' || " COUNT(*) || '|' || " err_index_owner || '|' || " err_index_name || '|' " FROM ctxsys.ctx_index_errors " GROUP BY err_index_owner, err_index_name; "PROMPT ___Macro_separator(6)___ "SELECT '| ' || " COUNT(*) || '|' || " pnd_index_owner || '|' || " pnd_index_name || '|' " FROM ctxsys.ctx_pending " GROUP BY pnd_index_owner, pnd_index_name; "PROMPT ___Macro_separator(7)___ "SELECT '| ' || " COUNT(*) || '|' || " wtg_cid || '|' " FROM ctxsys.dr$waiting " GROUP BY wtg_cid; } call separator(1) call writeSql($sql) call separator(0,'CTXSYS Information') =head1 SEE ALSO See also L, L. =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. =back =end credits =head1 COPYRIGHT NOTICE Copyright (c) 2002-2008, Oracle. All rights reserved. =head1 TRADEMARK NOTICE Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. =cut