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.
No comments:
Post a Comment