# DCxdb.ctl:209:Collects XDB Information
# $Id: DCxdb.ctl,v 1.4 2013/12/18 14:13:37 RDA Exp $
# ARCS: $Header: /home/cvs/cvs/RDA_8/src/scripting/lib/collect/DB/DCxdb.ctl,v 1.4 2013/12/18 14:13:37 RDA Exp $
#
# Change History
# 20131218  KRA  Fix spell.

=head1 NAME

DB:DCxdb - Collects XDB Information

=head1 DESCRIPTION

This module collects the XDB diagnostic information. This module applies to
Oracle Database 9i Release 2 and later only.

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

=cut

if !${I_DBC/E}
 return

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

# Initialization
var $TOC = '%TOC%'
var $TOP = '[[#Top][Back to top]]'
var @TXT
pretoc '^1:RDBMS'
pretoc '1+:XDB'

# Load the common macros
run DB:DBinfo()

=head2 db_info - Database Overview

Gets the database and XDB versions and lists the database features.

=cut

debug ' Inside XDB module, getting database overview'

set $sql
{SELECT 'DB=' || v.version
" FROM v$instance v;
"SELECT 'XDB=' || dbms_registry.version('XDB')
" FROM dual;
"SELECT '|' ||
"       s.comp_name || ' |' ||
"       s.status || ' |' ||
"       s.version || ' |'
" FROM dba_registry s;
}
if loadSql($sql)
{var $ver = value(grepLastSql('^DB=','f'))

 report db_info
 write '---+!! Database Overview'
 write $TOC
 write '---+ Versions'
 write '|*Database*|',$ver,' |'
 if value(grepLastSql('^XDB=','f'))
 {var $xdb = last
  write '|*XDB*|',$xdb,' |'
  if !compare ('VALID',$xdb,'9.2.0.3')
   write 'Version is unsupported. Minimum required version is 9.2.0.3.0.%BR%'
 }
 write $TOP

 prefix
 {write '---+ Other Database Features'
  write '|*Component*|*Status*|*Version*|'
 }
 loop $lin (grepLastSql('^\|'))
  write $lin
 if hasOutput(true)
  write $TOP
 toc '2:[[',getFile(),'][rda_report][Database Overview]]'
}

=head2 invalid - Invalid Objects

Gets invalid database objects.

=cut

debug ' Inside XDB Module, getting invalid objects'
report invalid
prefix
{write '---+ Invalid Objects'
 write '|*Type*|*Name*|'
}
set $sql
{SELECT '|' ||
"       object_type || ' |' ||
"       owner || '.' || object_name || ' |'
" FROM dba_objects
" WHERE status = 'INVALID'
"   AND owner IN ('SYS','XDB');
}
call writeSql($sql)
if isCreated(true)
{write $TOP
 toc '2:[[',getFile(),'][rda_report][Invalid Objects]]'
}

=for stopwords APIs XMLType

=head2 xdb_info - XDB Information

Gets XDB status, XMLType tables, columns, indexes, and views that have been
established. Indexes are collected for Oracle Database 11g and later only.

Gets XML schemas that have been established.

Gets items built with XML APIs.

Gets repository resources that have been established.

=cut

debug ' Inside XDB Module, getting XDB information'
report xdb_info

var $TTL = '---+!! XDB Information'
var @DBG = ('',\
            ' Inside XDB Module, getting XDB status',\
            ' Inside XDB Module, getting XDB configuration information',\
            ' Inside XDB Module, getting XMLType tables',\
            ' Inside XDB Module, getting XMLType columns',\
            ' Inside XDB Module, getting XMLType indexes',\
            ' Inside XDB Module, getting XMLType views',\
            ' Inside XDB Module, getting XML schemas',\
            ' Inside XDB Module, getting items built with XML APIs',\
            ' Inside XDB Module, getting repository resources')
var @TTL = ('',\
            '---+ XDB Status',\
            '---+ XDB Configuration Information',\
            '---+ XMLType Tables',\
            '---+ XMLType Columns',\
            '---+ XMLType Indexes',\
            '---+ XMLType Views',\
            '---+ XML Schemas',\
            '---+ Items Built with XML APIs',\
            '---+ Repository Resources')
var @HDR = ('',\
            '|*Status*|',\
            '|*Root Element Nodename*|*Node Value*|',\
            '|*Owner*|*Storage Type*| *Count*|',\
            '|*Owner*|*Storage Type*| *Count*|',\
            '|*Owner*|*Storage Type*| *Count*|',\
            '|*Owner*| *Count*|',\
            '|*Owner*| *Count*|',\
            '|*Owner*|*Name*|*Type*|',\
            '|*User*| *Count*|')

set $sql
{SELECT '|' || dbms_registry.status('XDB') || ' |'
" FROM dual;
"PROMPT ___Macro_separator(2)___
"SELECT '|' || value(x).getrootelement() || ' |' ||
"       extractValue(value(x),'/*') || ' |'
" FROM table(xmlsequence(extract(xdburitype('/xdbconfig.xml').getXML(),
"       '//*[text()]'))) x;
"PROMPT ___Macro_separator(3)___
"SELECT '|' ||
"       owner || ' |' ||
"       storage_type || ' | ' ||
"       COUNT(*) || '|'
" FROM dba_xml_tables
" GROUP BY owner,storage_type;
"PROMPT ___Macro_separator(4)___
"SELECT '|' || owner || ' |' ||
"       storage_type || ' | ' ||
"       COUNT(*) || '|'
" FROM dba_xml_tab_cols
" GROUP BY owner,storage_type;
}
if match($ver,'^11')
{append $sql
 {PROMPT ___Macro_separator(5)___
 "SELECT '|' ||
 "       index_owner || ' |' ||
 "       type || ' | ' ||
 "       COUNT(*) || '|'
 " FROM dba_xml_indexes
 " GROUP BY index_owner,type;
 }
}
append $sql
{PROMPT ___Macro_separator(6)___
"SELECT '|' ||
"       owner || ' | ' ||
"       COUNT(*) || '|'
" FROM dba_xml_views
" GROUP BY owner;
"PROMPT ___Macro_separator(7)___
"SELECT '|' ||
"       owner || ' | ' ||
"       COUNT(*) || '|'
" FROM dba_xml_schemas
" GROUP BY owner;
"PROMPT ___Macro_separator(8)___
"SELECT '|' ||
"       d.owner || ' |' ||
"       d.name || ' |' ||
"       d.type || ' |'
" FROM dba_dependencies d
" WHERE d.referenced_name IN
"       (SELECT o.object_name
"         FROM dba_objects o
"         WHERE o.object_name LIKE 'DBMS_XML%'
"            OR o.object_name LIKE 'DBMS_XSL%')
"   AND d.type != 'SYNONYM'
"   AND d.owner != 'SYS';
"PROMPT ___Macro_separator(9)___
"SELECT '|' ||
"       a.username || ' | ' ||
"       COUNT(r.xmldata) || '|'
" FROM dba_users a,xdb.xdb$resource r
" WHERE sys_op_rawtonum(extractvalue(value(r),'/Resource/OwnerID/text()'))
"       = a.user_id
" GROUP BY a.username;
}
call separator(1)
call writeSql($sql)
call separator(0,'XDB Information')

=head2 acl_dad - Network ACL and DAD Information

Gets network access controls (ACL) that have been configured and finds Oracle
Database Embedded PL/SQL Gateway (EPD) data access descriptor (DAD)
usage. (Only available for Oracle Database 11g and later.)

=cut

if match($ver,'^11')
{debug ' Inside XDB Module, getting ACL and DAD information'
 report acl_dad
 var $TTL = '---+!! Network ACL and DAD Information'
 var @TTL = ('',\
             '---+ Network Access Controls Configured',\
             '---+ Oracle Database EPG Data Access Descriptor Usage')
 var @HDR = ('',\
             '|*Host*|*Ports*|',\
             '|*Name*|')
 set $sql
 {SELECT '|' ||
 "       host || '|' ||
 "       upper_port || ' through ' || lower_port || '|'
 " FROM dba_network_acls;
 "PROMPT ___Macro_separator(2)___
 "SET serveroutput on size 100000
 "DECLARE
 " l_nam dbms_epg.varchar2_table;
 "BEGIN
 " dbms_epg.get_dad_list(l_nam);
 " FOR i IN 1 .. l_nam.COUNT
 " LOOP
 "  dbms_output.put_line('|' || l_nam(i) || ' |');
 " END LOOP;
 "END;
 "/
 }
 call separator(1)
 call writeSql($sql)
 call separator(0,'Network ACL and DAD Information')
}

# 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>

=begin credits

=over 10

=item RDA 4.16: Coby Adams.

=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