set echo off
set heading off
set feedback off

prompt ---------------------------------
prompt - Coalesce tous les tablespaces -
prompt ---------------------------------

set term off
spool tbs_coalesce.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter tablespace '||tablespace_name||' coalesce;'
FROM 
   dba_tablespaces
WHERE 
   contents not in ('TEMPORARY','UNDO');

spool off
set term on

@tbs_coalesce.tmp

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - plus grandes consommation CPU --
prompt ----------------------------------

column sidformat 999     heading "SID"
column usernameformat a20     heading "Utilisateur"
column commandformat a20     heading "Commande"
column osuserformat a20     heading "Utilisateur OS"
column processformat a20     heading "Processus OS"
column machineformat a20     heading "Machine"
column valueformat 99,999  heading "Temps CPU"

SELECT 
s.sidsid,
s.usernameusername,
UPPER(DECODE(command,
        1,'Create Table',2,'Insert',3,'Select',
        4,'Create Cluster',5,'Alter Cluster',6,'Update',
        7,'Delete', 8,'Drop Cluster', 9,'Create Index',
        10,'Drop Index', 11,'Alter Index', 12,'Drop Table',
        13,'Create Sequencfe', 14,'Alter Sequence', 15,'Alter Table',
        16,'Drop Sequence', 17,'Grant', 18,'Revoke',
        19,'Create Synonym', 20,'Drop Synonym', 21,'Create View',
        22,'Drop View', 23,'Validate Index', 24,'Create Procedure',
        25,'Alter Procedure', 26,'Lock Table', 27,'No Operation',
        28,'Rename', 29,'Comment', 30,'Audit',
        31,'NoAudit', 32,'Create Database Link', 33,'Drop Database Link',
        34,'Create Database', 35,'Alter Database', 36,'Create Rollback Segment',
        37,'Alter Rollback Segment', 38,'Drop Rollback Segment', 39,'Create Tablespace',
        40,'Alter Tablespace', 41,'Drop Tablespace', 42,'Alter Sessions',
        43,'Alter User', 44,'Commit', 45,'Rollback',
        46,'Savepoint', 47,'PL/SQL Execute', 48,'Set Transaction',
        49,'Alter System Switch Log', 50,'Explain Plan', 51,'Create User',
        52,'Create Role', 53,'Drop User', 54,'Drop Role',
        55,'Set Role', 56,'Create Schema', 57,'Create Control File',
        58,'Alter Tracing', 59,'Create Trigger', 60,'Alter Trigger',
        61,'Drop Trigger', 62,'Analyze Table', 63,'Analyze Index',
        64,'Analyze Cluster', 65,'Create Profile', 66,'Drop Profile',
        67,'Alter Profile', 68,'Drop Procedure', 69,'Drop Procedure',
        70,'Alter Resource Cost', 71,'Create Snapshot Log', 72,'Alter Snapshot Log',
        73,'Drop Snapshot Log', 74,'Create Snapshot', 75,'Alter Snapshot',
        76,'Drop Snapshot', 79,'Alter Role', 85,'Truncate Table',
        86,'Truncate Cluster', 88,'Alter View', 91,'Create Function',
        92,'Alter Function', 93,'Drop Function', 94,'Create Package',
        95,'Alter Package', 96,'Drop Package', 97,'Create Package Body',
        98,'Alter Package Body', 99,'Drop Package Body')) command,
s.osuserosuser,
s.machinemachine,
s.processprocess,
t.valuevalue
FROM
v$session s,
v$sesstat t,
v$statname n
WHERE
        s.sid = t.sid
AND
t.statistic# = n.statistic#
AND
n.name = 'CPU used by this session'
AND
t.value > 0
AND
audsid > 0
ORDER BY
t.value desc;

set echo off
set feedback off
set linesize 128

column NAME Format a40
column DATAGUARD_BROKER Format a20
column GUARD_STATUS Format a20

prompt -------------------------
prompt - Infos base de données -
prompt -------------------------

SELECT 
   NAME, 
   To_char(CREATED,'DD/MM/YYYY') CREATION, 
   LOG_MODE, 
   DATABASE_ROLE, 
   DATAGUARD_BROKER, 
   GUARD_STATUS           
FROM 
   V$DATABASE;

set echo off
set feedback off
set linesize 160

prompt -----------------------------------
prompt - Fichiers de controle de la base -
prompt -----------------------------------

column Fichier format a80

SELECT 
   NAME Fichier, 
   DECODE (STATUS, '', 'VALID', 'INVALID') "Statut"
FROM 
   V$CONTROLFILE
ORDER BY 
   NAME;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------------
prompt - Liste des tablespaces par défaut     -
prompt - et le nombre d'utilisateurs associés -
prompt ----------------------------------------

column count(username) heading 'Nbre utilisateurs'

SELECT 
   default_tablespace, 
   count(username)
FROM 
   dba_users
WHERE 
   username not in ('PUBLIC','_NEXT_USER')
GROUP BY 
   default_tablespace;

SELECT
   temporary_tablespace, 
   count(username)
FROM 
   dba_users
WHERE 
   username not in ('PUBLIC','_NEXT_USER')
GROUP BY 
   temporary_tablespace;

set echo off
set feedback off
set linesize 256

prompt --------------------------
prompt - Directories de la base -
prompt --------------------------

column CHEMIN format a80
column NOM format a40

SELECT 
   OWNER SCHEMA, 
   DIRECTORY_NAME NOM, 
   DIRECTORY_PATH CHEMIN
FROM 
   DBA_DIRECTORIES
ORDER BY 
   OWNER, 
   DIRECTORY_NAME;

set echo off
set feedback off
set linesize 512

prompt -------------------------------------
prompt - Liste des segments non extensible -
prompt -------------------------------------

column segment_name format a32

SELECT 
   a.owner,
   decode(partition_name, null, segment_name, segment_name || ':' || partition_name) name,
   a.SEGMENT_TYPE, a.tablespace_name, a.bytes, a.initial_extent, 
   a.next_extent, a.PCT_INCREASE, a.extents, a.max_extents,
   b.free, 
   b.remain,
   decode(c.autoextensible, 0, 'NO', 'YES') autoextensible,
   decode(c.autoextensible, 0, 0, c.morebytes) max_can_grow_by,
   decode(c.autoextensible, 0, 0, c.totalmorebytes) sum_can_grow_by
FROM   
   dba_segments a,
   (SELECT
       df.tablespace_name, 
       nvl(max(fs.bytes), 0) free, 
       nvl(sum(fs.bytes), 0) remain
    FROM
       dba_data_files df,
       dba_free_space fs
    WHERE    
       df.file_id = fs.file_id (+)
    GROUP BY 
       df.tablespace_name) b,
   (SELECT  
       tablespace_name, 
       max(maxbytes - bytes) morebytes,
       sum(decode(AUTOEXTENSIBLE, 'YES', maxbytes - bytes, 0)) totalmorebytes,
       sum(decode(AUTOEXTENSIBLE, 'YES', 1, 0)) autoextensible
    FROM
       dba_data_files
    GROUP BY 
       tablespace_name) c
WHERE 
   a.tablespace_name = b.tablespace_name
AND   
   a.tablespace_name = c.tablespace_name
AND   
   ((c.autoextensible = 0) OR ((c.autoextensible > 0) AND (a.next_extent > c.morebytes)))
AND   
   a.next_extent > b.free
ORDER BY 
   5 desc, 
   3;

set echo off
set feedback off
set linesize 512

prompt -----------------------------------------------------
prompt - Classement des Tablespaces par consommation d'E/S -
prompt -----------------------------------------------------

column file_name        format a60              heading "Data-File Name"
column ts_name          format a32              heading "Tablespace Name"
column stat_reads       format 999,999,999,999  heading "Physical Reads"
column stat_writes      format 999,999,999,999  heading "Physical Writes"
column stat_breads      format 999,999,999,999  heading "Physical Blk-Reads"
column stat_bwrites     format 999,999,999,999  heading "Physical Blk-Writes"

break on ts_name

SELECT  
   t.name       ts_name,
   f.name       file_name,
   s.phyrds     stat_reads,
   s.phyblkrd   stat_breads,
   s.phywrts    stat_writes,
   s.phyblkwrt  stat_bwrites
FROM
   v$tablespace t,
   v$datafile f,
   v$filestat s
WHERE
   t.ts# = f.ts# 
AND
   f.file# = s.file#
ORDER BY 
   s.phyrds desc, 
   s.phywrts desc;

set echo off
set feedback off
set linesize 512

prompt ---------------------------------------------
prompt - Classement des attentes d' E/S de la base -
prompt ---------------------------------------------

column event  format a30
column segment_type format a10
column segment_name format a20

SELECT 
   event,
   segment_type,
   segment_name,
   file_id,block_id,
   blocks
FROM 
   dba_extents, 
   v$session_wait
WHERE 
   p1text='file#'
AND 
   p2text='block#'
AND 
   p1=file_id 
AND 
   p2 between block_id 
AND 
   block_id + blocks
ORDER BY 
   segment_type,
   segment_name;

set echo off
set feedback off
set linesize 512

prompt -----------------------
prompt - Verrous sur la base -
prompt -----------------------

column sidformat 999     heading "SID"
column usernameformat a10     heading "Utilisateur"
column machineformat a20    heading "Nom Machine"
column object_nameformat a20    heading "Nom objet"
column typeformat a4     heading "Type"
column lmode format a20  heading "Mode de verrouillage"
column requestformat 9999999 heading "Request Mode"
column blockformat 9999999 heading "Lock Blocking"

SELECT 
   s.sid  sid,
   s.username  username,
   s.machine  machine,
   l.type  type,
   o.object_name  object_name,
   DECODE(l.lmode,
      0,'None',
      1,'Null',
      2,'Row Share',
      3,'Row Exlusive',
      4,'Share',
      5,'Sh/Row Exlusive',
      6,'Exclusive') lmode,
   DECODE(l.request,
      0,'None',
      1,'Null',
      2,'Row Share',
      3,'Row Exlusive',
      4,'Share',
      5,'Sh/Row Exlusive',
      6,'Exclusive') request,
   l.block  block
FROM
   v$lock l,
   v$session s,
   dba_objects o
WHERE
   l.sid = s.sid
AND
   username != 'SYSTEM'
AND
   o.object_id(+) = l.id1;

set echo off
set feedback off
set linesize 512
set pagesize 1000

prompt --------------------------------------
prompt - Paramètres fichier d'init INIT.ORA -
prompt --------------------------------------

column value format a80

SELECT
   NAME, 
   DECODE (TYPE, 1, 'Boolean', 2, 'String', 3, 'INTEGER', 4, 'FILE', 5, 'RESERVED', 6, 'BIG INTEGER') TYPE, 
   VALUE, 
   DESCRIPTION, 
   ISSES_MODIFIABLE, 
   ISSYS_MODIFIABLE, 
   ISDEFAULT,
   ISMODIFIED, 
   ISADJUSTED
FROM 
   V$PARAMETER
ORDER BY 
   NAME;

set echo off
set feedback off
set linesize 512

prompt ---------------------------------
prompt - Liste des profiles de la base -
prompt ---------------------------------

SELECT DISTINCT 
   PROFILE
FROM 
   DBA_PROFILES;

SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 256

PROMPT --------------------------------
PROMPT - Rollback Segments de la base -
PROMPT --------------------------------

column UTILISATEUR format a20

SELECT
r.segment_nameSEGMENT,
r.ownerUTILISATEUR,
r.tablespace_nameTABLESPACE,
r.statusSTATUT,
round(r.initial_extent/1024/1024) "Init (Mo)",
round(r.next_extent/1024/1024) "Next (Mo)",
s.extents "NBR EXTENTS",
ROUND(s.rssize/1024/1024)"Taile (Mo)",
s.xactsactive_trans
FROM
dba_rollback_segs r,
v$rollname  n,
v$rollstat  s
WHERE
r.segment_name = n.name
AND
n.usn = s.usn;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - liste des REDO LOGS de la base -
prompt ----------------------------------

column member format a60
column archived format a10

SELECT
   A.GROUP#, 
   B.MEMBER, 
   A.THREAD#, 
   A.SEQUENCE#, 
   A.BYTES, 
   A.MEMBERS,
   A.ARCHIVED, 
   A.STATUS, 
   A.FIRST_CHANGE#, 
   A.FIRST_TIME
FROM 
   V$LOG A, V$LOGFILE B
WHERE 
   A.GROUP# = B.GROUP#
ORDER BY 
   A.GROUP#;

set echo off
set feedback off
set linesize 512

prompt ----------------------------------------
prompt - Liste des roles attribues de la base -
prompt ----------------------------------------

break on GRANTED_ROLE skip 1
column ADMIN format a8
column DEFAUT format a8

SELECT 
   GRANTED_ROLE ROLE, 
   GRANTEE UTILISATEUR, 
   ADMIN_OPTION ADMIN, 
   DEFAULT_ROLE DEFAUT
FROM 
   DBA_ROLE_PRIVS
WHERE 
   GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)
ORDER BY 
   GRANTED_ROLE, 
   ADMIN_OPTION;

set echo off
set feedback off
set linesize 512

prompt --------------------------------------------------
prompt - Liste des roles attribues aux roles de la base -
prompt --------------------------------------------------

break on GRANTED_ROLE skip 1
column ADMIN format a8
column DEFAUT format a8

SELECT 
   GRANTED_ROLE, 
   GRANTEE, 
   ADMIN_OPTION ADMIN, 
   DEFAULT_ROLE DEFAUT
FROM 
   SYS.DBA_ROLE_PRIVS
WHERE
   GRANTEE IN (SELECT ROLE FROM DBA_ROLES)
ORDER BY 
   GRANTED_ROLE, 
   ADMIN_OPTION;

set echo off
set feedback off
set linesize 512

prompt -------------------------------------------
prompt - liste des roles utilisateurs de la base -
prompt -------------------------------------------

SELECT 
   ROLE, 
   PASSWORD_REQUIRED
FROM 
   SYS.DBA_ROLES
WHERE 
   ROLE NOT IN (
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'CONNECT',
'CTXAPP',
'DBA',
'DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'GLOBAL_AQ_USER_ROLE',
'HS_ADMIN_ROLE',
'IMP_FULL_DATABASE',
'JAVADEBUGPRIV',
'JAVAIDPRIV',
'JAVASYSPRIV',
'JAVAUSERPRIV',
'JAVA_ADMIN',
'JAVA_DEPLOY',
'OEM_MONITOR',
'OLAP_DBA',
'RECOVERY_CATALOG_OWNER',
'RESOURCE',
'SELECT_CATALOG_ROLE',
'SNMPAGENT',
'WKADMIN',
'WKUSER'
);

set echo off
set feedback off
set linesize 512

prompt -------------------------------------------------------
prompt - Liste des roles utilisateurs attribues dans la base -
prompt -------------------------------------------------------

break on GRANTED_ROLE skip 1
column ADMIN format a8
column DEFAUT format a8

SELECT 
   GRANTED_ROLE ROLE, 
   GRANTEE UTILISATEUR, 
   ADMIN_OPTION ADMIN, 
   DEFAULT_ROLE DEFAUT
FROM 
  SYS.DBA_ROLE_PRIVS
WHERE 
   GRANTEE IN (SELECT USERNAME FROM SYS.DBA_USERS)
AND 
   GRANTED_ROLE NOT IN (
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'CONNECT',
'CTXAPP',
'DBA',
'DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'GLOBAL_AQ_USER_ROLE',
'HS_ADMIN_ROLE',
'IMP_FULL_DATABASE',
'JAVADEBUGPRIV',
'JAVAIDPRIV',
'JAVASYSPRIV',
'JAVAUSERPRIV',
'JAVA_ADMIN',
'JAVA_DEPLOY',
'OEM_MONITOR',
'OLAP_DBA',
'RECOVERY_CATALOG_OWNER',
'RESOURCE',
'SELECT_CATALOG_ROLE',
'SNMPAGENT',
'WKADMIN',
'WKUSER'
)
ORDER BY 
   GRANTED_ROLE, 
   ADMIN_OPTION;

set echo off
set feedback off
set linesize 512

prompt ---------------------------------------------------------------
prompt - Liste des rôles utilisateurs attribués aux rôles de la base -
prompt ---------------------------------------------------------------

break on GRANTED_ROLE skip 1

SELECT 
   GRANTED_ROLE, 
   GRANTEE, 
   ADMIN_OPTION, 
   DEFAULT_ROLE
FROM 
   SYS.DBA_ROLE_PRIVS
WHERE 
   GRANTED_ROLE NOT In (
'AQ_ADMINISTRATOR_ROLE',
'AQ_USER_ROLE',
'CONNECT',
'CTXAPP',
'DBA',
'DELETE_CATALOG_ROLE',
'EXECUTE_CATALOG_ROLE',
'EXP_FULL_DATABASE',
'GLOBAL_AQ_USER_ROLE',
'HS_ADMIN_ROLE',
'IMP_FULL_DATABASE',
'JAVADEBUGPRIV',
'JAVAIDPRIV',
'JAVASYSPRIV',
'JAVAUSERPRIV',
'JAVA_ADMIN',
'JAVA_DEPLOY',
'OEM_MONITOR',
'OLAP_DBA',
'RECOVERY_CATALOG_OWNER',
'RESOURCE',
'SELECT_CATALOG_ROLE',
'SNMPAGENT',
'WKADMIN',
'WKUSER'
)
AND 
   GRANTEE IN (SELECT ROLE FROM SYS.DBA_ROLES)
ORDER BY 
   GRANTED_ROLE, 
   ADMIN_OPTION;

set echo off feedback off linesize 512 pagesize 50

prompt ------------------------------
prompt - Liste des roles de la base -
prompt ------------------------------

SELECT 
   ROLE, 
   PASSWORD_REQUIRED
FROM 
   DBA_ROLES
ORDER BY
   ROLE;

set echo off
set feedback off
set linesize 512

prompt -------------------------
prompt - Evenements de session -
prompt -------------------------

column event format a32

SELECT 
   B.USERNAME, 
   B.SID, 
   B.SERIAL#, 
   A.EVENT, 
   A.TOTAL_WAITS,
   A.TOTAL_TIMEOUTS, 
   A.TIME_WAITED, 
   A.AVERAGE_WAIT
FROM 
   V$SESSION_EVENT A, 
   V$SESSION B
WHERE 
   A.SID = B.SID
ORDER BY 
   1;

set echo off
set feed off
set linesize 512

prompt ------------------------------------------
prompt - Liste des sessions actives de la base --
prompt ------------------------------------------

column machine format a30
column osuser format a30
column module format a20

SELECT 
   machine, 
   process, 
   osuser, 
   username,
   schemaname, 
   status, 
   lockwait, 
   sid,
   serial#, 
   module, 
   action
FROM 
   v$session
WHERE 
   username is not null
AND 
   osuser is not null
ORDER BY 
   machine, 
   osuser, 
   username,
   schemaname, 
   status, 
   module;

set echo off
set feedback off
set linesize 512

prompt --------------------------------
prompt - Occupation memoire de la SGA -
prompt --------------------------------

column dummy      noprint
column area       format a20 heading 'Main SGA Areas'
column name       format a20
column pool       format a20
column bytes      format 999,999,999,999
column sum(bytes) format 999,999,999,999

break on report
compute sum of sum(bytes) on report

SELECT 
   1 dummy, 
   'DB Buffer Cache' area, 
   name, 
   sum(bytes)
FROM 
   v$sgastat
WHERE 
   pool is null 
AND
   name = 'db_block_buffers'
GROUP BY 
   name
UNION ALL
SELECT 
   2, 
   'Shared Pool', 
   pool, 
   sum(bytes) 
FROM 
   v$sgastat
WHERE 
   pool = 'shared pool'
GROUP BY 
   pool
UNION ALL
SELECT 
   3, 
   'Large Pool', 
   pool, 
   sum(bytes) 
FROM 
   v$sgastat
WHERE 
   pool = 'large pool'
GROUP BY 
   pool
UNION ALL
SELECT 
   4, 
   'Java Pool', 
   pool, 
   sum(bytes) 
FROM 
   v$sgastat
WHERE 
   pool = 'java pool'
GROUP BY 
   pool
UNION ALL
SELECT 
   5, 
   'Redo Log Buffer', 
   name, 
   sum(bytes)
FROM 
   v$sgastat
WHERE 
   pool is null 
AND
   name = 'log_buffer'
GROUP BY 
   name
UNION ALL
SELECT 
   6, 
   'Fixed SGA', 
   name, 
   sum(bytes)
FROM 
   v$sgastat
WHERE 
   pool is null 
AND
   name = 'fixed_sga'
GROUP BY 
   name
ORDER BY 
   4 desc;

column area       format a20 heading 'Shared Pool Areas'

prompt ----------------------------
prompt -- Detail du pool partagé --
prompt ----------------------------

SELECT 
   'Shared Pool' area, 
   name, 
   sum(bytes) 
FROM 
   v$sgastat
WHERE 
   pool = 'shared pool' 
AND
   name in ('library cache','dictionary cache','free memory','sql area')
GROUP BY 
   name
UNION ALL
SELECT 
   'Shared Pool' area, 
   'miscellaneous', 
   sum(bytes) 
FROM 
   v$sgastat
WHERE 
   pool = 'shared pool' 
AND
   name not in ('library cache','dictionary cache','free memory','sql area')
GROUP BY 
   pool
ORDER BY 
   3 desc;

set echo off
set feedback off
set linesize 512

prompt --------------------------------
prompt - Liste des evenements systeme -
prompt --------------------------------

column event format a32

column total_waits     format999,999,999,999 heading "Total Attentes"
column total_timeouts  format999,999,999,999 heading "Total Timeouts"
column time_waited     format999,999,999,999 heading "Temps attendu"
column average_wait    format999,999,999,999 heading "Moyenne d'attente"

SELECT 
   EVENT EVENEMENT, 
   TOTAL_WAITS, 
   TOTAL_TIMEOUTS, 
   TIME_WAITED, 
   AVERAGE_WAIT
FROM 
   V$SYSTEM_EVENT
ORDER BY 
  4 DESC;

set echo off
set feedback off
set verify off
set linesize 512

set term off
COLUMN block_size NOPRINT new_value block_size
SELECT 
   value block_size 
FROM 
   v$parameter
WHERE 
   name='db_block_size';

set term on

prompt ------------------------------
prompt - Infos sur les tablespaces --
prompt ------------------------------

col Tablespace_name Heading 'Tablespace'
col Megs_Alloc      Heading 'Mo alloués'
col Megs_Free       Heading 'Mo libres'
col Megs_Used       Heading 'Mo utilisés'
col Pct_Free        Heading 'Pct Free'
col Pct_Used        Heading 'Pct Used'
col Init_Ext        Heading 'Init Ext'
col Next_Ext        Heading 'Next Ext'
col Min_Ext         Heading 'Min Ext'
col Max_Ext         Heading 'Max Ext'
col Num_Segs        Heading 'Nbre Segments'
col Num_Exts        Heading 'Nbre Extents'


SELECT 
   c.tablespace_name,
   round(a.bytes/1048576) Megs_Alloc,
   round(b.bytes/1048576) Megs_Free,
   round((a.bytes-b.bytes)/1048576) Megs_Used,
   round(b.bytes/a.bytes * 100) Pct_Free,
   round((a.bytes-b.bytes)/a.bytes * 100) Pct_Used,
   round(c.initial_extent/1048576) Init_Ext,
   round(c.next_extent/1048576) Next_Ext,
   round(a.minbytes/1048576) Min_Ext,
   round(a.maxbytes/1048576) Max_Ext,
   nvl(d.num_segs,0) Num_segs,
   nvl(d.num_exts,0) Num_Exts
FROM 
   ( SELECT 
        tablespace_name,
        sum(a.bytes) bytes,
        min(a.bytes) minbytes,
        max(a.bytes) maxbytes
     FROM 
        sys.dba_data_files a
     GROUP BY tablespace_name) a,
   ( SELECT 
        a.tablespace_name,
        nvl(sum(b.bytes),0) bytes
     FROM 
        sys.dba_data_files a,
        sys.dba_free_space b
     WHERE 
        a.tablespace_name = b.tablespace_name (+)
     AND 
        a.file_id = b.file_id (+)
     GROUP BY 
        a.tablespace_name) b,
    sys.dba_tablespaces c,
    ( SELECT 
        tablespace_name, 
        count(distinct segment_name) num_segs,
        count(extent_id) num_exts
      FROM 
        sys.dba_extents
      GROUP BY 
      tablespace_name) d
WHERE 
   a.tablespace_name = b.tablespace_name(+)
AND 
   a.tablespace_name = c.tablespace_name
AND 
   a.tablespace_name = d.tablespace_name(+)
ORDER BY 
   c.tablespace_name;

set echo off
set feed off

PROMPT -------------------------------------
PROMPT - Liste des déclencheurs de la base -
PROMPT -------------------------------------

SELECT 
   owner UTILISATEUR, 
   trigger_name TRIGGER, 
   status STATUT
FROM 
   all_triggers
WHERE 
   owner not in ('SYS','SYSTEM')
ORDER BY 
   owner, 
   trigger_name;

set echo off
set feedback off
set linesize 512

prompt -------------------------------------
prompt - Liste des utilisateurs de la base -
prompt -------------------------------------

column count(b.object_id) heading 'Nbre d'objets'

SELECT 
   a.username UTILISATEUR,
   a.account_status STATUT,
   a.default_tablespace "TABLESPACE PAR DEFAUT",
   a.temporary_tablespace "TABLESPACE TEMPORAIRE",
   count(b.object_id)
FROM 
   dba_users a, 
   dba_objects b
WHERE 
   a.username = b.owner(+)
GROUP BY 
   a.username,
   a.account_status, 
   a.default_tablespace,
   a.temporary_tablespace
ORDER BY
   a.account_status DESC,
   a.username;

set echo off
set feedback off
set linesize 512

prompt ---------------------------------------------------------
prompt - Liste des activités du segment de rollback de la base -
prompt ---------------------------------------------------------

SELECT 
   A.NAME, 
   B.XACTS,
   C.SID, C.SERIAL#,
   C.USERNAME, D.SQL_TEXT
FROM 
   V$ROLLNAME A, 
   V$ROLLSTAT B, 
   V$SESSION C, 
   V$SQLTEXT D, 
   V$TRANSACTION E
WHERE 
   A.USN = B.USN
AND 
   B.USN = E.XIDUSN
AND 
   C.TADDR = E.ADDR
AND 
   C.SQL_ADDRESS = D.ADDRESS
AND 
   C.SQL_HASH_VALUE = D.HASH_VALUE
ORDER BY 
   A.NAME, 
   C.SID, 
   D.PIECE;

set echo off
set heading off
set feedback off

PROMPT --------------------------------------------
PROMPT - Mise Hors ligne des segments de rollback -
PROMPT --------------------------------------------

set term off
spool rbs_hors_ligne.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter rollback segment '||segment_name||' offline;'
FROM 
   dba_rollback_segs
WHERE 
   segment_name != 'SYSTEM'
AND 
   status = 'ONLINE';

spool off
set term on

@rbs_hors_ligne.tmp

set echo off
set heading off
set feedback off

PROMPT --------------------------------------------
PROMPT - Remise en ligne des segments de rollback -
PROMPT --------------------------------------------


set term off
spool rbs_en_ligne.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter rollback segment '||segment_name||' online;'
FROM 
   dba_rollback_segs
WHERE 
   segment_name != 'SYSTEM'
AND 
   status = 'OFFLINE';

spool off
set term on

@rbs_en_ligne.tmp

set echo off
set heading off
set feedback off

prompt -----------------------------------
prompt - Mise hors ligne des tablespaces -
prompt -----------------------------------

set term off
spool tbs_hors_ligne.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter tablespace '||tablespace_name||' offline;'
FROM 
   dba_tablespaces
WHERE 
   tablespace_name not in ('SYSTEM','OUTLN')
AND 
   status = 'ONLINE';

spool off
set term on

@tbs_hors_ligne.tmp

set echo off
set heading off
set feedback off

prompt ---------------------------------
prompt - Mise en ligne des tablespaces -
prompt ---------------------------------

set term off
spool tbs_en_ligne.tmp

SELECT 'set echo on' FROM dual;
SELECT 'set feedback on' FROM dual;

SELECT 
   'alter tablespace '||tablespace_name||' online;'
FROM 
   dba_tablespaces
WHERE 
   tablespace_name not in ('SYSTEM','OUTLN')
AND 
   status = 'OFFLINE';

spool off
set term on

@tbs_en_ligne.tmp

set echo off
set head off
set feed off

PROMPT -------------------------------------------------
PROMPT - Desactivation de tous les triggers de la base -
PROMPT -------------------------------------------------

spool triggers_hors_ligne.tmp

SELECT 
   'alter trigger '||owner||'.'||trigger_name||' disable;'
FROM 
   all_triggers
WHERE 
   owner not in ('SYS','SYSTEM')
AND 
   status = 'ENABLED';

spool off
set feed on
set echo on
@triggers_hors_ligne.tmp

set echo off
set head off
set feed off

PROMPT -------------------------------------------------
PROMPT - Re-activation de tous les triggers de la base -
PROMPT -------------------------------------------------

spool triggers_en_ligne.tmp

SELECT 
   'alter trigger '||owner||'.'||trigger_name||' enable;'
FROM 
   all_triggers
WHERE 
   owner not in ('SYS','SYSTEM')
AND 
   status = 'DISABLED';

spool off
set feed on
set echo on
@triggers_en_ligne.tmp

set echo off
set feedback off
set linesize 512

prompt ----------------------------------
prompt - Types d'objets des tablespaces -
prompt ----------------------------------

column count(*) heading 'Nbre d''objets'
column sum(bytes) heading 'Octets'

SELECT 
   tablespace_name TABLESPACE, 
   owner UTILISATEUR, 
   segment_type "TYPE SEGMENT", 
   count(*), 
   sum(bytes)
FROM 
   sys.dba_extents
GROUP BY 
   tablespace_name, owner, segment_type
ORDER BY 
   tablespace_name, owner, segment_type;

set echo off
set feedback off
set heading off

prompt ----------------------------------
prompt - Instance et version de la base -
prompt ----------------------------------

SELECT 'Block Size = '||value FROM v$parameter where name = 'db_block_size'
UNION
SELECT 'Version    = '||banner FROM v$version where rownum = 1
UNION
SELECT 'Instance   = '||name FROM v$database
;