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

湿婆7898

数据库-与Oracle SQL Developer一起使用的Oracle Database 10g版本10.2.0.4.0

编辑抱歉:Query-1应该是:

SELECT TABLE_NAME FROM USER_TABLES;

之前是 SELECT OWNER, TABLE_NAME FROM ALL_TABLES;

输出1:我拥有的所有表

查询2: SELECT COUNT(*) FROM MYTABLE_1;

输出2:特定表MYTABLE_1中的总行数

查询3: SELECT MAX(ORA_ROWSCN) FROM MYTABLE_1;

Query-3的输出是一个数字(493672033308),该数字将在Query-4中进一步使用

查询4: SELECT SCN_TO_TIMESTAMP(493672033308) FROM DUAL;

输出4是特定表MYTABLE_1的最后更新时间

如何将所有这些结合起来以获取所有用户表的列表,该表共有3列,列标题为TABLE_NAME,TOTAL_ROWS,LAST_UPDATE_TIME

EDIT-2:最终查询:

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;

感谢APC,StevieG,鲍勃·迪伦:)

装甲运兵车

您要使用数据字典的内容来驱动查询。在过程中,只能使用动态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)。

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章