Skip to Main Content
  • Questions
  • how to get regular updates on database space left and database used space in my table

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, raghuveer.

Asked: October 19, 2016 - 5:23 pm UTC

Last updated: October 21, 2016 - 1:37 am UTC

Version: 11gr2

Viewed 1000+ times

You Asked

how to get regular updates on database space left and database used space in my table .

i have created a table with 3 columns , dbspacetotal & dbspaceused,dbspaceremaining.

how to get data into these columns when i insert update or delete in my database tables.

any stored procedure or sub query etc

regards

and Connor said...

Here's an *example* query for usage and free space. There's plenty out there on the web as well

SQL> select d.tablespace_name, lpad(round(d.tot_size/1024/1024)||'m',10) tot_size, lpad(round(f.tot_free/1024/1024)||'m',10) tot_free, round(100-100*tot_free/tot_size) pct_used
  2  from
  3  ( select tablespace_name, sum(tot_free) tot_free
  4    from
  5    ( select tablespace_name, sum(bytes) tot_free
  6      from dba_free_space
  7      group by tablespace_name
  8      union all
  9      select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes)-bytes else 0 end )
 10      from dba_data_files
 11      group by tablespace_name
 12      union all
 13      select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end )
 14      from dba_temp_files
 15      group by tablespace_name
 16      union all
 17      select s.tablespace, -1*alloc*t.block_size
 18      from ( select /*+ NO_MERGE */ tablespace, sum(blocks) alloc
 19             from   v$sort_usage
 20             group by tablespace) s,
 21            dba_tablespaces t
 22      where t.tablespace_name = s.tablespace
 23    )
 24    group by tablespace_name
 25  ) f,
 26  ( select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end ) tot_size
 27    from dba_data_files
 28    group by tablespace_name
 29    union all
 30    select tablespace_name, sum(case when autoextensible = 'YES' then greatest(maxbytes,bytes) else bytes end )
 31    from dba_temp_files
 32    group by tablespace_name
 33  ) d
 34  where f.tablespace_name(+) = d.tablespace_name
 35  order by pct_used;

TABLESPACE_NAME      TOT_SIZE   TOT_FREE               PCT_USED
-------------------- ---------- -------------------- ----------
TEMP                     32768m     32766m                    0
UNDOTS                   12000m     11940m                    1
USERS                    32768m     32335m                    1
EXAMPLES                 32768m     31979m                    2
SYSTEM                   32768m     31753m                    3
SYSAUX                   32768m     31101m                    5
SOE                      32000m     29536m                    8
LARGE_TS                 32768m     29042m                   11
ASKTOM                   32768m     27294m                   17

9 rows selected.



So if you want to run that regularly, all you need do is use the scheduler to regular store those results into a table, ie, something like:

insert into MY_SPACE_MONITOR
select sysdate, ...
[rest of query above]

Here's an example of how to use the scheduler

https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9526796000346607949



Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

For TABLE or for TABLESPACE ?

J. Laurindo Chiappa, October 20, 2016 - 11:46 am UTC

Connor, the requester mentioned "space left and database used space in my ** table **" , not Tablespace : a query using DBA_SEGMENTS, DBA_EXTENTS and (maybe) DBMS_SPACE would not be better for this ?

Regards,

J. Laurindo Chiappa
Connor McDonald
October 21, 2016 - 1:37 am UTC

Yes, but the text is:

"how to get regular updates on database space left and database used space in my table. i have created a table with 3 columns , dbspacetotal & dbspaceused,dbspaceremaining."

So from that I am inferring the 'table' he is referring to is the TARGET for the information.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library