Historisierung der Tablespace-Belegung

Ein kleines Skript zur Historisierung der Tablespace-Ausnutzung

REM	Zuerst die notwendigen Grants vergeben -----------------------------------------------
REM (natuerlich als SYS)

GRANT SELECT ON SYS.DBA_TABLESPACE_USAGE_METRICS TO SYSTEM;
GRANT SELECT ON SYS.DBA_TABLESPACES TO SYSTEM;

REM	Dann die Tabelle fuer die Historierung anlegen ---------------------------------------

DROP TABLE SYSTEM.HIST$TABLESPACE_USAGE_METRICS;

CREATE TABLE SYSTEM.HIST$TABLESPACE_USAGE_METRICS
(
COLLECT_DATE		DATE			DEFAULT SYSDATE NOT NULL ,
TABLESPACE_NAME		VARCHAR2(30)	NOT NULL,
USED_SPACE_MB		NUMBER			NOT NULL
)
tablespace USERS;


REM Last but not least fuer ein automatisches Sammeln der Daten sorgen -------------------
REM 1. Die Prozedur 
CREATE OR REPLACE PROCEDURE 	SYSTEM.INS_HIST_TS_USAGE_METRICS
IS
BEGIN
INSERT INTO SYSTEM.HIST$TABLESPACE_USAGE_METRICS
(
COLLECT_DATE,
TABLESPACE_NAME,
USED_SPACE_MB
)
SELECT 
SYSDATE,
TS.TABLESPACE_NAME,
TRUNC(M.USED_SPACE*TS.BLOCK_SIZE/1024/1024)
FROM DBA_TABLESPACE_USAGE_METRICS M,DBA_TABLESPACES ts
where ts.tablespace_name=m.tablespace_name;

COMMIT;
END;
/

REM 	2. Der Job für den Scheduler (1xtaeglich)

BEGIN
sys.dbms_scheduler.create_job(
job_name => '"SYSTEM"."COLLECT_TS_USAGE_METRICS"',
job_type => 'STORED_PROCEDURE',
job_action => '"SYSTEM"."INS_HIST_TS_USAGE_METRICS"',
repeat_interval => 'FREQ=DAILY;INTERVAL=1',
start_date => to_timestamp_tz('2013-08-14 07:00:00 Europe/Vienna', 'YYYY-MM-DD HH24:MI:SS TZR'),
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'collect TS-usage history every day',
auto_drop => FALSE,
enabled => TRUE);
END;
/


REM --------------------------------------------------------------------------------------
REM	 und wenn die Daten nicht mehr gesammelt werden sollen, nicht vergessen:
REM	 1. Den Job zu loeschen
REM  2. Die Prozedur zu loeschen
REM  3. Tabelle zu loeschen
REM --------------------------------------------------------------------------------------

Disclaimer: Please test the script before you use it, there may be errors 🙂