set pages 0
set lines 999
set feedback off
set verify off
set escape ^
var username varchar2(30)
exec :username:=user

PROMPT Generating RAU Instrumentation report...

set termout off

define image_path=file:/&1


@..\logs\ckraudia_define.sql

REM bug 2949044 cheaps
create table temp_ckrau_diag (data varchar2(3000));


rem ***************************************************************************
rem ***************************************************************************

SPOOL ckraudia.html
PROMPT <HTML>
PROMPT <HEAD>
PROMPT <META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
PROMPT <TITLE>SCM Repository Configuration Report</TITLE>
PROMPT </HEAD>
PROMPT <FRAMESET cols="20%, 80%">
PROMPT  <FRAME src="ckrauind.html" name="ckrauind" >
PROMPT  <FRAME src="ckraurep.html"   name="ckraurep">
PROMPT </FRAMESET>
PROMPT </HTML>

SPOOL OFF
rem ***************************************************************************
rem ***************************************************************************

SPOOL ckrauind.html
PROMPT <HTML>
PROMPT 
PROMPT <HEAD>
PROMPT 	<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
PROMPT 	<TITLE>SCM Repository Configuration Report</TITLE>
PROMPT  <STYLE TYPE="text/css">
PROMPT    A {font-size:10pt;font-family:Arial, Helvetica, Sans-serif;color:#663300;}
PROMPT    .header {color:#336699;}
PROMPT  </STYLE>
PROMPT </HEAD>
PROMPT 
PROMPT <BODY BGCOLOR="#FFFFFF">
PROMPT 
PROMPT <H3 class="header">Index:</H3>
PROMPT <UL>
PROMPT <LI><A HREF="ckraurep.html#TOP"                                    target="ckraurep">Top</A></LI>
PROMPT <LI><A HREF="ckraurep.html#INSTALLED%20PRODUCTS"                   target="ckraurep">Installed Products</A></LI>
PROMPT <LI><A HREF="ckraurep.html#REPOSITORY%20INFORMATION"               target="ckraurep">Repository Information</A></LI>
PROMPT <LI><A HREF="ckraurep.html#DATABASE%20COMPONENT%20VERSIONS"        target="ckraurep">Database Component Versions</A></LI>
PROMPT <LI><A HREF="ckraurep.html#RAU%20PARAMETERS"                       target="ckraurep">RAU Parameters</A></LI>
PROMPT <LI><A HREF="ckraurep.html#ROLES%20GRANTED"                        target="ckraurep">Roles Granted</A></LI>
PROMPT <LI><A HREF="ckraurep.html#GRANTED%20SYSTEM%20PRIVILEGES"          target="ckraurep">Granted System Privileges</A></LI>
PROMPT <LI><A HREF="ckraurep.html#MISSING%20SYSTEM%20PRIVILEGES"          target="ckraurep">Missing System Privileges</A></LI>
PROMPT <LI><A HREF="ckraurep.html#DIRECT%20PRIVILEGES"                    target="ckraurep">Direct Privileges</A></LI>
PROMPT <LI><A HREF="ckraurep.html#MISSING%20DIRECT%20PRIVILEGES"          target="ckraurep">Missing Direct Privileges</A></LI>
PROMPT <LI><A HREF="ckraurep.html#DIRECT%20OBJECT%20PRIVILEGES"           target="ckraurep">Direct Object Privileges</A></LI>
PROMPT <LI><A HREF="ckraurep.html#MISSING%20DIRECT%20OBJECT%20PRIVILEGES" target="ckraurep">Missing Direct Object Privileges</A></LI>
PROMPT <LI><A HREF="ckraurep.html#TABLESPACE%20INFORMATION"               target="ckraurep">Tablespace Information</A></LI>
PROMPT <LI><A HREF="ckraurep.html#USER%20DETAILS"                         target="ckraurep">User Details</A></LI>
PROMPT <LI><A HREF="ckraurep.html#OBJECT%20STATUS"                        target="ckraurep">Object Status</A></LI>
PROMPT <LI><A HREF="ckraurep.html#OBJECTS%20FLAGGED%20FOR%20RELOAD"       target="ckraurep">Objects Flagged For Reload</A></LI>
PROMPT <LI><A HREF="ckraurep.html#REPOSITORY%20PATCH%20INFORMATION"       target="ckraurep">Repository Patch Information</A></LI>
PROMPT <LI><A HREF="ckraurep.html#DATABASE%20PROPERTIES"                  target="ckraurep">Database Properties</A></LI>
PROMPT <LI><A HREF="ckraurep.html#DATABASE%20PARAMETERS"                  target="ckraurep">Database Parameters</A></LI>
PROMPT </UL>
PROMPT </BODY>
PROMPT 
PROMPT </HTML>

SPOOL OFF
rem ***************************************************************************
rem ***************************************************************************

SPOOL ckraurep.html
PROMPT <HTML>

PROMPT <HEAD>
PROMPT	<META HTTP-EQUIV="Content-Type" CONTENT="text/html;CHARSET=iso-8859-1">
PROMPT	<A NAME="TOP"></A><TITLE>EJH 2 SCM Repository Configuration Report</TITLE>
PROMPT  <STYLE TYPE="text/css">
PROMPT    TD {font-size:10pt;font-family:Arial, Helvetica, Sans-serif;align:left;background-color:#f7f7e7;}
PROMPT    TD.bold {font-size:10pt;font-weight:bold;font-family:Arial, Helvetica, Sans-serif;color:#336699;background-color:#cccc99;}
PROMPT    TD.title {background-color:#336666;}
PROMPT    .header {color:#336699;}
PROMPT  </STYLE>
PROMPT </HEAD>


PROMPT <BODY BGCOLOR="#FFFFFF">
PROMPT <IMG align=top height=90 src="&image_path\oscm.gif" width=340><BR CLEAR="ALL">                                                                                                                                                                                      
PROMPT <TABLE border=0 cellPadding=0 cellSpacing=0 width="100%"><TR>                                                                                                                                                                                             
PROMPT <TR>                                                                                                                                                                                                                                                      
PROMPT <TD class="title" height=21 vAlign="top" width=9><IMG height=1 src="&image_path\space.gif" width=1></TD>                                                                                                                                             
PROMPT <TD class="title" height=21 vAlign="top" width=20><DIV align=left><IMG height=1 src="&image_path\space.gif" width=1></DIV></TD>                                                                                                                      
PROMPT <TD class="title" height=21 width=738><DIV align="left"><FONT color=#ffffff face="Arial, Helvetica, sans-serif" color=#ffffff size="+2"><B>SCM Repository Configuration Report</B></FONT></DIV></TD>                                                    
PROMPT <TD class="title" height=21 width=150><DIV align="right"><FONT color=#ffffff face="Arial, Helvetica, sans-serif" color=#ffffff><B>^&nbsp</B></FONT></DIV></TD>                                                                                        
PROMPT <TD class="title" style="background-color:#FFFFFF;" background="&image_path\rhshadow.gif" vAlign="top" width=5><IMG height=1 src="&image_path\space.gif" width=7><DIV align=left><IMG height=1 src="&image_path\space.gif" width=1></DIV></TD>                                                                                 
PROMPT </TR><TR>                                                                                                                                                                                                                                                 
PROMPT <TD class="title" height=16 vAlign=top width=9 ><IMG height=1 src="&image_path\space.gif" width=1></TD>                                                                                                                                              
PROMPT <TD class="title" height=16 vAlign=top width=11><IMG height=1 src="&image_path\space.gif" width=1></TD>                                                                                                                                              
PROMPT <TD class="title" style="background-color:#FFFFFF" background="&image_path\bot.gif" height=16 vAlign=top width=700 colspan=2><DIV align=left><IMG align=top height=16 src="&image_path\toparch.gif" width=26></DIV></TD>                                                                          
PROMPT <TD class="title" style="background-color:#FFFFFF" height=16 vAlign="top" width=5><DIV align="left"><IMG height=8 src="&image_path\rend.gif" width=8></DIV></TD>                                                                                                                                     
PROMPT </TR><TR>                                                                                                                                                                                                                                                 
PROMPT <TD class="title" height=8 vAlign="top" width=9><DIV align=right><IMG height=8 src="&image_path\lend.gif" width=10></DIV></TD>                                                                                                                                       
PROMPT <TD class="title" style="background-color:#FFFFFF" background="&image_path\bot.gif" height=8 vAlign=top width=11><DIV align=right><IMG height=1 src="&image_path\space.gif" width=1></DIV></TD>                                                                                                                   
PROMPT <TD class="title" style="background-color:#FFFFFF" height=8 vAlign=top width=700 colspan=2><IMG height=8 src="&image_path\archcorn.gif" width=6></TD>                                                                                                                                 
PROMPT <TD class="title" style="background-color:#FFFFFF;" height=8 vAlign=top width=5><IMG height=1 src="&image_path\space.gif" width=1></TD>                                                                                                                                                               
PROMPT </TR></TABLE>


PROMPT <TABLE border=0 cellPadding=0 cellSpacing=0 width="100%">
select '<TR><TD style="color:#336699;background-color:#ffffff;font-weight:bold" align=right>Run By: '||user||'</TD></TR>'
,      '<TR><TD style="color:#336699;background-color:#ffffff;font-weight:bold" align=right>Date: '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS')||'</TD></TR>'
from sys.dual
/
PROMPT <TD style="background-color:#ffffff" background=&image_path\seperator.gif height=1><IMG height=1 src="&image_path\space.gif" width=1></TD></TR></TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="INSTALLED%20PRODUCTS"></A>Installed Products</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Product</TD>
PROMPT		<TD class="bold">Version</TD>
PROMPT		<TD class="bold">Status</TD>
PROMPT	</TR>

select '<TR><TD>'||PRODUCT||'</TD>'||
           '<TD>'||VERSION||'</TD>'||
           '<TD>'||STATUS||'</TD></TR>'
from ckrau_dia_products
order by 1
/
PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="REPOSITORY%20INFORMATION"></A>Repository Information</H3>
PROMPT <TABLE BORDER="2" WIDTH="100%">
PROMPT	<TR>
PROMPT		<TD class="bold">Parameter</TD>
PROMPT		<TD class="bold">Setting</TD>
PROMPT	</TR>


rem bug 2949044 cheaps
declare

 tab_not_exist  EXCEPTION;
 PRAGMA         EXCEPTION_INIT(tab_not_exist, -00942);
 
 synonym_translation EXCEPTION;
 PRAGMA         EXCEPTION_INIT(synonym_translation, -00980); 

begin 

delete from temp_ckrau_diag;
execute immediate ('insert into temp_ckrau_diag 
(select 
  ''<TR><TD>Repository Version </TD><TD>''||REPOSITORY_VERSION||''</TD></TR>''
||  ''<TR><TD>Repository Owner   </TD><TD>''||OWNER_NAME||''</TD></TR>''
|| ''<TR><TD>Repository Language</TD><TD>''||NLS_LANGUAGE||''</TD></TR>''
|| ''<TR><TD>Supports Core      </TD><TD>''||decode(SUPPORTS_CORE,    ''Y'', ''Yes'', ''No'')||''</TD></TR>''
|| ''<TR><TD>Supports Designer  </TD><TD>''||decode(SUPPORTS_DESIGNER,''Y'', ''Yes'', ''No'')||''</TD></TR>''
|| ''<TR><TD>Supports Versioning</TD><TD>''||decode(SUPPORTS_VERSION, ''Y'', ''Yes'', ''No'')||''</TD></TR>''
|| ''<TR><TD>Synonym Access     </TD><TD>''||decode(PUBLIC_SYNONYM_FLAG,''Y'',''Public'',''Private'')||''</TD></TR>''
from RM$REPOSITORIES)');

execute immediate('insert into temp_ckrau_diag 
(select 
  ''<TR><TD>Performance Enhancements </TD><TD>''||DECODE(COUNT(*), 1, ''Enabled'', ''Disabled'')||''</TD></TR>''
	FROM V$OPTION WHERE LOWER(PARAMETER) = ''fine-grained access control'' AND VALUE = ''TRUE'' AND EXISTS(SELECT 1 FROM ALL_TAB_PRIVS_RECD WHERE TABLE_NAME = ''DBMS_RLS'' AND PRIVILEGE = ''EXECUTE'') and EXISTS (SELECT DISTINCT(1) FROM CK_INSTALLED_OBJECTS WHERE CI_STAGE = ''JRPOLICY'' AND CI_CREATED = ''Y''))');


exception 
  when tab_not_exist OR synonym_translation then
    execute immediate 'insert into temp_ckrau_diag  
                       values (''<TR><TD><I>Access to RM$REPOSITORIES unavailable</I></TD><TD>^&nbsp;</TD></TR>'') ';    

end;
/
select * from temp_ckrau_diag
/

PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="DATABASE%20COMPONENT%20VERSIONS"></A>Database Component Versions</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Components</TD>
PROMPT	</TR>
select '<TR><TD>'||BANNER||'</TD></TR>' 
from V$VERSION
order by 1
/
PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="RAU%20PARAMETERS"></A>RAU Parameters</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Name</TD>
PROMPT		<TD class="bold">Value</TD>
PROMPT	</TR>

PROMPT	<TR><TD> [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\CHELP72] </TD>
PROMPT	    <TD> ^&nbsp; </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; HELP72_RAU </TD>
PROMPT	    <TD> &HELP72_RAU </TD></TR>
PROMPT	<TR><TD> [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\REPADM61] </TD>
PROMPT	    <TD> ^&nbsp; </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; EXECUTE_OBJSTAT </TD>
PROMPT	    <TD> &EXECUTE_OBJSTAT </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; EXECUTE_REPOSPARAM </TD>
PROMPT	    <TD> &EXECUTE_REPOSPARAM </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; LOG_DIRECTORY_RAU </TD>
PROMPT	    <TD> &LOG_DIRECTORY_RAU </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; REPADM61 </TD>
PROMPT	    <TD> &REPADM61 </TD></TR>
PROMPT	<TR><TD> [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\REPOS61] </TD>
PROMPT	    <TD> ^&nbsp; </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; EXECUTE_EXPORT </TD>
PROMPT	    <TD> &EXECUTE_EXPORT </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; EXECUTE_IMPORT </TD>
PROMPT	    <TD> &EXECUTE_IMPORT </TD></TR>
PROMPT	<TR><TD> ^&nbsp;^&nbsp;^&nbsp; EXECUTE_PLUS_RAU </TD>
PROMPT	    <TD> &EXECUTE_PLUS_RAU </TD></TR>
PROMPT	<TR><TD> Working Directory </TD>
PROMPT	    <TD> &WORKING_DIRECTORY </TD></TR>
PROMPT	</TR>
PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="ROLES%20GRANTED"></A>Roles Granted</P>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Roles</TD>
PROMPT	</TR>
select '<TR><TD WIDTH="30%">'||role||'</TD></TR>' from SESSION_ROLES order by 1
/
PROMPT </TABLE>

PROMPT <H3 class="header"><A NAME="GRANTED%20SYSTEM%20PRIVILEGES"></A>Granted System Privileges</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">System Privileges</TD>
PROMPT	</TR>
select '<TR><TD>'||privilege||'</TD></TR>' from SESSION_PRIVS order by 1
/
PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="MISSING%20SYSTEM%20PRIVILEGES"></A>Missing System Privileges</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Missing System Privileges</TD>
PROMPT	</TR>
select '<TR><TD style="color:red;">'||a.priv_req||'</TD></TR>'
from ( select 'ALTER SESSION' priv_req from sys.dual
       union
       select 'CREATE DATABASE LINK' priv_req from sys.dual
       union
       select 'CREATE PROCEDURE' priv_req from sys.dual
       union
       select 'CREATE ROLE' priv_req from sys.dual
       union
       select 'CREATE SEQUENCE' priv_req from sys.dual
       union
       select 'CREATE SESSION' priv_req from sys.dual
       union
       select 'CREATE TABLE' priv_req from sys.dual
       union
       select 'CREATE TRIGGER' priv_req from sys.dual
       union
       select 'CREATE TYPE' priv_req from sys.dual
       union
       select 'CREATE VIEW' priv_req from sys.dual) a
where a.priv_req not in (select * from session_privs)
/

PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="DIRECT%20PRIVILEGES"></A>Direct Privileges</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Privileges</TD>
PROMPT	</TR>
select '<TR><TD>'||privilege||'</TD></TR>' from user_sys_privs order by 1
/
PROMPT </TABLE>

PROMPT <H3 class="header"><A NAME="MISSING%20DIRECT%20PRIVILEGES"></A>Missing Direct Privileges</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Missing Privileges</TD>
PROMPT	</TR>
select '<TR><TD style="color:red;">'||a.priv_req||'</TD></TR>'
from ( select 'CREATE ANY SYNONYM' priv_req from sys.dual
       union
       select 'DROP ANY SYNONYM' priv_req from sys.dual) a
where a.priv_req not in (select privilege from user_sys_privs)
/

PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="DIRECT%20OBJECT%20PRIVILEGES"></A>Direct Object Privileges</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Owner</TD>
PROMPT		<TD class="bold">Object</TD>
PROMPT		<TD class="bold">Grantor</TD>
PROMPT		<TD class="bold">Privilege</TD>
PROMPT		<TD class="bold">Grantable</TD>
PROMPT	</TR>
select 
   '<TR><TD>'||OWNER     ||'</TD>'||
       '<TD>'||TABLE_NAME||'</TD>'||
       '<TD>'||GRANTOR   ||'</TD>'||
       '<TD>'||PRIVILEGE ||'</TD>'||
       '<TD>'||GRANTABLE ||'</TD></TR>'
from  user_tab_privs_recd
order by table_name
/
PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="MISSING%20DIRECT%20OBJECT%20PRIVILEGES"></A>Missing Direct Object Privileges</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Object</TD>
PROMPT		<TD class="bold">Privilege</TD>
PROMPT	</TR>
select '<TR><TD>'||a.obj ||'</TD>'||
           '<TD>'||a.priv||'</TD></TR>'
from ( select 'EXECUTE' priv,'DBMS_LOCK' obj from sys.dual
       union
       select 'EXECUTE' priv,'DBMS_PIPE' obj from sys.dual
       union
       select 'SELECT' priv,'DBA_ROLLBACK_SEGS' obj from sys.dual
       union
       select 'SELECT' priv,'DBA_SEGMENTS' obj from sys.dual
       union
       select 'SELECT' priv,'V_$NLS_PARAMETERS' obj from sys.dual
       union
       select 'SELECT' priv,'V_$PARAMETER' obj from sys.dual
	   union 
	   select 'EXECUTE' priv,'DBMS_RLS' obj from sys.dual) a
where (a.obj,a.priv) not in (select TABLE_NAME,PRIVILEGE from user_tab_privs_recd)
/

PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="TABLESPACE%20INFORMATION"></A>Tablespace Information<P>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">TABLESPACE</TD>
PROMPT		<TD class="bold">STATUS</TD>
PROMPT		<TD class="bold">DFLT?</TD>
PROMPT		<TD class="bold">TEMP?</TD>
PROMPT		<TD class="bold">INIT.EXT.</TD>
PROMPT		<TD class="bold">NEXT.EXT.</TD>
PROMPT		<TD class="bold">PCT.INC.</TD>
PROMPT		<TD class="bold">MAX.EXT.</TD>
PROMPT		<TD class="bold">FREE</TD>
PROMPT		<TD class="bold">LARGEST.EXT.</TD>
PROMPT		<TD class="bold">QUOTA</TD>
PROMPT		<TD class="bold">AVAILABLE</TD>
PROMPT	</TR>

select 
'<TR><TD WIDTH="10%">'||ts.tablespace_name||'</TD>'||
'<TD>'||ts.status||'</TD>'||
'<TD>'||decode(ts.tablespace_name,u.default_tablespace,'Y','N')||'</TD>'|| 
'<TD>'||decode(ts.tablespace_name,u.temporary_tablespace,'Y','N')||'</TD>'|| 
'<TD>'||ts.initial_extent||'</TD>'|| 
'<TD>'||nvl(ts.next_extent,0)||'</TD>'||
'<TD>'||ts.pct_increase||'</TD>'||
'<TD>'||decode(ts.max_extents, null, '^&nbsp', ts.max_extents) ||'</TD>'|| 
'<TD>'||nvl(sum(i.bytes), 0)||'</TD>'||  
'<TD>'||nvl(max(i.bytes),0)||'</TD>'|| 
'<TD>'||'UNLIMITED'||'</TD>'|| 
'<TD>'||nvl(sum(i.bytes), 0)||'</TD></TR>'
from user_free_space i,user_users u, user_tablespaces ts
where ts.tablespace_name=i.tablespace_name(+)   
and exists (select 1 from session_privs where privilege='UNLIMITED TABLESPACE')   
group by i.tablespace_name  ,u.temporary_tablespace  ,u.default_tablespace  ,ts.initial_extent  
,ts.next_extent  ,ts.pct_increase  ,ts.max_extents  ,ts.status,ts.tablespace_name 	
UNION ALL  
select 
'<TR><TD WIDTH="10%">'||ts.tablespace_name||'</TD>'||
'<TD>'||ts.status||'</TD>'||
'<TD>'||decode(ts.tablespace_name,u.default_tablespace,'Y','N')||'</TD>'|| 
'<TD>'||decode(ts.tablespace_name,u.temporary_tablespace,'Y','N')||'</TD>'|| 
'<TD>'||ts.initial_extent||'</TD>'|| 
'<TD>'||ts.next_extent||'</TD>'||
'<TD>'||ts.pct_increase||'</TD>'||
'<TD>'||decode(ts.max_extents, null, '^&nbsp', ts.max_extents) ||'</TD>'|| 
'<TD>'||nvl(sum(i.bytes), 0)||'</TD>'||  
'<TD>'||nvl(max(i.bytes),0)||'</TD>'||
'<TD>'||decode(q.max_bytes,-1,'UNLIMITED', null, '^&nbsp', q.max_bytes)||'</TD>'||    
'<TD>'||decode(q.max_bytes,-1,sum(i.bytes),null, 0, least(sum(i.bytes),(q.max_bytes-q.bytes)))||'</TD></TR>'   
from   user_free_space i,user_ts_quotas q,user_users u, user_tablespaces ts  
where  q.tablespace_name(+)=i.tablespace_name 
and    ts.tablespace_name=i.tablespace_name(+)   
and not exists  (select 1 from session_privs where privilege = 'UNLIMITED TABLESPACE')   
group by q.tablespace_name  ,q.max_bytes  ,q.bytes ,u.temporary_tablespace  ,u.default_tablespace 
,ts.initial_extent ,ts.next_extent  ,ts.pct_increase ,ts.max_extents  ,ts.status,ts.tablespace_name
/
PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="USER%20DETAILS"></A>User Details</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD ROWSPAN="2" class="bold">Name</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Type</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Date Created</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Description</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Schema Owner</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Status</TD>
PROMPT		<TD COLSPAN="6" class="bold">Manager</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Set Policy</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Purge</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Force</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Global Perge</TD>
PROMPT		<TD COLSPAN="7" class="bold">Connect Via</TD>
PROMPT		<TD ROWSPAN="2" class="bold">Synonymn Access</TD>
PROMPT	</TR>
PROMPT	<TR>
PROMPT		<TD class="bold">WA</TD>
PROMPT		<TD class="bold">CFG</TD>
PROMPT		<TD class="bold">CNT</TD>
PROMPT		<TD class="bold">USR</TD>
PROMPT		<TD class="bold">BR</TD>
PROMPT		<TD class="bold">DEPN</TD>
PROMPT		<TD class="bold">RON</TD>
PROMPT		<TD class="bold">RAU</TD>
PROMPT		<TD class="bold">MTX</TD>
PROMPT		<TD class="bold">REP</TD>
PROMPT		<TD class="bold">CLI</TD>
PROMPT		<TD class="bold">VHV</TD>
PROMPT		<TD class="bold">VEV</TD>
PROMPT	</TR>

rem bug 2949044 cheaps
declare

 tab_not_exist  EXCEPTION;
 PRAGMA         EXCEPTION_INIT(tab_not_exist, -00942); 
 /* EJH 19-JUN-03 Bug 3001017 */
 invalid_column EXCEPTION;
 PRAGMA         EXCEPTION_INIT(invalid_column, -00904);
 /* EJH 19-JUN-03 Bug 3001017 */
 synonym_translation EXCEPTION;
 PRAGMA         EXCEPTION_INIT(synonym_translation, -00980); 



begin 

delete from temp_ckrau_diag;
execute immediate ( 'insert into temp_ckrau_diag
(select  ''<TR><TD>''||FULL_USER_NAME||''</TD>''||
        ''<TD>''||decode(USER_TYPE,''1'', ''USER'',''ROLE'')||''</TD>''||
        ''<TD>''||to_char(DATE_CREATED,''DD-MON-YYYY'')||''</TD>''||
        ''<TD>''||nvl(DESCRIPTION,''^&nbsp;'')||''</TD>''||
        ''<TD>''||nvl(SDD_OWNER,''N'')||''</TD>''||
        ''<TD>''||decode(STATUS,''I'',''NEW'',''D'',''DELETED'',''VALID'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''MANAGE_WORKAREA'')||''</TD>''||  
        ''<TD>''||jr_sys_privs.get_privilege(username, ''MANAGE_CONFIGURATION'')||''</TD>''||    
        ''<TD>''||jr_sys_privs.get_privilege(username, ''MANAGE_CONTAINER'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''MANAGE_USER'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''MANAGE_BRANCH_LABEL'')||''</TD>''||    
        ''<TD>''||jr_sys_privs.get_privilege(username, ''MANAGE_DEPENDENCY'')||''</TD>''||             
        ''<TD>''||jr_sys_privs.get_privilege(username, ''SET_POLICY'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''PURGE'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''FORCE'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''GLOBAL_PURGE'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''CONNECT_VIA_RON'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''CONNECT_VIA_RAU'')||''</TD>''||
        ''<TD>''||jr_sys_privs.get_privilege(username, ''CONNECT_VIA_MATRIX'')||''</TD>''||   
        ''<TD>''||jr_sys_privs.get_privilege(username, ''CONNECT_VIA_REPORTS'')||''</TD>''||    
        ''<TD>''||jr_sys_privs.get_privilege(username, ''CONNECT_VIA_CLI'')||''</TD>''||    
        ''<TD>''||jr_sys_privs.get_privilege(username, ''CONNECT_VIA_VHV'')||''</TD>''||    
        ''<TD>''||jr_sys_privs.get_privilege(username, ''CONNECT_VIA_VEV'')||''</TD>''|| 
        ''<TD>''||decode(PUBLIC_SYNONYM_FLAG,''Y'',''PUBLIC'',''PRIVATE'')||''</TD></TR>'' 
from sdw_users)');

exception 
  when tab_not_exist OR synonym_translation then
  null;
  /* EJH 19-JUN-03 Bug 3001017 */
  when invalid_column then
  null;
  /* <- EJH 19-JUN-03 Bug 3001017 */

end;
/

select * from temp_ckrau_diag 
/
rem order by 1

PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="OBJECT%20STATUS"></A>Object Status</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">State</TD>
PROMPT		<TD class="bold">Total</TD>
PROMPT	</TR>



rem bug 2949044 cheaps
declare

 tab_not_exist  EXCEPTION;
 PRAGMA         EXCEPTION_INIT(tab_not_exist, -00942); 


begin 

delete from temp_ckrau_diag;
execute immediate ('insert into temp_ckrau_diag
(select ''<TR><TD>Total number of ''||state||'' objects ''||''</TD>''||
           ''<TD>''||count(*)||''</TD></TR>'' 
from  (
select /*+ FIRST_ROWS */ 
  ci1.ci_type ty
, decode(uo1.status,NULL,''MISSING'',uo1.status) state
, ci1.ci_name name
from user_objects                  uo1
,    ck_installed_objects_resolved ci1 
where ci1.ci_type = ''TABLE'' 
and   ci1.ci_type = uo1.object_type(+) 
and   ci1.ci_name = uo1.object_name(+) 
UNION ALL
select  /*+ FIRST_ROWS */
  ci2.ci_type ty
, decode(uc2.status,NULL,''MISSING'',uc2.status) state
, ci2.ci_name name
from user_constraints              uc2
,    ck_installed_objects_resolved ci2 
where ci2.ci_type   = ''CONSTRAINT'' 
and   ci2.ci_name   = uc2.constraint_name(+) 
and   uc2.owner  (+)= user
UNION ALL
select /*+ FIRST_ROWS  */ 
  ci3.ci_type ty
, decode(ui3.status,NULL,''MISSING'',ui3.status) state
, ci3.ci_name name
from user_indexes                  ui3
,    ck_installed_objects_resolved ci3
where ci3.ci_type = ''INDEX'' 
and ci3.ci_name   = ui3.index_name(+) 
and ci3.ci_name not in (select ci33.ci_name 
                       from  ck_installed_objects ci33 
                       where ci33.ci_stage = ''JRLOBIDX'') 
and ui3.table_owner(+) = user
UNION ALL
select /*+ FIRST_ROWS  */ 
  ci4.ci_type ty
, decode(uo4.status,NULL,''MISSING'',uo4.status) state
, ci4.ci_name name
from user_objects                  uo4
,    ck_installed_objects_resolved ci4 
where ci4.ci_type in (''FUNCTION''
                    ,''LOB''
                    ,''PACKAGE''
                    ,''PACKAGE BODY''
                    ,''SEQUENCE''
                    ,''SYNONYM''
                    ,''TYPE'' 
                    ,''VIEW''
                    ,''TRIGGER'')
and ci4.ci_type = uo4.object_type(+) 
and ci4.ci_name = uo4.object_name(+) 
UNION ALL
select /*+ FIRST_ROWS */ 
  ci5.ci_type ty
, decode(ut5.status,NULL,''MISSING'',ut5.status) state
, ci5.ci_name name
from user_triggers                 ut5
,    ck_installed_objects_resolved ci5 
where ci5.ci_type = ''TRIGGER'' 
and   ci5.ci_name = ut5.trigger_name(+))
group by state
UNION
select ''<TR><TD WIDTH="60%">Total number of objects ''||''</TD>''||
        ''<TD WIDTH="60%">''||count(*)||''</TD></TR>''
from  (
select /*+ FIRST_ROWS */ 
  ci1.ci_type ty
, decode(uo1.status,NULL,''MISSING'',uo1.status) state
, ci1.ci_name name
from user_objects                  uo1
,    ck_installed_objects_resolved ci1 
where ci1.ci_type = ''TABLE'' 
and   ci1.ci_type = uo1.object_type(+) 
and   ci1.ci_name = uo1.object_name(+) 
UNION ALL
select  /*+ FIRST_ROWS */
  ci2.ci_type ty
, decode(uc2.status,NULL,''MISSING'',uc2.status) state
, ci2.ci_name name
from user_constraints              uc2
,    ck_installed_objects_resolved ci2 
where ci2.ci_type   = ''CONSTRAINT'' 
and   ci2.ci_name   = uc2.constraint_name(+) 
and   uc2.owner  (+)= user
UNION ALL
select /*+ FIRST_ROWS  */ 
  ci3.ci_type ty
, decode(ui3.status,NULL,''MISSING'',ui3.status) state
, ci3.ci_name name
from user_indexes                  ui3
,    ck_installed_objects_resolved ci3
where ci3.ci_type = ''INDEX'' 
and ci3.ci_name   = ui3.index_name(+) 
and ci3.ci_name not in (select ci33.ci_name 
                       from  ck_installed_objects ci33 
                       where ci33.ci_stage = ''JRLOBIDX'') 
and ui3.table_owner(+) = user
UNION ALL
select /*+ FIRST_ROWS  */ 
  ci4.ci_type ty
, decode(uo4.status,NULL,''MISSING'',uo4.status) state
, ci4.ci_name name
from user_objects                  uo4
,    ck_installed_objects_resolved ci4 
where ci4.ci_type in (''FUNCTION''
                    ,''LOB''
                    ,''PACKAGE''
                    ,''PACKAGE BODY''
                    ,''SEQUENCE''
                    ,''SYNOMYM''
                    ,''TYPE'' 
                    ,''VIEW''
                    ,''TRIGGER'')
and ci4.ci_type = uo4.object_type(+) 
and ci4.ci_name = uo4.object_name(+) 
UNION ALL
select /*+ FIRST_ROWS */ 
  ci5.ci_type ty
, decode(ut5.status,NULL,''MISSING'',ut5.status) state
, ci5.ci_name name
from user_triggers                 ut5
,    ck_installed_objects_resolved ci5 
where ci5.ci_type = ''TRIGGER'' 
and   ci5.ci_name = ut5.trigger_name(+))
)');

exception 
  when tab_not_exist then
  null;

end;
/

select * from temp_ckrau_diag
/


PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="OBJECTS%20FLAGGED%20FOR%20RELOAD"></A>Objects Flagged for Reload</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold"></TD>
PROMPT		<TD class="bold">Total</TD>
PROMPT	</TR>


rem bug 2949044 cheaps
declare

 tab_not_exist  EXCEPTION;
 PRAGMA         EXCEPTION_INIT(tab_not_exist, -00942); 


begin 

delete from temp_ckrau_diag;
execute immediate ('insert into temp_ckrau_diag
(select ''<TR><TD>Total number of RELOAD objects ''||''</TD>''||
        ''<TD>''||count(*)||''</TD></TR>''
from   ck_installed_objects_resolved
where  ci_created = ''R'')');

exception 
  when tab_not_exist then
  null;

end;
/

select * from temp_ckrau_diag
/


PROMPT </TABLE>

rem ***************************************************************************
PROMPT <H3 class="header"><A NAME="REPOSITORY%20PATCH%20INFORMATION"></A>Repository Patch Information</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Patch Name</TD>
PROMPT		<TD class="bold">Installed By</TD>
PROMPT		<TD class="bold">Installed On</TD>
PROMPT		<TD class="bold">Installed OK</TD>
PROMPT	</TR>

rem order by INSTALLED_ON,PATCH_NAME  -- taken from select below
rem bug 2949044 cheaps
declare

 tab_not_exist  EXCEPTION;
 PRAGMA         EXCEPTION_INIT(tab_not_exist, -00942); 
 
 synonym_translation EXCEPTION;
 PRAGMA         EXCEPTION_INIT(synonym_translation, -00980); 


begin 

delete from temp_ckrau_diag;
execute immediate ('insert into temp_ckrau_diag 
( select 
  ''<TR><TD>''||PATCH_NAME   ||''</TD>''||
      ''<TD>''||INSTALLED_BY ||''</TD>''||
      ''<TD>''||INSTALLED_ON ||''</TD>''||
      ''<TD>''||INSTALLED_OK ||''</TD></TR>''
from RM$PATCHES )');



exception 
  when tab_not_exist OR synonym_translation then
  execute immediate 'insert into temp_ckrau_diag  
                       values (''<TR><TD><I>Access to RM$PATCHES unavailable</I></TD><TD>^&nbsp;</TD><TD>^&nbsp;</TD><TD>^&nbsp;</TD></TR>'') ';    
  --null;

end;
/

select * from temp_ckrau_diag 
/

PROMPT </TABLE>

rem ***************************************************************************
rem
rem  Create temp table for Database parameters since
rem  on 8.1.7 view database_properties not available
rem
declare
  table_does_not_exist exception;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);  
begin
  begin
    execute immediate ' drop table rau_report_db_params';
  exception when table_does_not_exist then
    null;
  end;
    
  begin
    execute immediate ' create table rau_report_db_params 
                        as select * from database_properties ';
  exception when table_does_not_exist then
    execute immediate ' create table rau_report_db_params 
                        (
                          property_name  varchar2(30)
                        , property_value varchar2(4000)
                        , description    varchar2(4000)
                        ) ';
  
    execute immediate 'insert into rau_report_db_params(property_name, property_value, description) 
                       values (''^&nbsp;'',''^&nbsp;'', ''Access to view DATABASE_PROPERTIES not available in Oracle Server 8.1.7'') ';    
    
    commit;
  end; 
end;
/
PROMPT <H3 class="header"><A NAME="DATABASE%20PROPERTIES"></A>Database Properties</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Property</TD>
PROMPT		<TD class="bold">Setting</TD>
PROMPT		<TD class="bold">Description</TD>
PROMPT	</TR>

select '<TR><TD>'||PROPERTY_NAME||'</TD>'||
           '<TD>'||PROPERTY_VALUE||'</TD>'||
           '<TD>'||DESCRIPTION||'</TD></TR>'
from rau_report_db_params
order by 1
/
PROMPT </TABLE>

rem ***************************************************************************
rem EJH 19-JUN-03 Bug 3001017 ->
rem Create temporary table for V$PARAMETER
rem

declare
  table_does_not_exist exception;
  PRAGMA EXCEPTION_INIT(table_does_not_exist, -942);  
begin
  
  begin
    execute immediate ' drop table rau_report_v_parameter';
  exception when table_does_not_exist then
    null;
  end;
  
  
  begin
    execute immediate ' create table rau_report_v_parameter 
                        as select NAME, VALUE, DESCRIPTION from V$PARAMETER ';
  exception when table_does_not_exist then
    execute immediate ' create table rau_report_v_parameter 
                        ( NAME         VARCHAR2(64)
                        , VALUE        VARCHAR2(512)
                        , DESCRIPTION  VARCHAR2(64)
                        ) ';

    execute immediate 'insert into rau_report_v_parameter(NAME, VALUE, DESCRIPTION) 
                       values (''<I>Access to view V$PARAMETER not available</I>'', ''^&nbsp;'',''^&nbsp;'') ';    
   
    commit;

  end; 
end;
/
rem <- EJH 19-JUN-03 Bug 3001017

PROMPT <H3 class="header"><A NAME="DATABASE%20PARAMETERS"></A>Database Parameters</H3>
PROMPT <TABLE BORDER CELLPADDING="2">
PROMPT	<TR>
PROMPT		<TD class="bold">Name</TD>
PROMPT		<TD class="bold">Value</TD>
PROMPT		<TD class="bold">Description</TD>
PROMPT	</TR>
select '<TR><TD>'||NAME||'</TD>'||
       '<TD>'||nvl(VALUE,'<I>not set</I>')||'</TD>'||
       '<TD>'||DESCRIPTION ||'</TD></TR>'
from rau_report_v_parameter 
order by 1
/
PROMPT </TABLE>

rem ***************************************************************************
PROMPT <BR>
PROMPT <TABLE border=0 cellPadding=0 cellSpacing=0 width="100%">
PROMPT <TR>
PROMPT   <TD class="title" style="background-color:#FFFFFF;" background="&image_path\space.gif" align="left" vAlign="bottom" width=100%><IMG height=1 src="&image_path\space.gif" width=1></TD>
PROMPT   <TD class="title" style="background-color:#FFFFFF;" background="&image_path\space.gif" vAlign="top" width=60><DIV align=right><IMG height=18 src="&image_path\btailtop.gif" width=45></DIV></TD>
PROMPT   <TD class="title" align="left" background="&image_path\rhshadow.gif" height=18 vAlign=center width=5><IMG height=1 src="&image_path\space.gif" width=1></TD>
PROMPT </TR>
PROMPT <TR>
PROMPT   <TD class="title" height=15 vAlign="center" width=100%><IMG height=1 src="&image_path\space.gif" width=1></TD>
PROMPT   <TD class="title" height=15 vAlign="center" width=60><IMG height=1 src="&image_path\space.gif" width=1></TD>
PROMPT   <TD class="title" background="&image_path\rhshadow.gif" height=15 vAlign=center width=5><IMG height=1 src="&image_path\space.gif" width=1></TD>
PROMPT </TR>
PROMPT <TR>
PROMPT   <TD class="title" style="background-color:#FFFFFF" background="&image_path\bot.gif" vAlign="top" width=100%><IMG height=8 src="&image_path\lend.gif" width=10></TD>
PROMPT   <TD class="title" style="background-color:#FFFFFF" background="&image_path\bot.gif" height=10 vAlign=top width=60>^&nbsp;</TD>
PROMPT   <TD class="title" style="background-color:#FFFFFF" vAlign="top" width=5><IMG height=10 src="&image_path\rend.gif"></TD>
PROMPT </TR>
PROMPT </TABLE>
PROMPT </BODY>
PROMPT </HTML>

REM bug 2949044 cheaps
drop table temp_ckrau_diag;
spool off
exit
