e4dc54f2dab4485fb178a31161e0da93207a4b32
1 -- Description
2 --------------
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.
12 --
13 -- Requirements
14 ---------------
15 -- make sure you have:
16 -- write permission in $PWD
17 -- you have GID of oracle software owner
18 -- set $ORACLE_HOME
19 -- set $ORACLE_SID
20 -- DB is up an running in RW mode
21 -- execute like this:
22 -- sqlplus /nolog @ create_collectd-schema.dll
24 spool create_collectd-schema.log
25 connect / as sysdba
27 -- Create user, tablespace and permissions
29 CREATE TABLESPACE "COLLECTD-TBS"
30 DATAFILE SIZE 30M
31 AUTOEXTEND ON
32 NEXT 10M
33 MAXSIZE 300M
34 LOGGING
35 EXTENT MANAGEMENT LOCAL
36 SEGMENT SPACE MANAGEMENT AUTO
37 DEFAULT NOCOMPRESS;
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"
48 PROFILE "DEFAULT"
49 IDENTIFIED BY "Change_me-1st"
50 PASSWORD EXPIRE
51 DEFAULT TABLESPACE "COLLECTD-TBS"
52 TEMPORARY TABLESPACE "TEMP"
53 QUOTA UNLIMITED ON "COLLECTD-TBS"
54 ACCOUNT UNLOCK;
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),
73 bytes_free number,
74 bytes_used number,
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),
82 "BYTES" NUMBER,
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;
92 l_free_blks NUMBER;
93 l_total_blocks NUMBER;
94 l_total_bytes 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;
101 CURSOR cur_tbl IS
102 SELECT owner,
103 TABLE_NAME
104 FROM dba_tables
105 WHERE owner = v_owner;
107 CURSOR cur_idx IS
108 SELECT owner,
109 index_name,
110 TABLE_NAME
111 FROM dba_indexes
112 WHERE owner = v_owner;
114 CURSOR cur_lob IS
115 SELECT owner,
116 segment_name,
117 TABLE_NAME
118 FROM dba_lobs
119 WHERE owner = v_owner;
121 BEGIN
123 DELETE FROM c_tbl_size
124 WHERE owner = v_owner;
125 COMMIT;
127 FOR r_tbl IN cur_tbl
128 LOOP
129 BEGIN
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);
132 EXCEPTION
133 WHEN others THEN
134 DBMS_OUTPUT.PUT_LINE('tbl_name: ' || r_tbl.TABLE_NAME);
135 END;
136 INSERT
137 INTO c_tbl_size
138 VALUES(r_tbl.owner, r_tbl.TABLE_NAME, l_total_bytes -l_unused_bytes);
139 END LOOP;
141 COMMIT;
143 FOR r_idx IN cur_idx
144 LOOP
145 BEGIN
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);
148 EXCEPTION
149 WHEN others THEN
150 DBMS_OUTPUT.PUT_LINE('idx_name: ' || r_idx.index_name);
151 END;
153 UPDATE c_tbl_size
154 SET bytes = bytes + l_total_bytes -l_unused_bytes
155 WHERE owner = r_idx.owner
156 AND TABLE_NAME = r_idx.TABLE_NAME;
157 END LOOP;
159 COMMIT;
161 FOR r_lob IN cur_lob
162 LOOP
163 BEGIN
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);
166 EXCEPTION
167 WHEN others THEN
168 DBMS_OUTPUT.PUT_LINE('lob_name: ' || r_lob.segment_name);
169 END;
171 UPDATE c_tbl_size
172 SET bytes = bytes + l_total_bytes -l_unused_bytes
173 WHERE owner = r_lob.owner
174 AND TABLE_NAME = r_lob.TABLE_NAME;
175 END LOOP;
177 COMMIT;
179 END get_object_size;
180 /
182 create or replace PROCEDURE get_tbs_size AS
183 BEGIN
185 execute immediate 'truncate table c_tbs_usage';
187 insert into c_tbs_usage (
188 select df.tablespace_name as tablespace_name,
189 decode(df.maxbytes,
190 0,
191 sum(fs.bytes),
192 (df.maxbytes-(df.bytes-sum(fs.bytes)))) as bytes_free,
193 decode(df.maxbytes,
194 0,
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
198 (select
199 tablespace_name,
200 sum(bytes) bytes,
201 sum(decode(maxbytes,0,bytes,maxbytes)) maxbytes
202 from dba_data_files
203 group by tablespace_name ) df
204 on fs.tablespace_name = df.tablespace_name
205 group by df.tablespace_name,df.maxbytes,df.bytes);
207 COMMIT;
209 END get_tbs_size;
210 /
212 BEGIN
213 sys.dbms_scheduler.create_job(
214 job_name => '"COLLECTDU"."C_TBSSIZE_JOB"',
215 job_type => 'PLSQL_BLOCK',
216 job_action => 'begin
217 get_tbs_size();
218 end;',
219 repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
220 start_date => systimestamp at time zone 'Europe/Berlin',
221 job_class => '"DEFAULT_JOB_CLASS"',
222 auto_drop => FALSE,
223 enabled => TRUE);
224 END;
225 /
227 BEGIN
228 sys.dbms_scheduler.create_job(
229 job_name => '"COLLECTDU"."C_TBLSIZE_COLLECTDU_JOB"',
230 job_type => 'PLSQL_BLOCK',
231 job_action => 'begin
232 get_object_size( owner => ''COLLECTDU'' );
233 end;',
234 repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
235 start_date => systimestamp at time zone 'Europe/Berlin',
236 job_class => '"DEFAULT_JOB_CLASS"',
237 auto_drop => FALSE,
238 enabled => TRUE);
239 END;
240 /
242 spool off
243 quit