Mir Sayeed Hassan – Oracle Blog

Script to check the tablespace size used in Oracle database

[oracle@testdb scripts]$ pwd
 /home/oracle/scripts

[oracle@testdb scripts]$ cat checktbs.sql
 set linesize 180
 set pagesize 30
select
 fs.tablespace_name "Tablespace",
 (df.totalspace - fs.freespace) "Used MB",
 fs.freespace "Free MB",
 df.totalspace "Total MB",
 round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
 from
 (select
 tablespace_name,
 round(sum(bytes) / 1048576) TotalSpace
 from
 dba_data_files
 group by
 tablespace_name
 ) df,
 (select
 tablespace_name,
 round(sum(bytes) / 1048576) FreeSpace
 from
 dba_free_space
 group by
 tablespace_name
 ) fs
 where
 df.tablespace_name = fs.tablespace_name;

set linesize 180
 set pagesize 30
 col filename format a50;

select
 a.tablespace_name,
 round(a.bytes/1073741824,3) as used_gb,
 round(b.bytes/1073741824,3) free_gb,
 round(b.bytes/1073741824,3) max_possibale_gb,
 a.exsize exten_size_mb,
 a.cnt data_files,
 c.bigfile "is bigfile",
 round(((a.bytes-b.bytes)/a.bytes)*100,2) "use%",
 a.fname filename
 from
 (select tablespace_name,sum(bytes) bytes,count(*) cnt,max(file_name) fname,sum(maxbytes) maxsize,max(relative_fno) exsize 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 c.tablespace_name=b.tablespace_name
 order by "use%" desc;
exit;

[oracle@testdb scripts]$ !sq
 sqlplus

sys@TESTDB> @/home/oracle/scripts/checktbs.sql;

Tablespace Used MB Free MB Total MB Pct. Free
 ------------------------------ ---------- ---------- ---------- ----------
 GOLDENGATE 1 99 100 99
 SYSAUX     6490 326 6816 5
 UNDOTBS1 7 193 200 97
 USERS 310 290 600 48
 UNDONEW 156 44 200 22
 SYSTEM 34495 1805 36300 5
 EXAMPLE 1 49 50 98
 USERS_NEW 1 199 200 100

8 rows selected.

TABLESPACE_NAME USED_GB FREE_GB MAX_POSSIBALE_GB EXTEN_SIZE_MB DATA_FILES is use% FILENAME
 ------------------------------ ---------- ---------- ---------------- ------------- ---------- --- ---------- --------------------------------------------------
 SYSAUX 6.656 .318 .318 2 1 NO 95.22 /u01/app/oracle/oradata/prim/sysaux01.dbf
 SYSTEM 35.449 1.763 1.763 9 2 NO 95.03 /u01/app/oracle/oradata/prim/system02.dbf
 UNDONEW .195 .043 .043 6 1 NO 78.13 /u01/app/oracle/oradata/prim/undonew.dbf
 USERS .586 .283 .283 12 3 NO 51.65 /u01/app/oracle/oradata/prim/users02.dbf
 UNDOTBS1 .195 .189 .189 8 1 NO 3.38 /u01/app/oracle/oradata/prim/UNDOTBS1.dbf
 EXAMPLE .049 .048 .048 5 1 NO 2.13 /u01/app/oracle/oradata/prim/example01.dbf
 GOLDENGATE .098 .097 .097 10 1 NO 1.13 /u01/app/oracle/oradata/prim/goldengate01.dbf
 USERS_NEW .195 .194 .194 3 1 NO .56 /u01/app/oracle/oradata/prim/usersnew.dbf

8 rows selected.

=======Hence verified & tested========