使用 ErrorStack 在出现报错 ORA-14402 时产生的日志量

=======================================================

0、测试结论：

测试结果：设置 ErrorStack 级别为 1 时产生 Trace 的日志量最小，大小为 308K，同时在 alert 日志中也存在记录。

1、准备测试数据：

sqlplus / as sysdba

show pdbs

alter session set container=pdb;

create table my_part_table
(
  id int not null,
  addr varchar2(20))
  partition by range (id)
 (
  partition p1 values less than (1000),
  partition P2 values less than (2000),
  partition P3 values less than (maxvalue)
 );

insert into my_part_table values(1,'addr1');
insert into my_part_table values(2,'addr2');
insert into my_part_table values(3,'addr3');

insert into my_part_table values(1001,'addr1001');
insert into my_part_table values(1002,'addr1002');
insert into my_part_table values(1003,'addr1003');

insert into my_part_table values(2001,'addr2001');
insert into my_part_table values(2002,'addr2002');
insert into my_part_table values(2003,'addr2003');

commit;

select rowid,a.* from my_part_table partition(p1) a;

select rowid,a.* from my_part_table partition(p2) a;

select rowid,a.* from my_part_table partition(p3) a;

2、设置 ErrorStack 级别为 3 时捕获跟踪详细日志：

alter session set events='14402 trace name errorstack forever,level 3';

3、执行对 Oracle 分区表分区字段更新操作：

update my_part_table set id=3001 where id=1;

4、检查产生的日志量：

set linesize 200 pagesize 999
col tracefile format a100
select spid,tracefile from v$process a where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

ls -ltrh <通过上述查询得出的 trace 文件名>

测试结果：设置 ErrorStack 级别为 3 时产生 Trace 的日志量为 5.4M，同时在 alert 日志中也存在记录。

5、设置 ErrorStack 级别为 1 时产生的日志量：

conn / as sysdba

alter session set container=pdb;

alter session set events='14402 trace name errorstack forever,level 1';

update my_part_table set id=3002 where id=2;

set linesize 200 pagesize 999
col tracefile format a100
select spid,tracefile from v$process a where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

ls -ltrh <通过上述查询得出的 trace 文件名>

测试结果：设置 ErrorStack 级别为 1 时产生 Trace 的日志量为 308K，同时在 alert 日志中也存在记录。

6、设置 ErrorStack 级别为 0 时产生的日志量：

conn / as sysdba

alter session set container=pdb;

alter session set events='14402 trace name errorstack forever,level 0';

update my_part_table set id=3003 where id=3;

set linesize 200 pagesize 999
col tracefile format a100
select spid,tracefile from v$process a where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

ls -ltrh <通过上述查询得出的 trace 文件名>

测试结果：设置 ErrorStack 级别为 0 时不产生 Trace 日志，同时在 alert 日志中不存在记录。

=======================================================

附录 1 - 测试日志：

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE   RESTRICTED
    ----------   ------------------------------       ----------             ----------
             2    PDB$SEED                        READ ONLY     NO
             3    PDB                                    READ WRITE   NO
SQL> alter session set container=pdb;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO
SQL> create table my_part_table
  2  (
  3    id int not null,
  4    addr varchar2(20))
  5    partition by range (id)
  6   (
  7    partition p1 values less than (1000),
  8    partition P2 values less than (2000),
  9    partition P3 values less than (maxvalue)
 10   );

Table created.

SQL> insert into my_part_table values(1,'addr1');

1 row created.

SQL> insert into my_part_table values(2,'addr2');

1 row created.

SQL> insert into my_part_table values(3,'addr3');

1 row created.

SQL> insert into my_part_table values(1001,'addr1001');

1 row created.

SQL> insert into my_part_table values(1002,'addr1002');

1 row created.

SQL> insert into my_part_table values(1003,'addr1003');

1 row created.

SQL> insert into my_part_table values(2001,'addr2001');

1 row created.

SQL> insert into my_part_table values(2002,'addr2002');

1 row created.

SQL> insert into my_part_table values(2003,'addr2003');

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,a.* from my_part_table partition(p1) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF28AABAAAHmxAAA          1 addr1
AAAF28AABAAAHmxAAB          2 addr2
AAAF28AABAAAHmxAAC          3 addr3

SQL> select rowid,a.* from my_part_table partition(p2) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF29AABAAAHm5AAA       1001 addr1001
AAAF29AABAAAHm5AAB       1002 addr1002
AAAF29AABAAAHm5AAC       1003 addr1003

SQL> select rowid,a.* from my_part_table partition(p3) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF2+AABAAAHnBAAA       2001 addr2001
AAAF2+AABAAAHnBAAB       2002 addr2002
AAAF2+AABAAAHnBAAC       2003 addr2003

SQL> alter session set events='14402 trace name errorstack forever,level 3';

Session altered.

SQL> update my_part_table set id=3001 where id=1;
update my_part_table set id=3001 where id=1
                                      *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> set linesize 200 pagesize 999
SQL> col tracefile format a100
SQL> select spid,tracefile from v$process a where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

SPID                     TRACEFILE
------------------------ ----------------------------------------------------------------------------------------------------
53919                    /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_53919.trc

SQL> host ls -ltrh /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_53919.trc
-rw-r----- 1 oracle asmadmin 5.4M Sep 21 10:40 /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_53919.trc

SQL> host tail -15 /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/alert_yqbdb1.log
Pluggable database PDB opened read write
Completed: alter pluggable database pdb open
2023-09-21T10:40:05.964639+08:00
PDB(3):Errors in file /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_53919.trc:
ORA-14402: updating partition key column would cause a partition change
2023-09-21T10:40:08.246821+08:00
PDB(3):*****************************************************************
PDB(3):An internal routine has requested a dump of selected redo.
PDB(3):This usually happens following a specific internal error, when
PDB(3):analysis of the redo logs will help Oracle Support with the
PDB(3):diagnosis.
PDB(3):It is recommended that you retain all the redo logs generated (by
PDB(3):all the instances) during the past 12 hours, in case additional
PDB(3):redo dumps are required to help with the diagnosis.
PDB(3):*****************************************************************

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;

Session altered.

SQL> alter session set events='14402 trace name errorstack forever,level 1';

Session altered.

SQL> update my_part_table set id=3002 where id=2;
update my_part_table set id=3002 where id=2
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> set linesize 200 pagesize 999
SQL> col tracefile format a100
SQL> select spid,tracefile from v$process a where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

SPID                     TRACEFILE
------------------------ ----------------------------------------------------------------------------------------------------
69852                    /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_69852.trc

SQL> host ls -ltrh /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_69852.trc
-rw-r----- 1 oracle asmadmin 308K Sep 21 11:05 /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_69852.trc

SQL> host tail -15 /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/alert_yqbdb1.log
PDB(3):Errors in file /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_53919.trc:
ORA-14402: updating partition key column would cause a partition change
2023-09-21T10:40:08.246821+08:00
PDB(3):*****************************************************************
PDB(3):An internal routine has requested a dump of selected redo.
PDB(3):This usually happens following a specific internal error, when
PDB(3):analysis of the redo logs will help Oracle Support with the
PDB(3):diagnosis.
PDB(3):It is recommended that you retain all the redo logs generated (by
PDB(3):all the instances) during the past 12 hours, in case additional
PDB(3):redo dumps are required to help with the diagnosis.
PDB(3):*****************************************************************
2023-09-21T11:05:30.198564+08:00
PDB(3):Errors in file /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_69852.trc:
ORA-14402: updating partition key column would cause a partition change

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;

Session altered.

SQL> alter session set events='14402 trace name errorstack forever,level 0';

Session altered.

SQL> update my_part_table set id=3003 where id=3;
update my_part_table set id=3003 where id=3
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> set linesize 200 pagesize 999
SQL> col tracefile format a100
SQL> select spid,tracefile from v$process a where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum=1));

SPID                     TRACEFILE
------------------------ ----------------------------------------------------------------------------------------------------
81453                    /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_81453.trc

SQL> host ls -ltrh /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_81453.trc
ls: cannot access /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_81453.trc: No such file or directory

SQL> host tail -15 /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/alert_yqbdb1.log
PDB(3):Errors in file /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_53919.trc:
ORA-14402: updating partition key column would cause a partition change
2023-09-21T10:40:08.246821+08:00
PDB(3):*****************************************************************
PDB(3):An internal routine has requested a dump of selected redo.
PDB(3):This usually happens following a specific internal error, when
PDB(3):analysis of the redo logs will help Oracle Support with the
PDB(3):diagnosis.
PDB(3):It is recommended that you retain all the redo logs generated (by
PDB(3):all the instances) during the past 12 hours, in case additional
PDB(3):redo dumps are required to help with the diagnosis.
PDB(3):*****************************************************************
2023-09-21T11:05:30.198564+08:00
PDB(3):Errors in file /oracle/app/oracle/diag/rdbms/yqbdb/yqbdb1/trace/yqbdb1_ora_69852.trc:
ORA-14402: updating partition key column would cause a partition change

SQL> 

附录 2 - 解决 Oracle 分区表不能更新分区字段的方法：

默认情况下，Oracle 分区表对于分区字段是不允许进行 update 操作的，如果更新就会报 ORA-14402 错误，解决方法是临时开启表的 row movement 属性：

alter table my_part_table enable row movement;

update my_part_table set id=3003 where id=3;

commit;

select rowid,a.* from my_part_table partition(p1) a;

select rowid,a.* from my_part_table partition(p2) a;

select rowid,a.* from my_part_table partition(p3) a;

alter table my_part_table disable row movement;

update my_part_table set id=3002 where id=2;

限制：对于普通表（ heap-organized ）行迁移后 rowid 会发生变化，对于索引表（ index-organized ）rowid 虽然依然有效但是其实际对应的物理构成是错误的。

测试日志：

SQL> conn / as sysdba
Connected.
SQL> alter session set container=pdb;

Session altered.

SQL> select rowid,a.* from my_part_table partition(p1) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF3HAABAAAHnJAAA          1 addr1
AAAF3HAABAAAHnJAAB          2 addr2
AAAF3HAABAAAHnJAAC          3 addr3

SQL> select rowid,a.* from my_part_table partition(p2) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF3IAABAAAHnRAAA       1001 addr1001
AAAF3IAABAAAHnRAAB       1002 addr1002
AAAF3IAABAAAHnRAAC       1003 addr1003

SQL> select rowid,a.* from my_part_table partition(p3) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF3JAABAAAHnZAAA       2001 addr2001
AAAF3JAABAAAHnZAAB       2002 addr2002
AAAF3JAABAAAHnZAAC       2003 addr2003

SQL> update my_part_table set id=3003 where id=3;
update my_part_table set id=3003 where id=3
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change

SQL> alter table my_part_table enable row movement;

Table altered.

SQL> update my_part_table set id=3003 where id=3;

1 row updated.

SQL> commit;

Commit complete.

SQL> select rowid,a.* from my_part_table partition(p1) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF3HAABAAAHnJAAA          1 addr1
AAAF3HAABAAAHnJAAB          2 addr2

SQL> select rowid,a.* from my_part_table partition(p2) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF3IAABAAAHnRAAA       1001 addr1001
AAAF3IAABAAAHnRAAB       1002 addr1002
AAAF3IAABAAAHnRAAC       1003 addr1003

SQL> select rowid,a.* from my_part_table partition(p3) a;

ROWID                      ID ADDR
------------------ ---------- --------------------
AAAF3JAABAAAHnZAAA       2001 addr2001
AAAF3JAABAAAHnZAAB       2002 addr2002
AAAF3JAABAAAHnZAAC       2003 addr2003
AAAF3JAABAAAHnZAAD       3003 addr3

SQL> alter table my_part_table disable row movement;

Table altered.

SQL> update my_part_table set id=3002 where id=2;
update my_part_table set id=3002 where id=2
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change