--
--
SET LONG 2000000
set PAGESIZE 10000
--set head off 
--set verify off 
set feedback off 
--set linesize 132

var DockitVersion varchar2(20)
exec :DockitVersion := '2023.10.17'

set PAGESIZE 0
select '<?xml version="1.0" encoding="UTF-8"?>' from dual;
select '<?xml-stylesheet type="text/xsl" href="/static/dockit.xsl"?>' from dual;

select '<DATABASE>' from dual;

set PAGESIZE 0
select '<HOSTNAME><![CDATA[' from dual;
  ! hostname
set PAGESIZE 0
select ']]></HOSTNAME>' from dual;

set PAGESIZE 0
select '<DOCINFO><![CDATA[' from dual;
  select 'Document Date.....: '|| to_char(sysdate,'dd/mm/yyyy') from dual;
  select 'Document Version..: '|| :DockitVersion from dual;
set PAGESIZE 0
select ']]></DOCINFO>' from dual;

select '<ENV_VARS><![CDATA[' from dual;
  !set | grep "^ORA"
select ']]></ENV_VARS>' from dual;

select '<SQLNET_CONF><![CDATA[' from dual;
  !cat $ORACLE_HOME/network/admin/sqlnet.ora
select ']]></SQLNET_CONF>' from dual;

select '<LISTENER_CONF><![CDATA[' from dual;
  !cat $ORACLE_HOME/network/admin/listener.ora
select ']]></LISTENER_CONF>' from dual;

select '<TNSNAMES_CONF><![CDATA[' from dual;
  !cat $ORACLE_HOME/network/admin/tnsnames.ora
select ']]></TNSNAMES_CONF>' from dual;

set PAGESIZE 0
select '<ORATAB><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      set long  5000;
      !grep -v "^$" /etc/oratab | grep -v "^#.*"
set PAGESIZE 0
select ']]></ORATAB>' from dual;

select '<PATCHES><![CDATA[' from dual;
!$ORACLE_HOME/OPatch/opatch lsinventory | grep Interim -B100 -A1; 
!$ORACLE_HOME/OPatch/opatch lsinventory | grep "^Patch.*applied " -A3;
select ']]></PATCHES>' from dual;

set PAGESIZE 0
select '<INSTANCE><![CDATA[' from dual;
  select 
    HOST_NAME
  from gv$instance;
  set PAGESIZE 10000
  set lines 500;
  select 
    INST_ID, 
    INSTANCE_NAME, 
    VERSION, 
    ARCHIVER, 
    INSTANCE_ROLE 
    /*, DATABASE_TYPE*/ 
  from gv$instance;
set PAGESIZE 0
select ']]></INSTANCE>' from dual;

set PAGESIZE 0
select '<BANNER><![CDATA[' from dual;
  set PAGESIZE 10000
  set lines 500;
  set pages 500;
  set long  5000;
  select banner from v$version;
set PAGESIZE 0
select ']]></BANNER>' from dual;

set PAGESIZE 0
select '<LSNRCTL><![CDATA[' from dual;
  set lines 500;
  set pages 500;
  set long  5000;
  !lsnrctl status
set PAGESIZE 0
select ']]></LSNRCTL>' from dual;

set PAGESIZE 0
select '<NLS><![CDATA[' from dual;
  set PAGESIZE 10000
  col parameter format a30
  col value format a40  
  select * from nls_database_parameters;
set PAGESIZE 0
select ']]></NLS>' from dual;

set PAGESIZE 0
select '<PARAMETER><![CDATA[' from dual;
  set pagesize 500
  set linesize 500
  col name format a30
  col value format a90  
  select name, value from v$parameter where isdefault = 'FALSE' order by name;
set PAGESIZE 0
select ']]></PARAMETER>' from dual;

set PAGESIZE 0
select '<SYS_HUGEPAGE><![CDATA[' from dual;
  set PAGESIZE 10000
  ! sysctl -a 2>/dev/null | grep "nr_hugepages\ "; 
  set PAGESIZE 0
select ']]></SYS_HUGEPAGE>' from dual;

set PAGESIZE 0
select '<MEMORY><![CDATA[' from dual;
  set PAGESIZE 10000
  col value format a20  
  select name, value from v$parameter where upper(name)='MEMORY_MAX_TARGET'
      union all
      select name, value from v$parameter where upper(name)='MEMORY_TARGET'
      union all
      select '  '||name, value from v$parameter where upper(name)='SGA_MAX_SIZE'
      union all
      select '  '||name, value from v$parameter where upper(name)='SGA_TARGET'
      union all
      select '    '||name, value from v$parameter where upper(name)='DB_CACHE_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='JAVA_POOL_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='LARGE_POOL_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='SHARED_POOL_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='LOG_BUFFER'
      union all
      select '    '||name, value from v$parameter where upper(name)='STREAMS_POOL_SIZE'
      union all
      select '  '||name, value from v$parameter where upper(name)='_PGA_MAX_SIZE'
      union all
      select '  '||name, value from v$parameter where upper(name)='PGA_AGGREGATE_TARGET'
      union all
      select '  '||name, value from v$parameter where upper(name)='PGA_AGGREGATE_LIMIT'
      union all
      select '    '||name, value from v$parameter where upper(name)='BITMAP_MERGE_AREA_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='CREATE_BITMAP_AREA_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='HASH_AREA_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='SORT_AREA_SIZE'
      union all
      select '    '||name, value from v$parameter where upper(name)='WORKAREA_SIZE_POLICY'
      union all
      select name, value from v$parameter where upper(name)='USE_LARGE_PAGES';
set PAGESIZE 0
select ']]></MEMORY>' from dual;


set PAGESIZE 0
select '<SGA><![CDATA[' from dual;
  set PAGESIZE 10000
  col value format a12 
  SELECT a.name,
    a.value || '' value
    FROM   v$sga a;
set PAGESIZE 0
select ']]></SGA>' from dual;

set PAGESIZE 0
select '<CONTROLFILE><![CDATA[' from dual;
  set PAGESIZE 10000
  SELECT Substr(c.name,1,60) "Controlfile",
    NVL(c.status,'UNKNOWN') "Status"
    FROM   v$controlfile c
    ORDER BY 1;
set PAGESIZE 0
select ']]></CONTROLFILE>' from dual;


set PAGESIZE 0
select '<MULTI_TENANCY><![CDATA[' from dual;
  set PAGESIZE 10000
  show con_name

  show pdbs;

set PAGESIZE 0
select ']]></MULTI_TENANCY>' from dual;


set PAGESIZE 0
select '<SERVICE><![CDATA[' from dual;
  set PAGESIZE 0
  select name||' = '||value from gv$parameter where name like 'service%';
set PAGESIZE 0
select ']]></SERVICE>' from dual;

set PAGESIZE 0
select '<LISTENER><![CDATA[' from dual;
  set PAGESIZE 0
  select name||' = '||value from gv$parameter where name like '%listener%';
set PAGESIZE 0
select ']]></LISTENER>' from dual;

set PAGESIZE 0
select '<ARCHIVELOG><![CDATA[' from dual;
  set PAGESIZE 0
  archive log list;
set PAGESIZE 0
select ']]></ARCHIVELOG>' from dual;

set PAGESIZE 0
select '<LOGFILE><![CDATA[' from dual;
  set PAGESIZE 10000
  col member format a80
  select l.group#, l.bytes/1024/1024 MB, lf.member, l.bytes from v$log l left join v$logfile lf on lf.group# = l.group# order by group#, member;
set PAGESIZE 0
select ']]></LOGFILE>' from dual;


set PAGESIZE 0
select '<TABLESPACE><![CDATA[' from dual;
  set PAGESIZE 10000
  col usage     format a10  
  col tablespace_name format a30
  col allocmb   format a13
  col freebytes format a13
  col usedbytes format a13
  col maxbytes  format a13
  col freemb  format a13
  col usedmb  format a13
  col maxmb   format a13
  col usedpct format a12
  col freepct format a12
  col tssize  format a12

  select tablespace_name,
      TO_CHAR(megs_alloc/1024,'999999999') allocMB,
      TO_CHAR(megs_free/1024,'999999999') freeMB,
      TO_CHAR(megs_used/1024,'999999999') usedMB,
      TO_CHAR(Maxi/1024,'999999999') maxMB,
      TO_CHAR((megs_used*100/(1+maxi)),'999') usedpct,
      TO_CHAR(((maxi-megs_used)*100/(1+maxi)),'999999') freepct,
      lpad('#',round(megs_used*100/(1+maxi),0)/10,'#') USAGE
      from
      (
      select a.tablespace_name,
      round(a.bytes_alloc / 1024 , 2) megs_alloc,
      round(nvl(b.bytes_free, 0) / 1024 , 2) megs_free,
      round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 , 2) megs_used,
      round(maxbytes/1024,2) Maxi
      from ( select f.tablespace_name,
      sum(f.bytes) bytes_alloc,
      sum(decode(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
      from dba_data_files f
      group by tablespace_name) a,
      ( select f.tablespace_name,
      sum(f.bytes) bytes_free
      from dba_free_space f
      group by tablespace_name) b
      where a.tablespace_name = b.tablespace_name (+)
      union all
      select h.tablespace_name,
      round(sum(h.bytes_free + h.bytes_used) / 1024, 2) megs_alloc,
      round(sum((h.bytes_free + h.bytes_used) - nvl(p.bytes_used, 0)) / 1024, 2) megs_free,
      round(sum(nvl(p.bytes_used, 0))/ 1024, 2) megs_used,
      round(greatest(sum(f.maxbytes), sum(h.bytes_free + h.bytes_used)) / 1024, 2) max
      from sys.gv_$TEMP_SPACE_HEADER h, sys.gv_$Temp_extent_pool p, dba_temp_files f
      where p.file_id(+) = h.file_id
      and p.tablespace_name(+) = h.tablespace_name
      and f.file_id = h.file_id
      and f.tablespace_name = h.tablespace_name
      group by h.tablespace_name
      )
      order by tablespace_name;
set PAGESIZE 0
select ']]></TABLESPACE>' from dual;

set PAGESIZE 0
select '<DATAFILE><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col TABLESPACE_NAME format a20;
      col FILE_NAME format a80;
      col AUTOEXTENSIBLE format a14;
      col MAXBYTES_MB format a12;
      col BYTES_MB format a12;
      col INCBY_KB format a12;
      select 
      df.tablespace_name, 
      df.file_name,
      df.AUTOEXTENSIBLE,
      trunc(df.MAXBYTES/1024/1024,0)||'' as MAXBYTES_MB,
      trunc(df.BYTES/1024/1024,0)||'' as BYTES_MB,
      (df.INCREMENT_BY*ts.block_size/1024)||'' as INCBY_KB 
      from 
      dba_data_files df, 
      dba_tablespaces ts 
      where ts.tablespace_name=df.tablespace_name
      order by 1,2;
set PAGESIZE 0
select ']]></DATAFILE>' from dual;

set PAGESIZE 0
select '<PROFILE><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col profile format a30
      col resource format a30
      col type format a20
      col limit format a20
      select 
        profile, 
        resource_name "resource", 
        resource_type type, 
        limit 
      from dba_profiles 
      order by 1,3,2;
set PAGESIZE 0
select ']]></PROFILE>' from dual;

set PAGESIZE 0
select '<ROLE><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col role format a60
      select 
        role ROLE 
      from dba_roles 
      where role not IN (
        'CONNECT','RESOURCE','DBA','SELECT_CATALOG_ROLE',
        'EXECUTE_CATALOG_ROLE','DELETE_CATALOG_ROLE','EXP_FULL_DATABASE','IMP_FULL_DATABASE',
        'LOGSTDBY_ADMINISTRATOR','DBFS_ROLE','AQ_ADMINISTRATOR_ROLE','AQ_USER_ROLE',
        'DATAPUMP_EXP_FULL_DATABASE','DATAPUMP_IMP_FULL_DATABASE','ADM_PARALLEL_EXECUTE_TASK',
        'GATHER_SYSTEM_STATISTICS','RECOVERY_CATALOG_OWNER','SCHEDULER_ADMIN','HS_ADMIN_SELECT_ROLE',
        'HS_ADMIN_EXECUTE_ROLE','HS_ADMIN_ROLE','OEM_ADVISOR','OEM_MONITOR','WM_ADMIN_ROLE',
        'JAVAUSERPRIV','JAVAIDPRIV','JAVASYSPRIV','JAVADEBUGPRIV','EJBCLIENT','JMXSERVER','JAVA_ADMIN',
        'JAVA_DEPLOY','CTXAPP','XDBADMIN','XDB_SET_INVOKER','AUTHENTICATEDUSER','XDB_WEBSERVICES',
        'XDB_WEBSERVICES_WITH_PUBLIC','XDB_WEBSERVICES_OVER_HTTP','ORDADMIN','MGMT_USER',
        'APEX_ADMINISTRATOR_ROLE','GLOBAL_AQ_USER_ROLE','OLAP_XS_ADMIN','OLAP_DBA','CWM_USER','OLAP_USER',
        'SPATIAL_WFS_ADMIN','WFS_USR_ROLE','SPATIAL_CSW_ADMIN','CSW_USR_ROLE','OWB$CLIENT',
        'OWB_DESIGNCENTER_VIEW','OWB_USER'
      )
      -- and oracle_maintained = 'N'
      ;
set PAGESIZE 0
select ']]></ROLE>' from dual;

set PAGESIZE 0
select '<USER><![CDATA[' from dual;
      set PAGESIZE 10000
      set pagesize 500
      set linesize 500
      col USERNAME format a22
      col ACCOUNT_STATUS format a20
      col DEFAULT_TABLESPACE format a20
      col TEMPORARY_TABLESPACE format a20
      col PROFILE format a20
      select
      USERNAME , 
      ACCOUNT_STATUS, 
      --LOCK_DATE, 
      --EXPIRY_DATE,
      DEFAULT_TABLESPACE, 
      TEMPORARY_TABLESPACE,
      CREATED, 
      PROFILE
      from dba_users 
      order by ACCOUNT_STATUS DESC, USERNAME asc;
set PAGESIZE 0
select ']]></USER>' from dual;

set PAGESIZE 0
select '<USERHASH><![CDATA[' from dual;
      set PAGESIZE 10000
      set pagesize 500
      set linesize 500
      select 'USER:' || su.name || ' SPARE4:'|| spare4 || ' PWD:'|| su.password USER_HASH from sys.user$ su join dba_users du on su.name = du.username and du.ACCOUNT_STATUS = 'OPEN';
      set PAGESIZE 0
select ']]></USERHASH>' from dual;


set PAGESIZE 0
select '<QUOTA><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col USERNAME format a25;
      col TABLESPACE_NAME format a25;
      col QUOTA_MB format a15;
      select 
        USERNAME,
        TABLESPACE_NAME,
        decode(max_bytes,-1,'UNLIMITED',(max_bytes/1024/1024)||'' ) QUOTA_MB
      from dba_ts_quotas
      order by 1,2,3;
set PAGESIZE 0
select ']]></QUOTA>' from dual;



set PAGESIZE 0
select '<TABLESPACE_USAGE><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col USERNAME format a25;
      col TABLESPACE_NAME format a25;
      select 
        distinct owner USERNAME, 
        tablespace_name 
      from dba_extents
      order by 1,2;
set PAGESIZE 0
select ']]></TABLESPACE_USAGE>' from dual;


set PAGESIZE 0
select '<HUGE_OBJECTS><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col owner format a15
      col segment_name format a30
      col segment_type format a15
      col mb format 999,999,999
      select owner, segment_name, segment_type, mb
      from (
        select owner ,segment_name ,segment_type ,bytes / 1024 / 1024 "MB"
        from dba_segments
        order by bytes desc
      )
      where rownum < 11;
set PAGESIZE 0
select ']]></HUGE_OBJECTS>' from dual;


set PAGESIZE 0
select '<OBJECTS_RESUME><![CDATA[' from dual;
    set lines 300
    set pages 10000       

    col owner format a25
    col object_type format a25
    col status format a7
    col cnt format 999999

    select 
      owner, 
      object_type, 
      status, 
      count(*) cnt
    from dba_objects
    group by owner, object_type, status 
    order by owner, object_type, status;
set PAGESIZE 0
select ']]></OBJECTS_RESUME>' from dual;




set PAGESIZE 0
select '<GRANT><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col typ format a9
      col grantee format a30
      col priv format a30
      col admin format a10
      select
        a.*
        from (select 
                'ROLE' typ,
                grantee grantee,
                granted_role priv,
                admin_option admin
              from dba_role_privs rp join dba_roles r on rp.granted_role = r.role
              inner join dba_users du on 
                du.default_tablespace NOT IN ('SYSAUX','SYSTEM') and du.username NOT IN ('SYS','SYSTEM') and du.username = grantee
              union
              select 
                'SYSTEM' typ,
                grantee grantee,
                privilege priv,
                admin_option admin
              from dba_sys_privs       
              inner join dba_users du on 
                du.default_tablespace NOT IN ('SYSAUX','SYSTEM') and du.username NOT IN ('SYS','SYSTEM') and du.username = grantee
        ) a;
set PAGESIZE 0
select ']]></GRANT>' from dual;

set PAGESIZE 0
select '<JOB><![CDATA[' from dual;
      set lines 500;
      set pages 500;
      col schema_user format a30
      col jobs format 999
      select 
        SCHEMA_USER, 
        count(*) JOBS 
      from dba_jobs 
      group by SCHEMA_USER;
set PAGESIZE 0
select ']]></JOB>' from dual;

set PAGESIZE 0
select '<DBLINK><![CDATA[' from dual;
  set LINESIZE 150
  set pages 500;

  COLUMN owner FORMAT A30
  COLUMN db_link FORMAT A30
  COLUMN username FORMAT A30
  COLUMN host FORMAT A30

  SELECT 
    owner,
    db_link,
    username,
    host
  FROM   dba_db_links
  ORDER BY owner, db_link;
set PAGESIZE 0
select ']]></DBLINK>' from dual;


set PAGESIZE 0
select '<FEATURE><![CDATA[' from dual;
      set PAGESIZE 10000
      set lines 500;
      set pages 500;
      col COMP_ID format a20;
      col COMP_NAME format a42;
      col STATUS format a14;
      select COMP_ID,COMP_NAME,STATUS,MODIFIED FROM DBA_REGISTRY;
set PAGESIZE 0
select ']]></FEATURE>' from dual;

set PAGESIZE 0
select '<FEATURE_USAGE><![CDATA[' from dual;
      set PAGESIZE 10000
      set lines 500;
      set pages 500;
      col NAME format a56
      col VERSION format a12
      col USAGES format 999
      alter session set NLS_DATE_FORMAT='dd/mm/yyyy hh24:mi';
      select 
        DBID,
        NAME,
        VERSION,
        DETECTED_USAGES USAGES,
        FIRST_USAGE_DATE FIRST_USAGE,
        LAST_USAGE_DATE LAST_USAGE,
        CURRENTLY_USED CURR_USED
      from DBA_FEATURE_USAGE_STATISTICS 
      where CURRENTLY_USED='TRUE'
      order by NAME;
set PAGESIZE 0
select ']]></FEATURE_USAGE>' from dual;





set PAGESIZE 0
select '<BACKUP_RMAN><![CDATA[' from dual;
      set line 180
      set pages 180
      COL in_size  FORMAT a10
      COL out_size FORMAT a10
      COL Elapsed  FORMAT a10
      COL Status   FORMAT a15
      SELECT SESSION_KEY,
        INPUT_TYPE,
        COMPRESSION_RATIO,
        INPUT_BYTES_DISPLAY in_size,
        OUTPUT_BYTES_DISPLAY out_size,
        STATUS,
        to_char(START_TIME,'YYYY-MM-DD HH24:MI') START_TIME,
        trunc( END_TIME-START_TIME ) ||'d '||
          mod( trunc( ( END_TIME-START_TIME ) * 24 ), 24) ||':'||
          mod( trunc( ( END_TIME-START_TIME ) * 1440 ), 60 ) ||':'||
          mod( trunc( ( END_TIME-START_TIME ) * 86400 ), 60 ) Elapsed
      FROM   V$RMAN_BACKUP_JOB_DETAILS
      where INPUT_TYPE <> 'ARCHIVELOG' OR START_TIME > sysdate -1
      ORDER BY SESSION_KEY;
set PAGESIZE 0
select ']]></BACKUP_RMAN>' from dual;

set PAGESIZE 0
select '<BACKUP_DATAPUMP><![CDATA[' from dual;
      set PAGESIZE 10000
      set lines 500;
      set pages 500;
      !find /backup -iname "*.expdp*"
set PAGESIZE 0
select ']]></BACKUP_DATAPUMP>' from dual;


set PAGESIZE 0
select '<CRONTAB><![CDATA[' from dual;
      set PAGESIZE 10000
      set lines 500;
      set pages 500;
      !crontab -l
set PAGESIZE 0
select ']]></CRONTAB>' from dual;

set PAGESIZE 0
select '</DATABASE>' from dual;

exit;
