SET FLUSH OFF
SET TERM OFF
set pages 100
set linesize 132
column tablespace heading 'Tablespace' format a20
column HOST_NAME new_value host

select HOST_NAME from v$INSTANCE;

PROMPT " ETAT SUR LES ECHECS D'EXTENSIONS POSSIBLES DES TABLESPACES "

REM ACCEPT tbs_to_ignore CHAR PROMPT "Donnez le nom du tablespace a ignorer : "

define output = $ORACLE_HOME/tmp/EtatEchecsExtensionsPossiblesTablespaces.lis
spool &output
SELECT 
  substr(segment_name,1,30) object_name,
  tablespace_name,
  segment_type object_type,
  round(bytes/1024/1024, 2) Mb ,
  round(initial_extent/1024/1024, 2) initial_ex ,
  round(next_extent/1024/1024, 2) next_ex ,
  round ( 
  (
  SELECT 
   to_char(max(ufs.bytes))
  FROM 
   user_free_space  ufs 
   WHERE ufs.tablespace_name=dba_segments.tablespace_name
  ) /1024/1024,2) "Free max Block",  
  Owner
FROM 
  dba_segments WHERE 
  tablespace_name not in ('SYSTEM') and 
/*  tablespace_name not in ('$tbs_to_ignore') and */
  tablespace_name not like ('PERFDATA') and 
  tablespace_name not like ('SYSAUX') and 
  segment_type in ('TABLE','INDEX') and
(
( /* Juste pour le REBUILD des indexs */
  initial_extent/1024  > (
  SELECT 
   to_char(max(ufs.bytes) / 1024)
  FROM 
   user_free_space  ufs 
  WHERE ufs.tablespace_name=dba_segments.tablespace_name
  ) AND segment_type in ('INDEX')
) OR ( 
  next_extent/1024  > ( 
  SELECT 
   to_char(max(ufs.bytes) / 1024)
  FROM 
   user_free_space  ufs 
  WHERE ufs.tablespace_name=dba_segments.tablespace_name))
);
spool off
clear columns
SET FLUSH ON
SET TERM ON