列出表空间的使用情况

List tablespaces
- space allocated
- space used
- %free (without maxbytes)
- Autoextend Y|N|Y/N
- Sum of maxbytes
- increment (in Mb) 10/100 or 0/10/.... if more than 2 datafiles with different increment size
- Largest free extent
- Space management (DICT MAN -> Dictionnary Manual| ASSM | LMTS)
- Number of files
- T: tempfile | D: datafile
- %Used (using maxbytes in calcul) 

 

set linesize   145
set pagesize    60
set trimout     on
set trimspool   on
Set Feedback   off
set timing     off
set verify     off


prompt

prompt -- ----------------------------------------------------------------------- ---

prompt --   Tablespaces                                                           ---

prompt -- ----------------------------------------------------------------------- ---

prompt


Set Heading  Off
Set Termout  Off

create or replace function show_autoext_tbs(P_Tablespace_Name In Varchar2) return varchar2
Is
  --
  Cursor CX Is
Select distinct Substr(autoextensible,1,1)
      from dba_data_files
      where tablespace_name=P_Tablespace_Name
      Union
Select distinct Substr(autoextensible,1,1)
      from dba_temp_files
      where tablespace_name=P_Tablespace_Name
;
  --
  W_Texte     Varchar2(20);
  W_TempVarchar2(20);
  --
Begin
  --
  W_Texte := Null;
  --
  Open CX;
  Loop
    Fetch CX Into W_Temp;
    Exit When CX%notfound;
    W_Texte := W_Texte|| ' ' || W_Temp;
  End Loop;
  Close CX;
  --
  W_Texte := Replace(Substr(W_Texte, 2, 4), ' ', '/');
  --
  Return W_Texte;
  --
End;
/

-- show error



create or replace function show_increment_tbs(P_Tablespace_Name In Varchar2, P_Db_Block_Size In Number) return varchar2
Is
  --
  Cursor CX Is
Select distinct To_Char(round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
      from dba_data_files
      where tablespace_name=P_Tablespace_Name
      Union
Select distinct To_Char(Round(((Increment_By * P_Db_Block_Size)/1024/1024),1) )
      from dba_temp_files
      where tablespace_name=P_Tablespace_Name
;
  --
  W_Texte     Varchar2(20);
  W_TempVarchar2(20);
  --
Begin
  --
  W_Texte := Null;
  --
  Open CX;
  Loop
    Fetch CX Into W_Temp;
    Exit When CX%notfound;
    W_Texte := W_Texte|| ' ' || W_Temp;
  End Loop;
  Close CX;
  --
  W_Texte := W_Texte||' ';
  W_Texte := Replace(W_Texte,' ', '/');
  W_Texte := Substr(W_Texte,2, Instr(W_Texte, '/', -1, 1)-2);
  If Instr(W_Texte, '/', 1,2) != 0 then
    W_Texte := Substr(W_Texte, 1, Instr(W_Texte, '/', 1,2))||'...';
  End If;
  --
  Return W_Texte;
  --
End;
/

-- show error





Column Var_DB_BLOCK_SIZE new_value Var_DB_BLOCK_SIZE noprint

Select
       value Var_DB_BLOCK_SIZE
  from
       v$parameter
where
       Upper(name) = Upper ('db_block_size')
;

Set Termout  On
Set Heading  On



clear breaks
break on contents -
skip 1
compute Sum of alloc used maxbytes nfree nbfrag on contents


column tblsp         format a22 wrap         heading  "Tablespace|Name"
column Alloc         format 99999999         heading  "Alloc|(Mb)"
column file_name     format a45 wrap         heading  "DataFile Name"
column rfno          format 999              heading  "R.|Fno"
-- column inc        format 9999999          heading  "Incr|(Mb)"

column inc           format a15              heading  "Incr|(Mb)"
column maxbytes      format 9999999          heading  "Sum Max|(Gb)"
column user_bytes    format 999999           heading  "Userbytes|(Mb)"
column free          format 999999           heading  "Free|(Mb)"
column nfree         format 99999            heading  "Max|Free|(Mb)"
column used          format 99999999         heading  "Used|(Mb)"
column pused         format 990.9            heading  "%Used"
column maxpused      format 990.9            heading  "Max|%Used"
column contents      format a5               heading  "Cont."
column fragmax       format 99999            heading  "Larg.|Free|Ext|(Mb)"
column nbfrag        format 99999            heading  "Nb|frag"
column nbfile        format 99               heading  "Nb|F."
column autoext       format a3 wrap          heading  "Auto|Ext."
column ftype         format a2 wrap          heading  "F.|T."
column ext_manage    format a5 wrap          heading  "Ext.|M."
column seg_sp_mg     format a2 wrap          heading  "S.|M."
column seg_space_mg  format a7 wrap          heading  "Space|Mg."


set linesize 145

Select
       dt.contents contents
      , dt.Tablespace_Name tblsp
      , df.allocalloc
      , df.alloc - nvl(fs.free,0)used
      , ((df.alloc - nvl(fs.free,0))* 100)/df.allocpused
      , dt.autoext      autoext
      , df.summaxbytes/1024maxbytes
      , dt.Inc inc
--    , nvl(fs.free,0) free
--    , fs.nbfrag
      , Nvl(fs.fragmax,0)fragmax
--    , dt.ext_manage
--    , dt.seg_sp_mgseg_sp_mg
      , Decode(dt.ext_manage||' '||dt.seg_sp_mg,'LOC MA', ' LMTS', 'LOC AU', '  ASSM',dt.ext_manage||' '||dt.seg_sp_mg) seg_space_mg
      , df.nbfnbfile
      , df.ftypeftype
--    , decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0)) nfree
      , 100 - ((decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes))-(df.alloc - nvl(fs.free,0))) * 100 / decode((df.summaxbytes),0,(df.alloc),(df.summaxbytes)))  maxpused
  from
       ( select
                Substr(contents,1,4)     contents
              , tablespace_name
              , Substr(extent_management,1,3)   ext_manage
              , Substr(segment_space_management,1,2) seg_sp_mg
              , show_autoext_tbs(tablespace_name) autoext
  , show_increment_tbs (tablespace_name, &Var_DB_BLOCK_SIZE) inc
           from dba_tablespaces
       ) dt
    ,  ( select
                Sum(Bytes/1024/1024)alloc
              , Max(MaxBytes/1024/1024)maxbytes
              , Sum(MaxBytes/1024/1024)summaxbytes
              , count(*)nbf
              , tablespace_name
              , 'D'ftype
           from Dba_Data_Files
          Group
             By tablespace_name
         Union
         select
                Sum(Bytes/1024/1024)alloc
              , Max(MaxBytes/1024/1024)maxbytes
              , Sum(MaxBytes/1024/1024)summaxbytes
              , count(*)nbf
              , tablespace_name
              , 'T'ftype
           from Dba_Temp_Files
          Group
             By tablespace_name
       ) df
    ,  (select
                Nvl(Sum(nvl(bytes/1024/1024,0)),0)free
              , Nvl(max (bytes)/1048576,0)   fragmax
              , tablespace_name
              , count(*)                        nbfrag
          from (Select distinct tablespace_name, bytes from dba_free_space)
         Group
            By tablespace_name
         order by max (bytes)/1048576 desc
       ) fs
Where
       df.tablespace_name  =  fs.tablespace_name (+)
  and
       df.tablespace_name  = dt.tablespace_name
Group
    By  dt.contents
     , dt.Tablespace_Name
     , df.alloc
     , df.maxbytes
     , df.summaxbytes
     , df.nbf
     , dt.Inc
--   , nvl(fs.free,0)
     , df.alloc - nvl(fs.free,0)
--   , fs.nbfrag
     , fs.fragmax
     , dt.autoext
     , dt.ext_manage
     , df.ftype
     , dt.seg_sp_mg  
Order
    By contents
     , maxpused desc
     , dt.inc
;


Prompt

Set Heading  Off
Set Termout  Off

Drop function show_autoext_tbs;
Drop function show_increment_tbs;

Set Heading  On
Set Termout  On