Kwestia wersji – część druga

tele_img_08

Oracle 11gr2 i usuwanie danych z tymczasowej tablicy w autonomicznej transakcji (ORA-14462)
Wprowadzenie globalnych tablic tymczasowych było bardzo wyczekiwanym rozszerzeniem możliwości bazy danych ORACLE w wersji 8i. Od tego czasu wiele rozwiązań zostało zaimplementowanych przy ich użyciu. Podczas migracji na nowsze wersje tablice także nie sprawiały problemów. Ale w wersji 11gr2 czeka na nas niespodzianka związana ze zmianą zachowania.

Nowa wersja nie pozwala na wykonanie operacji TRUNCATE na tymczasowej tablicy w autonomicznej transakcji, jeśli jesteśmy w transakcji, która używa danych z tej tablicy. Próba skończy się błędem :

ORA-14462: nie można przyciąć (TRUNCATE) tabeli tymczasowej w transakcji autonomicznej, ponieważ tabela ta jest właśnie używana przez transakcję nadrzędną

Największa zmiana dotyczy tablic tymczasowych utworzonych z opcją zachowującą dane po zatwierdzeniu transakcji (ON COMMIT PRESERVE ROWS).

Przypadek testowy :

1
 CREATE global temporary TABLE GTT(n NUMBER) ON COMMIT preserve rows;

lub

1
CREATE global temporary TABLE GTT(n NUMBER) ON COMMIT DELETE rows;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
DECLARE
l_cnt NUMBER;
PROCEDURE p_GTT_AT IS
PRAGMA autonomous_transaction;
l_cnt NUMBER;
BEGIN
SELECT COUNT(*) INTO l_cnt FROM gtt;
DBMS_OUTPUT.put_line('p_GTT_AT:' || l_cnt);
EXECUTE IMMEDIATE 'truncate table gtt';
END p_GTT_AT;
BEGIN
INSERT INTO GTT VALUES (1);
--commit; opcjonalny commit
p_GTT_AT();
SELECT COUNT(*) INTO l_cnt FROM gtt;
DBMS_OUTPUT.put_line('MAIN:' || l_cnt);
ROLLBACK; -- wycofanie transakcji które kończy się błędem < 11g
END;

Wyniki przedstawione w poniższej tabelce pokazują zmianę zachowania przy migracji na wersję 11g.

Z commit Bez commit
9.2.0.6 | 10.2.0.4 11.2.0.2 9.2.0.6 | 10.2.0.4 11.2.0.2
PRESERVE ROWS Wykonanie poprawne ORA-14462 Wykonanie poprawne, ale wycofanie transakcji kończy się błędem ORA-00600 [4552] [2] ORA-14462
DELETE ROWS Wykonanie poprawne Wykonanie poprawne ORA-14462 ORA-14462

Oczywiście możemy się zastanowić nad zmianą zachowania tablicy tak aby sama usuwała dane podczas operacji zatwierdzania (ON COMMIT DELETE ROWS). Niestety czasem musimy zmierzyć się z tysiącami linii kodu i opcja zmiany konstrukcji przetwarzania danych nie wchodzi w rachubę.

Rozwiązanie

Jeśli dopisze nam szczęście, może okazać się, że za te operacje będzie odpowiedzialny jeden program. Proponowane obejście to TRUNCATE z degradacją do DELETE poprzez obsłużenie błędu tak jak w poniższym przykładzie :

1
2
3
4
5
6
7
8
9
10
11
12
PROCEDURE p_clear_temp(pvBufferName IN VARCHAR2)
PRAGMA autonomous_transaction;
truncate_temp EXCEPTION;
PRAGMA EXCEPTION_INIT(truncate_temp, -14462);
BEGIN
EXECUTE IMMEDIATE 'truncate table ' || pvBufferName || '_temp';
COMMIT;
EXCEPTION
WHEN truncate_temp THEN -- w 11gr2 nie można czyścić tablic tymczasowych przez truncate w autonomicznej transakcji - usuwamy więc delete'm
EXECUTE IMMEDIATE 'delete from ' || pvBufferName || '_temp';
COMMIT;
END;

 

Poszukiwania

Jeśli kod wykonujący te operacje jest zapisany po stronie bazy w funkcji/procedurze PL/SQL to możemy pokusić się o wyszukanie miejsc, które wykonują tego typu operacje. Warunkiem jest wykonywanie operacji TRUNCATE bezpośrednio w ciele autonomicznej funkcji/procedury.

Aby szybko znaleźć miejsca w kodzie PL/SQL, które mogą wykonywać takie operacje możemy posłużyć się załączonym skryptem.

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
DECLARE
lv_name VARCHAR2(2000);
lv_procname VARCHAR2(2000);
ln_proclnr VARCHAR2(2000);
ln_nextproclnr VARCHAR2(2000);
ln_trunclnr VARCHAR2(2000);
BEGIN
FOR r IN (SELECT *
FROM dba_source
WHERE TYPE IN ('PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
AND UPPER(text) LIKE UPPER('%pragma%autonomous_transaction%')
AND owner NOT IN ('SYS', 'SYSTEM')
ORDER BY owner, name, TYPE, line) LOOP
SELECT text, line
INTO lv_procname, ln_proclnr
FROM (SELECT text, line
FROM dba_source s
WHERE s.owner = r.owner
AND s.name = r.name
AND s.TYPE = r.TYPE
AND (UPPER(s.text) LIKE '%PROCEDURE%' OR
UPPER(s.text) LIKE '%FUNCTION%')
AND s.line < r.line ORDER BY line DESC) WHERE ROWNUM = 1; BEGIN SELECT line -- następna procedura into ln_nextproclnr from (select text, line from dba_source s where s.owner = r.owner and s.name = r.name and s.TYPE = r.type and (upper(s.text) like '%PROCEDURE%' or upper(s.text) like '%FUNCTION%') and s.line > r.line
ORDER BY line)
WHERE ROWNUM = 1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
ln_nextproclnr := 999999999999999; -- to już była ostatnia, szukamy do końca pliku
END;
BEGIN
SELECT line
INTO ln_trunclnr
FROM (SELECT text, line
FROM dba_source s
WHERE s.owner = r.owner
AND s.name = r.name
AND s.TYPE = r.TYPE
AND UPPER(s.text) LIKE '%TRUNCATE%'
AND s.line BETWEEN r.line AND ln_nextproclnr
ORDER BY line)
WHERE ROWNUM = 1;

htp.p(r.owner || '.' || r.name || ' ' || lv_procname || ' [' ||
ln_proclnr || ':' || r.line || ': truncate '||ln_trunclnr||']
'
);

WHEN NO_DATA_FOUND THEN
NULL; -- nie ma truncate w tej procedurze
END;
END LOOP;
END;

Wnioski

Im większy, bardziej skomplikowany system, tym więcej tego typu różnic może wypłynąć podczas procesu migracji na nowszą wersję bazy danych ORACLE. Różnice w zachowaniu potrafią spowodować, że poprawny merytorycznie i technicznie kod przestanie się bezbłędnie wykonywać. Niezbędne są więc testy obejmujące cały system.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *