Posted by fsengonul on July 28, 2011 When you suggest a new method to move the data from one db to another db (previous post) , you should prove that every row is migrated successfully.
There are lots of examples for ORA_HASH implementation on the net. This is yet another one:
The sp uses listagg and ora_hash together.
The input may include the owner,table_name,partition or subpartition.
create or replace procedure GET_ORA_HASH_TABLE (owner in varchar2, table_name in varchar2,partition_name in varchar2defaultNULL,sub_partition_name in varchar2defaultNULL,hash_value out varchar2) is l_all_columns varchar2(4000); v_dyntask varchar2(20000); CURSOR get_columns(p_owner varchar2,p_table_name varchar2) IS select listagg(column_name,`' |
'`) WITHIN GROUP (order by column_id) all_columns from dba_tab_columns where owner=p_owner and table_name=p_table_name;BEGIN open get_columns(owner,table_name); fetch get_columns into l_all_columns; close get_columns; v_dyntask := 'select sum(ora_hash('` |
l_all_columns | `')) from '` |
owner | `'.'` |
table_name ;` if sub_partition_name is NOT NULL THEN `v_dyntask := v_dyntask |
`' subpartition ('` |
sub_partition_name | `')'; else if partition_name is NOT NULL THEN `v_dyntask := v_dyntask |
`' partition ('` |
partition_name | `')'; end if; end if; execute immediate v_dyntask into hash_value ;END; |
|---|
Usage on subpartitions :
SQL> SET SERVEROUTPUT ON;SQL> declare 2hash_value varchar2(4000); 3BEGIN 4GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','','SP2011JAN01_01',hash_value); 5DBMS_OUTPUT.PUT_LINE(hash_value); 6END; 7/43437576967369636PL/SQL procedure successfully completed. |
|---|
And on partitions:
SQL> r 1declare 2hash_value varchar2(4000); 3BEGIN 4GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','P2011JAN01','',hash_value); 5DBMS_OUTPUT.PUT_LINE(hash_value); 6* END;695708730528399811PL/SQL procedure successfully completed.SQL> |
|---|
And on table:
1declare 2hash_value varchar2(4000); 3BEGIN 4GET_ORA_HASH_TABLE('OWNER','TABLE_NAME','','',hash_value); 5DBMS_OUTPUT.PUT_LINE(hash_value); 6* END; |
|---|
Advertisements### Share this:
- More
4 bloggers like this.### _Related_ impdp via dblink on partitions and tbl$or$idx$part$num (It's not a curse, just a function)In "oracle"
Finding out the tables in a query: a free SQLParserIn "oracle"
Mind The Gap in APEX graph : nonexistent values in time series and model clauseIn "oracle"
This entry was posted on July 28, 2011 at 16:40 and is filed under [oracle](https://ferhatsengonul.wordpress.com/category/oracle/).
You can follow any responses to this entry through the [RSS 2.0](https://ferhatsengonul.wordpress.com/2011/07/28/ora_hash-to-compare-two-tablessubpartitions/feed/) feed. You can [leave a response](https://ferhatsengonul.wordpress.com/2011/07/28/ora_hash-to-compare-two-tablessubpartitions/#respond), or [trackback](https://ferhatsengonul.wordpress.com/2011/07/28/ora_hash-to-compare-two-tablessubpartitions/trackback/) from your own site.