Rem
Rem Filename : ckreport.sql
Rem
Rem This script reports on the database objects created by the RAU Install scripts
Rem
Rem Author   : Oracle Corporation UK
Rem

connect &REPOSOWNER/MANAGER@&conn

set verify off
set feedback off

spool ckreport.lst

prompt **********************************************************************
prompt       Report on the missing database objects 
prompt **********************************************************************

prompt
Rem Report on the missing tablespaces
prompt Following tablespaces are missing
prompt ---------------------------------

select value
from   ckrau_temp ckt
where  not exists ( select 1 from user_tablespaces
                    where tablespace_name = ckt.value )
and    paramtype = 'TABLESPACE';


prompt   
Rem Report the missing rollback segment
prompt Following Rollback segments are missing
prompt ----------------------------------------

select value
from   ckrau_temp ckt
where  not exists ( select 1 from dba_rollback_segs
                    where segment_name = ckt.value )
and    paramtype = 'ROLLBACK SEGMENT';

prompt 
prompt

prompt **********************************************************************
prompt Report on the database objects created during the Install process     
prompt **********************************************************************

set head on
Rem Rollback segments created
prompt 
prompt  
prompt Rollback Segments created
prompt -------------------------

select segment_name
       ,tablespace_name
       ,status
from   dba_rollback_segs
where  segment_name = '&RBSNAME';

set head off
Rem Roles created
prompt  
prompt  Following Roles were created
prompt  ----------------------------

select role
from   session_roles
where  role = 'CK_ORACLE_REPOS_OWNER';

set pages 30

Rem Privileges granted to the role
prompt  
prompt The following privilege were granted to the CK_ORACLE_REPOS_ROLE
prompt ----------------------------------------------------------------

select privilege
from   role_sys_privs
where  role = 'CK_ORACLE_REPOS_OWNER';

column tablespace_name format a25
set head on

prompt 
prompt Tablespaces and their size
prompt --------------------------

select tablespace_name
       , sum(bytes/1024) "Size (in KB)"
from   user_free_space
where  tablespace_name in ('CONSTANT_GROW_INDEXES','CONSTANT_GROW_TABLES'
                          ,'DEPENDENCY_INDEXES'   ,'DEPENDENCY_TABLES'
                          ,'DIAGRAM_INDEXES'      ,'DIAGRAM_TABLES'
                          ,'LOB_DATA'             ,'RAPID_GROW_INDEXES'
                          ,'RAPID_GROW_TABLES'    ,'SYSTEM_META_INDEXES'
                          ,'SYSTEM_META_TABLES'   ,'TEMPORARY_INDEXES'
                          ,'TEMPORARY_TABLES'     ,'VERSION_INDEXES'
                          ,'VERSION_TABLES'       , upper('&RTBLSPACE')
                          ,upper('&TEMPTBLSPACE')
                          )
group by tablespace_name
order by tablespace_name;

prompt 
prompt Tablespace and their Storage parameters
prompt ---------------------------------------

column initial format 9999
column next format 9999
column "min extents" format 999
column "max extents" format 9999999999999

select  tablespace_name
        , initial_extent/1024 "Initial"
        , next_extent/1024 "Next"
        , pct_increase "PCT Inc"
        , min_extents "Min Extents"
        , max_extents "Max Extents"
from    user_tablespaces
where   tablespace_name in ('CONSTANT_GROW_INDEXES','CONSTANT_GROW_TABLES'
                          ,'DEPENDENCY_INDEXES'   ,'DEPENDENCY_TABLES'
                          ,'DIAGRAM_INDEXES'      ,'DIAGRAM_TABLES'
                          ,'LOB_DATA'             ,'RAPID_GROW_INDEXES'
                          ,'RAPID_GROW_TABLES'    ,'SYSTEM_META_INDEXES'
                          ,'SYSTEM_META_TABLES'   ,'TEMPORARY_INDEXES'
                          ,'TEMPORARY_TABLES'     ,'VERSION_INDEXES'
                          ,'VERSION_TABLES'       , upper('&RTBLSPACE')
                          ,upper('&TEMPTBLSPACE')
                          )
order by tablespace_name;

set head off

Rem Repository Owner
prompt  
prompt Name of the Repository Owner.
prompt -----------------------------

select username "Repository Owner"
from   user_users;


Rem Privileges granted to the user
prompt  
prompt The following privileges were granted to REPOS_MANAGER
prompt ------------------------------------------------------

select privilege
from   user_sys_privs;

Rem Roles granted to the user
prompt  
prompt The following Roles were granted to REPOS_MANAGER
prompt -------------------------------------------------

select granted_role
from   user_role_privs;

prompt 

set feedback on

spool off
