3 -- This will create a schema to provide collectd with the required permissions
4 -- and space for statistic data.
5 -- The idea is to store the output of some expensive queries in static tables
6 -- and fill these tables with dbms_scheduler jobs as often as necessary.
7 -- collectd will then just read from the static tables. This will reduces the
8 -- chance that your system will be killed by excessive monitoring queries and
9 -- gives the dba control on the interval the information provided to collectd
10 -- will be refreshed. You have to create a dbms_scheduler job for each of the
11 -- schemas you what to monitor for object-space-usage. See the example below.
15 -- make shure you have:
16 -- write permission in $PWD
17 -- you have GID of oracle software owner
20 -- DB is up an running in RW mode
22 -- sqlplus /nolog @ create_collectd-schema.dll
24 spool create_collectd-schema.log
27 -- Create user, tablespace and permissions
29 CREATE TABLESPACE "COLLECTD-TBS"
35 EXTENT MANAGEMENT LOCAL
36 SEGMENT SPACE MANAGEMENT AUTO
39 CREATE ROLE "CREATE_COLLECTD_SCHEMA" NOT IDENTIFIED;
40 GRANT CREATE JOB TO "CREATE_COLLECTD_SCHEMA";
41 GRANT CREATE SEQUENCE TO "CREATE_COLLECTD_SCHEMA";
42 GRANT CREATE SYNONYM TO "CREATE_COLLECTD_SCHEMA";
43 GRANT CREATE TABLE TO "CREATE_COLLECTD_SCHEMA";
44 GRANT CREATE VIEW TO "CREATE_COLLECTD_SCHEMA";
45 GRANT CREATE PROCEDURE TO "CREATE_COLLECTD_SCHEMA";
47 CREATE USER "COLLECTDU"
49 IDENTIFIED BY "Change_me-1st"
51 DEFAULT TABLESPACE "COLLECTD-TBS"
52 TEMPORARY TABLESPACE "TEMP"
53 QUOTA UNLIMITED ON "COLLECTD-TBS"
56 GRANT "CONNECT" TO "COLLECTDU";
57 GRANT "SELECT_CATALOG_ROLE" TO "COLLECTDU";
58 GRANT "CREATE_COLLECTD_SCHEMA" TO "COLLECTDU";
59 GRANT analyze any TO "COLLECTDU";
60 GRANT select on dba_tables TO "COLLECTDU";
61 GRANT select on dba_lobs TO "COLLECTDU";
62 GRANT select on dba_indexes TO "COLLECTDU";
63 GRANT select on dba_segments TO "COLLECTDU";
64 GRANT select on dba_tab_columns TO "COLLECTDU";
65 GRANT select on dba_free_space TO "COLLECTDU";
66 GRANT select on dba_data_files TO "COLLECTDU";
67 -- Create tables and indexes
69 alter session set current_schema=collectdu;
71 create table c_tbs_usage (
72 tablespace_name varchar2(30),
75 CONSTRAINT "C_TBS_USAGE_UK1" UNIQUE ("TABLESPACE_NAME") USING INDEX
76 TABLESPACE "COLLECTD-TBS" ENABLE)
77 TABLESPACE "COLLECTD-TBS";
79 CREATE TABLE "COLLECTDU"."C_TBL_SIZE" (
80 "OWNER" VARCHAR2(30 BYTE),
81 "TABLE_NAME" VARCHAR2(30 BYTE),
83 CONSTRAINT "C_TBL_SIZE_UK1" UNIQUE ("OWNER", "TABLE_NAME")
84 USING INDEX TABLESPACE "COLLECTD-TBS" ENABLE)
85 TABLESPACE "COLLECTD-TBS" ;
88 create or replace PROCEDURE get_object_size(owner IN VARCHAR2) AS
90 v_owner VARCHAR2(30) := owner;
93 l_total_blocks NUMBER;
95 l_unused_blocks NUMBER;
96 l_unused_bytes NUMBER;
97 l_lastusedextfileid NUMBER;
98 l_lastusedextblockid NUMBER;
99 l_last_used_block NUMBER;
105 WHERE owner = v_owner;
112 WHERE owner = v_owner;
119 WHERE owner = v_owner;
123 DELETE FROM c_tbl_size
124 WHERE owner = v_owner;
130 dbms_space.unused_space(segment_owner => r_tbl.owner, segment_name => r_tbl.TABLE_NAME, segment_type => 'TABLE', total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_lastusedextfileid, last_used_extent_block_id => l_lastusedextblockid, last_used_block => l_last_used_block);
134 DBMS_OUTPUT.PUT_LINE('tbl_name: ' || r_tbl.TABLE_NAME);
138 VALUES(r_tbl.owner, r_tbl.TABLE_NAME, l_total_bytes -l_unused_bytes);
146 dbms_space.unused_space(segment_owner => r_idx.owner, segment_name => r_idx.index_name, segment_type => 'INDEX', total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_lastusedextfileid, last_used_extent_block_id => l_lastusedextblockid, last_used_block => l_last_used_block);
150 DBMS_OUTPUT.PUT_LINE('idx_name: ' || r_idx.index_name);
154 SET bytes = bytes + l_total_bytes -l_unused_bytes
155 WHERE owner = r_idx.owner
156 AND TABLE_NAME = r_idx.TABLE_NAME;
164 dbms_space.unused_space(segment_owner => r_lob.owner, segment_name => r_lob.segment_name, segment_type => 'LOB', total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, last_used_extent_file_id => l_lastusedextfileid, last_used_extent_block_id => l_lastusedextblockid, last_used_block => l_last_used_block);
168 DBMS_OUTPUT.PUT_LINE('lob_name: ' || r_lob.segment_name);
172 SET bytes = bytes + l_total_bytes -l_unused_bytes
173 WHERE owner = r_lob.owner
174 AND TABLE_NAME = r_lob.TABLE_NAME;
182 create or replace PROCEDURE get_tbs_size AS
185 execute immediate 'truncate table c_tbs_usage';
187 insert into c_tbs_usage (
188 select df.tablespace_name as tablespace_name,
192 (df.maxbytes-(df.bytes-sum(fs.bytes)))) as bytes_free,
195 round((df.bytes-sum(fs.bytes))),
196 round(df.maxbytes-(df.maxbytes-(df.bytes-sum(fs.bytes))))) as bytes_used
197 from dba_free_space fs inner join
201 sum(decode(maxbytes,0,bytes,maxbytes)) maxbytes
203 group by tablespace_name ) df
204 on fs.tablespace_name = df.tablespace_name
205 group by df.tablespace_name,df.maxbytes,df.bytes);
213 sys.dbms_scheduler.create_job(
214 job_name => '"COLLECTDU"."C_TBSSIZE_JOB"',
215 job_type => 'PLSQL_BLOCK',
219 repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
220 start_date => systimestamp at time zone 'Europe/Berlin',
221 job_class => '"DEFAULT_JOB_CLASS"',
228 sys.dbms_scheduler.create_job(
229 job_name => '"COLLECTDU"."C_TBLSIZE_COLLECTDU_JOB"',
230 job_type => 'PLSQL_BLOCK',
232 get_object_size( owner => ''COLLECTDU'' );
234 repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
235 start_date => systimestamp at time zone 'Europe/Berlin',
236 job_class => '"DEFAULT_JOB_CLASS"',