I have created these two tables and launch every day a crontab to insert the number of rows from stats tables.

CREATE TABLE HIST_OWNER_TABLES_ROWS
( FECHA DATE NOT NULL,
OWNER VARCHAR2(30 CHAR) NOT NULL,
TABLE_NAME VARCHAR2(30 CHAR) NOT NULL,
NUM_ROWS NUMBER NOT NULL,
LAST_ANALYZED DATE,
CONSTRAINT HIST_OWNER_TABLES_ROWS_PK PRIMARY KEY (FECHA, OWNER, TABLE_NAME));

CREATE TABLE HIST_OWNER_SEGMENTS_BYTES
( FECHA DATE NOT NULL,
OWNER VARCHAR2(30 CHAR) NOT NULL,
TABLE_NAME VARCHAR2(30 CHAR) NOT NULL,
BYTES NUMBER NOT NULL ,
CONSTRAINT HIST_OWNER_SEGMENT_BYTES_PK PRIMARY KEY (FECHA, OWNER, TABLE_NAME));

---------------------------------------

insert into hist_owner_tables_rows
select sysdate,owner,table_name,decode(num_rows,null,0,num_rows),LAST_ANALYZED
from dba_tables
where last_analyzed is not null;


commit;

insert into hist_owner_segments_bytes
select sysdate,owner, segment_name,sum(bytes) from dba_segments
where segment_type='TABLE'
group by owner, segment_name;

commit;

Is following script is right or wrong?

SELECT ds.tablespace_name
,ds.segment_name
,dobj.object_type
,ROUND(SUM(dhss.space_used_delta) / 1024 / 1024 ,2) "Growth (MB)"
FROM dba_hist_snapshot snpshot
,dba_hist_seg_stat dhss
,dba_objects dobj
,dba_segments ds
WHERE begin_interval_time > TRUNC(SYSDATE)-360
AND snpshot.snap_id = dhss.snap_id
AND dobj.object_id = dhss.obj#
AND dobj.owner = ds.owner
AND dobj.object_name = ds.segment_name
AND ds.owner ='&owner' AND ds.SEGMENT_NAME IN ('&table_name') AND dobj.object_type='TABLE'
GROUP BY ds.tablespace_name,ds.segment_name,dobj.object_type
ORDER BY 3 ASC

Hi Connor,

I was using a similar query to look for rapidly growing objects in a client Oracle 19 database, and I was getting some implausible results. The PK index of one table was growing by up to 3 GB between each half-hourly snapshot according to the space_used_total value, and had a current size of 250 GB. This was for a table that only held transient rows used to manage concurrent processes, where rows were frequently INSERTED and DELETED. On average, the table was completely empty, as was the index. Your query is similar to many other examples I could find, including those on Oracle's online documentation site.

Finally, after many days of searching and head scratching, I found this post: https://www.josip-pojatina.com/en/calculating-segment-growth-and-related-issues/?unapproved=15337&moderation-hash=e986537de16fd750aead6dcfee0506d4#comment-15337

In it, the author describes the relationship between dba_hist_seg_stat.obj# and dba_hist_seg_stat.dataobj#. Initially, they are the same, but over time, as objects are ALTERED or otherwise modified, the value of dataobj# can change. As a result, when JOINing dba_hist_seg_stat to dba_objects, one should use dba_objects.object_id = dba_hist_seg_stat.dataobj#, at least if you are looking for object growth over a recent time period and not from birth.

Once I changed my query from

FROM
    dba_hist_seg_stat dhss
JOIN
    dba_hist_snapshot sn ON dhss.snap_id = sn.snap_id
JOIN
    dba_objects dobj ON dobj.object_id = dhss.obj#

to

FROM
    dba_hist_seg_stat dhss
JOIN
    dba_hist_snapshot sn ON dhss.snap_id = sn.snap_id
JOIN
    dba_objects dobj ON dobj.object_id = (SELECT MAX(dataobj#) FROM dba_hist_seg_stat ss WHERE ss. obj# = dhss.obj#)

per Josip Pojatina's suggestion, the results were much more reasonable (I was only looking at object growth over a one week period).

Thanks,

Patrick