如何将本地 CLOB 列与远程数据库实例中的 CLOB 列进行比较

发布于 2024-12-06 01:31:45 字数 318 浏览 1 评论 0原文

我想验证 2 个 CLOB 列中的数据在 2 个不同的实例上是否相同。如果这些是 VARCHAR2 列，我可以使用 MINUS 或联接来确定行是否在一个实例中或另一个实例中。不幸的是，Oracle 不允许您对 CLOB 列执行设置操作。

如何比较 2 个 CLOB 列，其中一列位于本地实例中，另一列位于远程实例中？

示例表结构：

CREATE OR REPLACE TABLE X.TEXT_TABLE
(   ID VARCHAR2,
  NAME VARCHAR2,
  TEXT CLOB
);

原文

I want to verify that the data in 2 CLOB columns is the same on 2 different instances. If these were VARCHAR2 columns, I could use a MINUS or a join to determine if rows were in one instance or the other. Unfortunately, Oracle does not allow you to perform set operations on CLOB columns.

How do I compare 2 CLOB columns, one of which is in my local instance and one that is in a remote instance?

Example table structure:

CREATE OR REPLACE TABLE X.TEXT_TABLE
(   ID VARCHAR2,
  NAME VARCHAR2,
  TEXT CLOB
);

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

优雅的叶子 2024-12-13 01:31:45

您可以使用 Oracle 全局临时表将 CLOB 临时拉取到本地实例。然后，您可以使用 DBMS_LOB.COMPARE 函数来比较 CLOB 列。

如果此查询返回任何行，则 CLOB 不同（或多或少的字符、换行符等），或者其中一行仅存在于其中之一中实例。

--Create temporary table to store the text in
CREATE GLOBAL TEMPORARY TABLE X.TEMP_TEXT_TABLE
ON COMMIT DELETE ROWS
AS
   SELECT * FROM X.TEXT_TABLE@REMOTE_DB;

--Use this statement if you need to refresh the TEMP_TEXT_TABLE table
INSERT INTO X.TEMP_TEXT_TABLE
SELECT * FROM X.TEXT_TABLE@REMOTE_DB;

--Do the comparision   
SELECT DISTINCT
       TARGET.NAME TARGET_NAME
      ,SOURCE.NAME SOURCE_NAME
      ,DBMS_LOB.COMPARE (TARGET.TEXT, SOURCE.TEXT) AS COMPARISON
  FROM    (SELECT ID, NAME, TEXT FROM X.TEMP_TEXT_TABLE) TARGET
       FULL OUTER JOIN
          (SELECT ID, NAME, TEXT FROM X.TEXT_TABLE) SOURCE
       ON TARGET.ID = SOURCE.ID
 WHERE DBMS_LOB.COMPARE (TARGET.TEXT, SOURCE.TEXT) <> 0
    OR  DBMS_LOB.COMPARE (TARGETTEXT, SOURCE.TEXT) IS NULL;

You can use an Oracle global temporary table to pull the CLOBs over to your local instance temporarily. You can then use the DBMS_LOB.COMPARE function to compare the CLOB columns.

If this query returns any rows, the CLOBs are different (more or less characters, newlines, etc) or one of the rows exists in only one of the instances.

--Create temporary table to store the text in
CREATE GLOBAL TEMPORARY TABLE X.TEMP_TEXT_TABLE
ON COMMIT DELETE ROWS
AS
   SELECT * FROM X.TEXT_TABLE@REMOTE_DB;

--Use this statement if you need to refresh the TEMP_TEXT_TABLE table
INSERT INTO X.TEMP_TEXT_TABLE
SELECT * FROM X.TEXT_TABLE@REMOTE_DB;

--Do the comparision   
SELECT DISTINCT
       TARGET.NAME TARGET_NAME
      ,SOURCE.NAME SOURCE_NAME
      ,DBMS_LOB.COMPARE (TARGET.TEXT, SOURCE.TEXT) AS COMPARISON
  FROM    (SELECT ID, NAME, TEXT FROM X.TEMP_TEXT_TABLE) TARGET
       FULL OUTER JOIN
          (SELECT ID, NAME, TEXT FROM X.TEXT_TABLE) SOURCE
       ON TARGET.ID = SOURCE.ID
 WHERE DBMS_LOB.COMPARE (TARGET.TEXT, SOURCE.TEXT) <> 0
    OR  DBMS_LOB.COMPARE (TARGETTEXT, SOURCE.TEXT) IS NULL;

回复收藏 0 原文

溺孤伤于心 2024-12-13 01:31:45

您可以使用DBMS_SQLHASH来比较相关数据的哈希值。与移动和比较 CLOB 相比，这应该使用更少的 IO。下面的查询只会告诉您整个表中是否存在任何差异，但您可以缩小范围。

select sys.dbms_sqlhash.gethash(sqltext => 'select text from text_table'
    ,digest_type => 1/*MD4*/) from dual
minus
select sys.dbms_sqlhash.gethash(sqltext => 'select text from text_table@remoteDB'
    ,digest_type => 1/*MD4*/) from dual;

You can use DBMS_SQLHASH to compare the hashes of the relevant data. This should use significantly less IO than moving and comparing the CLOBs. The query below will just tell you if there are any differences in the entire table, but you can narrow it down.

select sys.dbms_sqlhash.gethash(sqltext => 'select text from text_table'
    ,digest_type => 1/*MD4*/) from dual
minus
select sys.dbms_sqlhash.gethash(sqltext => 'select text from text_table@remoteDB'
    ,digest_type => 1/*MD4*/) from dual;

回复收藏 0 原文

~没有更多了~