Monday, August 12, 2013

Script to get the Tablespace size

[oracle@source ~]$ cat > /u01/tab_size.sql
select a.tablespace_name name,
       total_size,
       total_size-nvl(bytes_free,0) used,
       nvl(bytes_free,0) free,
       ((total_size-nvl(bytes_free,0))/total_size)*100 pct_used
       from ( select sum(bytes/(1024*1024*1024)) bytes_free,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes/(1024*1024*1024)) total_size,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name )b
where a.tablespace_name = b.tablespace_name
and a.tablespace_name like '&i';

[oracle@source ~]$ sqlplus "/as sysdba"

SQL> @/u01/tab_size.sql
Enter value for i: USERS
old  16: and a.tablespace_name like '&i'
new  16: and a.tablespace_name like 'USERS'

NAME                           TOTAL_SIZE       USED       FREE   PCT_USED
------------------------------ ---------- ---------- ---------- ----------
USERS                          .574951172 .001708984 .573242188 .297239915

SQL> l
  1  select a.tablespace_name name,
  2         total_size,
  3         total_size-nvl(bytes_free,0) used,
  4         nvl(bytes_free,0) free,
  5         ((total_size-nvl(bytes_free,0))/total_size)*100 pct_used
  6         from ( select sum(bytes/(1024*1024*1024)) bytes_free,
  7                tablespace_name
  8         from  sys.dba_free_space
  9         group by tablespace_name ) a,
 10       ( select sum(bytes/(1024*1024*1024)) total_size,
 11                tablespace_name
 12         from sys.dba_data_files
 13         group by tablespace_name )b
 14  where a.tablespace_name = b.tablespace_name
 15* and a.tablespace_name like '&i'

No comments:

Post a Comment