contrib/oracle/create_schema.ddl

   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
  23
  24 spool create_collectd-schema.log
  25 connect / as sysdba
  26
  27 -- Create user, tablespace and permissions
  28
  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;
  38
  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";
  46
  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;
  55
  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
  68
  69 alter session set current_schema=collectdu;
  70
  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";
  78
  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" ;
  86
  87
  88 create or replace PROCEDURE get_object_size(owner IN VARCHAR2) AS
  89
  90 v_owner VARCHAR2(30) := owner;
  91
  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;
 100
 101 CURSOR cur_tbl IS
 102 SELECT owner,
 103   TABLE_NAME
 104 FROM dba_tables
 105 WHERE owner = v_owner;
 106
 107 CURSOR cur_idx IS
 108 SELECT owner,
 109   index_name,
 110   TABLE_NAME
 111 FROM dba_indexes
 112 WHERE owner = v_owner;
 113
 114 CURSOR cur_lob IS
 115 SELECT owner,
 116   segment_name,
 117   TABLE_NAME
 118 FROM dba_lobs
 119 WHERE owner = v_owner;
 120
 121 BEGIN
 122
 123   DELETE FROM c_tbl_size
 124   WHERE owner = v_owner;
 125   COMMIT;
 126
 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);
 131
 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;
 140
 141   COMMIT;
 142
 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);
 147
 148     EXCEPTION
 149     WHEN others THEN
 150       DBMS_OUTPUT.PUT_LINE('idx_name: ' || r_idx.index_name);
 151     END;
 152
 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;
 158
 159   COMMIT;
 160
 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);
 165
 166     EXCEPTION
 167     WHEN others THEN
 168       DBMS_OUTPUT.PUT_LINE('lob_name: ' || r_lob.segment_name);
 169     END;
 170
 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;
 176
 177   COMMIT;
 178
 179 END get_object_size;
 180 /
 181
 182 create or replace PROCEDURE get_tbs_size AS
 183 BEGIN
 184
 185 execute immediate 'truncate table c_tbs_usage';
 186
 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);
 206
 207 COMMIT;
 208
 209 END get_tbs_size;
 210 /
 211
 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 /
 226
 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 /
 241
 242 spool off
 243 quit