Tablespace usage script1:
select a.tablespace_name,
round(sum(a.bytes)/1024/1024) Size_MB,
round(sum(b.bytes)/1024/1024) free_MB,
case when round(sum(a.maxbytes)/1024/1024) > 0 then round(sum(a.maxbytes)/1024/1024) else round(sum(a.bytes)/1024/1024) end Max_MB,
round(sum(a.bytes)/1024/1024) - round(sum(b.bytes)/1024/1024) Used_MB,
c.bigfile
from (select tablespace_name, sum(bytes) bytes, sum(maxbytes) maxbytes from dba_data_files group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) b,
dba_tablespaces c
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name = c.tablespace_name
group by a.tablespace_name, c.bigfile
union
select a.tablespace_name,
round(sum(a.bytes)/1024/1024) Size_MB,
round(sum(b.free_space)/1024/1024) free_MB,
case when round(max(a.maxbytes)/1024/1024) > 0 then round(max(a.maxbytes)/1024/1024) else round(sum(a.bytes)/1024/1024) end Max_MB,
round(sum(a.bytes)/1024/1024) - round(sum(b.free_space)/1024/1024) Used_MB,
c.bigfile
from (select tablespace_name, sum(bytes) bytes, sum(maxbytes) maxbytes from dba_temp_files group by tablespace_name) a,
DBA_TEMP_FREE_SPACE b,
dba_tablespaces c
where a.tablespace_name = b.tablespace_name (+)
and a.tablespace_name = c.tablespace_name
group by a.tablespace_name, c.bigfile ;
Tablespace usage script2:
clear breaks
set linesize 132
set pagesize 60
break on tablespace_name skip 1
col tablespace_name format a15
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM (bytes) TOTAL_SPACE,
ROUND( SUM (bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM (bytes) FREE_SPACE,
ROUND( SUM (bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
Tablespace usage script3:
set linesize 200
set feedback off
set echo off
set arraysize 25
set heading off
set pagesize 0
set verify off
set wrap on
set tab off
SELECT * FROM (
SELECT c.tablespace_name,
ROUND(a.bytes/1048576,2) MB_Allocated,
ROUND(b.bytes/1048576,2) MB_Free,
ROUND((a.bytes-b.bytes)/1048576,2) MB_Used,
ROUND(b.bytes/a.bytes * 100,2) tot_Pct_Free,
ROUND((a.bytes-b.bytes)/a.bytes,2) * 100 tot_Pct_Used
FROM (SELECT tablespace_name,
SUM(a.bytes) bytes
FROM sys.DBA_DATA_FILES a
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name,
NVL(SUM(b.bytes),0) bytes
FROM sys.DBA_DATA_FILES a,
sys.DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+)
AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
sys.DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+)
AND a.tablespace_name = c.tablespace_name
) WHERE tot_Pct_Used >=0
ORDER BY MB_Free;
Tablespace usage script4:
set lines 223 pages 999;
select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,
round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where USED_PERCENT>70
order by USED_PERCENT;
Tablespace usage script5:
select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,
round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where tablespace_name like '%TEMP%' order by USED_PERCENT;
Tablespace usage script6:
select tablespace_name,round((USED_SPACE*8)/1024)CURRENT_SIZE_MB,round((TABLESPACE_SIZE*8)/1024) MAX_SIZE_MB,
round(USED_PERCENT,2) USED_PERCENT from DBA_TABLESPACE_USAGE_METRICS where tablespace_name like '%DATA%' order by USED_PERCENT;
calculate how much undo tablespace we need exactly:
SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
(TO_NUMBER(e.value) * TO_NUMBER(f.value) *
g.undo_block_per_sec) / (1024*1024)
"NEEDED UNDO SIZE [MByte]"
FROM (
SELECT SUM(a.bytes) undo_size
FROM v$datafile a, v$tablespace b, dba_tablespaces c
WHERE c.contents = 'UNDO' AND c.status = 'ONLINE'
AND b.name = c.tablespace_name AND a.ts# = b.ts#
) d,
v$parameter e, v$parameter f,
(
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = 'undo_retention' AND f.name = 'db_block_size';
Solution:
Increasing UNDO tablespace size is not the solution for this.
Run Undo advisory
Increase UNDO Retension Period
Enable Undo Retention Guarantee
Run this Select Query during non-business hours where heavy transactions are not happening.
Tune the Query
Displays Space Usage for Each Datafile:
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
COLUMN "Tablespace Name" FORMAT A20
COLUMN "File Name" FORMAT A80
SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
Substr(df.file_name,1,80) "File Name",
Round(df.bytes/1024/1024,0) "Size (M)",
decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM DBA_DATA_FILES DF,
(SELECT file_id,
sum(bytes) used_bytes
FROM dba_extents
GROUP by file_id) E,
(SELECT sum(bytes) free_bytes,
file_id
FROM dba_free_space
GROUP BY file_id) f
WHERE e.file_id (+) = df.file_id
AND df.file_id = f.file_id (+)
ORDER BY df.tablespace_name,
df.file_name
/
col "Tablespace" for a22
col "Used MB" for 99,999,999
col "Free MB" for 99,999,999
col "Total MB" for 99,999,999
select df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
"Pct. Free"
from
(select tablespace_name,
round(sum(bytes) / 1048576) TotalSpace
from dba_data_files
group by tablespace_name) df,
(select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
from dba_segments
group by tablespace_name) tu
where df.tablespace_name = tu.tablespace_name ;
Tablespace Used MB Free MB Total MB Pct. Free
---------------------- ----------- ----------- ----------- ----------
SYSAUX 2,786 814 3,600 23
To check the space available in that particular tablespace:
select c.tablespace_name,
round(decode(d.autoext,NULL,decode(e.free,NULL,0,e.free)/(1024*1024),(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz)-f.totsz+decode(e.free,NULL,0,e.free))/ (1024*1024))) free_mb,
round(f.totsz/(1024*1024)) Total_mb,
round((f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))/(1024*1024)) maxsize_mb,
decode(d.autoext, NULL, 'NO','YES') ae,
round(decode(d.autoext,NULL,(f.totsz-decode(e.free,NULL,0,e.free))/f.totsz*100,(f.totsz-decode(e.free,NULL,0,e.free))/(f.maxsz+decode(g.nonauto_sz,NULL,0,g.nonauto_sz))*100)) as Used_pct
from dba_tablespaces c,
(select a.tablespace_name, count(b.autoextensible) autoext
from dba_tablespaces a, dba_data_files b
where a.tablespace_name = b.tablespace_name(+) and b.autoextensible = 'YES'
group by a.TABLESPACE_NAME
having count(b.autoextensible) > 0) d,
(select tablespace_name, sum(bytes) free
from dba_free_space
group by tablespace_name) e,
(select df1.tablespace_name, sum(df1.bytes) totsz, sum(df1.maxbytes) maxsz
from dba_data_files df1, dba_tablespaces ts1
where ts1.tablespace_name = df1.tablespace_name
group by df1.tablespace_name) f,
(select df2.tablespace_name, sum(df2.bytes) nonauto_sz
from dba_data_files df2, dba_tablespaces ts2
where ts2.tablespace_name = df2.tablespace_name and df2.autoextensible = 'NO'
group by df2.tablespace_name) g
where c.tablespace_name = d.tablespace_name(+) and
c.tablespace_name = e.tablespace_name(+) and
c.tablespace_name = f.tablespace_name and
c.tablespace_name = g.tablespace_name(+) and c.tablespace_name like '%DATA%' order by c.tablespace_name;
Script to check usage of each datafile in a tablespace:
set lines 200 pages 600
col file_name for a70
select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_data_files where tablespace_name='DATA';
Script to check space in temp file:
select file_name,autoextensible,ceil(maxbytes/(1024*1024)) maxsize_mb, ceil(bytes/(1024*1024)) cursize_mb from dba_temp_files where tablespace_name='TEMP';
check the free space available in the ASM Disk group
set lines 200
SELECT name,total_mb/1024 "TOTAL(GB)",(total_mb-free_mb)/1024 "USED(GB)",free_mb/1024 "FREE(GB)",(free_mb/total_mb)*100 "FREE PERCENTAGE" FROM v$asm_diskgroup;
Script to check space in particular asm diskgroup:
set lines 200
SELECT name,total_mb/1024 "TOTAL(GB)",(total_mb-free_mb)/1024 "USED(GB)",free_mb/1024 "FREE(GB)",(free_mb/total_mb)*100 "FREE PERCENTAGE"
FROM v$asm_diskgroup; where name='DATA';
Asm Rebalance query:
SET pagesize 299
SET lines 2999
SELECT GROUP_NUMBER,
OPERATION,
STATE,
POWER,
ACTUAL,
ACTUAL,
EST_MINUTES
FROM gv$asm_operation;
HOW TO ASM ADD DISK:
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk';
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk*' REBALANCE POWER 1024 WAIT;
ALTER DISKGROUP DATA ADD DISK'/devices/hdisk5' NAME DATA5,'/devices/hdisk6' NAME DATA6,'/devices/hdisk7' NAME DATA7,'/devices/hdisk8' NAME DATA8,
Drop disk in Asm diskgroup:
ALTER DISKGROUP data DROP DISK DATA_5;
ALTER DISKGROUP data UNDROP DISKS;
sqlplus '/ as sysasm'
SQL> alter diskgroup Mars_data mount;
Diskgroup altered.
Script to check space Diskgroup :
select distinct (NAME) "DISKGROUP",round(TOTAL_MB/1024) "Total Size in GB",round(FREE_MB/1024)
"Free Space in GB",round(FREE_MB/TOTAL_MB*100) "Pct Free",round((TOTAL_MB-FREE_MB)/TOTAL_MB*100) "Pct used" from gv$ASM_DISKGROUP where NAME='&DS';
Query to check FRA percentage used:
show parameter db_recovery_file_dest;
col name format a20
clear breaks
clear computes
select name
, round(space_limit / 1024 / 1024) size_mb
, round(space_used / 1024 / 1024) used_mb
, decode(nvl(space_used,0),0,0,round((space_used/space_limit) * 100)) pct_used
from v$recovery_file_dest
order by name
/
To find ASM Diskgroup and Disks status:
select GROUP_NUMBER,DISK_NUMBER,MOUNT_STATUS,HEADER_STATUS,MODE_STATUS,STATE,VOTING_FILE,name,path from v$asm_disk;
Query to find out which asm diskgroup occupied much space in database
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
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 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
/
Enjoy the work and do like me if you like this article!!!.
