How to get all tablespace name, allocated size, free size, capacity from one request?

Question

How can I get information about all table spaces in my database in the following format.

TABLESPACE_NAME | FILE_NAME | ALLOCATED_MB | FREE_MB | CAPACITY | 

Is there a way to save the daily size of the entire tablespace in another table automatically? In fact, I need to make a daily checklist for the table space. Therefore, I will not create an interface that automatically sends me data about the size of the table space based on this table, which daily stores information about the size of the table space.

Answer 1

Try the query below to get all tablespace data in oracle. Assuming 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; 

Answer 2

In Oracle, the link is below:

How to calculate table 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 see below

Get the size of all tables in the database

Answer 3

 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.

Answer 4

The above is helpful. Hope this can also be useful here:

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

Check the table information with another command, the command above 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';