--  axiUm Pre-Upgrade report
--

set SERVEROUTPUT ON;
set lines 9999;
set pages 9999;
DECLARE

  vRunTSFree boolean := true; -- set to TRUE to run this check - Slow!
  vRunSequenceCheck boolean := true; -- set to TRUE to run this check - Slow!

  vVer VARCHAR2( 8 ) := '1.27'; -- update when changing script
  
  TYPE curtype is ref cursor; 
  vcur curtype;
  vStatement varchar2(1000);
  vText VARCHAR2( 8000 );
  vText2 VARCHAR2( 8000 );
  vText3 VARCHAR2( 8000 );
  vText4 VARCHAR2( 8000 );
  vText5 VARCHAR2( 8000 );
  vText6 VARCHAR2( 8000 );
  vNumber Number;
  vCount NUMBER := 0 ;
  vInvalidIndexes Number :=0;
  vCompTables NUMBER := 0 ;
  vUndo NUMBER := 0;
  vUndoDemand NUMBER := 0;
  vReten NUMBER := 0;
  vOracleVer Number :=0;
  vUpgradeOK Boolean := true;
  vSchema varchar2(30);
  vNoAccess Boolean := false;
  vSectionNum number:=0;
  
  --exceptions
  ora942 EXCEPTION;
  PRAGMA EXCEPTION_INIT(ora942, -942);
  
  --cursors
  cursor tblspaces_cur is select tablespace_name from user_tables where tablespace_name is not null group by tablespace_name order by tablespace_name;
  cursor idxspaces_cur is select tablespace_name from user_indexes where tablespace_name is not null group by tablespace_name order by tablespace_name;
  cursor comptables_cur is select table_name from user_tables where compression <> 'DISABLED' order by table_name;
  Cursor invalidobj_cur is select object_name, object_type, status from user_objects where status <> 'VALID' order by object_type, object_name;
  cursor practice_cur is select "Id", "Name", "Version", "VersionSrc", "Inactive" from practice order by "Id";
  /*cursor datafiles_cur is 
     select tablespace_name, autoextensible, (round(bytes/1048576)) MB, (round((maxbytes - bytes)/1048576)) MBToGrow, Increment_by nextblocks, file_name 
     from dba_data_files
     order by tablespace_name;*/
  /*cursor tablespaces_cur is 
    SELECT TABLESPACE_NAME, round(SUM(BYTES)/1048576)  FreeMB
    FROM SYS.DBA_FREE_SPACE
    GROUP  BY TABLESPACE_NAME
    order by tablespace_name;*/
  cursor tablespaces_cur is
    select rpad(a.TABLESPACE_NAME, 30) TABLESPACE_NAME,
	   rpad(to_char(a.BYTES), 10) Size_MB,
       rpad(to_char(a.BYTES - b.BYTES), 10) Used_MB,
	   rpad(to_char(b.BYTES), 10) Free_MB,
       rpad(to_char(a.MAXBYTES-(a.BYTES - b.BYTES)), 10) MaxFree_MB,
	   round(((a.BYTES-(a.BYTES - b.BYTES))/a.BYTES)*100,2) Percent_Free,
       round(((a.MAXBYTES-(a.BYTES - b.BYTES))/a.MAXBYTES)*100,2) Percent_MaxFree
    from
	(select	TABLESPACE_NAME,
		round(sum(BYTES)/1048576, 0) BYTES,
                round(sum(decode (MAXBYTES, 0, BYTES, MAXBYTES))/1048576, 0) MAXBYTES
         from  dba_data_files
         group by TABLESPACE_NAME) a,
        (select TABLESPACE_NAME,
                round(sum(BYTES)/1048576, 0) BYTES
         from  dba_free_space
         group by TABLESPACE_NAME)b
    where a.TABLESPACE_NAME=b.TABLESPACE_NAME
    order by (a.MAXBYTES-(a.BYTES - b.BYTES));    
  cursor webservers_cur is select machine from v$session where Upper(username) = upper(user) and upper( program) = 'W3WP.EXE' group by machine;  
  cursor sessions_cur is Select nvl(username, '<Oracle>') username, count(1) sessions from v$session group by username;
  cursor weektrans_cur is 
    select trunc("ModifiedDate") mdate, count(1) trans from history 
    where "ModifiedDate" >= Trunc(Sysdate - 7)
    group by trunc("ModifiedDate")
    order by  trunc("ModifiedDate");
  cursor lowercaseviews_cur is select object_name from user_objects where object_type = 'VIEW' and REGEXP_LIKE(object_name,'[:lower:]');
  cursor bigtables_cur is select rpad(table_name, 15) tablename,  to_char(num_rows, '999,999,999') numrows from user_Tables where num_rows > 1000000 order by num_rows desc;

procedure sectionheader(pTitle in varchar2) is
Begin
  vSectionNum := vSectionNum + 1;
  dbms_output.put_line(' ');
  dbms_output.put_line(ptitle);
  dbms_application_info.set_client_info(vSectionNum || '. ' || ptitle);
end sectionheader;  

FUNCTION customtblspace ( textin IN VARCHAR2) RETURN BOOLEAN 
IS
    tblspace   VARCHAR2(256);
BEGIN
  tblspace := trim(upper(textin) );
  IF tblspace NOT IN ( 'TBLSPC_PHUGE','TBLSPC_PHIGH', 'TBLSPC_PBLOB', 'TBLSPC_IDYNAMO', 'TBLSPC_ISTATIC', 'TBLSPC_PMEDIUM', 'TBLSPC_IGROW', 'TBLSPC_PGROW',
    'TBLSPC_PSTATIC', 'TBLSPC_IMEDIUM', 'TBLSPC_IHIGH', 'TBLSPC_PDYNAMO', 'TBLSPC_IBLOB', 'TBLSPC_PTLETTER')
  THEN
    RETURN true;
  END IF;
  RETURN false;
END;

Procedure checkAuditPolicies(pSchema in varchar2)
Is
  vCount Number :=0;
  
  cursor axaudit_cur is select policy_name, object_name from all_audit_policies 
    where object_schema = UPPER(pSchema) 
    and object_Name = substr(policy_name, 1, length(object_name))
    and enabled = 'YES'
    order by object_name;
    
  cursor non_axaudit_cur is select * from all_audit_policies 
    where object_schema = UPPER(pSchema) 
    and object_Name = substr(policy_name, 1, length(object_name))
    and enabled <> 'YES'
    order by object_name;

Begin
  sectionheader('axiUm Audit Policies:');
  for crow in axaudit_cur loop
    dbms_output.put_line(crow.policy_name || '   ' || crow.object_name);
    vCount := vCount + 1;
  end loop;
  
  sectionheader('Non-axiUm Audit Policies:');
  for crow in non_axaudit_cur loop
    dbms_output.put_line(crow.policy_name || '   ' || crow.object_name);
    vCount := vCount + 1;   
  end loop;
  
  if vcount  > 0 then
    dbms_output.put_line(' ');
    dbms_output.put_line('***** Advanced Auditing Enabled ******');
    dbms_output.put_line('Auditing can slow down the upgrade and use LOTS of disk space');
    dbms_output.put_line('Disable Auditing before Upgrading and re-enable after');
  end if;  
end checkAuditPolicies;

Procedure CheckPrivs
is 
  j number :=0;
  MissingPrivs number := 0;
  type array_t is varray(3) of varchar2(20);
  type array_s is varray(31) of varchar2(34);
  tabprivs array_t := array_t('DBMS_CRYPTO', 'DBMS_LOCK', 'DBMS_FGA');
  sysprivs array_s := array_s(
        'ALTER ANY CLUSTER',
        'ALTER ANY INDEX',
        'ALTER ANY PROCEDURE',
        'ALTER ANY SEQUENCE',
        'ALTER ANY TABLE',
        'ALTER SESSION',
        'ANALYZE ANY', 
        'CREATE ANY INDEX', 
        'CREATE ANY MATERIALIZED VIEW',
        'CREATE ANY PROCEDURE', 
        'CREATE ANY SEQUENCE', 
        'CREATE ANY TABLE',
        'CREATE ANY VIEW',
        'CREATE PROCEDURE',
        'CREATE SEQUENCE',
        'CREATE TABLE', 
        'CREATE TRIGGER', 
        'DELETE ANY TABLE',
        'DROP ANY INDEX',
        'DROP ANY PROCEDURE',
        'DROP ANY SEQUENCE',
        'DROP ANY TABLE', 
        'EXECUTE ANY TYPE', 
        'INSERT ANY TABLE',
        'QUERY REWRITE', 
        'SELECT ANY DICTIONARY', 
        'SELECT ANY SEQUENCE',
        'SELECT ANY TABLE', 
        'UNLIMITED TABLESPACE',
        'UPDATE ANY TABLE',
        'CREATE ANY TYPE');


Begin
  sectionheader('Missing Privileges:'); 
   
  for i in 1 .. tabprivs.count loop
    Select count(1) into j from dba_tab_privs where grantee = vSchema and table_name = tabprivs(i);
    if j < 1 then
      dbms_output.put_line('Missing ' || tabprivs(i) || ' privilege');
      MissingPrivs := MissingPrivs + 1;
    end if;
  end loop;
  
  for i in 1 .. sysprivs.count loop
    Select count(1) into j from dba_sys_privs where grantee = vSchema and privilege = sysprivs(i);
    if j < 1 then
      dbms_output.put_line('Missing ' || sysprivs(i) || ' privilege');
      MissingPrivs := MissingPrivs + 1;
    end if;
  end loop;
   
  If MissingPrivs = 0 then
    dbms_output.put_line('No Missing Privileges'); 
  end if;   
end CheckPrivs;

Procedure checkSequences
is
  CURSOR c1 IS
    SELECT sequence_name, table_name, column_name, increment_by, cache_size,  ' ' where_clause
    FROM user_sequences, user_tab_columns
    WHERE instr(sequence_name,'_') > 0
      AND table_name = substr(sequence_name,1,instr(sequence_name,'_') - 1)
      AND
        upper(column_name) = substr(
          sequence_name,
          instr(sequence_name,'_') + 1,
          instr(substr(sequence_name,instr(sequence_name,'_') + 1),'_SEQ') - 1
        )
    and sequence_name not in ('KIT_KIT_SEQ', 'LABORDER_CASENO_SEQ')    
    UNION 
    SELECT sequence_name, table_name, column_name, increment_by, cache_size, ' ' where_clause
    FROM user_sequences, user_tab_columns
    WHERE instr(table_name,'_') > 0
      AND instr(sequence_name,table_name) > 0
      AND  upper(column_name) = substr(
          sequence_name,
          length(TRIM(table_name) ) + 2,
          instr(substr(sequence_name,length(TRIM(table_name) ) + 2),'_SEQ') - 1
        )
    UNION 
    SELECT sequence_name, table_name, column_name, increment_by, cache_size,
        DECODE(
          sequence_name,
          'TRX_ID_ADJUSTMENT_SEQ',
          ' where "Type"=6',
          'TRX_ID_INSADJUSTMENT_SEQ',
          ' where "Type"=7',
          'TRX_ID_OFFICECHARGES_SEQ',
          ' where "Type"=2',
          'TRX_ID_PAYMENT_SEQ',
          ' where "Type"in(3,4)',
          'TRX_ID_PLANNEDTREATMENT_SEQ',
          ' where "Type"=5',
          'TRX_ID_TREATMENT_SEQ',
          ' where "Type"=1'
        ) where_clause
    FROM  user_sequences,  user_tab_columns
    WHERE table_name = 'TRX'
      AND column_name = 'Id'
      AND sequence_name LIKE 'TRX_ID_%'
    UNION 
    SELECT sequence_name, table_name, column_name, increment_by, cache_size, ' where "Type"=4' where_clause
    FROM user_sequences, user_tab_columns
    WHERE table_name = 'NOTES2'
      AND column_name = 'OtherId'
      AND sequence_name = 'NOTES2_CLINICALNOTE_SEQ'
    UNION 
    SELECT sequence_name, NULL, NULL, increment_by, cache_size,  '  ' where_clause
    FROM user_sequences
    WHERE sequence_name = 'POLICY_SHAREDENTRYID_SEQ'

    --FAPTWATCH_FAPTWATCH_SEQ
    UNION 
    SELECT sequence_name, table_name, column_name, increment_by, cache_size,  ' ' where_clause
    FROM user_sequences, user_tab_columns
    WHERE table_name = 'FAPTWATCH'
      AND column_name = 'Id'
      AND sequence_name = 'FAPTWATCH_FAPTWATCH_SEQ'
    UNION 
    SELECT sequence_name, table_name, column_name, increment_by, cache_size, ' ' where_clause
    FROM user_sequences, user_tab_columns
    WHERE table_name = 'FUSERTICKET'
      AND column_name = 'Id'
      AND sequence_name = 'FUSERTICKET_FUSERTICKET_SEQ'
    UNION 
    SELECT sequence_name, table_name, column_name, increment_by, cache_size, ' ' where_clause
    FROM user_sequences, user_tab_columns
    WHERE table_name = 'PTPHYSH'
      AND column_name = 'PtPhysH'
      AND sequence_name = 'PTPHYSHIS_PTPHYSHIS_SEQ';


  sqlstmt                VARCHAR2(1000);
  TYPE txcurtyp IS REF CURSOR;
  tx_cur                 txcurtyp;
  maxid                  NUMBER(10) := 0;
  sequence_value         NUMBER(10);
  sequence_out_of_sync   BOOLEAN;
  outofsynccount         NUMBER(5) := 0;
    
BEGIN
  sectionheader('Out of Sync Sequences:');
  FOR p IN c1 LOOP
    sequence_out_of_sync := false;
    IF p.sequence_name = 'POLICY_SHAREDENTRYID_SEQ' THEN
      SELECT MAX("SharedEntryId")INTO maxid
      FROM
        (  SELECT MAX("SharedEntryId") "SharedEntryId" FROM polins
            UNION 
            SELECT MAX("SharedEntryId") "SharedEntryId" FROM ptpol
        );

    ELSE
      IF p.increment_by > 0 THEN
          sqlstmt := 'select nvl(max("' ||  p.column_name || '"),0) from "' || p.table_name || '"' || p.where_clause;
      ELSIF p.increment_by < 0 THEN
          sqlstmt := 'select nvl(min("' ||  p.column_name || '"),2147483646) from "' || p.table_name || '"' || p.where_clause;
      END IF;
      OPEN tx_cur FOR sqlstmt;
      FETCH tx_cur INTO maxid;
      CLOSE tx_cur;
    END IF;

    sqlstmt := 'select last_number from user_sequences where sequence_name = ''' || p.sequence_name ||  '''';
    OPEN tx_cur FOR sqlstmt;
    FETCH tx_cur INTO sequence_value;
    CLOSE tx_cur;

    IF ( ( p.increment_by > 0 AND sequence_value <= maxid ) OR ( p.increment_by < 0 AND sequence_value > maxid ) ) THEN
      IF p.cache_size = 0 THEN
        sequence_out_of_sync := true;
      ELSE
        sqlstmt := 'select ' ||  p.sequence_name || '.nextval from dual';
        OPEN tx_cur FOR sqlstmt;
        FETCH tx_cur INTO sequence_value;
        CLOSE tx_cur;
        IF ( ( p.increment_by > 0 AND sequence_value <= maxid ) OR ( p.increment_by < 0 AND sequence_value >= maxid ) ) THEN
          sequence_out_of_sync := true;
        END IF;
      END IF;

      IF sequence_out_of_sync THEN
        dbms_output.put_line( p.sequence_name || '   Seq Next: ' || sequence_value || '  Max:  ' || maxid || '  Inc: ' || p.increment_by);
        outofsynccount := outofsynccount + 1;
      END IF;
    END IF;
  END LOOP;
END checksequences;


BEGIN
  -- Script Version and Run Date
  dbms_output.put_line( 'axiUm Pre-Upgrade Report version ' || vVer );
  sectionheader( 'Run at:         ' || TO_CHAR( sysdate, 'yyyy-Mon-dd hh24:mi' ) );
  
  -- Oracle Info
  sectionheader( '**ORACLE SERVER INF0** ' );
  BEGIN
    vStatement := 'select user, instance_name, TO_CHAR( startup_time, ''yyyy-Mon-dd hh24:mi:ss'' ), host_name, version, archiver from v$instance';
    vText := '';
    open vcur for vStatement ;
    loop
     fetch vcur into vText, vtext2, vtext3, vtext4, vtext5, vtext6;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    dbms_output.put_line( 'Schema:         ' || vText );  
    dbms_output.put_line( 'Instance Name:  ' || vText2 );
    dbms_output.put_line( 'Startup Time:   ' || vText3 );
    dbms_output.put_line( 'Host Name:      ' || vText4 );
    dbms_output.put_line( 'Oracle Version: ' || vText5 );
    dbms_output.put_line( 'Archiver :      ' || vText6 );
   
    vSchema := vText;   
    vOracleVer := to_number(substr(vText5, 1, 4));
    
    If vOracleVer >= 19 then
       vStatement := 'select version_full from v$instance';
       vText := '';
       open vcur for vStatement ;
       loop
         fetch vcur into vText;
         exit when vcur%NOTFOUND;
       end loop;
       close vcur;
       dbms_output.put_line( 'Full Version:   ' || vText ); 
    end if;
  EXCEPTION
   when ora942 then
      dbms_output.put_line( 'No access to v$session' );
   when others then   
   dbms_output.put_line( 'Error encountered: '  || SQLERRM);
  END;
  
  SELECT value INTO vText FROM NLS_DATABASE_PARAMETERS WHERE parameter = 'NLS_CHARACTERSET';
    dbms_output.put_line( 'Character Set:  ' || vText );   
  
  BEGIN
  
    vStatement := 'SELECT Banner FROM v$version WHERE banner LIKE ''%Database%''';
    vText := '';
    open vcur for vStatement ;
    loop
     fetch vcur into vText;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    dbms_output.put_line(  'Oracle Edition: '|| vText );
      
    vStatement := 'SELECT Banner FROM v$version WHERE banner LIKE ''%TNS%''';
    vText := '';
    open vcur for vStatement ;
    loop
     fetch vcur into vText;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    dbms_output.put_line(  'Oracle Network: '|| vText );
  EXCEPTION
    when ora942 then
      dbms_output.put_line( 'No access to v$version' );
    when others then   
      dbms_output.put_line( 'Error encountered: '  || SQLERRM);
  END;
  
  select dbms_utility.port_string into vtext from dual;
  dbms_output.put_line( 'Oracle Port:    ' || vText );
  
  Begin
    vstatement := 'select value from V$Parameter where upper(name) = ''SERVICE_NAMES''';
    vtext := ' ';
    OPEN vcur FOR vstatement ;
    LOOP
       FETCH vcur INTO vtext;
       EXIT WHEN   vcur%notfound;
    END LOOP;
    dbms_output.put_line('Service Name:   ' || vtext);
  
  exception when ora942 then
      dbms_output.put_line( 'Service Name:   No access to v$Parameter' );
    when others then   
      dbms_output.put_line( 'Service Name:   Error encountered: '  || SQLERRM);
  End;
  
  IF vOracleVer >= 12 THEN
    vstatement := 'select Property_value from cdb_properties where upper(property_name) = ''GLOBAL_DB_NAME''';
    vtext := ' ';
    OPEN vcur FOR vstatement ;
    LOOP
       FETCH vcur INTO vtext;
       EXIT WHEN   vcur%notfound;
    END LOOP;
    dbms_output.put_line('Container Name: ' || vtext);
    
    -- Oracle 12 Parameters
    Sectionheader('Oracle DB Parameters: ');  
    BEGIN
  
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''TEMP_UNDO_ENABLED''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur;
      dbms_output.put_line( 'TEMP_UNDO_ENABLED:             '|| vText );
        
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''COMPATIBLE''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur;
      dbms_output.put_line(  'COMPATIBLE:                    ' || vText );
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''OPEN_CURSORS''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur;
      dbms_output.put_line(  'OPEN_CURSORS:                  ' || vText );
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''SESSIONS''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur;
      dbms_output.put_line(  'SESSIONS:                      ' || vText );
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''PROCESSES''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur;
      dbms_output.put_line(  'PROCESSES:                     ' || vText );
      
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''OPTIMIZER_ADAPTIVE_FEATURES''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur; 
      if length(vtext) > 0 then
        dbms_output.put_line('OPTIMIZER_ADAPTIVE_FEATURES:   ' || vtext); 
      else
        dbms_output.put_line('OPTIMIZER_ADAPTIVE_FEATURES:   ' || 'NOT SET');
      end if;
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''OPTIMIZER_ADAPTIVE_PLANS''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur; 
      if length(vtext) > 0 then
        dbms_output.put_line('OPTIMIZER_ADAPTIVE_PLANS:      ' || vtext); 
      else
        dbms_output.put_line('OPTIMIZER_ADAPTIVE_PLANS:      ' || 'NOT SET');
      end if;
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''OPTIMIZER_ADAPTIVE_STATISTICS''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur; 
      if length(vtext) > 0 then
        dbms_output.put_line('OPTIMIZER_ADAPTIVE_STATISTICS: ' || vtext); 
      else
        dbms_output.put_line('OPTIMIZER_ADAPTIVE_STATISTICS: ' || 'NOT SET');
      end if;
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''_OPTIMIZER_REDUCE_GROUPBY_KEY''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur; 
      if length(vtext) > 0 then
        dbms_output.put_line('_OPTIMIZER_REDUCE_GROUPBY_KEY: ' || vtext); 
      else
        dbms_output.put_line('_OPTIMIZER_REDUCE_GROUPBY_KEY: ' || 'NOT SET');
      end if;
      
      vStatement := 'SELECT upper(value) FROM v$parameter WHERE upper(name) = ''OPTIMIZER_FEATURES_ENABLE''';
      vText := '';
      open vcur for vStatement ;
      loop
       fetch vcur into vText;
       exit when vcur%NOTFOUND;
      end loop;
      close vcur;
      dbms_output.put_line(  'OPTIMIZER_FEATURES_ENABLE:     ' || vText );
	  
	    SELECT sys_context('USERENV', 'NETWORK_PROTOCOL') into vText FROM dual;
	    dbms_output.put_line(  'Network Protocol:              ' || vText );
      
    EXCEPTION
      when ora942 then
        dbms_output.put_line( 'No access to v$parameter' );
      when others then   
        dbms_output.put_line( 'Error encountered: '  || SQLERRM);
    END;
  END IF;  -- vOracleVer >= 12

  BEGIN
    SELECT upper(value)  INTO vtext FROM v$parameter WHERE upper(name) = 'UNDO_MANAGEMENT';
    IF vtext = 'AUTO' THEN
      dbms_output.put_line('Undo Space:                    AUTO');
    ELSE
      SELECT
        d.undo_size / ( 1024 * 1024 ),
        substr(e.value,1,25),
        ( to_number(e.value) * to_number(f.value) * g.undo_block_per_sec ) / ( 1024 * 1024 )
      INTO
          vundo,vreten,vundodemand
      FROM
          (    SELECT
                  SUM(a.bytes) undo_size
              FROM
                  v$datafile a,
                  v$tablespace b,
                  dba_tablespaces c
              WHERE c.contents = 'UNDO'
              AND   c.status = 'ONLINE'
              AND  b.name = c.tablespace_name
              AND a.ts# = b.ts#
          ) d,
          v$parameter e,
          v$parameter f,
            (    SELECT
                    MAX(undoblks / ( (end_time - begin_time) * 3600 * 24) ) undo_block_per_sec
                FROM
                    v$undostat
            ) g
        WHERE e.name = 'undo_retention'
        AND f.name = 'db_block_size';
    
      dbms_output.put_line('Undo Size (MB): ' || vundo);
      dbms_output.put_line('Retention Secs: ' || vreten);
      dbms_output.put_line('Demand (MB):    ' || vundodemand);
    END IF; -- vText = Auto    
  EXCEPTION
    when ora942 then
      dbms_output.put_line( 'No access to v$parameter' );
    when others then   
      dbms_output.put_line( 'Error encountered: '  || SQLERRM);
  END;  
  
  if length(vSchema) > 0 then
    CheckPrivs;
  else 
    dbms_output.put_line( 'Priveleges not checked ' );
  end if;
  
  sectionheader( '**AXIUM VERSION INF0** ' );
  -- axiUm DB Versions
  SELECT MAX( "Version" ) INTO vNumber FROM practice;
  dbms_output.put_line( 'axiUm Base Version:      ' || vNumber );
  if vNumber < 60305 then
    vUpgradeOK := False;
  end if;  
  
  -- Doctor Access Version
  select count(1) into vCount from user_tables where upper(table_name) = 'DSETTING';
  if vCount = 1 then
    vStatement := 'select "Value" from dsetting where "Key" = ''DA.General.Version''';
    vText := '0';
    open vcur for vStatement ;
    loop
     fetch vcur into vText;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    vNumber := to_number(substr(vText,1,3));
    if vNumber  > 1  then
      dbms_output.put_line( 'Doctor Access Version:   ' || vText );
    else
      dbms_output.put_line( 'Doctor Access Version:   Partial Install DSetting exists but no version number ' || vText );
    end if  ;
    if vNumber < 603 then
      vUpgradeOK := False;
    end if;  
  else 
   dbms_output.put_line( 'Doctor Access Version:   NOT INSTALLED! no dsetting table ' );
  end if;
  
  -- 1st Access (Kiosk) Version
  select count(1) into vCount from user_tables where upper(table_name) = 'FTKSETTING';
  if vCount = 1 then
    vStatement := 'select "Value" from ftksetting where "Key" = ''FTK.General.Version''';
    vText := '0';
    open vcur for vStatement ;
    loop
     fetch vcur into vText;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    vNumber := to_number(substr(vText,1,3));
    if vNumber  > 1  then
      dbms_output.put_line( '1st Access Kiosk Version:'  || vText );
    else
      dbms_output.put_line( '1st Access Kiosk Version: Partial Install FTKSetting exists but no version number ' || vText );
    end if;
   
    if vNumber < 603 then
      vUpgradeOK := False;
    end if;  
  else 
   dbms_output.put_line( 'First Access Version:    NOT INSTALLED! no ftksetting table ' );
  end if;
  -- Patient Access Version
  select count(1) into vCount from user_tables where upper(table_name) = 'ISETTING';
  if vCount = 1 then
    vStatement := 'select "Value" from isetting where "Key" = ''Gen.VersionPatientAccess''';
    vText := '0';
    open vcur for vStatement ;
    loop
     fetch vcur into vText;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    vNumber := to_number(substr(vText,1,4));
    if vNumber  > 1  then
      dbms_output.put_line( 'Patient Access Version:  ' || vText );
    else
      dbms_output.put_line( 'Patient Access Version:   Partial Install ISetting exists but no version number ' || vText );
    end if;
    if vNumber < 6.03 then
      vUpgradeOK := False;
    end if;  
  else 
   dbms_output.put_line( 'Patient Access Version:   NOT INSTALLED! no isetting table ' );
  end if;
   
  -- Faculty Access Version
  select count(1) into vCount from user_tables where upper(table_name) = 'FSETTING';
  if vCount = 1 then
    vStatement := 'select "Value" from fsetting where "Key" = ''FP.General.Version''';
    vText := '0';
    open vcur for vStatement ;
    loop
     fetch vcur into vText;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    vNumber := to_number(substr(vText,1,4));
    if vNumber  > 1  then
      dbms_output.put_line( 'Faculty Access Version:  ' || vText );
    else
      dbms_output.put_line( 'Faculty Access Version:   Partial Install FSetting exists but no version number ' || vText );
    end if;
    if vNumber < 6.03 then
      vUpgradeOK := False;
    end if;  
  else 
   dbms_output.put_line( 'Faculty Access Version:  NOT INSTALLED! no fsetting table ' );
  end if;
  
    -- MUD Version
  select count(1) into vCount from user_tables where upper(table_name) = 'MSETTING';
  if vCount = 1 then
    vStatement := 'select "Value" from msetting where "Key" = ''MU.General.Version''';
    vText := '0';
    open vcur for vStatement ;
    loop
     fetch vcur into vText;
     exit when vcur%NOTFOUND;
    end loop;
    close vcur;
    vNumber := to_number(substr(vText,1,4));
    if vNumber  > 1  then
      dbms_output.put_line( 'MUD Version:             ' || vText );
    else
      dbms_output.put_line( 'MUD Version:             Partial Install MSetting exists but no version number ' || vText );
    end if;
    if vNumber < 6.03 then
      vUpgradeOK := False;
    end if;  
  else 
   dbms_output.put_line( 'MUD Version:             NOT INSTALLED! no msetting table ' );
  end if;
  
  -- Upgrade Tool OK?
  sectionheader('**UPGRADE DATABASE OBJECTS WITH:**');
  if vUpgradeOK = true then
    dbms_output.put_line('Upgrade Database Utility');
  else
    dbms_output.put_line('Scripts run by axiUm support must be used to upgrade DB');
  end if;  

  -- Practices
  sectionheader('Practices:');
  select count(1) into vCount from practice where "Id" = 1 and "Inactive" = 0;
  if vcount = 0 then
    dbms_output.put_line('No active practice with ID = 1. Auto Update will not work ');
    dbms_output.put_line(' ');
  end if;
  FOR practice IN practice_cur LOOP
    dbms_output.put_line( practice."Id" || chr(9) ||  trim(practice."Name") || chr(9) || trim(practice."VersionSrc") || chr(9) || practice."Version" || chr(9) || practice."Inactive");
  END LOOP;

  -- RegVal Settings
  sectionheader('Licensing: ');
  select "RegVal1" into vtext from axiumopt;
  dbms_output.put_line( 'RegVal1: ' || vtext);
  select "RegVal2" into vtext from axiumopt;
  dbms_output.put_line( 'RegVal2: ' || vtext);
  select "RegVal3" into vtext from axiumopt;
  dbms_output.put_line( 'RegVal3: ' || vtext);
  
  --PTLetter
  sectionheader( 'PTLetter Table: ' );
  dbms_application_info.set_client_info('5-PTLETTER');
  select count(1) into vcount from user_tables where upper(TABLE_NAME) = 'PTLETTER';
  if vcount > 0 then
    dbms_output.put_line( 'PTLETTER');
    select count(1) into vcount from ptletter;
    dbms_output.put_line( 'Rows:    ' || vcount);
    select count(1) into vcount from user_tab_cols where table_name = 'PTLETTER';
    dbms_output.put_line( 'Columns: ' || vcount);
  else
    select count(1) into vcount from user_tables where upper(TABLE_NAME) = 'PTLCONTENT';
    if vcount > 0 then             
        dbms_output.put_line( 'PTLCONTENT');
        vStatement := 'select count(1) from PTLCONTENT';
        vText := '0';
        open vcur for vStatement ;
        loop
          fetch vcur into vText;
          exit when vcur%NOTFOUND;
       end loop;
       close vcur;
        dbms_output.put_line( 'Rows:    ' || vText);
        select count(1) into vcount from user_tab_cols where table_name = 'PTLCONTENT';
        dbms_output.put_line( 'Columns: ' || vcount);
    else
       dbms_output.put_line( 'PTLETTER / PTLCONTENT table not found' );
    end if;   
  END if;
  
  -- lower case views
  sectionheader('Lowercase Views:');
  FOR lowercaseview IN lowercaseviews_cur LOOP
    dbms_output.put_line( lowercaseview.object_name);
  END LOOP;  
  
  --bigtables
  sectionheader('Biggest Tables:');
  FOR bigtable IN bigtables_cur LOOP
    dbms_output.put_line( bigtable.tablename || '    ' || bigtable.numrows);
  END LOOP;
    
  sectionheader('Connected Webservers:');
  FOR webserver IN webservers_cur LOOP
    dbms_output.put_line( webserver.machine);
  END LOOP;
  
  sectionheader('Last Week''s Transactions:');
  for thisday in weektrans_cur LOOP
     dbms_output.put_line(to_char(thisday.mdate ,'yyyy-mon-dd') || ':  ' || thisday.trans);
  END LOOP;  
  
  -- Audit Policies Enabled
  checkAuditPolicies(vSchema);
  
  sectionheader( '**ORACLE STORAGE INF0** ' );
  -- Compressed Tables  
  SELECT COUNT( 1 )
  INTO vComptables
  FROM user_tables
  WHERE compression <> 'DISABLED';
  dbms_output.put_line( 'Compressed Tables: ' || vCompTables );

  -- Tables with stale statistics
  SELECT COUNT( 1 )
  INTO vCount
  FROM user_tables
  WHERE last_analyzed <( Sysdate - 31 );
  dbms_output.put_line( 'Tables not analyzed in over a month: ' || vCount );

  --invalid objects
  Sectionheader('** Invalid Objects **');
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'SEQUENCE' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid SEQUENCES:          ' || vCount );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'PROCEDURE' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid PROCEDURES:         ' || vCount );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'PACKAGE' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid PACKAGES:           ' || vCount );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'PACKAGE BODY' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid PACKAGE BODYS:      ' || vCount );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'TRIGGER' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid TRIGGERS:           ' || vCount );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'MATERIALIZED VIEW' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid MATERIALIZED VIEWS: ' || vCount );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'TABLE' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid TABLES:             ' || vCount );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'FUNCTION' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid FUNCTIONS:          ' || vCount );
  SELECT COUNT( 1 )
  INTO vInvalidIndexes
  FROM user_objects
  WHERE object_type = 'INDEX' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid INDEXS:             ' || vInvalidIndexes );
  SELECT COUNT( 1 )
  INTO vcount
  FROM user_objects
  WHERE object_type = 'VIEW' AND status <> 'VALID';
  dbms_output.put_line( 'Invalid VIEWS:              ' || vCount );
  
-- tablespaces
  Sectionheader('Tablespaces used for Tables');
  
  FOR tblspace IN tblspaces_cur LOOP
    vtext := tblspace.tablespace_name;
    IF customtblspace(vtext) THEN
        dbms_output.put('*** Nonstandard: ');
    END IF;
    dbms_output.put_line(vtext);
  END LOOP;
  
  sectionheader('Tablespaces used for Indexes:');
  
  FOR tblspace IN idxspaces_cur LOOP
    vtext := tblspace.tablespace_name;
    IF customtblspace(vtext) THEN
      dbms_output.put('*** Nonstandard: ');
    END IF;
    dbms_output.put_line(vtext);
  END LOOP;
    
/*  
  -- Free Space in Tablespaces
  if vRunTSFree then
    sectionheader('Tablespace free space:');
    FOR tspace IN tablespaces_cur LOOP
      dbms_output.put_line( tspace.tablespace_name || chr(9) ||  tspace.freeMB );
    END LOOP;
  else
    dbms_output.put_line('Tablespace free space: SKIPPED');
  end if;
  
  -- Datafiles 
  sectionheader('Datafiles:');
  dbms_output.put_line( 'Tablespace' || chr(9) || 'AutoExt' || chr(9) || 'SizeMB' || chr(9) || 'GrowthMB' || chr(9) || 'NextBlocks' || chr(9) ||'FileName');
  FOR dfile IN datafiles_cur LOOP
    dbms_output.put_line( dfile.tablespace_name || chr(9) ||  dfile.autoextensible || chr(9) ||  dfile.MB || chr(9) || dfile.MBToGrow || chr(9) || dfile.nextblocks || chr(9)|| dfile.file_name);
  END LOOP;
*/
 
  -- Free Space in Tablespaces
  if vRunTSFree then
    sectionheader('Tablespace free space:');
    dbms_output.put_line( 'Tablespace                    ' || chr(9) || 'Size Mb   ' || chr(9) || 'Used Mb   ' || chr(9) || 'Free Mb   ' || chr(9) || 'MaxFree Mb' || chr(9) ||'% Free' || chr(9) || '% Max Free');
    FOR tspace IN tablespaces_cur LOOP
      dbms_output.put_line( tspace.tablespace_name || chr(9) ||  tspace.Size_MB || chr(9) ||  tspace.Used_MB || chr(9) || tspace.Free_MB || chr(9) || tspace.MaxFree_MB || chr(9)|| tspace.Percent_Free || chr(9) || tspace.Percent_MaxFree);
    END LOOP;
  else
    dbms_output.put_line('Tablespace free space: SKIPPED');
  end if;

  -- Current Sessions
  sectionheader('User Sessions:');
    For auser in sessions_cur LOOP
    dbms_output.put_line( 'User: ' || auser.username || '  Sessions: ' || auser.sessions);
  END LOOP;

  IF vCompTables > 0 THEN
    SectionHeader('Compressed Tables:');
    FOR tbl IN comptables_cur LOOP
      dbms_output.put_line(tbl.table_name);
    END LOOP;
  END IF;
  
  sectionheader('Invalid Objects:');
  FOR obj IN invalidobj_cur LOOP
    dbms_output.put_line( obj.object_type || chr(9) ||  obj.object_name|| chr(9) || obj.status);
  END LOOP;
  
  if vRunSequenceCheck = True then
    checksequences;
  end if;

  sectionheader('Materialized views:');
  BEGIN
  
    dbms_output.put('V_PTLETTER_MU: ');
  
    SELECT 1
    INTO vCount
    FROM user_mviews 
    WHERE mview_name = 'V_PTLETTER_MU';
    
    dbms_output.put_line('OK, exists and is a materialized view');
    
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    
    SELECT listagg(object_type,',') WITHIN GROUP (ORDER BY object_name)
    INTO vText
    FROM user_objects 
    WHERE object_name = 'V_PTLETTER_MU';
   
    IF vText IS NOT NULL THEN

      dbms_output.put_line('Error, is a ' || vText || ' instead of a materialized view');
    
    ELSE
  
      dbms_output.put_line('Ok, does not exist.');
    
    END IF;
  
  END;
  
END;