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.

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.

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.