在Oracle Sql中合并多个查询的结果

您要使用数据字典的内容来驱动查询。在过程中，只能使用动态SQL来完成此操作。

请记住以下几点：

1. Oracle维护SCN时间戳映射以支持闪回查询。它只是在支持的UNDO_RETENTION期间保留映射。因此，我们只能将SCN_TO_TIMESTAMP（）用于具有最近活动的表。Staler表将向ORA-08181投掷。
2. 没有行的表将没有关联的SCN。如果我们将SCN的null传递给SCN_TO_TIMESTAMP（），则抛出该错误。

因此，可靠的解决方案非常复杂。这是使用DBMS_OUTPUT来显示结果的方法。其他机制可用：

declare
    n pls_integer;
    max_scn number;
    x_scn_too_old exception;
    pragma exception_init(x_scn_too_old ,-08181);
    txt varchar2(30);
begin
    for lrec in ( select table_name from user_tables )
    loop
        execute immediate 
            'select count(*), max(ora_rowscn) from '
                || lrec.table_name
                into n, max_scn;
        dbms_output.put(lrec.table_name
                                ||' count='||to_char(n));
        begin
            if n > 0 then
                select to_char(scn_to_timestamp(max_scn), 'yyyy-mm-dd hh24:mi:ss.ff3')
                into txt
                from dual;
            else
                txt := null;
            end if;
        exception
            when x_scn_too_old then
                txt := ('earlier');
        end;
        dbms_output.put_line(' ts='||txt );
    end loop;
end;
/

有一个纯SQL替代方法，使用USER_TABLES中的NUM_ROWS和USER_TAB_MODIFICATIONS视图。该视图由Oracle维护，以监视表统计信息的陈旧性。当您使用10g时，这将自动发生（在9i中，我们必须打开对特定表的监视）。

USER_TAB_MODIFICATIONS为我们在每个表上的DML活动提供了编号，这很整洁，因为我们可以将这些编号添加到NUM_ROWS中以获得准确的总数，这比发出COUNT（）更有效。

再有几点。

1. 任何缺少统计信息的表将具有NUM_ROWS = 0。因此，我在算术列中使用NVL（）
2. USER_TAB_MODIFICATIONS仅包含自上次收集统计信息以来已更改的表的数据。一旦我们在表上收集统计信息，它就会从该视图中消失，直到发出更多DML。因此，请使用外部联接。
3. 请注意，我们只会为统计数据过时的表添加时间戳。与上面使用的SCN_TO_TIMESTAMP相比，这是不可预测的，因为它取决于您的统计信息收集策略。

所以这里是：

select t.table_name
        , m.timestamp
        , t.num_rows
        , ((nvl(t.num_rows,0) + m.inserts) - m.deletes) as tot_rows
from user_tables t
     left outer join USER_TAB_MODIFICATIONS m
         on t.table_name = m.table_name
order by t.table_name
/

也许最好的解决方案是结合使用NUM_ROWS和USER_TAB_MODIFICATIONS来避免计数，并且仅检查ORA_ROWSCN是否包含最新统计信息的表。

请注意，这只是一个问题，因为您没有自己的日志记录或表审核。许多地方在其表上添加了元数据列以跟踪更改数据（例如CREATED_ON，CREATED_BY，UPDATED_ON，UPDATED_BY）。