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