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
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