Im Rahmen eines Projektes ging es u.a. um die Einführung einer Referenz-Partitionierung mit Intervall, d.h. zwei oder mehr Tabellen, die zueinander über einen FK (Foreign Key) in Beziehung zu einander stehen, werden analog des FK partitioniert. Dieses Feature gab es an sich schon in 11g. Seit 12c gibt es nun die Möglichkeit, die Basistabelle mit einer Intervallpartitioninierung zu versehen, während in 11g die notwendigen Partitionen noch manuell oder durch einen Job angelegt werden mussten.
Um einen besseren Einblick in die Brauchbarkeit dieses neuen Features zu haben, habe ich dieses Feature im Rahmen einer kleinen Funktion genutzt. Ich habe mein häufig eingesetztes Packages zum LOGGING so erweitert, dass bei Protokollierung einer Oracle Fehlermeldung, diese zusätzlich in eine abhängige Tabelle (Partition) geschrieben wird.
Nach ein paar Wochen Laufzeit im täglichen Betrieb sind dabei keine Probleme mit der Partitionierung aufgetreten. Auch die unten nur sehr sporadisch implemtierte Funktion HOUSEKEEPING, erfüllte ihren Zweck – allerdings anders implementiert. Darauf werde ich später – im neuen Jahr – nochmal zurückkommen. Hier werden zukünftig alte Partitionen komplett entfernt (DROP) – und nicht nur die Zeilen, so wie hier dargestellt.
Um das gleiche Testszenario aufzubauen, müssen Sie drei Schritte ausführen:
Dazu müssen im ersten Schritt die notwendigen Tabellen angelegt werden:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 | CREATE TABLE LOGGING( ID NUMBER generated AS IDENTITY NOT NULL , TMSTMP TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL , SID NUMBER , UNAME VARCHAR2(30 CHAR) , OSUSER VARCHAR2(30 CHAR) , MACHINE VARCHAR2(64 CHAR) , TERMINAL VARCHAR2(30 CHAR) , PROGRAM VARCHAR2(48 CHAR) , MODULE VARCHAR2(64 CHAR) , ACTION VARCHAR2(64 CHAR) , CLIENT_INFO VARCHAR2(64 CHAR) , LOGON_TIME DATE , STATE VARCHAR2(19 CHAR) , LEV NUMBER , message VARCHAR2(2000 CHAR) ) PARTITION BY RANGE (TMSTMP) INTERVAL( NUMTODSINTERVAL(1,'DAY') ) ( PARTITION P_RANGE_INI VALUES LESS THAN (TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) COMPRESS ) STORAGE ( initial 64K ) ENABLE ROW MOVEMENT COMPRESS ; CREATE UNIQUE INDEX U01_LOGGING_TIME_ID ON LOGGING(TMSTMP, ID) INITRANS 20 MAXTRANS 255 STORAGE (INITIAL 64K) LOCAL compute statistics ; ALTER TABLE LOGGING ADD CONSTRAINT UC01_LOGGING_TIME_ID UNIQUE(TMSTMP, ID) ; CREATE TABLE ERROR_LOGGING( ID NUMBER generated AS IDENTITY NOT NULL , TMSTMP TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL , MAIN_ID INTEGER NOT NULL, CONSTRAINT FK01_ERROR_LOGGING FOREIGN KEY (TMSTMP, MAIN_ID) REFERENCES LOGGING(TMSTMP, ID) ON DELETE CASCADE , ERRnum VARCHAR2(10 CHAR) , ERRtype VARCHAR2(10 CHAR) , message VARCHAR2(2000 CHAR) ) STORAGE ( initial 64K ) PARTITION BY REFERENCE ( FK01_ERROR_LOGGING ) ENABLE ROW MOVEMENT COMPRESS ; CREATE UNIQUE INDEX U01_ERROR_LOGGING_ID ON ERROR_LOGGING(TMSTMP, MAIN_ID, ID) INITRANS 20 MAXTRANS 255 STORAGE (INITIAL 64K) LOCAL compute statistics ; |
Im zweiten Schritt muss das Package implementiert werden:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 | CREATE OR REPLACE PACKAGE LOGG AS c_version CONSTANT VARCHAR2(200 CHAR) := '1.4.01 - internal'; c_id CONSTANT VARCHAR2(200 CHAR) := 'procedure.logg.sql 4674 2014-12-16 18:14:50Z rainer_hartwig'; c_dtmask CONSTANT VARCHAR2(30 CHAR) := 'YYYY-MM-DD HH24:MI:SS'; c_date CONSTANT VARCHAR2(30 CHAR) := substr(c_id, instr(c_id, '2014-'), LENGTH(c_dtmask)-2); c_datemask CONSTANT VARCHAR2(20 CHAR) := 'YYYY-MM-DD'; c_timemask CONSTANT VARCHAR2(20 CHAR) := 'HH24:MI:SS'; c_datetimmask CONSTANT VARCHAR2(40 CHAR) := c_datemask||' '||c_timemask; c_ret_false CONSTANT VARCHAR2(10 CHAR) := 'FALSE'; c_ret_true CONSTANT VARCHAR2(10 CHAR) := 'TRUE'; c_ret_na CONSTANT VARCHAR2(10 CHAR) := 'N/A'; c_lg_Start CONSTANT INTEGER := 0; c_lg_Ende CONSTANT INTEGER := 1; c_lg_Error CONSTANT INTEGER := 2; c_lg_ErrorStart CONSTANT INTEGER := 3; c_lg_ErrorEnde CONSTANT INTEGER := 4; c_lg_Exec CONSTANT INTEGER := 5; c_lg_StartExec CONSTANT INTEGER := 6; c_lg_EndeExec CONSTANT INTEGER := 7; PROCEDURE logerror(p_title IN VARCHAR2); PROCEDURE log(p_txt IN VARCHAR2 , p_err IN BOOLEAN DEFAULT FALSE); PROCEDURE log(p_txt IN VARCHAR2 , p_lev IN NUMBER , p_err IN BOOLEAN DEFAULT FALSE); END LOGG; / SHOW error CREATE OR REPLACE PACKAGE BODY LOGG AS c_prefix CONSTANT VARCHAR2(200 CHAR) := ' + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +'; c_lg_first CONSTANT INTEGER := c_lg_Start; c_lg_last CONSTANT INTEGER := c_lg_EndeExec; c_title CONSTANT VARCHAR2(2000 CHAR) := 'LOGG.INIT'; c_txt_start CONSTANT VARCHAR2(10 CHAR) := 'START'; c_txt_ende CONSTANT VARCHAR2(10 CHAR) := 'ENDE'; c_log_save CONSTANT NUMBER := 6 * 31; -- Aufbewahrungszeit der Einträge in LOGGING c_usr CONSTANT VARCHAR2(30 CHAR) := USER; v_level NUMBER := 0; TYPE LOG_T IS TABLE OF VARCHAR2(40 CHAR) INDEX BY BINARY_INTEGER; c_LOGTXTS LOG_T; ---------------------------------------- FUNCTION get_level RETURN NUMBER IS BEGIN IF( (v_level IS NULL) OR (v_level <= 0) ) THEN v_level := 0; END IF; RETURN nvl(v_level, 0); END get_level; ---------------------------------------- PROCEDURE set_level(p_lev IN NUMBER DEFAULT 0) IS v_lev NUMBER; BEGIN v_level := nvl(p_lev, 0); v_lev := get_level; v_level := v_lev; END set_level; ---------------------------------------- FUNCTION GetLogTxt(p_nr IN NUMBER) RETURN varchar2 IS c_nr_0 CONSTANT INTEGER := CASE WHEN nvl(p_nr,0) <= c_lg_first THEN c_lg_first ELSE round(nvl(p_nr,0)) END; c_nr CONSTANT INTEGER := CASE WHEN c_nr_0 >= c_lg_last THEN c_lg_last ELSE c_nr_0 END; BEGIN RETURN c_logtxts(c_nr); END getlogtxt; ---------------------------------------- PROCEDURE logerror(p_title IN VARCHAR2) IS BEGIN log(GetLogTxt(C_LG_ErrorStart)||p_title); log(DBMS_UTILITY.FORMAT_ERROR_STACK, TRUE); log(DBMS_UTILITY.format_error_backtrace, TRUE); log(DBMS_UTILITY.FORMAT_CALL_STACK, TRUE); log(GetLogTxt(C_LG_ErrorEnde)||p_title); END logerror; ---------------------------------------- PROCEDURE HOUSEKEEPING IS BEGIN -- LOGGING: alte Eintraege entfernen -- BEGIN DELETE FROM LOGGING l WHERE l.tmstmp < trunc(SYSDATE) - c_log_save; commit; exception WHEN others THEN NULL; END; END Housekeeping; ---------------------------------------- -- internal procedure to write the message to LOGGING -- it is only called by LOG() -- PROCEDURE dolog(p_txt IN VARCHAR2 , p_err IN BOOLEAN) IS c_lev CONSTANT NUMBER := get_level() - 1; c_err CONSTANT VARCHAR2(100 CHAR) := c_logtxts(c_lg_Error); c_pref CONSTANT VARCHAR2(32 CHAR) := CASE WHEN (c_lev <= 0) OR (substr(p_txt,1,LENGTH(c_err)) = c_err) THEN NULL ELSE substr( substr(c_prefix, 1, c_lev * 2) , 1, 32) || ' ' END; c_sid CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','SID'); c_osusr CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','OS_USER'); c_host CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','HOST'); c_term CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','TERMINAL'); c_info CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','CLIENT_INFO'); c_ident CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER'); c_mod CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','MODULE'); c_act CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','ACTION'); c_usrid CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','SESSION_USERID'); c_usr CONSTANT VARCHAR2(1000 CHAR) := SYS_CONTEXT('USERENV','SESSION_USER'); v_err VARCHAR2(2000 CHAR); v_err_code NUMBER; v_err_msg VARCHAR2(2000 CHAR); v_id LOGGING.ID%TYPE; v_tmstmp LOGGING.TMSTMP%TYPE; v_msg LOGGING.MESSAGE%TYPE; PRAGMA AUTONOMOUS_TRANSACTION; BEGIN -- log Housekeeping; FOR r IN (SELECT c_sid AS sid , USER AS USERNAME , c_osusr AS OSUSER , c_host AS MACHINE , c_term AS TERMINAL , nvl(c_info,c_ident) AS PROGRAM , c_mod AS MODULE , c_act AS ACTION , nvl(nvl(c_usrid,c_usr), c_info) AS CLIENT_INFO , NULL AS LOGON_TIME , NULL AS STATE , c_lev AS LEV , c_pref || p_txt AS TXT FROM dual ) loop v_msg := substr(r.TXT,1,2000); INSERT INTO LOGGING( SID ,UNAME ,OSUSER ,MACHINE ,TERMINAL ,PROGRAM ,MODULE ,ACTION ,CLIENT_INFO ,LOGON_TIME ,STATE ,LEV ,message) VALUES( r.SID ,r.USERNAME ,r.OSUSER ,r.MACHINE ,r.TERMINAL ,r.PROGRAM ,r.MODULE ,r.ACTION ,r.CLIENT_INFO ,r.LOGON_TIME ,r.STATE ,r.LEV ,v_msg); IF( p_err = TRUE ) THEN SELECT ID, TMSTMP INTO v_id, v_tmstmp FROM LOGGING l WHERE l.ID = (SELECT MAX(l.ID) FROM LOGGING l WHERE l.TMSTMP >= trunc(SYSDATE)) ; v_err := ''; IF(instr(UPPER(v_msg), 'ORA-') > 0) THEN v_err := 'ORA'; v_err_msg := substr(v_msg, instr(UPPER(v_msg), 'ORA-')+4, 5); ELSE IF(instr(UPPER(v_msg), 'TNS-') > 0) THEN v_err := 'TNS'; v_err_msg := substr(v_msg, instr(UPPER(v_msg), 'TNS-')+4, 5); END IF; END IF; IF(( v_err != '' ) AND ( v_err IS NOT NULL )) THEN INSERT INTO ERROR_LOGGING(MAIN_ID , TMSTMP , ERRNUM , ERRTYPE , message) VALUES(v_id , v_tmstmp , v_err_msg , v_err , v_msg); END IF; END IF; END loop; commit; EXCEPTION WHEN OTHERS THEN BEGIN v_err_code := SQLCODE; v_err_msg := substr(SQLERRM, 1, 1000); v_err := substr('*** ERROR: '||v_err_code||' - MSG ->'||v_err_msg||'<',1,1000); INSERT INTO LOGGING(tmstmp, message) VALUES(SYSTIMESTAMP, v_err) ; INSERT INTO LOGGING(tmstmp, message) VALUES(SYSTIMESTAMP, p_txt) ; commit; END; END dolog; ---------------------------------------- -- -- entfernt alle CR -- zerlegt den String in alle Zeilen, die mit LF enden -- und schreibt jede Zeile (ohne LF) in die Datei -- -- PROCEDURE log(p_txt IN VARCHAR2 , p_err IN BOOLEAN DEFAULT FALSE) IS c_cr CONSTANT VARCHAR2(1 CHAR) := chr(13); c_lf CONSTANT VARCHAR2(1 CHAR) := chr(10); v_txt VARCHAR2(32767 CHAR) := NULL; i INTEGER := 0; BEGIN -- remove all CR -- SELECT substr( REPLACE(p_txt, c_cr, ''), 1, 32767 ) INTO v_txt FROM dual; while (instr(v_txt, c_lf) > 0) loop i := instr(v_txt, c_lf); dolog( p_txt => substr(v_txt, 1, i-1) , p_err => p_err ); v_txt := substr(v_txt, i+1); END loop; dolog(p_txt => v_txt, p_err => p_err); END log; ---------------------------------------- PROCEDURE log(p_txt IN VARCHAR2 , p_lev IN NUMBER , p_err IN BOOLEAN DEFAULT FALSE) IS c_orglev CONSTANT NUMBER := v_level; BEGIN BEGIN set_level(p_lev); log(p_txt, p_err); exception WHEN others THEN NULL; END; v_level := c_orglev; END log; -- ======================================== -- PROCEDURE Init IS c_title CONSTANT VARCHAR2(1000 CHAR) := 'Init'; BEGIN c_logtxts(c_lg_Start) := c_txt_start||' '; c_logtxts(c_lg_Ende) := c_txt_ende||' '; c_logtxts(c_lg_Error) := '*** ERROR '; c_logtxts(c_lg_ErrorStart) := c_logtxts(c_lg_Error) ||c_logtxts(c_lg_Start); c_logtxts(c_lg_ErrorEnde) := c_logtxts(c_lg_Error) ||c_logtxts(c_lg_Ende); c_logtxts(c_lg_Exec) := 'EXEC =>;'; c_logtxts(c_lg_StartExec) := c_logtxts(c_lg_Start) || c_logtxts(c_lg_Exec); c_logtxts(c_lg_EndeExec) := c_logtxts(c_lg_Ende) || c_logtxts(c_lg_Exec); EXCEPTION WHEN others THEN logerror(c_title); END init; BEGIN init; EXCEPTION WHEN others THEN logerror(c_title); END LOGG; / SHOW error |
Im dritten Schritt können Sie nun die Routine LOG() zum Protokollieren und die Routine LOGERROR() zum Protokollieren von Fehlern in Ihrem Code einbinden.
Viel Erfolg.