Often the support need to know which tables are growing fast and which are stable. Out of the Box, Oracle don’t provide such a statistic on object level. Classic monitoring tools collect this information only on the database-, tablespace- and datafile-level, but not on object level.
A small script helps you to answer this question in minutes.
Create a table to store the data
CREATE TABLE TABLE_SIZE ( ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 CACHE 20 CYCLE ORDER) NOT NULL, STAMP DATE DEFAULT TRUNC(sysdate) NOT NULL, OWNER VARCHAR2(30 CHAR) NOT NULL, TABLE_NAME VARCHAR2(30 CHAR) NOT NULL, SIZE_GB INTEGER NOT NULL, ROW_COUNT INTEGER NOT NULL ); ALTER TABLE TABLE_SIZE ADD ( CONSTRAINT TABLE_SIZE_PK PRIMARY KEY (ID) ENABLE VALIDATE);
Create a SQL-Job to gather the data on weekly basis
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
JOB_NAME => 'GATHER_TABLE_SIZES'
,JOB_TYPE => 'PLSQL_BLOCK'
,JOB_ACTION =>
q'{BEGIN
INSERT INTO DBM_ADMIN.TABLE_SIZE (STAMP, OWNER, TABLE_NAME, SIZE_GB, ROW_COUNT)
SELECT A.*, B.NUM_ROWS from
(SELECT TRUNC(SYSDATE) as STAMP, OWNER, TABLE_NAME, ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) GB
FROM (SELECT SEGMENT_NAME TABLE_NAME, OWNER, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE IN
('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT I.TABLE_NAME, I.OWNER, S.BYTES
FROM DBA_INDEXES I, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME = I.INDEX_NAME
AND S.OWNER = I.OWNER
AND S.SEGMENT_TYPE = 'INDEX'
UNION ALL
SELECT L.TABLE_NAME, L.OWNER, S.BYTES
FROM DBA_LOBS L, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME = L.SEGMENT_NAME
AND S.OWNER = L.OWNER
AND S.SEGMENT_TYPE = 'LOBSEGMENT'
UNION ALL
SELECT L.TABLE_NAME, L.OWNER, S.BYTES
FROM DBA_LOBS L, DBA_SEGMENTS S
WHERE S.SEGMENT_NAME = L.INDEX_NAME
AND S.OWNER = L.OWNER
AND S.SEGMENT_TYPE = 'LOBINDEX')
GROUP BY TABLE_NAME, OWNER
HAVING SUM (BYTES) / 1024 / 1024 > 10) A
LEFT JOIN dba_tables B on A.owner=B.owner and A.Table_name=b.table_name; END; COMMIT;}'
,START_DATE => SYSDATE
,REPEAT_INTERVAL => 'FREQ=WEEKLY'
,ENABLED => TRUE
,COMMENTS => 'Gather table size');
END;
/
gabrielkeusen.com 