A reader, May 02, 2018 - 9:10 am UTC
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;
A reader, May 04, 2018 - 5:14 am UTC
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
May 07, 2018 - 4:36 am UTC
It is a reasonable approximation.
I say approximation because:
dba_hist_snapshot
dba_hist_seg_stat
are historical view of data, and
dba_objects dobj
dba_segments ds
are current views of data.
History and current are of course, not guaranteed to align