Re: Database object checksums, v7.3.4

In article <953248065.17794.0.nnrp-04.c1ed77a9_at_news.demon.co.uk>, "Mark B" <markb_at_mrb-basys.demon.co.uk> wrote:
> Oracle Serverr 7.3.4...
>
> Does anyone know of a way I can easily generate a 'checksum' from the
> internal definition of any database object?
>
> For PL/SQL, I called Oracle support asking if I could get access to
the
> internal "signiture" used to determine whether or not packages,
functions
> and procedures need recompiling when any related packages, etc. are
> recompiled. They couldn't (or wouldn't) tell me how to access them.
>
> For those with UNIX knowledge, I'm trying to duplicate the "cksum"
command,
> but within the database. I'd really rather not
spool/dbms_output/utl_file
> the source and host a call to UNIX if it can be avoided - so I can
make it
> platform independent and not have to worry about file system space
for the
> spooled file.
>
> I'm interested in checksums for the definitions of tables, views,
sequences,
> indexes, etc. - the lot.
>
> Any simple solutions would be most welcome.
>
> Thanks in advance
>
> Mark
>

I do not think creating a check sum for database objects has any real value, but you must so here are my suggestions:

1. Sys.dba_objects is out because the create date and last ddl date in dba_objects will not work due to the fact they will change with maintenance but may still have the exact same definition and all you care about is the definition changing.
2. The dictionary table sys.dba_object_size has the sorce and parsed size for stored code and tables but these values could easily change with an Oracle version change or maybe even a patch. Still for stored code this is the best I can think of.
3. For tables, views, and indexes I would suggest you lool at sys.dba_tab_columns. It should be relatively straight forward to devise some method of counting the number of columns, assigning values to each datatype, and/or summing the lengths to produce a checksum type value for tables and views. This would be very straigth forward for tables and views. If you need to do indexes you could perform the same logic just for the columns involved.

I hope you at least find these ideas interesting. --
Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Mar 17 2000 - 10:05:21 CST