Oracle checks whether there are garbled stored procedures in table records

 

HelloJin said that the data synchronized by their OGG may contain garbled characters and the entire method should be checked. After half a day of discussion, I did not find any good method. I can only query each table and each record, and then compare it.

 

The judgment process is as follows:

View plain

/* Formatted on 10:45:33 (QP5 v5.163.1008.3004 )*/

Create or replace procedure lank_test (pv_tbname IN VARCHAR2,

Pv_owner IN VARCHAR2)

IS

Num NUMBER;

P_flag NUMBER;

P_flag2 NUMBER;

P_col VARCHAR2 (30 );

P_str VARCHAR2 (1000 );

P_tbname VARCHAR2 (30 );

P_owner VARCHAR2 (30 );

BEGIN

P_tbname: = pv_tbname;

P_owner: = pv_owner;

 

-- P_tbname: = 'lank _ f1 ';

-- P_owner: = 'sys ';

-- Dbms_output.put_line (p_tbname | p_owner );

Select count (*)

INTO p_flag

FROM dba_tab_columns

WHERE table_name = p_tbname AND owner = p_owner;

 

-- Dbms_output.put_line ('total: '| p_flag );

WHILE p_flag> 0

LOOP

SELECT column_name

INTO p_col

FROM dba_tab_columns

WHERE table_name = p_tbname AND COLUMN_ID = p_flag AND owner = p_owner;

 

-- Dbms_output.put_line (p_str );

EXECUTE IMMEDIATE

'Select count (*) from'

| P_owner

| '.'

| P_tbname

| 'Where asciistr ('

| P_col

| ') Like ''% ?? % ''Or asciistr ('

| P_col

| ') Like ''% \ FFFD % '''

INTO p_flag2;

 

IF p_flag2> 0

THEN

DBMS_OUTPUT.put_line (

P_owner | '.' | p_tbname | 'have: '| p_flag |', '| p_col );

 

Insert into resout

VALUES (p_owner,

P_tbname,

P_flag,

P_col );

 

COMMIT;

End if;

 

-- Init

P_flag2: = 0;

P_flag: = p_flag-1;

End loop;

 

Insert into table_temp

VALUES (p_owner, p_tbname );

 

COMMIT;

END lank_test;

 

This stored procedure was written by HelloJin and thanked him for his hard work.

 

The core statement here is:

'Selectcount (*) from'

| P_owner

| '.'

| P_tbname

| 'Whereasciistr ('

| P_col

| ') Like ''% ?? % ''Or asciistr ('

| P_col

| ') Like ''% \ FFFD % '''

 

We wanted to use parallel queries to improve the efficiency, because the efficiency would be very low if the table is large, but because the partition table is not used, the effect of using parallel queries is not obvious.

 

Oracle Parallel Execution (Parallel Execution): http://www.bkjia.com/database/201110/107273.html

If other students have a better way to judge, leave a message or email to me.