How to Get all table space name, Allocated size, Free size, Capacity from single query?

Question

How can i get the information about all tables space in my database in below format.

TABLESPACE_NAME | FILE_NAME |  ALLOCATED_MB  | FREE_MB |  CAPACITY |

Is there way to store daily size of all table-space in another table automatically ?. Actually i need to prepare checklist regarding table space on daily basis. So I wan't to create front end which email me the table space size details automatically on the basis of that table which store the information about table space size on daily basis..

possible duplicate of [How do I calculate tables size in Oracle](http://stackoverflow.com/questions/264914/how-do-i-calculate-tables-size-in-oracle) — bgs, Oct 08 '13 at 06:20
Don't be confused, I am looking for table spaces not for table size. — Regon, Oct 08 '13 at 06:27

score 22 · Accepted Answer · answered Oct 08 '13 at 06:23

Try the below query to get all tablespace details in oracle. Assuming that you have the necessary privileges to access dba tables.

SELECT a.file_name,
       substr(A.tablespace_name,1,14) tablespace_name,
       trunc(decode(A.autoextensible,'YES',A.MAXSIZE-A.bytes+b.free,'NO',b.free)/1024/1024) free_mb,
       trunc(a.bytes/1024/1024) allocated_mb,
       trunc(A.MAXSIZE/1024/1024) capacity,
       a.autoextensible ae
FROM (
     SELECT file_id, file_name,
            tablespace_name,
            autoextensible,
            bytes,
            decode(autoextensible,'YES',maxbytes,bytes) maxsize
     FROM   dba_data_files
     GROUP BY file_id, file_name,
              tablespace_name,
              autoextensible,
              bytes,
              decode(autoextensible,'YES',maxbytes,bytes)
     ) a,
     (SELECT file_id,
             tablespace_name,
             sum(bytes) free
      FROM   dba_free_space
      GROUP BY file_id,
               tablespace_name
      ) b
WHERE a.file_id=b.file_id(+)
AND A.tablespace_name=b.tablespace_name(+)
ORDER BY A.tablespace_name ASC;

With some modification this query works perfectly, but Is there any way to store these information in another table automatically rather running Insert Query on daily basis. — Regon, Oct 08 '13 at 06:33
Currently i don't think there is an option to store the information automatically. — Dba, Oct 08 '13 at 06:40
Instead, you can create a procedure to compare the difference in tablespace size and run on daily basis using `dbms_scheduler`. — Dba, Oct 08 '13 at 06:41

score 2 · Answer 2 · answered Feb 08 '17 at 07:53

Select a.tablespace_name,a.file_name,a.bytes/1024/1024 TABLESPACE_SIZE_MB,
Sum(b.bytes)/1024/1024 FREE_IN_MB from dba_free_space b,dba_data_files a
Where a.tablespace_name = b.tablespace_name
AND a.file_id = b.file_id
GROUP by a.tablespace_name, a.file_name,a.bytes/1024/1024
Order by a.tablespace_name, a.file_name;

you can run this query this may help.

score 2 · Answer 3 · edited May 19 '17 at 11:12

Above are useful. Hope this may also helpful here:

https://ora-data.blogspot.in/2016/12/how-to-find-details-of-tablespace.html

Check the Tablespace details with different command, above command may not work:

SQL>select round((bytes/1024)/1024,0) "Used Space(MB)",
round(total,0) "Allocated size(MB)",
round(max,0) "Maximum allowable(MB)",
round(max-(BYTES/1024)/1024,0) "Effective free(MB)",
round(((max-(BYTES/1024)/1024)/max)*100,2) "FREE(%)"
from SYS.SM$TS_USED,
(select sum((BYTES/1024)/1024) total, sum((decode(MAXBYTES,0,bytes,maxbytes)/1024)/1024) max
from dba_data_files where tablespace_name='&1') where tablespace_name='&1';

score 1 · Answer 4 · edited May 23 '17 at 12:18

In Oracle refer below link :

How do I calculate tables size in Oracle

https://forums.oracle.com/thread/2160787

COLUMN TABLE_NAME FORMAT A32
COLUMN OBJECT_NAME FORMAT A32
COLUMN OWNER FORMAT A10

SELECT
   owner, table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
 FROM dba_segments
 WHERE segment_type = 'TABLE'
 UNION ALL
 SELECT i.table_name, i.owner, s.bytes
 FROM dba_indexes i, dba_segments s
 WHERE s.segment_name = i.index_name
 AND   s.owner = i.owner
 AND   s.segment_type = 'INDEX'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.segment_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBSEGMENT'
 UNION ALL
 SELECT l.table_name, l.owner, s.bytes
 FROM dba_lobs l, dba_segments s
 WHERE s.segment_name = l.index_name
 AND   s.owner = l.owner
 AND   s.segment_type = 'LOBINDEX')
WHERE owner in UPPER('&owner')
GROUP BY table_name, owner
HAVING SUM(bytes)/1024/1024 > 10  /* Ignore really small tables */
ORDER BY SUM(bytes) desc
;

In SQL refer below

Get size of all tables in database

Thank You, This exactly i am looking for but i wan't to list all tablespaces not space of tables. — Regon, Oct 08 '13 at 06:25

How to Get all table space name, Allocated size, Free size, Capacity from single query?

4 Answers4