Link to home
Avatar of xoxomos

asked on

Table checksum

How do you do a checksum on an Oracle table?
Avatar of davdonin

Do you mean a checksum of all the data stored in a table?
Avatar of xoxomos


That sounds good.  Basically i want to know that there has or has not been some change.
I do not think there is such a thing built into Oracle. You can think about writing your own procedure which would calculate a checksum for the table data using defenition of the table from the dictionary. I would not do it considering LOB fields and such.

The easy workaround would be to import a table into text / dmp file at different points of time and compare them using unix 'diff' or dos 'fc' utility.

By the way, why would not you consider writing a trigger(s) on the table which would store information about when information the table is updated?
Why not use Oracle's built in auditing.  Once auditing on an object is enabled, a log of all changes to that object is kept by the database.  Keep in mind, that this may affect performance on your system.  The statement:

audit update, insert on <schema>.<table_name> by session whenever successful;

will create a record in dba_audit_trail whenever this table is successfully updated or insert into.  If you are not the DBA, you should discuss this with your DBA first.  Most users cannot enable auditing and you will probably need access to the DBA_AUDIT_TRAIL (or a view of it).
Hey ,

There is a way by which u can do the checksum on the data i.e when ever any data that is put on to the database it will check for it. i.e block corruption and other things.

If u want tell me..

hey increase the points from 50 to 200..

Srikant Sarda
Avatar of xoxomos


srikant.  The question actually starts with 'how do you ...'  not 'can you...'.  I'll put the same question out there again at 150 points and we can go from there provided the answer is something other than 'there is a way..'.  If someone comes along later and searches for 'checksum', i don't think it would be worth even 5 points to get your proposed answer.    
Avatar of xoxomos


I rejected the answer only because i did not think the question had been answered.  It would have been fine as a comment.  I explained this to srikant, posted the same question again at 150 points (q10453016).  Srikant answered the question there.
Avatar of dbalaski

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of xoxomos


Adjusted points from 50 to 100
Avatar of xoxomos


Answer accepted
Avatar of xoxomos


Thanks !!!  What i'm trying to detect is if i'm loading the same table every day which could  be an indication the data is not being refreshed at its source.
I am glad I could Help!

The O'reilly book on Oracle Design & Oracle Security have some good information on those methods of using triggers -- and a more complete discussion of the SHADOW Table & Trigger Methods

Let me know if i can be of futher help!