Oracle DBA 4 Dummies

Script to check all Tablespaces Usage

select df.tablespace_name "TABLESPACE",df.totalspace "TOTAL_SIZE_MB",totalusedspace "USED_SIZE_MB",(df.totalspace - tu.totalusedspace) "FREE_SIZE_MB",round(100 * ( (tu.totalusedspace)/ df.totalspace))"USED_PCT" from(select tablespace_name,round(sum(bytes) / 1048576) TotalSpacefrom dba_data_filesgroup by tablespace_name) df,(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_namefrom dba_segmentsgroup by tablespace_name) tuwhere df.tablespace_name = tu.tablespace_nameunionSELECT a.tablespace_name,ROUND((c.total_blocks*b.block_size)/1024/1024,2)"TOTAL_SIZE_MB",ROUND((a.used_blocks*b.block_size)/1024/1024,2) "USED_SIZE_MB",ROUND(((c.total_blocks-a.used_blocks)*b.block_size)/1024/1024,2) "FREE_SIZE_MB",ROUND((a.used_blocks/c.total_blocks)*100,2) "USED_PCT"FROM v$sort_segment a,dba_tablespaces b,(SELECT tablespace_name,SUM(blocks)total_blocks FROM dba_temp_files GROUP by tablespace_name) cWHERE a.tablespace_name=b.tablespace_name AND a.tablespace_name=c.tablespace_name order by 5 desc;

Temporary Space used by each Session

SELECT b.tablespace, ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size, a.inst_id as Instance, a.sid||','||a.serial# AS sid_serial, NVL(a.username, '(oracle)') AS username, a.program, a.status, a.sql_idFROM gv$session a, gv$sort_usage b, gv$parameter pWHERE p.name = 'db_block_size'AND a.saddr = b.session_addrAND a.inst_id=b.inst_idAND a.inst_id=p.inst_idORDER BY b.tablespace, b.blocks desc/

Check eligible Datafiles to shrink

set linesize 1000 pagesize 0 feedback off trimspool onwithhwm as ( -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn),hwmts as ( -- join ts# with tablespace_name select name tablespace_name,relative_fno,hwm_blocks from hwm join v$tablespace using(ts#)),hwmdf as ( -- join with datafiles, put 5M minimum for datafiles with no extents select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes from hwmts right join dba_data_files using(tablespace_name,relative_fno))selectcase when autoextensible='YES' and maxbytes>=bytesthen -- we generate resize statements only if autoextensible can grow back to current size '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ ' ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'else -- generate only a comment when autoextensible is off '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999) ||'M from '||to_char(ceil(bytes/1024/1024),999999) ||'M after setting autoextensible maxsize higher than current size for file ' || file_name||' */'end SQLfrom hwmdfwherebytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimedorder by bytes-hwm_bytes desc/