I need to identify if a schema in database has any change in metadata such as changed table columns or changed procedure/package PL/SQL-codes additional/deleted triggers etc. I've tried to make a expdp with content=metadata_only and calculated a checksum of the dump. But this doesn't work because the checksum changes every time despite the same unchanged database. How to identify if a schema in database (its structure) has changed or not? Do I have to export the plain text metadata instead? Thx.
CodePudding user response：
If you only need to know who did what when, use database auditing.
If you only need to know something might have changed, but don't care what and are okay with the possibility of the change not being significant, you can use the last_ddl_time from dba_objects and compare it to the last maximum value you got on the previous check. This can be done either at the schema or object level.
If you really do need to generate a delta and know for certain that something changed, you have two choices:
Construct data dictionary queries against all application dictionary views (lots of work, because there are lot of views - columns, tables, partitions, subpartitions, indexes, index partitions, index subpartitions, lobs, lob partitions, etc, etc, etc.)
(Recommended) Use dbms_metadata to extract the DDL of the entire schema. See this answer for a query that will export almost every object you would likely care about.
Either using #1 or #2, you can then compare old/new strings or use a hash function (e.g. dbms_crypto.hash) to compute a hash value and compare that. I wrote a schema upgrade tool that does exactly this - surgically identifies and upgrades individual objects that are different than some template source schema. I use dbms_metadata to look for diffs on the hash values. You will, however, need to set certain transforms to omit clauses you don't care about and that could have arbitrary changes, or mask them with regexp_replace after the fact (e.g. a sequence will contain the current value which will always be different.. you don't want to see this as a change). It can be a bit of work.