12c: ein Beispiel einer Interval-Reference Partitionierung

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.

Kommentar verfassen