from this Blog
http://blog.csdn.net/flighting/article/details/1119253
Oracle Tablespace Monitoring Script
Oracle Tablespace Monitoring
from this Blog
http://blog.csdn.net/flighting/article/details/1119253
-- 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
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
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;
/
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
Larg.
Free
Tablespace Alloc Used Aut Sum Max Incr Ext Space Nb F. Max
Cont. Name (Mb) (Mb) %Used Ext (Gb) (Mb) (Mb) Mg. F. T. %Used
----- ---------------------- --------- --------- ------ --- -------- --------------- ------ ------- --- -- ------
PERM SYSTEM 690 684 99.2 Y 32 10 5 LMTS 1 D 2.1
SYSAUX 550 513 93.4 Y 32 10 32 ASSM 1 D 1.6
EXAMPLE 100 79 78.8 Y 32 .6 19 ASSM 1 D 0.2
USERS 5 4 81.3 Y 32 1.3 1 ASSM 1 D 0.0
***** --------- --------- --------
sum 1345 1280 128
TEMP TEMP 29 29 100.0 Y 32 .6 0 LMTS 1 T 0.1
Larg.
Free
Tablespace Alloc Used Aut Sum Max Incr Ext Space Nb F. Max
Cont. Name (Mb) (Mb) %Used Ext (Gb) (Mb) (Mb) Mg. F. T. %Used
----- ---------------------- --------- --------- ------ --- -------- --------------- ------ ------- --- -- ------
***** --------- --------- --------
sum 29 29 32
UNDO UNDOTBS1 95 40 41.8 Y 32 5 40 LMTS 1 D 0.1
***** --------- --------- --------
sum 95 40 32
6 rows selected.
