contrib/oracle/create_schema.ddl

   1 -- collectd - contrib/oracle/create_schema.ddl
   2 -- Copyright (C) 2008,2009  Roman Klesel
   3 --
   4 -- This program is free software; you can redistribute it and/or modify it
   5 -- under the terms of the GNU General Public License as published by the
   6 -- Free Software Foundation; only version 2 of the License is applicable.
   7 --
   8 -- This program is distributed in the hope that it will be useful, but
   9 -- WITHOUT ANY WARRANTY; without even the implied warranty of
  10 -- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
  11 -- General Public License for more details.
  12 --
  13 -- You should have received a copy of the GNU General Public License along
  14 -- with this program; if not, write to the Free Software Foundation, Inc.,
  15 -- 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA
  16 --
  17 -- Authors:
  18 --   Roman Klesel <roman.klesel at noris.de>
  19
  20 -- Description
  21 --------------
  22 -- This will create a schema to provide collectd with the required permissions
  23 -- and space for statistic data.
  24 -- The idea is to store the output of some expensive queries in static tables
  25 -- and fill these tables with dbms_scheduler jobs as often as necessary.
  26 -- collectd will then just read from the static tables. This will reduces the
  27 -- chance that your system will be killed by excessive monitoring queries and
  28 -- gives the dba control on the interval the information provided to collectd
  29 -- will be refreshed. You have to create a dbms_scheduler job for each of the
  30 -- schemas you what to monitor for object-space-usage. See the example below.
  31 --
  32 -- Requirements
  33 ---------------
  34 -- make sure you have:
  35 --              write permission in $PWD
  36 --              you have GID of oracle software owner
  37 --              set $ORACLE_HOME
  38 --              set $ORACLE_SID
  39 --              DB is up an running in RW mode
  40 -- execute like this:
  41 -- sqlplus /nolog @ create_collectd-schema.dll
  42
  43 spool create_collectd-schema.log
  44 connect / as sysdba
  45
  46 -- Create user, tablespace and permissions
  47
  48 CREATE TABLESPACE "COLLECTD-TBS"
  49         DATAFILE SIZE 30M
  50         AUTOEXTEND ON
  51         NEXT 10M
  52         MAXSIZE 300M
  53         LOGGING
  54         EXTENT MANAGEMENT LOCAL
  55         SEGMENT SPACE MANAGEMENT AUTO
  56         DEFAULT NOCOMPRESS;
  57
  58 CREATE ROLE "CREATE_COLLECTD_SCHEMA" NOT IDENTIFIED;
  59 GRANT CREATE JOB TO "CREATE_COLLECTD_SCHEMA";
  60 GRANT CREATE SEQUENCE TO "CREATE_COLLECTD_SCHEMA";
  61 GRANT CREATE SYNONYM TO "CREATE_COLLECTD_SCHEMA";
  62 GRANT CREATE TABLE TO "CREATE_COLLECTD_SCHEMA";
  63 GRANT CREATE VIEW TO "CREATE_COLLECTD_SCHEMA";
  64 GRANT CREATE PROCEDURE TO "CREATE_COLLECTD_SCHEMA";
  65
  66 CREATE USER "COLLECTDU"
  67         PROFILE "DEFAULT"
  68         IDENTIFIED BY "Change_me-1st"
  69         PASSWORD EXPIRE
  70         DEFAULT TABLESPACE "COLLECTD-TBS"
  71         TEMPORARY TABLESPACE "TEMP"
  72         QUOTA UNLIMITED ON "COLLECTD-TBS"
  73         ACCOUNT UNLOCK;
  74
  75 GRANT "CONNECT" TO "COLLECTDU";
  76 GRANT "SELECT_CATALOG_ROLE" TO "COLLECTDU";
  77 GRANT "CREATE_COLLECTD_SCHEMA" TO "COLLECTDU";
  78 GRANT analyze any TO "COLLECTDU";
  79 GRANT select on dba_tables TO "COLLECTDU";
  80 GRANT select on dba_lobs TO "COLLECTDU";
  81 GRANT select on dba_indexes TO "COLLECTDU";
  82 GRANT select on dba_segments TO "COLLECTDU";
  83 GRANT select on dba_tab_columns TO "COLLECTDU";
  84 GRANT select on dba_free_space TO "COLLECTDU";
  85 GRANT select on dba_data_files TO "COLLECTDU";
  86 -- Create tables and indexes
  87
  88 alter session set current_schema=collectdu;
  89
  90 create table c_tbs_usage (
  91         tablespace_name varchar2(30),
  92         bytes_free number,
  93     bytes_used  number,
  94         CONSTRAINT "C_TBS_USAGE_UK1" UNIQUE ("TABLESPACE_NAME") USING INDEX
  95         TABLESPACE "COLLECTD-TBS"  ENABLE)
  96         TABLESPACE "COLLECTD-TBS";
  97
  98 CREATE TABLE "COLLECTDU"."C_TBL_SIZE" (
  99     "OWNER" VARCHAR2(30 BYTE),
 100         "TABLE_NAME" VARCHAR2(30 BYTE),
 101         "BYTES" NUMBER,
 102          CONSTRAINT "C_TBL_SIZE_UK1" UNIQUE ("OWNER", "TABLE_NAME")
 103          USING INDEX TABLESPACE "COLLECTD-TBS"  ENABLE)
 104          TABLESPACE "COLLECTD-TBS" ;
 105
 106
 107 create or replace PROCEDURE get_object_size(owner IN VARCHAR2) AS
 108
 109 v_owner VARCHAR2(30) := owner;
 110
 111 l_free_blks NUMBER;
 112 l_total_blocks NUMBER;
 113 l_total_bytes NUMBER;
 114 l_unused_blocks NUMBER;
 115 l_unused_bytes NUMBER;
 116 l_lastusedextfileid NUMBER;
 117 l_lastusedextblockid NUMBER;
 118 l_last_used_block NUMBER;
 119
 120 CURSOR cur_tbl IS
 121 SELECT owner,
 122   TABLE_NAME
 123 FROM dba_tables
 124 WHERE owner = v_owner;
 125
 126 CURSOR cur_idx IS
 127 SELECT owner,
 128   index_name,
 129   TABLE_NAME
 130 FROM dba_indexes
 131 WHERE owner = v_owner;
 132
 133 CURSOR cur_lob IS
 134 SELECT owner,
 135   segment_name,
 136   TABLE_NAME
 137 FROM dba_lobs
 138 WHERE owner = v_owner;
 139
 140 BEGIN
 141
 142   DELETE FROM c_tbl_size
 143   WHERE owner = v_owner;
 144   COMMIT;
 145
 146   FOR r_tbl IN cur_tbl
 147   LOOP
 148     BEGIN
 149       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);
 150
 151     EXCEPTION
 152     WHEN others THEN
 153       DBMS_OUTPUT.PUT_LINE('tbl_name: ' || r_tbl.TABLE_NAME);
 154     END;
 155     INSERT
 156     INTO c_tbl_size
 157     VALUES(r_tbl.owner,   r_tbl.TABLE_NAME,   l_total_bytes -l_unused_bytes);
 158   END LOOP;
 159
 160   COMMIT;
 161
 162   FOR r_idx IN cur_idx
 163   LOOP
 164     BEGIN
 165       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);
 166
 167     EXCEPTION
 168     WHEN others THEN
 169       DBMS_OUTPUT.PUT_LINE('idx_name: ' || r_idx.index_name);
 170     END;
 171
 172     UPDATE c_tbl_size
 173     SET bytes = bytes + l_total_bytes -l_unused_bytes
 174     WHERE owner = r_idx.owner
 175      AND TABLE_NAME = r_idx.TABLE_NAME;
 176   END LOOP;
 177
 178   COMMIT;
 179
 180   FOR r_lob IN cur_lob
 181   LOOP
 182     BEGIN
 183       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);
 184
 185     EXCEPTION
 186     WHEN others THEN
 187       DBMS_OUTPUT.PUT_LINE('lob_name: ' || r_lob.segment_name);
 188     END;
 189
 190     UPDATE c_tbl_size
 191     SET bytes = bytes + l_total_bytes -l_unused_bytes
 192     WHERE owner = r_lob.owner
 193      AND TABLE_NAME = r_lob.TABLE_NAME;
 194   END LOOP;
 195
 196   COMMIT;
 197
 198 END get_object_size;
 199 /
 200
 201 create or replace PROCEDURE get_tbs_size AS
 202 BEGIN
 203
 204 execute immediate 'truncate table c_tbs_usage';
 205
 206 insert into c_tbs_usage (
 207 select df.tablespace_name as tablespace_name,
 208        decode(df.maxbytes,
 209                0,
 210                sum(fs.bytes),
 211                (df.maxbytes-(df.bytes-sum(fs.bytes)))) as bytes_free,
 212        decode(df.maxbytes,
 213                0,
 214                round((df.bytes-sum(fs.bytes))),
 215                round(df.maxbytes-(df.maxbytes-(df.bytes-sum(fs.bytes))))) as bytes_used
 216 from dba_free_space fs inner join
 217        (select
 218                tablespace_name,
 219                sum(bytes) bytes,
 220                sum(decode(maxbytes,0,bytes,maxbytes))  maxbytes
 221         from dba_data_files
 222         group by tablespace_name ) df
 223 on fs.tablespace_name = df.tablespace_name
 224 group by df.tablespace_name,df.maxbytes,df.bytes);
 225
 226 COMMIT;
 227
 228 END get_tbs_size;
 229 /
 230
 231 BEGIN
 232 sys.dbms_scheduler.create_job(
 233 job_name => '"COLLECTDU"."C_TBSSIZE_JOB"',
 234 job_type => 'PLSQL_BLOCK',
 235 job_action => 'begin
 236    get_tbs_size();
 237 end;',
 238 repeat_interval => 'FREQ=MINUTELY;INTERVAL=5',
 239 start_date => systimestamp at time zone 'Europe/Berlin',
 240 job_class => '"DEFAULT_JOB_CLASS"',
 241 auto_drop => FALSE,
 242 enabled => TRUE);
 243 END;
 244 /
 245
 246 BEGIN
 247 sys.dbms_scheduler.create_job(
 248 job_name => '"COLLECTDU"."C_TBLSIZE_COLLECTDU_JOB"',
 249 job_type => 'PLSQL_BLOCK',
 250 job_action => 'begin
 251    get_object_size( owner => ''COLLECTDU'' );
 252 end;',
 253 repeat_interval => 'FREQ=HOURLY;INTERVAL=12',
 254 start_date => systimestamp at time zone 'Europe/Berlin',
 255 job_class => '"DEFAULT_JOB_CLASS"',
 256 auto_drop => FALSE,
 257 enabled => TRUE);
 258 END;
 259 /
 260
 261 spool off
 262 quit
 263
 264 -- vim: set syntax=sql :