| Unique hash based on user database objects. [message #643927] |
Wed, 21 October 2015 18:09  |
tmcallister
Messages: 107
Registered: December 2007
|
Senior Member |
|
|
I'd like to create a unique hash for database schema objects.
Something along of lines of the following:
WITH tabcols AS (SELECT SUBSTR(TRIM(table_name || column_name || data_type || data_length || data_precision || data_scale || nullable || column_id), 1, 4) tabcol FROM user_tab_columns),
hashexpr AS (SELECT LISTAGG(tabcol) WITHIN GROUP (ORDER BY tabcol) expr FROM tabcols)
SELECT ORA_HASH(expr) hash
FROM hashexpr
Note: depending on this size of your database you may need to adjust the substr size to prevent ORA-1489
Problems:
(1) I obviously don't want to use the substr command.
(2) I'm not including all objects. Packages, procedures, functions, indexes, keys, constraints, etc should all be included.
It occurs to me that someone else has probably wanted to do this before, and I probably don't need to reinvent the wheel here. If there is an easy shortcut or already completed solution I'd love to hear it; otherwise I'll start progressing using clobs and/or plsql; etc.
[Updated on: Wed, 21 October 2015 18:14] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Unique hash based on user database objects. [message #643937 is a reply to message #643929] |
Thu, 22 October 2015 09:34  |
tmcallister
Messages: 107
Registered: December 2007
|
Senior Member |
|
|
I'm trying to create a unique hash, in order to give a each specific representation of a schema a type of version number and to easily identify any modifications by comparing that single value.
My idea for doing that is to concatenate all the tables, columns, functions (including code), procedures, packages, triggers, sequences, etc together and hashing them.
[Updated on: Thu, 22 October 2015 09:50] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Unique hash based on user database objects. [message #643946 is a reply to message #643938] |
Thu, 22 October 2015 16:33 |
tmcallister
Messages: 107
Registered: December 2007
|
Senior Member |
|
|
BlackSwan wrote on Thu, 22 October 2015 08:37tmcallister wrote on Thu, 22 October 2015 07:34I'm trying to create a unique hash, in order to give a each specific representation of a schema a type of version number and to easily identify any modifications by comparing that single value.
such as USER_OBJECTS.LAST_DDL
I see LAST_DDL_TIME? I don't think either would satisfy my needs.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
