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_Temp Varchar2(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_Temp Varchar2(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.alloc alloc
, df.alloc - nvl(fs.free,0) used
, ((df.alloc - nvl(fs.free,0))* 100)/df.alloc pused
, dt.autoext autoext
, df.summaxbytes/1024 maxbytes
, dt.Inc inc
-- , nvl(fs.free,0) free
-- , fs.nbfrag
, Nvl(fs.fragmax,0) fragmax
-- , dt.ext_manage
-- , dt.seg_sp_mg seg_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.nbf nbfile
, df.ftype ftype
-- , 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