-- Ce script vient du livre d'Administration pour Oracle 10g de G Briard

set echo off
spool /audit.txt

-- date du jour
select sysdate from dual;

-- utilisateur connecte

set pause off
set pagesize 150
col tablespace_name for a15
col owner for a6
ttitle center 'Utilisateur connecte' skip 2
show user



-- version d'oracle
ttitle center 'version oracle' skip 2
select banner from v$version
/

select * from product_component_version
/


-- base auditée
select name "NOM BASE",  created, log_mode, checkpoint_change#, archive_change# from v$database;

-- taille totale de la zone mémoire réservée par Oracle pour l'instance

break on report
compute sum of value on report
col name for a20
col value for 9999999999;
select * from v$sga
/
clear break
clear compute

-- calcul du cache hit ratio : le pourcentage des requetes deja resolues avec les donnees en memoire

select distinct (to_number(S2.value) + 
                 to_number(S3.value) - 
                 to_number(S1.value))/ + 
                 (to_number(S2.value) + 
                 to_number(S3.value))*100 "Cache Hit Ratio" 
from v$sysstat S1,
     v$sysstat S2,
     v$sysstat S3,
     v$statname N1,
     v$statname N2,
     v$statname N3
where S1.statistic# = N1.statistic#
  and S2.statistic# = N2.statistic#
  and S3.statistic# = N3.statistic#
  and N1.name = 'physical reads'
  and N2.name = 'consistent gets'
  and N3.name = 'db block gets'
/


-- parametres d'init.ora
ttile center 'parametres d'init.ora' skip 2
col name for a40 heading "Nom du parametre"
col name for a38 heading "valeur'
select name, value from v$parameter order by name 
/


-- liste des fichiers physiques associés aux tablespaces
ttile center 'Liste des fichiers' skip 2
col "Fichier physique" for a40
col TBS for a11
col bytes for 999999999
break on report
compute sum of bytes on status report
select file_name "Fichier physique", tablespace_name "TBS", bytes, status
from sys.dba_data_files
order by tablespace_name
/
clear break
clear compute
/

-- liste des fichiers auto extensible
ttitle center 'liste des fichiers autoextensibles' skip 2
col "Fichier physique" for a40
select file_name "Fichier physique", autoextensible, maxbytes, maxblocks, increment_by
from sys.dba_data_files
order by tablespace_name
/

-- liste des fichiers redo-log
ttitle center 'liste des fichiers redo-log' skip 2
col "nom des fichiers redo-log" for a60
select group#,status,member "nom des fichiers redo-log" from v$logfile
/

-- liste des fichiers de controle
ttitle center 'liste des fichiers de controle' skip 2
col "nom des fichiers de controle" for a70
select status, name "nom des fichiers de controle" from v$controlfile
/


-- liste et proprietes des tablespaces
ttitle center 'liste des tablespaces' skip 2
col initial_extent for 9999999 head "Ini.Ext"
col next_extent    for 9999999 head "Nex.Ext"
col pct_increase   for 999999  head "Pct.In"
col min_extents    for 9999999 head "Min.Ext"
col max_extents    for 9999999 head "Max.Ext"
select * from sys.dba_tablespaces
/

-- taille totale et espace disponible dans les tablespaces
ttitle center "Taille et occupation des tablespaces" skip 2
col "% occupe" for 99.9

select A.tablespace_name,
   A.total_size "Taille totale (Ko)", 
   B.free_size  "Espace disponible (Ko)" ,
  ((A.total_size - B.free_size)*100 / A.total_size) "% occupe"
from
(select tablespace_name,
   sum(bytes)/1024 total_size
   from sys.dba_data_files
   group by tablespace_name) A ,
(select tablespace_name,
   sum(bytes)/1024 free_size
   from sys.dba_free_space
   group by tablespace_name) B
where a.tablespace_name = b.tablespace_name
/


-- espace disponible dans les tablespaces
ttitle center "espace disponible en nb de blocks contigus" skip 1 -
       center " (1 block vaut db_block_size_octets)" skip 2
select tablespace_name,
       round(avg(blocks)) "En moyenne",
       min(blocks) "Minimum",
       max(blocks) "Maximum",
       count(*) "Nombre"
from sys.dba_free_space
group by tablespace_name
/

-- liste des segments d'annulation
ttitle center "Liste des segments d'annulation" skip 2
col nomsegment for a10
col iniext for 999999999
col nextex for 99999999
col pctin  for 9999
col status for a8
col tablespace_name for a13 heading "Tablespace"
select segment_name "NomSegment", owner tablespace_name, initial_extent "IniExt",
       next_extent "NexEx", pct_increase "PctIn", status
       from sys.dba_rollback_segs
/


-- Place utilisee par segment
ttitle center "Place utilisee par segment" skip -
       center "(tri par owner puis type de segment)" skip 2
col owner for a8
col segment_name for a17
col segment_type for a9 heading "Type Seg"
col extents for 9999 heading "Ext"
col max_extents for 99999 heading "Next"
select segment_name, tablespace_name, segment_type, extents, max_extents, bytes, owner
from sys.dba_segments
where owner != 'SYS' or (owner = 'SYS' and segment_type = 'ROLLBACK')
order by owner, segment_Type desc, segment_name
/


-- Liste des procedures, fonctions et triggers
ttile "Liste des procedures, fonctions et triggers" skip 2
col object_name for a20
col owner for a20
select owner, object_name, object_type, status from dba_objects
where object_type in ('PROCEDURE', 'FUNCTION', 'TRIGGER', 'PACKAGE')
and owner not in ('SYS', 'SYSTEM')
order by owner, object_name
/

-- Liste et profils des utilisateurs
ttitle center "Liste et profils des utilisateurs" skip 2
col "Def. TBS" for a11
col "Temp. TBS" for a10
col C for 9
col R for 9
col DBA for 999
col owner for a6
select username, default_tablespace "Def. TBS",
temporary_tablespace "Temp. TBS", profile
from sys.dba_users
order by username
/


-- Liste des personnes avec SYSOPER et SYSDBA
ttitle center "Liste des personnes avec SYSOPER et SYSDBA" skip 2
select username, sysoper, sysdba from v$pwfile_users
order by username
/

-- Liste des roles existants
ttitle center "Liste des roles existants" skip 2
select role, password_required from sys.dba_roles
order by role
/

-- Roles affectes aux utilisateurs
ttitle center "Roles affectes aux utilisateurs" skip 2
break on grantee
select grantee, granted_role, admin_option, default_role
from sys.dba_role_privs
order by grantee, granted_role
/

-- Liste des profils existants
ttitle center "Liste des profils existants" skip 2
col profile for a15
col resource_name for a25
col limit for a35
break on profile
select profile, resource_name, limit from sys.dba_profiles
/


clear break
spool off
set pagesize 20
exit