oracle 19624,续：跨平台版本迁移之 XTTS 方案操作指南

1 SELECT owner, groupid, COUNT (*)

2 FROM SYSTEM.checkout_table

3* GROUP BY owner, groupid,dbnme Order by owner,groupid

14:05:21 SQL> SELECT owner, groupid, COUNT (*)

14:05:31 2 FROM SYSTEM.checkout_table

14:05:32 3 GROUP BY owner, groupid,dbnme Order by owner,groupid;

OWNER GROUPID COUNT(*)

------------------------------ ---------- ----------

LUOKLE 1 32

LUOKLE 2 31

LUOKLE 3 31

LUOKLE 4 31

LUOKLE 5 31

LUOKLE 6 31

LUOKLE 7 31

LUOKLE 8 31

LUOKLE 9 31

LUOKLE 10 31

创建 hash 函数

grant select on sys.dba_tab_columns to system;

CREATE OR REPLACE PROCEDURE SYSTEM.get_hv_of_data (

avc_owner VARCHAR2,

avc_table VARCHAR2)

AS

lvc_sql_text VARCHAR2 (30000);

ln_hash_value NUMBER;

lvc_error VARCHAR2 (100);

BEGIN

SELECT 'select /*+parallel(a,25)*/sum(dbms_utility.get_hash_value('

|| column_name_path

|| ',0,power(2,30)) ) from '

|| owner

|| '.'

|| table_name

|| ' a '

INTO LVC_SQL_TEXT

FROM (SELECT owner,

table_name,

column_name_path,

ROW_NUMBER ()

OVER (PARTITION BY table_name

ORDER BY table_name, curr_level DESC)

column_name_path_rank

FROM ( SELECT owner,

table_name,

column_name,

RANK,

LEVEL AS curr_level,

LTRIM (

SYS_CONNECT_BY_PATH (column_name, '||''|''||'),

'||''|''||')

column_name_path

FROM ( SELECT owner,

table_name,

'"' || column_name || '"' column_name,

ROW_NUMBER ()

OVER (PARTITION BY table_name

ORDER BY table_name, column_name)

RANK

FROM dba_tab_columns

WHERE owner = UPPER (avc_owner)

AND table_name = UPPER (avc_table)

AND DATA_TYPE IN ('TIMESTAMP(3)',

'INTERVAL DAY(3) TO SECOND(0)',

'TIMESTAMP(6)',

'NVARCHAR2',

'CHAR',

'BINARY_DOUBLE',

'NCHAR',

'DATE',

'RAW',

'TIMESTAMP(6)',

'VARCHAR2',

'NUMBER')

ORDER BY table_name, column_name)

CONNECT BY table_name = PRIOR table_name

AND RANK - 1 = PRIOR RANK))

WHERE column_name_path_rank = 1;

EXECUTE IMMEDIATE lvc_sql_text INTO ln_hash_value;

lvc_sql_text :=

'insert into system.get_has_value(owner,table_name,value) values(:x1,:x2,:x3)';

EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, ln_hash_value;

commit;

DBMS_OUTPUT.put_line (

avc_owner || '.' || avc_table || ' ' || ln_hash_value);

EXCEPTION

WHEN NO_DATA_FOUND

THEN

lvc_error := 'NO DATA FOUND';

lvc_sql_text :=

'insert into system.get_has_value(owner,table_name,error) values(:x1,:x2,:x3)';

EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, lvc_error;

commit;

WHEN OTHERS

THEN

lvc_sql_text :=

'insert into system.get_has_value(owner,table_name,value) values(:x1,:x2,:x3)';

EXECUTE IMMEDIATE lvc_sql_text USING avc_owner, avc_table, SQLERRM;

commit;

END;

/

sqlplus system/oracle<

set heading off linesize 170 pagesize 0 feedback off echo off trimout on trimspool on termout off verify off

exit;

EOF

nohup ./check_source.sh LUOKLE 1 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 2 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 3 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 4 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 5 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 6 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 7 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 8 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 9 >./source_LUOKLE_cd_1.log 2>&1 &

nohup ./check_source.sh LUOKLE 10 >./source_LUOKLE_cd_1.log 2>&1 &

checkdata_source.sh

date

sqlplus system/oracle<

set heading off linesize 170 pagesize 0 feedback off

spool source_check_$1_$2.sql

SELECT 'exec system.get_hv_of_data('''

|| owner

|| ''','''

|| table_name

|| ''')'

FROM system.checkout_table

WHERE owner = UPPER ('$1') and groupid=$2

AND table_name NOT IN (SELECT table_name

FROM dba_tables

WHERE owner = UPPER ('$1')

AND iot_type IS NOT NULL)

AND table_name IN (SELECT table_name

FROM ( SELECT table_name, COUNT (*)

FROM dba_tab_columns

WHERE owner = UPPER ('$1')

AND DATA_TYPE IN ('TIMESTAMP(3)',

'INTERVAL DAY(3) TO SECOND(0)',

'TIMESTAMP(6)',

'NVARCHAR2',

'CHAR',

'BINARY_DOUBLE',

'NCHAR',

'DATE',

'RAW',

'VARCHAR2',

'NUMBER')

GROUP BY table_name

HAVING COUNT (*) > 0))

ORDER BY table_name;

spool off

set serveroutput on

@source_check_$1_$2.sql

exit;

EOF

date

运行 hash 计算函数脚本，在LINUX环境对 LUOKLE 下所有表进行 hash 计算耗时30分钟，总共311张表，有52张表没有计算出 hash 经分析发现这些表为空表。

SQL> select count(*) from LUOKLE.XXXX;

COUNT(*)

----------

0