Im trying to find a way of estimating the daily rate of growth in bytes of all system and user tablespaces (specifically the tables and indexes contained in the tablespaces).
To my understanding, Oracle doesn't provide a mechanism that returns the rate of growth in bytes of a tablespace (please correct me if there's a query to get this) so my approach is to calculate the size of the tablespaces (tables and indexes) on a daily basis and then subtract the size in bytes of one day to another, to estimate the daily growth
To do this I'm using the following query, which gets the tablespace_name, the sum in bytes of the tablespace tables and indexes (both user and system ones), and the time the query was executed
SELECT
tablespace_name,
sum(bytes) "BYTES",
current_timestamp
FROM (
SELECT
tablespace_name,
sum(bytes) "BYTES",
current_timestamp
FROM user_segments
WHERE segment_type = 'INDEX' OR segment_type = 'TABLE'
GROUP BY tablespace_name, current_timestamp
UNION ALL
SELECT
tablespace_name,
sum(bytes) "BYTES",
current_timestamp
FROM dba_segments
WHERE segment_type = 'INDEX' OR segment_type = 'TABLE'
GROUP BY tablespace_name, current_timestamp)
GROUP BY tablespace_name, current_timestamp;
Is this query correct to what I'm looking for? Are this really the sum of the bytes of the tables and indexes by tablespace or am I missing something?
Is there any way to estimate the growth in bytes of the tablespaces instead of subtracting the sum of bytes on a daily basis?
Thanks in advance,
I can make use of dba_hist_tbspc_space_usage which is snapshots in time of tablespace usage
SQL> desc dba_hist_tbspc_space_usage
Name Null? Type
----------------------------------------------------------------------- -------- -----------------------------------
SNAP_ID NUMBER
DBID NOT NULL NUMBER
TABLESPACE_ID NUMBER
TABLESPACE_SIZE NUMBER
TABLESPACE_MAXSIZE NUMBER
TABLESPACE_USEDSIZE NUMBER
RTIME VARCHAR2(25)
CON_DBID NUMBER
CON_ID NUMBER
We dont have tablespace *name* in there, but we can get that with v$tablespace
SQL> desc v$tablespace
Name Null? Type
----------------------------------------------------------------------- -------- -----------------------------------
TS# NUMBER
NAME VARCHAR2(30)
INCLUDED_IN_DATABASE_BACKUP VARCHAR2(3)
BIGFILE VARCHAR2(3)
FLASHBACK_ON VARCHAR2(3)
ENCRYPT_IN_BACKUP VARCHAR2(3)
CON_ID NUMBER
And to make those snap_id's to calendar dates, we can use dba_hist_snapshot
SQL> desc dba_hist_snapshot
Name Null? Type
----------------------------------------------------------------------- -------- -----------------------------------
SNAP_ID NOT NULL NUMBER
DBID NOT NULL NUMBER
INSTANCE_NUMBER NOT NULL NUMBER
STARTUP_TIME NOT NULL TIMESTAMP(3)
BEGIN_INTERVAL_TIME NOT NULL TIMESTAMP(3)
END_INTERVAL_TIME NOT NULL TIMESTAMP(3)
FLUSH_ELAPSED INTERVAL DAY(5) TO SECOND(1)
SNAP_LEVEL NUMBER
ERROR_COUNT NUMBER
SNAP_FLAG NUMBER
SNAP_TIMEZONE INTERVAL DAY(0) TO SECOND(0)
CON_ID NUMBER
A quick check of the docs tells us that the figures in dba_hist_tbspc_space_usage are in blocks, so we'll need to bring in dba_tablespaces as well get to the block size (or if you're database is all on 8k you can omit that) but other than that we can put together a query to suit our needs fairly quickly
So something like this should get you started
select
dhs.begin_interval_time,
dt.tablespace_name,
trunc(dhtsu.tablespace_size*dt.block_size/1024/1024/1024) gb,
trunc(dhtsu.tablespace_usedsize*dt.block_size/1024/1024/1024) gb_used
from
dba_hist_tbspc_space_usage dhtsu,
v$tablespace vts,
dba_tablespaces dt,
dba_hist_snapshot dhs
where dhtsu.snap_id = dhs.snap_id
and dhtsu.tablespace_id = vts.ts#
and vts.name = dt.tablespace_name
order by 2,1;