|
Well, I do not know of a specific checksum other than Oracle's internal Block checksum --
I read your other question and I do not feel it is what you are looking for, here is the definition of what it does:
DB_BLOCK_CHECKSUM determines whether DBWn and the direct loader will calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are verified when a block is read only if this parameter is TRUE and the last write of the block stored a checksum. In addition, Oracle will give every log block a checksum before writing it to the current log.
If this parameter is set to FALSE, DBWn calculates checksums only for the SYSTEM tablespace, but not for user tablespaces. Checksums allow Oracle to detect corruption caused by underlying disks, storage systems, or I/O systems. Turning on this feature typically causes only 1% to 2% overhead. Therefore, Oracle Corporation recommends that you set DB_BLOCK_CHECKSUM to TRUE.
So, these checksums are on the Block Level, all objects reside in blocks and can span multiple blocks. If you re-org a table then the checksums will change where there has been no data change -- therefore this isn't really going to tell you anything useful. Plus this is an internal number that you are not going to have direct access to, futher making this useless your needs.
========================================
Here are some options that I feel might meet your needs:
You said your basic needs are to determine if there are changes to tables.
Well, Here are several options:
Option #1 -- Oracle Auditing.
Refer to Oracle8i Administrator's Guide chapter 24 (or the Oracle8 Admin Guide)
The data dictionary of every database has a table named SYS.AUD$, commonly referred to as the "database audit trail", that is designed to store
records auditing database statements, privileges, or schema objects.
For example, if you are auditing to gather information about database activity, determine exactly what types of activities you are tracking, audit only the activities of interest, and audit only for the amount of time necessary to gather the information you desire.
So, you can audit access on an object -- in this case, you would be interested in INSERTs, Updates, & Deletes.
To audit all Update, INSERT, and DELETE statements on all tables by all database users, and by individual audited statement, issue the following statement:
AUDIT UPDATE TABLE, INSERT TABLE, DELETE TABLE
BY ACCESS;
Now, query the DBA_AUDIT_TRAIL view, -- it will log if a person inserts, updates, or deletes on any table in the system.
Note: to use auditing, set the init.ora parameter AUDIT_TRAIL=true
Also, you should read the chapter on auditing before you use this.
---------------------------------------------------------------------------------
Option#2 Use triggers and additional columns to track data changes:
add additional columns to a table: USER_NAME and TIMESTAMP
then create a trigger on the table that inserts the user's name and sysdate whenever someone updates or inserts into the table (a delete would be useless to track)..... The "user" and "sysdate" are tracked internaly by oracle.
The Trigger Code and test output is below; (say the table is emp):
SQL> desc emp;
Name Null? Type
--------------- -------- --------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
USER_NAME VARCHAR2(30)
TIMESTAMP DATE
SQL> l
1 CREATE TRIGGER EMP_TRACKING
2 BEFORE INSERT or UPDATE on EMP
3 for each row
4 declare
5 begin
6 :new.user_name := user;
7 :new.timestamp := sysdate;
8* end;
SQL> insert into emp (EMPNO,ENAME) values(1,'test');
1 row created.
SQL> commit;
Commit complete.
SQL> select empno,ename,USER_NAME,TIMESTAMP from emp;
EMPNO ENAME USER_NAME TIMESTAMP
---------- ---------- ---------- ---------
1 test BALASKIDE 16-JUL-00
so now you can track all the updates and inserts and track when they occured as well as the user who did the update or insert.
Then use auditing to track deletes, and the let the user and timestamp be an in-record audit trail (I seen products do this in the past, especially for validation!).
============================================
There is a third option, using shadow tables -- I go into it if you need it.
Hope you find this helpful and it gives you an answer.
sincerely,
dB
|