No image

Oracle Monitoring Scripts 9

1. sql_pid

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set linesize 999
set pagesize 999
SELECT SQL_TEXT 
FROM V$SQLTEXT 
WHERE ADDRESS=(SELECT 
				  HEXTORAW(s.sql_address)
				FROM 
				  V$SESSION S,
				  V$PROCESS P,
				  sys.V_$SESS_IO si
				WHERE 
				  S.paddr = P.addr  and
				  si.sid(+)=s.sid   and
				  (s.USERNAME is not null) and
				  (s.osuser<>'SYSTEM')  and
				  (s.type<>'BACKGROUND') and
				  p.spid = &spid)
  AND HASH_VALUE=(SELECT 
				  TO_NUMBER(s.sql_hash_value)
				FROM 
				  V$SESSION S,
				  V$PROCESS P,
				  sys.V_$SESS_IO si
				WHERE 
				  S.paddr = P.addr  and
				  si.sid(+)=s.sid   and
				  (s.USERNAME is not null) and
				  (s.osuser<>'SYSTEM')  and
				  (s.type<>'BACKGROUND') and
				  p.spid = &spid)
ORDER BY PIECE;

2. sql_sid

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set pagesize 999
SELECT SQL_TEXT 
FROM V$SQLTEXT 
WHERE ADDRESS=(SELECT 
				  HEXTORAW(s.sql_address)
				FROM 
				  V$SESSION S,
				  V$PROCESS P,
				  sys.V_$SESS_IO si
				WHERE 
				  S.paddr = P.addr  and
				  si.sid(+)=s.sid   and
				  (s.USERNAME is not null) and
				  (s.osuser<>'SYSTEM')  and
				  (s.type<>'BACKGROUND') and
				  si.sid = &sid)
  AND HASH_VALUE=(SELECT 
				  TO_NUMBER(s.sql_hash_value)
				FROM 
				  V$SESSION S,
				  V$PROCESS P,
				  sys.V_$SESS_IO si
				WHERE 
				  S.paddr = P.addr  and
				  si.sid(+)=s.sid   and
				  (s.USERNAME is not null) and
				  (s.osuser<>'SYSTEM')  and
				  (s.type<>'BACKGROUND') and
				  si.sid = &sid)
ORDER BY PIECE;

3. sqlbind

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set verify off
col var_name    form A18
col type_value  form A30
col sess_param  form A80
col stmt        form a150
-- 20080506  jailee
--accept sql_id      prompt 'Please enter the value for Sql_id : '

set termout off
col sql_id new_value sql_id
select '&1' sql_id from dual ;
set termout on
 
select *
from (select
	     case when trim(translate(name,':0123456789', '          ')) is null then
                       case when no = 1  then 'var '   || replace(name,':','b')  || ' '    ||         datatype_string
                            else              'exec :' || replace(name,':','b')  || ' := ' || '''' || decode(value_string,'NULL','',value_string) || ''';'
                       end
                  else
                       case when no = 1  then 'var '   || replace(name,':','')   || ' '    ||         datatype_string
                            else              'exec :' || replace(name,':','b')  || ' := ' || '''' || decode(value_string,'NULL','',value_string) || ''';'
                       end
             end stmt
      from v$sql_bind_capture b
          ,(select level no from dual connect by level <= 2) t
      where sql_id = '&sql_id'
      order by sql_id,CHILD_NUMBER,no,position
     )
union all
select '--DBA_HIST_SQLBIND' from dual
union all
select *
from (select
	     case when trim(translate(name,':0123456789', '          ')) is null then
                       case when no = 1  then 'var '   || replace(name,':','b')  || ' '    ||         datatype_string
                            else              'exec :' || replace(name,':','b')  || ' := ' || '''' || decode(value_string,'NULL','',value_string) || ''';'
                       end
                  else
                       case when no = 1  then 'var '   || replace(name,':','')   || ' '    ||         datatype_string
                            else              'exec :' || replace(name,':','b')  || ' := ' || '''' || decode(value_string,'NULL','',value_string) || ''';'
                       end
             end stmt
      from dba_hist_sqlbind b
          ,(select level no from dual connect by level <= 2) t
      where  dbid = (select dbid from v$database)
      and sql_id = '&&sql_id'
      and (snap_id , dbid, instance_number ) =
          (select max(snap_id),max(dbid), max(instance_number)
           from dba_hist_sqlbind b
           where sql_id =  '&&sql_id')
      --order by b.snap_id,1
      order by sql_id,snap_id,no,position
     )
;

--select --SQL_ID,
--       CHILD_ADDRESS,isdefault,' "'||NAME||'" = '||value||' ;' sess_param
--from v$sql_optimizer_env b
--where sql_id = '&&sql_id'
--and isdefault = 'NO'
--order by isdefault,name
--;

col VAR_NAME   clear 
col TYPE_VALUE clear 
col sess_param clear 
col stmt       clear 
set verify on

4. sqlpid

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select 	s.sid, s.serial#, substr(s.program,1,20) program, status, p.spid, osuser, process, q.sql_text, rows_processed, event
from 	v$session s, v$process p, v$sqlarea q, v$session_wait w
where 	s.paddr = p.addr
and 	s.sql_address = q.address
and 	s.sid = w.sid
and 	p.spid = &1;

5. sqlpid1

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select 	s.sid, s.serial#, substr(s.program,1,20) program, status, p.spid, osuser, process, q.sql_text, rows_processed, event
from 	v$session s, v$process p, v$sqlarea q, v$session_wait w
where 	s.paddr = p.addr
and 	s.sql_address = q.address
and 	s.sid = w.sid
and 	s.process = &1; 

6. sqlsid

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set linesize 900
set pagesize 124

select b.sid||'-'||b.serial# as "S_INFO", b.username, b.osuser, b.machine,
b.process, b.program, a.sql_text SQL
from v$sql a, v$session b
where a.address = b.sql_address
and a.hash_value = b.sql_hash_value
and b.sid = &sid
/

7. src

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

SELECT /*+ index_ss( a iesm_equip_ifdet_idx3 ) gather_plan_statistics */id_if,
       tp_if,
       id_if_det,
       tp_msg
  FROM iesm_equip_ifdet a
 WHERE tp_run = 'B'
   AND tp_if = 'VOIP'
   AND cnt_try = 0
   AND cd_rslt IS NULL
   AND dt_insert > ADD_MONTHS (SYSDATE, -1)
   AND ROWNUM = 1
/

8. stat_def

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select
           'CASCADE          : '||dbms_stats.GET_PARAM('CASCADE')
||chr(10)||'DEGREE           : '||dbms_stats.GET_PARAM('DEGREE')
||chr(10)||'ESTIMATE_PERCENT : '||dbms_stats.GET_PARAM('ESTIMATE_PERCENT')
||chr(10)||'METHOD_OPT       : '||dbms_stats.GET_PARAM('METHOD_OPT')
||chr(10)||'NO_INVALIDATE    : '||dbms_stats.GET_PARAM('NO_INVALIDATE')
||chr(10)||'GRANULARITY      : '||dbms_stats.GET_PARAM('GRANULARITY') default_value
from dual
/

9. stats

-- writer : ezis.cloud (noname)
-- Description  :  gather table statistics
-- Requirements : 

set    verify off

define _owner            = 'EXBILL'
define _degree           = '4'
define _estimate_percent = '100'
define _granularity      = 'global and partition'

accept v_owner             prompt 'please enter owner ( default : &_owner ) : '                       default '&_owner'
accept v_tname             prompt 'please enter table_name : '
accept v_degree            prompt 'please enter pq degree ( default : &_degree ) : '                  default '&_degree'
accept v_estimate_percent  prompt 'please enter estimate_percent ( default : &_estimate_percent ) : ' default '&_estimate_percent'
accept v_granularity       prompt 'please enter granularity ( default : &_granularity ) : '           default '&_granularity'

exec   dbms_stats.gather_table_stats (                  -
         ownname          => '&v_owner'                 -
        ,tabname          => '&v_tname'                 -
        ,granularity      => '&v_granularity'           -
        ,degree           => &v_degree                  -
        ,estimate_percent => &v_estimate_percent        -
        ,cascade          => true                       -
        ,no_invalidate    => false                      -
        ,method_opt       => 'for all columns size 1');

undef  v_owner
undef  v_tname
undef  v_degree
undef  v_estimate_percent
undef  _owner
undef  _degree
undef  _estimate_percent

set    verify on

10. sw

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

select sid, event  from v$session_wait where event not like '%message%';

11. t_used

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

SELECT h.file_id,
       ROUND(SUM(h.bytes_free + h.bytes_used) / 1048576, 2) total,
       ROUND(SUM(NVL(p.bytes_used, 0))/ 1048576, 2) used,
       ROUND(SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / 1048576, 2) free,
       ROUND((SUM((h.bytes_free + h.bytes_used) - NVL(p.bytes_used, 0)) / SUM(h.bytes_used + h.bytes_free)) * 100,2) free_pct
FROM   sys.v_$TEMP_SPACE_HEADER h, sys.v_$Temp_extent_pool p
WHERE  p.file_id(+) = h.file_id
AND    p.tablespace_name(+) = h.tablespace_name
GROUP BY rollup(h.file_id);

12. tab_stat

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

SET    VERIFY OFF TIMING OFF FEEDBACK OFF

VAR    V_OWNER       VARCHAR2(20);
VAR    V_TABLE_NAME  VARCHAR2(35);

EXEC  :V_OWNER      := CASE WHEN INSTRB('&&1','.') > 0 THEN UPPER(SUBSTR('&&1', 1,INSTRB('&&1','.')-1)) ELSE '&dft_owner' END ;
EXEC  :V_TABLE_NAME := UPPER( SUBSTR('&&1', INSTRB('&&1','.')+1));

start  arg2

set    termout off
alter  session set NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi' ;
set    termout on

COL    OWNER          FOR A12
COL    TABLE_NAME     FOR A32
COL    PARTITION_NAME FOR A24 HEADING PNAME
COL    INDEX_NAME     FOR A30
COL    BLEVEL         FOR 999999
COL    BLOCKS         FOR 999,999,999
COL    CLUFAC         FOR 999,999,999
COL    LAST_ANALYZED  FOR A20

SELECT
--       OWNER ||'.' || TABLE_NAME TABLE_NAME
       PARTITION_NAME || CASE WHEN SUBPARTITION_NAME IS NOT NULL THEN '-' END || SUBPARTITION_NAME PARTITION_NAME
      ,NUM_ROWS
      ,BLOCKS
      ,AVG_ROW_LEN
      ,SAMPLE_SIZE
      ,LAST_ANALYZED
      ,STATTYPE_LOCKED
       --,EMPTY_BLOCKS
       --,AVG_SPACE
       --,CHAIN_CNT
       --,AVG_SPACE_FREELIST_BLOCKS
       --,NUM_FREELIST_BLOCKS
       --,AVG_CACHED_BLOCKS
       --,AVG_CACHE_HIT_RATIO
FROM   DBA_TAB_STATISTICS
WHERE  1=1
AND    TABLE_NAME = :V_TABLE_NAME
AND    OWNER      = :V_OWNER
ORDER  BY NVL(PARTITION_NAME,' ')
/

SELECT
--       OWNER ||'.' || TABLE_NAME TABLE_NAME
       INDEX_NAME
      ,PARTITION_NAME || CASE WHEN SUBPARTITION_NAME IS NOT NULL THEN '-' END || SUBPARTITION_NAME PARTITION_NAME
      ,BLEVEL
      ,LEAF_BLOCKS LEAFCNT
      ,DISTINCT_KEYS DISTKEY
      ,CLUSTERING_FACTOR CLUFAC
      ,NUM_ROWS
      ,LAST_ANALYZED
      ,STATTYPE_LOCKED
    --,PARTITION_POSITION
    --,SUBPARTITION_POSITION
    --,OBJECT_TYPE
    --,AVG_LEAF_BLOCKS_PER_KEY
    --,AVG_DATA_BLOCKS_PER_KEY
    --,AVG_CACHED_BLOCKS
    --,AVG_CACHE_HIT_RATIO
    --,SAMPLE_SIZE
    --,GLOBAL_STATS
    --,USER_STATS
    --,STALE_STATS
FROM   DBA_IND_STATISTICS
WHERE  1=1
AND    TABLE_NAME = :V_TABLE_NAME
AND    OWNER = :V_OWNER
AND    INDEX_NAME NOT LIKE 'SYS_IL%'
ORDER  BY INDEX_NAME,NVL(PARTITION_NAME,' ')
/

set    termout off
alter  session set NLS_DATE_FORMAT='&dft_nls_date_format' ;
set    termout on

COL    OWNER          CLEAR
COL    TABLE_NAME     CLEAR
COL    PARTITION_NAME CLEAR
COL    INDEX_NAME     CLEAR
COL    BLEVEL         CLEAR
COL    BLOCKS         CLEAR
COL    CLUFAC         CLEAR
COL    LAST_ANALYZED  CLEAR

PROMPT
SET    VERIFY ON TIMING ON FEEDBACK ON

13. table_chain

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

spool analyze_chain.sql;

select 'analyze table '||table_name||' list chained rows;'
from user_tables;

spool off;

truncate table chained_rows;

start analyze_chain;

set pause on;
Select owner_name,table_name,head_rowid from chained_rows;
set pause off;

14. table_emptyblocks

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

# empty block(free block) is > 10 % Table 

select table_name, blocks, empty_blocks
     from dba_tables
     where empty_blocks / (blocks + empty_blocks) > .1;


# empty block(free block) is < 10 % Table 

select table_name, blocks, empty_blocks
     from dba_tables
     where empty_blocks / (blocks + empty_blocks) < .1;

15. table_size

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

spool analyze_table.sql

select 'analyze table '||owner||'.'||table_name||' compute statistics;'
from dba_tables
Where owner = 'DBAM';

spool off;
start analyze_table;

column t_name heading 'Tables' format a15;
column tt_name heading 'Tablespace' format a10;
set pagesize 25;
set pause 'Please Enter...';
set pause on;

Select table_name t_name,num_rows,avg_row_len,
       nvl(to_char(round(round((num_rows*(avg_row_len+2))/1768.1)*2048+1.0)),0) table_size
  from dba_tables
 where owner = 'DBAM';

Set pause off;

16. tablespace

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

column totalsize heading "total size(M)"
column usedsize heading "used size(M)"
column freesize heading "free size(M)"
column tablespace_name format a30
column totalsize format 999,999.9
column usedsize format 999,999.9
column freesize format 999,999.9


select a.tablespace_name, a.totalsize,
       nvl(b.usedsize,0) usedsize, c.freesize
  from ( select tablespace_name, sum(bytes)/1024/1024 totalsize
           from dba_data_files
          group by tablespace_name ) a,
       ( select tablespace_name, sum(bytes)/1024/1024 usedsize
           from dba_segments
          group by tablespace_name ) b,
       ( select tablespace_name, sum(bytes)/1024/1024 freesize
           from dba_free_space
          group by tablespace_name ) c
 where a.tablespace_name = b.tablespace_name(+)
   and a.tablespace_name = c.tablespace_name(+)
order by c.freesize desc
/

17. tablespace1

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

column totalsize heading "total size(M)"
column usedsize heading "used size(M)"
column freesize heading "free size(M)"
column tablespace_name format a30
column totalsize format 999,999.9
column usedsize format 999,999.9
column freesize format 999,999.9


select a.tablespace_name, a.totalsize,
       nvl(b.usedsize,0) usedsize, c.freesize
  from ( select tablespace_name, sum(bytes)/1024/1024 totalsize
           from dba_data_files
          group by tablespace_name ) a,
       ( select tablespace_name, sum(bytes)/1024/1024 usedsize
           from dba_segments
          group by tablespace_name ) b,
       ( select tablespace_name, sum(bytes)/1024/1024 freesize
           from dba_free_space
          group by tablespace_name ) c
 where a.tablespace_name = b.tablespace_name(+)
   and a.tablespace_name = c.tablespace_name(+)
   and a.tablespace_name = upper('&tablespace')
/

18. tblspcmap

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

rem 
rem  File: filemap.sql 
rem 
rem  This script provides information on the location/path, file name, 
rem  size and physical I/Os (if applicable) of all the Oracle files 
rem  (database, control and redo log) in an Oracle/UNIX platform. 
rem 
rem 
set pages 999 
col path  format a20     heading 'Path' 
col fname format a15     heading 'File Name' 
col tblsp format a30     heading 'Tablespace Name' 
col fsize format 999999  heading 'M bytes' 
col pr    format 999999 heading 'Phy. Reads' 
col pw    format 999999 heading 'Phy. Writes' 
break on path skip 1 
spool zzfilemap.rep 
select	substr(tablespace_name, 1, 30) tblsp,
	sum(nvl(fsize,0)) fsize, sum(pr) pr, sum(pw) pw
from (
select substr(name,1,instr(name, '/', -1)-1 ) path,   
       substr(name,instr(name, '/', -1)+1 )  fname, 
       df.bytes/1048576  fsize, 
       phyrds pr,  
       phywrts pw,
       tablespace_name
  from v$datafile df, v$filestat fs, dba_data_files ts
 where df.file# = fs.file# and fs.file# = ts.file_id
UNION 
select substr(name,1,instr(name, '/', -1)-1 ) path, 
       substr(name,instr(name, '/', -1)+1 ) fname, 
       0 fsize, 
       0 pr, 
       0 pw,
       'Controlfile' tablespace_name 
  from v$controlfile 
UNION 
select substr(lgf.member,1,instr(lgf.member,'/', -1)-1) path, 
       substr(lgf.member,instr(lgf.member, '/', -1)+1 ) fname, 
       lg.bytes/1048576 fsize, 
       0 pr, 
       0 pw,
       'Redolog' tablespace_name
  from v$logfile lgf, v$log lg 
 where lgf.group# = lg.group# 
)
group by tablespace_name 
order by tablespace_name
/ 
spool off 

19. temp

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

col total_mbytes    for 999,999,999
col used_mbytes     for 999,999,999
col free_mbytes     for 999,999,999
col cached_mbytes   for 999,999,999
col max_used_mbytes for 999,999,999
col max_sort_mbytes for 999,999,999
col TABLESPACE_NAME for a15

--select inst_id
--,tablespace_name
--,file_id
--, (blocks_used*value)/1024/1024 used_mbytes
--, (blocks_free*value)/1024/1024 free_mbytes
--from gv$temp_space_header
--,(select to_number(value) value from v$parameter where name like 'db_block_size')
--order by 1,2,3
--; 

select inst_id
, tablespace_name
, file_id
, (blocks_cached*value)/1024/1024 cached_mbytes
, (blocks_used*value)/1024/1024 used_mbytes
from gv$temp_extent_pool
,(select to_number(value) value from v$parameter where name like 'db_block_size')
order by 1,2,3
;

select inst_id, tablespace_name, segment_file
     , (total_blocks*8)/1024    total_mbytes
     , (used_blocks*8)/1024     used_mbytes
     , (free_blocks*8)/1024     free_mbytes
     , (max_used_blocks*8)/1024 max_used_mbytes
     , (max_sort_blocks*8)/1024 max_sort_mbytes
     ,free_requests
     ,freed_extents
from gv$sort_segment
order by inst_id;


col total_mbytes    clear
col used_mbytes     clear
col free_mbytes     clear
col max_used_mbytes clear
col max_sort_mbytes clear
col TABLESPACE_NAME clear

20. toto

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

SELECT COUNT(*) FROM SLIPRTLTUSER.TB_PTIFF00
/

21. tprivs

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set    verify off

col    owner       for a20
col    table_name  for a20
col    grantee     for a20
col    grantor     for a20
col    PRIVILEGE   for a20

SELECT *
FROM   dba_tab_privs
WHERE  table_name = upper('&&1')
order  by owner,grantee,PRIVILEGE
/

col    grantee     clear
col    grantor     clear
col    PRIVILEGE   clear
col    owner       clear
col    table_name  clear
       
set    verify on

22. transtat

-- writer : ezis.cloud (noname)
-- Description  :  transaction status in this instance 
-- Requirements : 

SET    PAUSE OFF

COL    NAME       FOR A22
COL    USN        FOR 9999
COL    SLOT       FOR 99
COL    UREC       FOR 99999999999
COL    SID        FOR 9999
COL    ID1        FOR 9999999
COL    ID2        FOR 9999999
COL    PROGRAM    FOR A20 TRUNC
COL    MACHINE    FOR A15 TRUNC
COL    PROCESS    FOR A12
COL    START_TIME FOR A18

PROMPT TRANSACTIONS FOR EACH ROLLBACK SEGS

SELECT
       B.NAME, D.XIDUSN USN, D.XIDSLOT SLOT , D.USED_UREC UREC
     , D.USED_UBLK UBLK , A.SID SID, A.ID1 ID1, A.ID2 ID2
     , PROGRAM, MACHINE , C.PROCESS , D.START_TIME
FROM   V$LOCK        A
     , V$ROLLNAME    B
     , V$SESSION     C
     , V$TRANSACTION D
WHERE  A.TYPE = 'TX'
AND    TRUNC(A.ID1/65536) = B.USN
AND    A.LMODE > 0
AND    A.REQUEST = 0
AND    A.SID = C.SID
AND    C.SADDR = D.SES_ADDR
ORDER  BY B.NAME
/

COL    OBJECT_NAME    FOR A35
COL    OWNER          FOR A15
COL    SID            FOR A15

BREAK  ON SID

PROMPT LOCKED OBJECT BY USER SESSION
SELECT /*+ RULE */
       A.SID||','||C.SERIAL# SID, A.ID1 ID1, B.OWNER OWNER, B.OBJECT_NAME, PROGRAM, C.MACHINE, C.PROCESS 
FROM   V$LOCK      A
     , DBA_OBJECTS B
     , V$SESSION   C
WHERE  A.TYPE = 'TM'
AND    A.LMODE > 0
AND    A.REQUEST = 0
AND    A.SID = C.SID
AND    A.ID1 = B.OBJECT_ID
ORDER  BY A.SID
/

COL    NAME        CLEAR
COL    USN         CLEAR
COL    SLOT        CLEAR
COL    UREC        CLEAR
COL    SID         CLEAR
COL    ID1         CLEAR
COL    ID2         CLEAR
COL    PROGRAM     CLEAR
COL    MACHINE     CLEAR
COL    OWNER       CLEAR
COL    PROCESS     CLEAR
COL    START_TIME  CLEAR
COL    OBJECT_NAME CLEAR

CLEAR  BREAKS

23. ts

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set echo off

col Allocated for  99,999,999,999  heading "Allocated(M)"
col Used      for  99,999,999,999  heading "Used(M)"
col Free      for  99,999,999,999  heading "Free Byte(M)"
col Max_free  for  99,999,999,999  heading "Max Free(M)"
col PCT_Used  for  99.99        heading "Used(%)"
col TABLESPACE_NAME for  a30

set feedback off verify off

SELECT TABLESPACE_NAME
       ,SUM(t_mbytes)                                   AS      Allocated
       ,SUM(t_mbytes)-SUM(f_mbytes)                     AS      Used
       ,SUM(f_mbytes)                                   AS      Free
       ,(SUM(t_mbytes)-SUM(f_mbytes))/SUM(t_mbytes)*100 AS      PCT_Used
       ,sum(mf_mbytes)                                  AS      Max_free
FROM (
       SELECT TABLESPACE_NAME, SUM(bytes/1024/1024) f_mbytes,0 t_mbytes, max(bytes/1024/1024) mf_mbytes
       FROM dba_free_space a
       WHERE a.TABLESPACE_NAME LIKE upper('&&1.%')
       GROUP BY TABLESPACE_NAME
       UNION ALL
       SELECT TABLESPACE_NAME, 0,SUM(bytes/1024/1024) t_mbytes,0
       FROM dba_data_files a
       WHERE a.TABLESPACE_NAME LIKE upper('&&1.%')
       GROUP BY TABLESPACE_NAME
       UNION ALL
       SELECT TABLESPACE_NAME, 0,SUM(bytes/1024/1024) t_mbytes,0
       FROM dba_temp_files a
       WHERE a.TABLESPACE_NAME LIKE upper('&&1.%')
       GROUP BY TABLESPACE_NAME
)
GROUP BY rollup(TABLESPACE_NAME)
order BY TABLESPACE_NAME
/

set feedback on verify on

col Allocated clear
col Used      clear
col Free      clear
col Max_free  clear
col PCT_Used  clear
col TABLESPACE_NAME clear

24. ts_datafile

-- writer : ezis.cloud (noname)
-- Description  : 
-- Requirements : 

set verify off
col tablespace_name for a30
col file_name       for a60
col tot_mb          for 999,999,999
col free_mb         for 999,999,999
col pct_used        for 999

break on tablespace_name

select t.tablespace_name
      ,d.file_id
      ,file_name
      ,d.mbytes tot_mb
      ,f.mbytes free_mb
      ,(d.mbytes-f.mbytes) * 100 / d.mbytes pct_used
      ,AUTOEXTENSIBLE
  from dba_tablespaces t,
       (
       select tablespace_name
             ,file_id
             ,max(file_name) file_name
             ,max(AUTOEXTENSIBLE) AUTOEXTENSIBLE
             ,sum(bytes)/1024/1024 mbytes
         from dba_data_files
        where tablespace_name like upper('&&1')||'%'
        group by tablespace_name,file_id
       union all
       select tablespace_name
             ,file_id
             ,max(file_name) file_name
             ,max(AUTOEXTENSIBLE) AUTOEXTENSIBLE
             ,sum(bytes)/1024/1024 mbytes
         from dba_temp_files
        where tablespace_name like upper('&&1')||'%'
        group by tablespace_name,file_id
       ) d,
       (
       select tablespace_name
             ,file_id
             ,sum(bytes)/1024/1024 mbytes
         from dba_free_space
        where tablespace_name like upper('&&1')||'%'
        group by tablespace_name,file_id
       ) f
 where t.tablespace_name = d.tablespace_name (+)
   and d.tablespace_name = f.tablespace_name (+)
   and d.file_id = f.file_id (+)
   and t.tablespace_name like upper('&&1')||'%'
 order by 1,2
/

clear breaks
col tablespace_name clear
col file_name       clear
col tot_mb          clear
col free_mb         clear
col pct_used        clear
set verify on
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]