Oracle中表比较 对比准备与结果分析（oracle一张表比较）

Oracle 中表比较: 对比准备与结果分析

数据库管理系统是现代软件系统中最重要的组成部分之一，它极大地影响着数据管理的效率和稳定性。当需要比较两个表的数据时，Oracle 提供了许多方式。在本文中，我们将介绍在 Oracle 数据库中比较两个表的数据的准备和分析结果的方法。

比较数据表在数据库管理中很常见。例如，在项目启动时可能需要比较存储在测试库和生产库中的表中的数据是否相同。在这种情况下，可以使用 Oracle 提供的工具比较表中数据的方式来执行此任务。

要使用 Oracle 中的表比较功能，需要先安装 Oracle 数据库软件，并获得相应的访问权限。在本文中，我们将使用以下 SQL 语句创建两个表并插入数据，出于演示目的，我们使用较小的表：

CREATE TABLE table1 (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  value INT
);
CREATE TABLE table2 (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  value INT
);

INSERT INTO table1 (id, name, value) VALUES (1, 'first', 10);
INSERT INTO table1 (id, name, value) VALUES (2, 'second', 20);
INSERT INTO table1 (id, name, value) VALUES (3, 'third', 30);

INSERT INTO table2 (id, name, value) VALUES (1, 'first', 10);
INSERT INTO table2 (id, name, value) VALUES (2, 'second', 25);
INSERT INTO table2 (id, name, value) VALUES (3, 'third', 30);

在这个例子中，我们创建了两个表，名为 `table1` 和 `table2` ，每个表包含 3 行数据。请注意，我们在 `table2` 表中更改了第二行的 `value` 值。

现在，我们将介绍 Oracle 提供的两种表比较工具：`DBMS_COMPARISON` 和 `DBMS_REDIFINITION`。

### 准备阶段

– 使用 `DBMS_COMPARISON`

`DBMS_COMPARISON` 是 Oracle 提供的一种检测数据表之间的差异的方式，通过它可以快速比较两个表的数据。在 `DBMS_COMPARISON` 中，需要定义一个相互联系的连接和两个要相互比较的表。

以下是 `DBMS_COMPARISON` 示例：

DECLARE
  v_name VARCHAR2(128);
BEGIN
  v_name := DBMS_COMPARISON.CREATE_COMPARISON (
    comparison_name => 'test_comparison',
    schema_name => USER,
    object_type => 'TABLE',
    object_name => 'table1',
    second_object_type => 'TABLE',
    second_object_name => 'table2'
  );
  DBMS_OUTPUT.PUT_LINE('Comparison created: ' || v_name);
END;

在上面的代码中，我们创建了一个名为 test_comparison 的比较任务，使用 `table1` 表的数据作为基础，比较 `table2` 表的数据。执行上述代码后，将在 SQL 开发工具的输出窗口中看到以下消息：Comparison created: test_comparison。

当我们创建好比较任务后，可以通过以下代码来执行检测：

DECLARE
  l_differences     dbms_comparison.comparison_type;
  l_diff_count      NUMBER;
BEGIN
  DBMS_COMPARISON.COMPARE (
    comparison_name => 'test_comparison',
    differences     => l_differences,
    diff_count      => l_diff_count
  );
  DBMS_OUTPUT.PUT_LINE('Differences: ' || l_diff_count);
END;

在比较结果中，l_diff_count 表示两个表不同的行数，在上面的例子中，它的值为 1。

– 使用 `DBMS_REDIFINITION`

另一种比较表中数据的方式是使用 `DBMS_REDIFINITION`。这种方法通常用于比较过程中需要迁移或重新定义表的情况。使用此方式，我们还可以自定义表之间的比较顺序与比较字段。

以下是 `DBMS_REDIFINITION` 示例：

BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE(
    uname => USER,
    orig_table => 'table1',
    int_table => 'table1_int',
    col_mapping => 'id, name, value',
    options_flag => DBMS_REDEFINITION.CONS_USE_ROWID
  );
  INSERT INTO table1_int SELECT * FROM table1;
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (
    uname => USER,
    orig_table => 'table1',
    int_table => 'table1_int',
    options_flag => DBMS_REDEFINITION.CONS_ORIG_PARAMS
  );
  DBMS_REDEFINITION.REDEF_TABLE (
    uname => USER,
    orig_table => 'table1',
    int_table => 'table1_int',
    flags => DBMS_REDEFINITION.CONS_USE_ROWID
  );
  DBMS_OUTPUT.PUT_LINE('Table redefined ');
END;

在上面的代码中，我们创建了一个名为 `table1_int` 的临时表，在该表中插入了 `table1` 中的所有数据。接下来，我们通过调用 `COPY_TABLE_DEPENDENTS` 将所有的相关对象从 `table1` 复制到 `table1_int`，并修复了两个表之间的任何错误。我们通过调用 `REDEF_TABLE` 回滚了所有更改，并复位了原始表。

准备阶段完成后，我们可以执行实际的数据表比较。

### 结果分析阶段

无论使用 `DBMS_COMPARISON` 还是 `DBMS_REDIFINITION`，在比较阶段都会产生比较结果。下面我们将介绍如何分析结果：

使用 `DBMS_COMPARISON`：

在 `DBMS_COMPARISON` 中，可以通过查看 `COMPARISON_OUTPUT` 视图来检查比较结果。

以下是 `DBMS_COMPARISON` 结果分析示例：

SELECT *
FROM dba_comparison_job_results
WHERE comparison_name = 'test_comparison';

在上面的代码中，我们检查了 `test_comparison` 的工作结果，并看到以下结果：

JOB_NAME                                   JOB_ROLE LAST_POSITION       COMPARISON_NAME
------------------------------------------ -------- -------------------- --------------
TEST_COMPARISON                            D        PREPARE              TEST_COMPARISON
TEST_COMPARISON                            D        PRECOMPARE           TEST_COMPARISON
TEST_COMPARISON                            D        COMPARE              TEST_COMPARISON
TEST_COMPARISON                            A        SYNC                 TEST_COMPARISON
TEST_COMPARISON                            A        POSTCOMPARE          TEST_COMPARISON

可以看到，你可以从上面的数据中了解比较工作的各个阶段的详细信息。

使用 `DBMS_REDEFINITION`：

在使用 `DBMS_REDEFINITION` 模块时，还有一种更直观的方法可以帮助我们分析比较结果，即查看生成的表。即，新建的临时表一定会与原始表存在差异。

使用以下示例可以查看比较结果：

SELECT * FROM table1 MINUS SELECT * FROM table1_int;
SELECT * FROM table1_int MINUS SELECT * FROM table1;

在上面的代码中，第一个语句将显示 `table1` 与 `table1_int` 之间的差异。而第二个语句则将显示 `table1_int` 与 `table1` 之间的差异，从而得到两个表中数据的差异。

总结

在这篇文章中，我们介绍了 Oracle 数据库中比较表中数据的两种方式：`DBMS_COMPARISON` 和 `DBMS_REDEFINITION`。在数据表之间存在差异的情况下，可以使用上述模块来检查表之间的差异并分析结果。无论使用哪种方法，我们都可以快速准确地比较两个表中的数据。