Sunday, November 23, 2008

executing_packages.sql

In our in-house application we are developing new features and fixing bugs with approximately 40 developers in total. Sometimes the installation of a new version of a database package "hangs" and eventually times out with a ORA-04021: timeout occurred while waiting to lock object". This is caused by another session that is currently executing the same package. When there's no time pressure, the developer just postpones the installation to the end of the day, or early next morning. But every now and then, a high priority bugfix or a project nearing its deadline cannot wait this long. The developer calls up the DBA and asks to bounce the developer database. The DBA sends an e-mail to warn everybody and kills all developers' sessions in the process of course. And some five or ten minutes later the DBA sends an e-mail to inform that everything is back to normal. This ritual is causing not only annoyance to 40 developers and a DBA, it also costs money: 41 times 10 minutes times X euros/hour.

Two or three years ago, I was wondering whether it is visible in the data dictionary who is executing the package that needed a new version. I stumbled upon this priceless script by Steve Adams, called executing_packages.sql. It lists the sessions that are currently executing stored code. He had found out that an object that is currently executing, has the sys.x$kglob.kglhdpmd column set to 2. See for example this question and answer. From then on, whenever I received an e-mail announcing the bounce of the developer database, I quickly turned to the developer experiencing the problem asking him the name of the package. I ran executing_packages.sql which revealed who is executing this package. Next, I called the DBA to stop the bounce and just kill one session instead. And I could hand over the sid/serial# of the session to be killed. Or even better, we just asked the responsible developer to end his session manually if possible.

But then we did an upgrade to 10.2.0.4. The script was originally developed for 8.0 and 8.1 according to Steve Adams' site, and I noticed more than once that it worked perfectly in 9.2.0.7. On 10.2.0.4 however, there are two problems:

  1. I used a special DBA-account to be able to execute the script. The same upgraded account is now unable to see the sys.x$ tables.

  2. Even when logged in as SYS, the sys.x_$ tables (note the underscore) are not there anymore.

I was surprised to see very little information about this when googling. I had imagined that such a useful script would be used anywhere and that several people would have bumped into the same problems by now. So this gives me the opportunity to find these things out myself at home.

It would be nice to address the first problem by defining a view under the SYS-schema and giving access to the view to all developers, and not just the DBA-account. The only thing that might worry the DBA's, is that the view might be used in the application. This can be addressed by granting access to the view to a role that every developer has. You cannot base new database objects on objects that have not been granted directly to you, so this will appease the DBA's.

The second problem was easy. Just remove the underscores in the table names and the query works: sys.x$kglob, sys.x$kglpn and sys.x$ksuse are still there in 10 and 11. I just want to extend the session information a little more. The existing script only shows the sid and serial#. In the new query, I added the username, program, module, action and client_info as well, as this additional information will help me in my conversation with the developer who has to end his session. I could have used the v$session view for this, but in the same style as the original script, I used sys.x$ksusex to get the "dbms_application_info" fields module, action and client_info. This results in this view:

sys@ORA11GR1> create view v_executing_packages
2 as
3 select
4 decode(o.kglobtyp,
5 7, 'PROCEDURE',
6 8, 'FUNCTION',
7 9, 'PACKAGE',
8 12, 'TRIGGER',
9 13, 'CLASS'
10 ) "TYPE",
11 o.kglnaown "OWNER",
12 o.kglnaobj "NAME",
13 s.indx "SID",
14 s.ksuseser "SERIAL",
15 s.ksuudnam "USERNAME",
16 s.ksuseapp "PROGRAM",
17 x.app "MODULE",
18 x.act "ACTION",
19 x.clinfo "CLIENT_INFO"
20 from
21 sys.x$kglob o,
22 sys.x$kglpn p,
23 sys.x$ksuse s,
24 sys.x$ksusex x
25 where
26 o.inst_id = userenv('Instance') and
27 p.inst_id = userenv('Instance') and
28 s.inst_id = userenv('Instance') and
29 x.inst_id = userenv('Instance') and
30 p.kglpnhdl = o.kglhdadr and
31 s.addr = p.kglpnses and
32 s.indx = x.sid and
33 s.ksuseser = x.serial and
34 o.kglhdpmd = 2 and
35 o.kglobtyp in (7, 8, 9, 12, 13)
36 order by 1,2,3
37 /

View is aangemaakt.


Normally I won't include an order by clause in a view, but in this case I only want to do a "select * from sys.v_executing_packages", and not bother about the ordering there.

For now I'll just grant the select privilege directly to my account. As said, granting to a developer role is better in real life.

sys@ORA11GR1> grant select on v_executing_packages to rwijk
2 /

Toekennen is geslaagd.


Below is a small test to see how this view can be used. In session 1 I'm creating a package that executes a long time:

rwijk@ORA11GR1> select sid
2 , serial#
3 from v$session
4 where sid in (select sid from v$mystat)
5 /

SID SERIAL#
---------- ----------
136 35

1 rij is geselecteerd.

rwijk@ORA11GR1> create package mypck
2 as
3 procedure test;
4 end mypck;
5 /

Package is aangemaakt.

rwijk@ORA11GR1> create package body mypck
2 as
3 procedure test
4 is
5 begin
6 dbms_lock.sleep(3600); -- an hour
7 dbms_output.put_line('version 1.0');
8 end test;
9 end mypck;
10 /

Package-body is aangemaakt.

rwijk@ORA11GR1> exec mypck.test

And now session 1 is asleep for an hour.

In session 2 I'm trying to create a second version of the package, that will wait for session 1 to complete. This will eventually lead to a ORA-04021, after some 15 minutes:

rwijk@ORA11GR1> select sid
2 , serial#
3 from v$session
4 where sid in (select sid from v$mystat)
5 /

SID SERIAL#
---------- ----------
139 37

1 rij is geselecteerd.

rwijk@ORA11GR1> create or replace package body mypck
2 as
3 procedure test
4 as
5 begin
6 dbms_lock.sleep(3600); -- an hour
7 dbms_output.put_line('version 1.1');
8 end test;
9 end mypck;
10 /
create or replace package body mypck
*
FOUT in regel 1:
.ORA-04021: Time-out tijdens wachten op vergrendeling van object .


Now I will use the view in a third session (or in session 2 after the time-out) to find out who is executing which package:

rwijk@ORA11GR1> select * from sys.v_executing_packages
2 /

TYPE OWNER NAME SID SERIAL
--------- ----- ------------------------------ ---------- ----------
USERNAME PROGRAM
------------------------------ ------------------------------------------------
MODULE
------------------------------------------------
ACTION
--------------------------------
CLIENT_INFO
----------------------------------------------------------------
PACKAGE RWIJK MYPCK 136 35
RWIJK SQL*Plus
SQL*Plus



PACKAGE SYS DBMS_LOCK 136 35
RWIJK SQL*Plus
SQL*Plus




2 rijen zijn geselecteerd.

And this points to the SQL*Plus session 1.

Sunday, November 16, 2008

RETURNING INTO

While doing a quality check on new code, I encountered some row by row processing. This always triggers me to make a remark about rewriting the code to a single SQL, or at least bulk processing. But in this case, inside the loop, several local variables were used to do some counting. And at the end of the loop, a processing report was created to inform the end user about how many records had been processed, split out for several statusses. When rewriting it to a single SQL, you can use the SQL%ROWCOUNT to determine how many rows were processed, but if you want to split this number up per status, how to do that?

Here is an example, which doesn't look like the original code in any way, but it shows the same principle. First, let's create a table with some random statusses and create a procedure that creates a processing report in the dbms_output buffer:

rwijk@ORA11GR1> create table t (id,col,status)
2 as
3 select level
4 , '**********'
5 , cast(dbms_random.string('u',1) as varchar2(1))
6 from dual
7 connect by level <= 1000
8 /

Table created.

rwijk@ORA11GR1> select status
2 , count(*)
3 from t
4 where status in ('A','B','C','D','E')
5 group by status
6 order by status
7 /

S COUNT(*)
- ----------
A 38
B 42
C 37
D 39
E 29

5 rows selected.

rwijk@ORA11GR1> create procedure print_processing_report
2 ( p_count_a in number
3 , p_count_b in number
4 , p_count_c in number
5 , p_count_d in number
6 , p_count_e in number
7 )
8 is
9 procedure print_one_line
10 ( p_status in t.status%type
11 , p_count in number
12 )
13 is
14 begin
15 dbms_output.put_line
16 ( '* Total number of ' || p_status || '''s processed: ' ||
17 rpad(to_char(p_count),4) || '*'
18 );
19 end print_one_line
20 ;
21 begin
22 dbms_output.put_line('**************************************');
23 dbms_output.put_line('* P R O C E S S I N G R E P O R T *');
24 dbms_output.put_line('* ================================== *');
25 print_one_line('A',p_count_a);
26 print_one_line('B',p_count_b);
27 print_one_line('C',p_count_c);
28 print_one_line('D',p_count_d);
29 print_one_line('E',p_count_e);
30 dbms_output.put_line('**************************************');
31 end;
32 /

Procedure created.


And here is the code doing row by row processing:

rwijk@ORA11GR1> declare
2 cursor c_t
3 is
4 select t.status
5 from t
6 where t.status in ('A','B','C','D','E')
7 for update of t.status
8 ;
9 a pls_integer := 0;
10 b pls_integer := 0;
11 c pls_integer := 0;
12 d pls_integer := 0;
13 e pls_integer := 0;
14 begin
15 for r in c_t
16 loop
17 update t
18 set col = 'z'
19 where current of c_t
20 ;
21 case r.status
22 when 'A' then a := a + 1;
23 when 'B' then b := b + 1;
24 when 'C' then c := c + 1;
25 when 'D' then d := d + 1;
26 when 'E' then e := e + 1;
27 end case
28 ;
29 end loop
30 ;
31 print_processing_report(a,b,c,d,e);
32 end;
33 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 38 *
* Total number of B's processed: 42 *
* Total number of C's processed: 37 *
* Total number of D's processed: 39 *
* Total number of E's processed: 29 *
**************************************

PL/SQL procedure successfully completed.


When rewriting it to a single SQL statement, it is of course possible to execute another statement to retrieve the numbers per status, but that's an extra query, and a query that's executed at a different time, so you'd have to switch to a serializable isolation level, or use flashback technology to be accurate.

Or you can use aggregate functions in your returning into clause. When reading about the RETURNING INTO clause in the documentation, it talks about a "single_row_expression" being "An expression that returns a single row of a table." So it is not immediately obvious that an aggregate can be used. But it is allowed:

rwijk@ORA11GR1> rollback
2 /

Rollback complete.

rwijk@ORA11GR1> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 count(decode(status,'A',1))
13 , count(decode(status,'B',1))
14 , count(decode(status,'C',1))
15 , count(decode(status,'D',1))
16 , count(decode(status,'E',1))
17 into
18 a
19 , b
20 , c
21 , d
22 , e
23 ;
24 print_processing_report(a,b,c,d,e);
25 end;
26 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 38 *
* Total number of B's processed: 42 *
* Total number of C's processed: 37 *
* Total number of D's processed: 39 *
* Total number of E's processed: 29 *
**************************************

PL/SQL procedure successfully completed.


Needless to say, this is the fastest solution.

The funny thing though is that this exact same code doesn't work in Oracle10 (tested on 10.2.0.1 and 10.2.0.4):

rwijk@ORA10GR2> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 count(decode(status,'A',1))
13 , count(decode(status,'B',1))
14 , count(decode(status,'C',1))
15 , count(decode(status,'D',1))
16 , count(decode(status,'E',1))
17 into
18 a
19 , b
20 , c
21 , d
22 , e
23 ;
24 print_processing_report(a,b,c,d,e);
25 end;
26 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 190 *
* Total number of B's processed: 190 *
* Total number of C's processed: 190 *
* Total number of D's processed: 190 *
* Total number of E's processed: 190 *
**************************************

PL/SQL-procedure is geslaagd.


And I don't know why the above fails to produce the right numbers, but it does work on Oracle10g when using the sum aggregate function:

rwijk@ORA10GR2> rollback
2 /

Rollback is voltooid.

rwijk@ORA10GR2> declare
2 a pls_integer;
3 b pls_integer;
4 c pls_integer;
5 d pls_integer;
6 e pls_integer;
7 begin
8 update t
9 set col = 'z'
10 where status in ('A','B','C','D','E')
11 returning
12 sum(case status when 'A' then 1 else 0 end)
13 , sum(case status when 'B' then 1 else 0 end)
14 , sum(case status when 'C' then 1 else 0 end)
15 , sum(case status when 'D' then 1 else 0 end)
16 , sum(case status when 'E' then 1 else 0 end)
17 into a, b, c, d, e
18 ;
19 print_processing_report(a,b,c,d,e);
20 end;
21 /
**************************************
* P R O C E S S I N G R E P O R T *
* ================================== *
* Total number of A's processed: 36 *
* Total number of B's processed: 35 *
* Total number of C's processed: 41 *
* Total number of D's processed: 43 *
* Total number of E's processed: 35 *
**************************************

PL/SQL-procedure is geslaagd.


So yet another excuse less to write cursor for loops with DML inside.

***

Two other random nice-things-to-know about the returning into clause:

1) You can use an object type to "return into":

rwijk@ORA11GR1> create type my_object is object
2 ( col1 number
3 , col2 varchar2(1)
4 );
5 /

Type created.

rwijk@ORA11GR1> declare
2 o my_object;
3 begin
4 update t
5 set col = 'z'
6 where id = 123
7 returning my_object(t.id,t.status)
8 into o
9 ;
10 dbms_output.put_line(o.col1);
11 dbms_output.put_line(o.col2);
12 end;
13 /
123
T

PL/SQL procedure successfully completed.


2) The returning into clause doesn't work together with the "where current of" clause:

rwijk@ORA11GR1> declare
2 cursor c is select ename from emp for update of sal;
3 l_ename emp.ename%type;
4 begin
5 for r in c
6 loop
7 update emp
8 set sal = sal * 1.1
9 where current of c
10 returning ename into l_ename
11 ;
12 dbms_output.put_line(l_ename);
13 end loop;
14 end;
15 /
returning ename into l_ename
*
ERROR at line 10:
ORA-06550: line 10, column 12:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 5:
PL/SQL: SQL Statement ignored


So you'll have to mimic the current of clause by selecting and using the rowid:

rwijk@ORA11GR1> declare
2 cursor c is select rowid, ename from emp for update of sal;
3 l_ename emp.ename%type;
4 begin
5 for r in c
6 loop
7 update emp
8 set sal = sal * 1.1
9 where rowid = r.rowid
10 returning ename into l_ename
11 ;
12 dbms_output.put_line(l_ename);
13 end loop;
14 end;
15 /
SMITH
ALLEN
WARD
JONES
MARTIN
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER

PL/SQL procedure successfully completed.

Saturday, November 15, 2008

Journaling using flashback data archives - redux

Last year, with version 11.1.0.6, I investigated how useful the new flashback data archive was for implementing journaling on a table in this post. There were two main issues:

1) The documented restriction "DDLs that alter the structure of a table (such as drop/modify column, move table, drop partition, truncate table/partition, and add constraint) invalidate any existing undo data for the table. If you try to retrieve data from a time before such a DDL executed, you will get error ORA-1466." Most of these actions result in a "ORA-55610: Invalid DDL statement on history-tracked table", so existing undo is not invalidated.

2) There was one unfortunate exception to the above: the addition of constraints. They are allowed in 11.1.0.6 and invalidate the undo without a warning.

In the previous post I wrote:

So if you make the mistake of adding a constraint, you lose all journaling information... This sure is something to be aware of. I think the adding of constraints to a table with a flashback archive should also raise an ORA-55610. Hopefully Oracle will fix this in a future release or patch set.


And that's exactly what has been fixed in 11.1.0.7: adding constraints on a history-tracked table now leads to an ORA-55610. Using the same script, slightly edited for cosmetic reasons only, this is the new section where constraints are being added:

rwijk@ORA11GR1> alter table mijn_emp
2 add constraint my_pk primary key (empno)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


rwijk@ORA11GR1> alter table mijn_emp
2 add constraint my_uk1 unique (ename)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


rwijk@ORA11GR1> alter table mijn_emp
2 add constraint my_fk1 foreign key (deptno) references dept(deptno)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


rwijk@ORA11GR1> alter table mijn_emp
2 add constraint ck1 check (sal>500)
3 /
alter table mijn_emp
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


rwijk@ORA11GR1> select jn_operation
2 , jn_user
3 , to_char(jn_datetime,'dd-mm-yy hh24:mi:ss')
4 , jn_scn
5 , empno
6 , ename
7 , sal
8 , deptno
9 from mijn_emp_jn_v
10 order by empno
11 , jn_scn
12 /

J JN_USER TO_CHAR(JN_DATETI JN_SCN EMPNO ENAME SAL DEPTNO
- ------------- ----------------- ---------- ----- ---------- ----- ----------
I 19-10-08 22:40:16 3175631 7369 SMITH 800 20
U 19-10-08 22:40:19 3175634 7369 SMITH 880 20
U 19-10-08 22:40:19 3175638 7369 Smith 880 20
I 19-10-08 22:40:16 3175631 7499 ALLEN 1600 30
I 19-10-08 22:40:16 3175631 7521 WARD 1250 30
I 19-10-08 22:40:16 3175631 7566 JONES 2975 20
U 19-10-08 22:40:19 3175634 7566 JONES 3273 20
U 19-10-08 22:40:19 3175638 7566 Jones 3273 20
I 19-10-08 22:40:16 3175631 7654 MARTIN 1250 30
I 19-10-08 22:40:16 3175631 7698 BLAKE 2850 30
I 19-10-08 22:40:19 3175644 7777 VAN WIJK 1600 40
D IEMAND_ANDERS 19-10-08 22:40:52 3175705 7777 VAN WIJK 1600 40
I RWIJK 19-10-08 22:40:31 3175667 7778 ROB 1700 40
U RWIJK 19-10-08 22:40:58 3175725 7778 ROB 1800 40
I 19-10-08 22:40:16 3175631 7782 CLARK 2450 10
U 19-10-08 22:40:19 3175638 7782 Clark 2450 10
I 19-10-08 22:40:16 3175631 7788 SCOTT 3000 20
U 19-10-08 22:40:19 3175634 7788 SCOTT 3300 20
U 19-10-08 22:40:19 3175638 7788 Scott 3300 20
D 19-10-08 22:40:19 3175644 7788 Scott 3300 20
I 19-10-08 22:40:16 3175631 7839 KING 5000 10
U 19-10-08 22:40:19 3175638 7839 King 5000 10
I 19-10-08 22:40:16 3175631 7844 TURNER 1500 30
I 19-10-08 22:40:16 3175631 7876 ADAMS 1100 20
U 19-10-08 22:40:19 3175634 7876 ADAMS 1210 20
U 19-10-08 22:40:19 3175638 7876 Adams 1210 20
I 19-10-08 22:40:16 3175631 7900 JAMES 950 30
I 19-10-08 22:40:16 3175631 7902 FORD 3000 20
U 19-10-08 22:40:19 3175634 7902 FORD 3300 20
U 19-10-08 22:40:19 3175638 7902 Ford 3300 20
D 19-10-08 22:40:19 3175644 7902 Ford 3300 20
I 19-10-08 22:40:16 3175631 7934 MILLER 1300 10
U 19-10-08 22:40:19 3175638 7934 Miller 1300 10

33 rows selected.

So with this scary bug out of the way, journaling using flashback data archives becomes an even more serious option. The question that remains, is how to handle the first issue. What exactly should be done when you want some maintenance on a history-tracked table? The next part will try to provide an answer.

First let's create a tablespace and two tables and apply journaling using flashback data archive to the main table:

rwijk@ORA11GR1> create tablespace my_tablespace datafile 'extra_file.dat' size 10M
2 /

Tablespace created.

rwijk@ORA11GR1> create flashback archive flashback_archive_10_years
2 tablespace my_tablespace
3 retention 10 year
4 /

Flashback archive created.

rwijk@ORA11GR1> create table fktable (col number(10) primary key)
2 /

Table created.

rwijk@ORA11GR1> create table t
2 ( pkcol number(10) primary key
3 , fkcol number(10) not null references fktable(col)
4 , description varchar2(11)
5 )
6 flashback archive flashback_archive_10_years
7 /

Table created.

rwijk@ORA11GR1> exec dbms_lock.sleep(15)

rwijk@ORA11GR1> insert into fktable (col) values (1)
2 /

1 row created.


And add auditing to provide usernames of all actions. Auditing information is stored in the sys.aud$ table which resides in the system tablespace by default. If you want to use it for real, you'll probably want to move it to another tablespace. The dbms_audit_mgmt package can do that for you.

rwijk@ORA11GR1> audit insert,update,delete on t by access
2 /

Audit succeeded.

rwijk@ORA11GR1> begin
2 sys.dbms_audit_mgmt.set_audit_trail_location
3 ( audit_trail_type => sys.dbms_audit_mgmt.audit_trail_aud_std
4 , audit_trail_location_value => 'my_tablespace'
5 );
6 end;
7 /

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> select tablespace_name
2 from all_tables
3 where owner = 'SYS'
4 and table_name = 'AUD$'
5 /

TABLESPACE_NAME
------------------------------
MY_TABLESPACE

1 row selected.


And create a view again to be able to view the journaling information easily:

rwijk@ORA11GR1> create view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 from t versions between scn minvalue and maxvalue tv
11 , user_audit_object ao
12 where tv.versions_xid = ao.transactionid (+)
13 /

View created.


Now do some DML against table T and watch the information in view v:

rwijk@ORA11GR1> insert into t
2 select level
3 , 1
4 , lpad('*',11,'*')
5 from dual
6 connect by level <= 10
7 /

10 rows created.

rwijk@ORA11GR1> commit
2 /

Commit complete.

rwijk@ORA11GR1> update t
2 set description = 'a'
3 where pkcol = 1
4 /

1 row updated.

rwijk@ORA11GR1> commit
2 /

Commit complete.

rwijk@ORA11GR1> delete t
2 where pkcol = 7
3 /

1 row deleted.

rwijk@ORA11GR1> commit
2 /

Commit complete.

rwijk@ORA11GR1> select * from v
2 /

J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION
- ---------- --------------------- ---------- ----- ----- -----------
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********

12 rows selected.


So far the same ideas as described in the previous post. Next let's try to add a new status column with two possible values, enforced with a check constraint:

rwijk@ORA11GR1> alter table t add (status varchar2(3))
2 /

Table altered.

rwijk@ORA11GR1> alter table t add constraint ck_status check (status in ('NEW','OLD'))
2 /
alter table t add constraint ck_status check (status in ('NEW','OLD'))
*
ERROR at line 1:
ORA-55610: Invalid DDL statement on history-tracked table


As said in the beginning of this post, this generates an ORA-55610. This is the true idea behind the flashback data archives: it is tamper proof. But for journaling you need some more flexibility. So the idea is to introduce a fixed journaling table containing all the information currently present in view v, and setup a renewed table with a flashback data archive. The view v will "union all" both pieces together. Notice that this idea implies downtime for your table and/or application.

rwijk@ORA11GR1> create table t_jn
2 as
3 select jn_operation
4 , jn_user
5 , jn_datetime
6 , jn_scn
7 , pkcol
8 , fkcol
9 , description
10 , cast(null as varchar2(3)) status
11 from v
12 /

Table created.

rwijk@ORA11GR1> alter table t_jn read only
2 /

Table altered.

rwijk@ORA11GR1> create table t2
2 ( pkcol number(10) primary key
3 , fkcol number(10) not null references fktable(col)
4 , description varchar2(100)
5 , status varchar2(3) check (status in ('OLD','NEW'))
6 )
7 /

Table created.

rwijk@ORA11GR1> insert /*+ append */ into t2
2 ( pkcol
3 , fkcol
4 , description
5 , status
6 )
7 select pkcol
8 , fkcol
9 , description
10 , null
11 from t
12 /

9 rows created.

rwijk@ORA11GR1> alter table t no flashback archive
2 /

Table altered.

rwijk@ORA11GR1> drop table t purge
2 /

Table dropped.

rwijk@ORA11GR1> rename t2 to t
2 /

Table renamed.

rwijk@ORA11GR1> alter table t flashback archive flashback_archive_10_years
2 /

Table altered.

rwijk@ORA11GR1> audit insert,update,delete on t by access
2 /

Audit succeeded.


Here I simply renamed table t2 to t, but in real life you might want to rename (drop/add) some indexes and constraints as well. Next, create a new view v:

rwijk@ORA11GR1> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 from t versions between scn minvalue and maxvalue tv
12 , user_audit_object ao
13 where tv.versions_xid = ao.transactionid (+)
14 union all
15 select t.jn_operation
16 , t.jn_user
17 , t.jn_datetime
18 , t.jn_scn
19 , t.pkcol
20 , t.fkcol
21 , t.description
22 , t.status
23 from t_jn t
24 /

View created.

rwijk@ORA11GR1> exec dbms_lock.sleep(15)

PL/SQL procedure successfully completed.

rwijk@ORA11GR1> select * from v
2 /

J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- --------------------- ---------- ----- ----- ----------- ------
1 1 a
2 1 ***********
3 1 ***********
4 1 ***********
5 1 ***********
6 1 ***********
8 1 ***********
9 1 ***********
10 1 ***********
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********

21 rows selected.


This is not exactly what we want: the new rows from v without journaling information should be filtered, as they are present in the t_jn table as well. So add a "tv.versions_startscn is not null" clause:

rwijk@ORA11GR1> create or replace view v
2 as
3 select tv.versions_operation jn_operation
4 , ao.username jn_user
5 , tv.versions_starttime jn_datetime
6 , tv.versions_startscn jn_scn
7 , tv.pkcol
8 , tv.fkcol
9 , tv.description
10 , tv.status
11 from t versions between scn minvalue and maxvalue tv
12 , user_audit_object ao
13 where tv.versions_xid = ao.transactionid (+)
14 and tv.versions_startscn is not null
15 union all
16 select t.jn_operation
17 , t.jn_user
18 , t.jn_datetime
19 , t.jn_scn
20 , t.pkcol
21 , t.fkcol
22 , t.description
23 , t.status
24 from t_jn t
25 /

View created.

rwijk@ORA11GR1> select * from v
2 /

J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- --------------------- ---------- ----- ----- ----------- ------
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********

12 rows selected.


And now we can continue doing DML and having journaling:

rwijk@ORA11GR1> insert into t values ( 11, 1, 'bla', 'OLD' )
2 /

1 row created.

rwijk@ORA11GR1> commit
2 /

Commit complete.

rwijk@ORA11GR1> delete t where pkcol in (3,5)
2 /

2 rows deleted.

rwijk@ORA11GR1> commit
2 /

Commit complete.

rwijk@ORA11GR1> select * from v
2 /

J JN_USER JN_DATETIME JN_SCN PKCOL FKCOL DESCRIPTION STATUS
- ---------- --------------------- ---------- ----- ----- ----------- ------
D RWIJK 15-NOV-08 11.12.55 AM 997900 3 1 ***********
D RWIJK 15-NOV-08 11.12.55 AM 997900 5 1 ***********
I RWIJK 15-NOV-08 11.12.55 AM 997896 11 1 bla OLD
D RWIJK 15-NOV-08 11.12.36 AM 997358 7 1 ***********
U RWIJK 15-NOV-08 11.12.36 AM 997355 1 1 a
I RWIJK 15-NOV-08 11.12.36 AM 997352 1 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 2 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 3 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 4 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 5 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 6 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 7 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 8 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 9 1 ***********
I RWIJK 15-NOV-08 11.12.36 AM 997352 10 1 ***********

15 rows selected.


Note also that the new t_jn table will not automatically lose its information after the originally specified retention period. If that's what you want to happen, you'll have to schedule a job to purge the old data.