As a DBA one of the routine tasks we should do is keeping an eye on the growth of the tablespaces in our databases. It is also important to check which tablespace has been using how much of the storage space allocated to it and how much is left for use out of it? The point here is to make sure that the database is not in danger of freezing from shortage of space as segments keep on increasing in size. I have posted the following scripts along with respective sample outputs.
The following script is used to display the tablespace growth over a period of time
set linesize 175
set pagesize 2000
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
DAYS TSNAME CUR_SIZE_GB USEDSIZE_GB
---------- ------------------- ----------- -----------
01-11-2012 EMPDATA 1119.87 1117.33
17-10-2014 EMPDATA 6145.67 6074.91
18-10-2014 EMPDATA 6145.67 6078.53
19-10-2014 EMPDATA 6145.67 6078.54
20-10-2014 EMPDATA 6145.67 6078.54
21-10-2014 EMPDATA 6145.67 6078.94
22-10-2014 EMPDATA 6145.67 6079.16
23-10-2014 EMPDATA 6145.67 6080.6
24-10-2014 EMPDATA 6145.67 6086.11
25-09-2014 EMPDATA 6129.67 6000.02
25-10-2014 EMPDATA 6145.67 6086.11
26-09-2014 EMPDATA 6133.67 6043.16
27-09-2014 EMPDATA 6133.67 6001.93
28-09-2014 EMPDATA 6133.67 6007.77
29-09-2014 EMPDATA 6133.67 6018.81
30-09-2014 EMPDATA 6145.67 6073.52
The following script checks tablespaces for percentage of used and free spaces.
SET LINESIZE 175
SET PAGESIZE 5000
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- - --------- ----------
UNDOTBS1 32767.9844 21782.875 66 34
TEMP 391 197 50 50
SYSAUX 8228 4052.25 49 51
SYSTEM 6530 2898.1875 44 56
The following script is used to display the tablespace growth over a period of time
set linesize 175
set pagesize 2000
SELECT TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY') days,
ts.tsname , max(round((tsu.tablespace_size* dt.block_size )/(1024*1024),2) ) cur_size_MB,
max(round((tsu.tablespace_usedsize* dt.block_size )/(1024*1024),2)) usedsize_MB
FROM DBA_HIST_TBSPC_SPACE_USAGE tsu, DBA_HIST_TABLESPACE_STAT ts, DBA_HIST_SNAPSHOT sp,
DBA_TABLESPACES dt
WHERE tsu.tablespace_id= ts.ts# AND tsu.snap_id = sp.snap_id
AND ts.tsname = dt.tablespace_name
GROUP BY TO_CHAR (sp.begin_interval_time,'DD-MM-YYYY'), ts.tsname
ORDER BY ts.tsname, days;
DAYS TSNAME CUR_SIZE_GB USEDSIZE_GB
---------- ------------------- ----------- -----------
01-11-2012 EMPDATA 1119.87 1117.33
17-10-2014 EMPDATA 6145.67 6074.91
18-10-2014 EMPDATA 6145.67 6078.53
19-10-2014 EMPDATA 6145.67 6078.54
20-10-2014 EMPDATA 6145.67 6078.54
21-10-2014 EMPDATA 6145.67 6078.94
22-10-2014 EMPDATA 6145.67 6079.16
23-10-2014 EMPDATA 6145.67 6080.6
24-10-2014 EMPDATA 6145.67 6086.11
25-09-2014 EMPDATA 6129.67 6000.02
25-10-2014 EMPDATA 6145.67 6086.11
26-09-2014 EMPDATA 6133.67 6043.16
27-09-2014 EMPDATA 6133.67 6001.93
28-09-2014 EMPDATA 6133.67 6007.77
29-09-2014 EMPDATA 6133.67 6018.81
30-09-2014 EMPDATA 6145.67 6073.52
The following script checks tablespaces for percentage of used and free spaces.
SET LINESIZE 175
SET PAGESIZE 5000
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)", SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024), SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- - --------- ----------
UNDOTBS1 32767.9844 21782.875 66 34
TEMP 391 197 50 50
SYSAUX 8228 4052.25 49 51
SYSTEM 6530 2898.1875 44 56
