Oracle 使用当前时间戳更新所有数据库表

嗨，

我有一个生产数据库 (版本如下所示)，有大约2000个表，每个有超过1000万行。

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Release 11.2.0.3.0 - 64bit Production                       
PL/SQL Release 11.2.0.3.0 - Production                                          
CORE 11.2.0.3.0 Production                                                      
TNS for Linux: Version 11.2.0.3.0 - Production                                  
NLSRTL Version 11.2.0.3.0 - Production   
复制

为了解决冲突，我在这些表中添加了一个时间戳列。用当前时间戳更新每行的最有效方法是什么，最少的表锁定周期和可能的存档日志生成周期，并提交每10,000行？

SET SERVEROUTPUT ON SIZE UNLIMITED

DECLARE
    table_own    VARCHAR2 (30);
    table_name   VARCHAR2 (30);
    l_time       TIMESTAMP := SYSTIMESTAMP;
    sqlstmt      VARCHAR2 (1000);

    CURSOR table_cur
    IS
          SELECT a.owner, a.table_name
            FROM dba_tables a, dba_segments b
           WHERE     a.TABLE_NAME = b.SEGMENT_NAME
                 AND a.owner = b.owner
                 AND a.owner NOT IN ('ANONYMOUS',
                                     'APEX_030200',
                                     'APEX_PUBLIC_USER',
                                     'APPQOSSYS',
                                     'CTXSYS',
                                     'DBSNMP',
                                     'DIP',
                                     'EXFSYS',
                                     'FLOWS_FILES',
                                     'MDSYS',
                                     'ORACLE_OCM',
                                     'ORDDATA',
                                     'ORDPLUGINS',
                                     'ORDSYS',
                                     'OUTLN',
                                     'MDDATA',
                                     'MGMT_VIEW',
                                     'OLAPSYS',
                                     'OWBSYS',
                                     'OWBSYS_AUDIT',
                                     'SPATIAL_CSW_ADMIN_USR',
                                     'SPATIAL_WFS_ADMIN_USR',
                                     'SI_INFORMTN_SCHEMA',
                                     'SYS',
                                     'SYSTEM',
                                     'WMSYS',
                                     'XDB',
                                     'XS$NULL',
                                     'SYSMAN',
                                     'OLAPSYS')
                 AND (a.owner, a.table_name) NOT IN
                         (SELECT owner, table_name
                            FROM dba_tab_columns
                           WHERE     column_name = 'LAST_DML_TIME'
                                 AND NUM_NULLS IS NOT NULL)
        ORDER BY b.bytes / 1024 / 1024 / 1024 DESC;
BEGIN
    OPEN table_cur;

    LOOP
        FETCH table_cur INTO table_own, table_name;

        EXIT WHEN table_cur%NOTFOUND;

        sqlstmt :=
               'update '
            || table_own
            || '.'
            || table_name
            || ' set last_dml_time = '''
            || l_time
            || '''';

        EXECUTE IMMEDIATE sqlstmt;

        i := i + 1;

        IF MOD (i, 10000) = 0
        THEN
            COMMIT;
        END IF;
    END LOOP;

    CLOSE table_cur;
END;
/
复制

3次运行的平均时间为3天，它是标准版数据库，因此我无法生成AWR报告。谢谢

你已经错过了最快的方法:

将列添加为not null，默认为sysdate!

这是从11g开始的 “即时” 操作:

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:2575782100346318728

您可以删除新列，然后再次添加。但是删除列会产生大量重做，并具有锁定含义。

幸运的是有一个快捷方式: 将列设置为未使用。这也是一次 “即时” 行动。然后，您可以将其添加回默认值，而不是null:

create table t as 
  select level x from dual
  connect by level <= 10000;
  
alter table t add ( dt timestamp );

alter table t set unused column dt;

alter table t add dt timestamp default systimestamp not null ;

desc t

Name   Null?      Type           
X                 NUMBER         
DT     NOT NULL   TIMESTAMP(6)  
复制

如果您不想这样做，则下一个最快的方法是执行 “创建即选择”。通过选择sysdate此处设置更新日期:

create table t_updated as 
  select c1, c2, ..., sysdate last_updated
  from   t
复制

您可以使用dbms_redefinition在线执行此操作:

https://oracle-base.com/articles/10g/online-table-redefinition-enhancements-10gr1
https://oracle-base.com/articles/11g/online-table-redefinition-enhancements-11gr1

但这仍然是一个问题。添加默认列是我的首选方法。