Regular monitoring SQL queries for DBA

Regular monitoring SQL queries for DBA

================================

Free Space checking & Solution : -


select tbs.tbs, total_mb tbs_mb, nvl(free_mb,0) free_mb, round(nvl(free_mb,0)/total_mb*100,2) pct_free, total_mb*15/100 -nvl(free_mb,0) Est_mbfrom(select tablespace_name tbs,round(sum(bytes)/1024/1024,2) free_mb from dba_free_space group by tablespace_name) free_tbs,(select tablespace_name tbs,round(sum(bytes)/1024/1024,2) total_mb from dba_data_files group by tablespace_name) tbswhere tbs.tbs=free_tbs.tbs(+)AND round(nvl(free_mb,0)/total_mb*100,2) <=20;



Particular Tablespace Free Space :

select tablespace_name, tbs_mb, free_mb,round(free_mb/tbs_mb*100,2) pct_free, round(tbs_mb*11/100-free_mb,2) Est_mb from( select a.tablespace_name ,sum(a.bytes)/1024/1024 tbs_mb,nvl((select sum(bytes)/1024/1024 from dba_free_space where tablespace_name=a.tablespace_name),0) free_mbfrom dba_data_files a group by tablespace_name)where free_mb/tbs_mb*100 >10 and tablespace_name='UNDO';



col file_name for a65



select file_name,BYTES/1024/1024 from dba_data_files where TABLESPACE_NAME='



alter database datafile '/dbo/inx/CFTACC/CFTACC_cft_matrix_index_00.dbf' resize 1300m;



alter tablespace X0006 add datafile '/ldata/gmd01/31pbp1d9/oradata/pbp1/pbp1_X0006e.dbf' size 1000m;



Find High Water Mark :

SELECT file_name,round(f.bytes / 1024 / 1024) "Total Size M",round(s.bytes / 1024 / 1024) "Can be shrunk by M "-- , 'alter database datafile ''' file_name ''' resize ' to_char(round(s.bytes / 1024 / 1024)) ' M ;'FROM dba_data_files f, dba_free_space sWHERE f.file_id = s.file_idAND s.block_id IN(SELECT MAX(block_id) FROM dba_free_space WHERE file_id = s.file_id)AND f.tablespace_name='&TABLESPACE_NAME' ORDER BY 3 DESC



Datafile free size and max resize size :


set verify off
set pages 10000 lines 132 trimspool on
column file_name format a65 word_wrapped
column smallest format 999,990 heading "SmallestSizePoss."
column currsize format 999,990 heading "CurrentSize"
column savings format 999,990 heading "Poss.Savings"
break on report
compute sum of savings on report
spool resize01.txt
column value new_val blksize head "Block Size" for a20
select value from v$parameter where name = 'db_block_size'/ select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savingsfrom dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) bwhere a.file_id = b.file_id(+);



Datafile free size in specified mount location :-

set verify off
set pages 10000 lines 132 trimspool on
column file_name format a65 word_wrapped
column smallest format 999,990 heading "SmallestSizePoss."
column currsize format 999,990 heading "CurrentSize"
column savings format 999,990 heading "Poss.Savings"
break on report
compute sum of savings on report
spool resize01.txt
column value new_val blksize head "Block Size" for a20select value from v$parameter where name = 'db_block_size'/select file_name, ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest, ceil( blocks*&&blksize/1024/1024) currsize, ceil( blocks*&&blksize/1024/1024) - ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savingsfrom dba_data_files a, ( select file_id, max(block_id+blocks-1) hwm from dba_extents group by file_id ) bwhere a.file_id = b.file_id(+) and FILE_NAME like '/u26/oradata%';



User quotas on all tablespaces :

col quota format a10
select username, tablespace_name, decode(max_bytes, -1, 'unlimited' , ceil(max_bytes / 1024 / 1024) 'M' ) "QUOTA"from dba_ts_quotaswhere tablespace_name not in ('TEMP')

/



Show all tablespaces used by a user

select tablespace_name
, ceil(sum(bytes) / 1024 / 1024) "MB"
from dba_extents
where owner like '&user_id'
group by tablespace_name
order by tablespace_name
/