yangtingkun

分区表UNUSED列后的EXCHANGE PARTITION操作

Posted on 2012/07/22 by yangtingkun

碰到一个有意思的问题，如果分区表执行过SET UNUSED操作，那么是否还可以进行分区的EXCHANGE操作。
一个简单的测试就可以说明这个问题：

SQL> CREATE TABLE t_part_unused 
  2  (id NUMBER, name varchar2(30), other varchar2(30))
  3  partition BY range (id) 
  4  (partition p1 VALUES less than (10), 
  5  partition pmax VALUES less than (maxvalue));
TABLE created.
SQL> INSERT INTO t_part_unused 
  2  SELECT rownum, TABLE_NAME, 'abc' 
  3  FROM user_tables;
48 ROWS created.
SQL> commit;
Commit complete.
SQL> ALTER TABLE t_part_unused SET unused (other);
TABLE altered.
SQL> DESC t_part_unused
 Name                                     NULL?    TYPE
 ---------------------------------------- -------- ------------------------
 ID                                                NUMBER
 NAME                                              VARCHAR2(30)
SQL> CREATE TABLE t_temp_unused AS
  2  SELECT * 
  3  FROM t_part_unused
  4  WHERE 1 = 2;
TABLE created.
SQL> DESC t_temp_unused
 Name                                     NULL?    TYPE
 ---------------------------------------- -------- ------------------------
 ID                                                NUMBER
 NAME                                              VARCHAR2(30)
SQL> ALTER TABLE t_part_unused
  2  exchange partition p1
  3  WITH TABLE t_temp_unused;
WITH TABLE t_temp_unused
           *
ERROR at line 3:
ORA-14097: COLUMN TYPE OR SIZE mismatch IN ALTER TABLE EXCHANGE PARTITION
 
SQL> ALTER TABLE t_temp_unused ADD (other varchar2(30));
TABLE altered.
SQL> ALTER TABLE t_part_unused
  2  exchange partition p1
  3  WITH TABLE t_temp_unused;
WITH TABLE t_temp_unused
           *
ERROR at line 3:
ORA-14096: TABLES IN ALTER TABLE EXCHANGE PARTITION must have the same NUMBER OF COLUMNS
 
SQL> ALTER TABLE t_temp_unused SET unused (other);
TABLE altered.
SQL> ALTER TABLE t_part_unused
  2  exchange partition p1
  3  WITH TABLE t_temp_unused;
TABLE altered.

很明显执行了SET UNUSED操作后的表，和普通的表是存在区别的，这种区别导致要求进行EXCHANGE的表必须同样执行SET UNUSED操作，否则就无法执行EXCHANGE的操作。
当目标表中不包含SETE UNUSED的列时，EXCHANGE操作会出现ORA-14097的错误，而如果把列添加到目标表，则会报错ORA-14096，必须在目标表同样对列执行SET UNUSED操作，才能通过EXCHANGE之前的检查。
其实这也不难理解，执行SET UNUSED命令后，数据字典虽然发生了改变，但是表上的数据并没有删除，而EXCHANGE操作只是将两个段的数据字典进行互换，因此如果目标表上缺少SET UNUSED列，是无法执行EXCHANGE操作的。
解决问题的方法有两个，第一个就是例子中展示的可以在目标表上建立列然后同样的执行SET UNUSED操作；另外的一个方法就是对于SET UNUSED列执行DROP COLUMN操作，彻底删除该列。

Posted in ORACLE | Tagged ALTER TABLE, DROP COLUMN, exchange, ORA-14096, ORA-14097, partition, SET UNUSED | Leave a comment

ORA-600(kccida_kccsgfsz)错误

Posted on 2012/07/21 by yangtingkun

客户数据库10.1.0.4碰到这个ORA-600错误。
详细错误信息为：

Sat Feb 4 13:04:31 2006
ALTER DATABASE MOUNT
Sat Feb 4 13:04:31 2006
Errors IN file /oracle/admin/orcl/bdump/orcl_ckpt_122986.trc:
ORA-00600: internal error code, arguments: [kccida_kccsgfsz], [], [], [], [], [], [], []
Sat Feb 4 13:04:32 2006
Errors IN file /oracle/admin/orcl/bdump/orcl_ckpt_122986.trc:
ORA-00600: internal error code, arguments: [kccida_kccsgfsz], [], [], [], [], [], [], []
Sat Feb 4 13:04:32 2006
CKPT: terminating instance due TO error 470
Instance TERMINATED BY CKPT, pid = 122986

查询MOS发现和文档Alter Database Mount Returns ORA-3113 And ORA-600 [kccida_kccsgfsz] [ID 315112.1]描述的问题一致。导致问题的原因是客户在迁移或断电等因素导致控制文件和数据文件的格式不兼容。
在下次重启时，告警日志中出现的下面的信息也说明了这一点：

Sat Feb 4 13:20:15 2006
ALTER DATABASE mount
Sat Feb 4 13:20:15 2006
Controlfile IDENTIFIED WITH block SIZE 16384

显然导致这个问题的原因和客户之前的恢复或迁移操作有关。如果如bug所述，数据库是直接从其他平台拷贝到当前环境下，那么正确的方法肯定是通过逻辑备份EXP/EXPDP进行数据库的迁移。
而如果和当前的情况类似，由于异常导致控制文件的损坏，可以考虑从备份中进行恢复或直接重建控制文件。

Posted in BUG | Tagged control_file, kccida_kccsgfsz, mount, ORA-600 | Leave a comment

ORA-600(KSSRMP2)错误

Posted on 2012/07/20 by yangtingkun

客户的Oracle 10.1.0.4环境碰到这个错误。
详细错误信息为：

Wed DEC 14 21:10:27 2005
Errors IN file /oracle/admin/ocrl/bdump/ocrl_smon_160060.trc:
ORA-00600: internal error code, arguments: [KSSRMP2], [0x1102F0128], [0], [0], [], [], [], []
ORA-01157: cannot identify/LOCK DATA file 201 - see DBWR trace file
ORA-01110: DATA file 201: '/dev/temp_ts01'
Wed DEC 14 21:10:28 2005
Non-fatal internal error happenned while SMON was doing WORK ON sort segment.
SMON encountered 1 OUT OF maximum 100 non-fatal internal errors.
Wed DEC 14 21:10:29 2005
Starting background process QMNC
QMNC started WITH pid=24, OS id=66362
Wed DEC 14 21:10:32 2005
replication_dependency_tracking turned off (no async multimaster replication found)
Wed DEC 14 21:10:34 2005
Starting background process MMON
Starting background process MMNL
MMON started WITH pid=25, OS id=119736
MMNL started WITH pid=26, OS id=139406
Wed DEC 14 21:10:34 2005
Completed: ALTER DATABASE OPEN

显然问题发生在数据库的打开阶段，Oracle没有找到临时表空间对应的临时文件。
查询MOS，这是10.1.0.4上的bug：Bug 4075231 OERI[kssrmp2] / startup fails，这个问题在10.1.0.5中被解决。

Posted in BUG | Tagged KSSRMP2, ORA-1110, ORA-1157, ORA-600, startup | Leave a comment

10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空的解决

Posted on 2012/07/19 by yangtingkun

Oracle10g的DBA_TAB_STATISTICS视图的STATTYPE_LOCKED列没有正确的显示结果。
10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空：https://yangtingkun.net/?p=1023
上文提到了DBA_TAB_STATISTICS中的STATTYPE_LOCKED列在10g中对于分区锁定统计信息显示为空，那么在10g中有没有办法获取到正确的结果呢：

SQL> SELECT TABLE_NAME, partition_name, last_analyzed, stattype_locked
  2  FROM dba_tab_statistics
  3  WHERE owner = USER
  4  AND TABLE_NAME = 'T_PART';
TABLE_NAME                     PARTITION_NAME                 LAST_ANAL STATT
------------------------------ ------------------------------ --------- -----
T_PART                                                        16-JUL-12
T_PART                         P1
T_PART                         P2                             16-JUL-12
T_PART                         PMAX                           16-JUL-12
SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART', partname => 'P1')
BEGIN dbms_stats.gather_table_stats(USER, 'T_PART', partname => 'P1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 15027
ORA-06512: at "SYS.DBMS_STATS", line 15049
ORA-06512: at line 1

显然虽然Oracle在DBA_TAB_STATISTICS视图中没有正确的显示分区的锁定状态，但是Oracle在内部确实记录了分区的锁定状态，既然Oracle记录了这个信息，就有办法将这个信息显示出来。
既然11g能够显示该列的值，最简单的方法莫过于对比10g和11g中DBA_TAB_STATISTICS视图的区别，10g视图的结果：

SQL> SELECT text FROM dba_views WHERE view_name = 'DBA_TAB_STATISTICS';
TEXT
--------------------------------------------------------------------------------
SELECT /* TABLES */
    u.name, o.name, NULL, NULL, NULL, NULL, 'TABLE', t.rowcnt,
.
.
.
    decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* PARTITIONS,  NOT IOT */
    u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
    decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab_stats$ ts, sys.tab$ tab,
    sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* IOT Partitions */
    u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
    decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* COMPOSITE PARTITIONS */
    u.name, o.name, o.subname, tcp.part#, NULL, NULL, 'PARTITION',
.
.
.
    decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp,
    sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* SUBPARTITIONS */
    u.name, po.name, po.subname, tcp.part#,  so.subname, tsp.subpart#,
.
.
.
    decode(bitand(tab.trigflag, 67108864) + bitand(tab.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp,
    sys.tabsubpartv$ tsp,  sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* FIXED TABLES */
    'SYS', t.kqftanam, NULL, NULL, NULL, NULL, 'FIXED TABLE',
.
.
.

对比一下11g的查询结果：

SQL> SELECT text FROM dba_views WHERE view_name = 'DBA_TAB_STATISTICS';
TEXT
--------------------------------------------------------------------------------
SELECT /* TABLES */
    u.name, o.name, NULL, NULL, NULL, NULL, 'TABLE', t.rowcnt,
.
.
.
    decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
           0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* PARTITIONS,  NOT IOT */
    u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level
       */
      decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
      /*
       * Following decode returns 2 if CACHE stats locked for partition
       * or at table level
       */
      decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
      /* if 0 => not locked, 3 => data and cache stats locked */
      0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab_stats$ ts, sys.tab$ tab,
    sys.mon_mods_all$ m
.
.
.
  UNION ALL
  SELECT /* IOT Partitions */
    u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION',
.
.
.
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level
       */
      decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
      /*
       * Following decode returns 2 if CACHE stats locked for partition
       * or at table level
       */
      decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
      /* if 0 => not locked, 3 => data and cache stats locked */
      0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* COMPOSITE PARTITIONS */
    u.name, o.name, o.subname, tcp.part#, NULL, NULL, 'PARTITION',
.
.
.
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level
       */
      decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
      /*
       * Following decode returns 2 if CACHE stats locked for partition
       * or at table level
       */
      decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
      /* if 0 => not locked, 3 => data and cache stats locked */
      0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp,
    sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* SUBPARTITIONS */
    u.name, po.name, po.subname, tcp.part#,  so.subname, tsp.subpart#,
.
.
.
    decode(
      /*
       * Following decode returns 1 if DATA stats locked for partition
       * or at table level.
       * Note that dbms_stats does n't allow locking subpartition stats.
       * If the composite partition is locked, all subpartitions are
       * considered locked. Hence decode checks for tcp entry.
       */
      decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
      /*
       * Following decode returns 2 if CACHE stats locked for partition
       * or at table level
       */
      decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
      /* if 0 => not locked, 3 => data and cache stats locked */
      0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'),
.
.
.
  FROM
    sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp,
    sys.tabsubpartv$ tsp,  sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m
  WHERE
.
.
.
  UNION ALL
  SELECT /* FIXED TABLES */
.
.
.

SQL> select text from dba_views where view_name = 'DBA_TAB_STATISTICS'; TEXT -------------------------------------------------------------------------------- SELECT /* TABLES */ u.name, o.name, NULL, NULL, NULL, NULL, 'TABLE', t.rowcnt, . . . decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728), 0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL'), . . . FROM sys.user$ u, sys.obj$ o, sys.tab$ t, sys.tab_stats$ ts, sys.mon_mods_all$ m WHERE . . . UNION ALL SELECT /* PARTITIONS, NOT IOT */ u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION', . . . decode( /* * Following decode returns 1 if DATA stats locked for partition * or at table level */ decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) + /* * Following decode returns 2 if CACHE stats locked for partition * or at table level */ decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2), /* if 0 => not locked, 3 => data and cache stats locked */ 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'), . . . FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m . . . UNION ALL SELECT /* IOT Partitions */ u.name, o.name, o.subname, tp.part#, NULL, NULL, 'PARTITION', . . . decode( /* * Following decode returns 1 if DATA stats locked for partition * or at table level */ decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) + /* * Following decode returns 2 if CACHE stats locked for partition * or at table level */ decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2), /* if 0 => not locked, 3 => data and cache stats locked */ 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'), . . . FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab, sys.mon_mods_all$ m WHERE . . . UNION ALL SELECT /* COMPOSITE PARTITIONS */ u.name, o.name, o.subname, tcp.part#, NULL, NULL, 'PARTITION', . . . decode( /* * Following decode returns 1 if DATA stats locked for partition * or at table level */ decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) + /* * Following decode returns 2 if CACHE stats locked for partition * or at table level */ decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2), /* if 0 => not locked, 3 => data and cache stats locked */ 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'), . . . FROM sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp, sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m WHERE . . . UNION ALL SELECT /* SUBPARTITIONS */ u.name, po.name, po.subname, tcp.part#, so.subname, tsp.subpart#, . . . decode( /* * Following decode returns 1 if DATA stats locked for partition * or at table level. * Note that dbms_stats does n't allow locking subpartition stats. * If the composite partition is locked, all subpartitions are * considered locked. Hence decode checks for tcp entry. */ decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) + /* * Following decode returns 2 if CACHE stats locked for partition * or at table level */ decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2), /* if 0 => not locked, 3 => data and cache stats locked */ 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL'), . . . FROM sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp, sys.tabsubpartv$ tsp, sys.tab_stats$ ts, sys.tab$ tab, sys.mon_mods_all$ m WHERE . . . UNION ALL SELECT /* FIXED TABLES */ . . .

显然在11g中Oracle对于分区锁定的显示采用了新的算法，那么可以仿照11g中建立一个视图，来解决10g中分区显示存在错误的问题：

SQL> CREATE OR REPLACE VIEW DBA_TAB_STATISTICS_LOCK
  2  (OWNER, TABLE_NAME, PARTITION_NAME,
  3  SUBPARTITION_NAME, OBJECT_TYPE, STATTYPE_LOCKED)
  4  AS
  5  SELECT u.name, o.name, NULL, NULL, 'TABLE',
  6      decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728),
  7             0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL')
  8    FROM sys.user$ u, sys.obj$ o, sys.tab$ t
  9    WHERE o.owner# = u.user#
 10      AND o.obj# = t.obj#
 11      AND bitand(t.property, 1) = 0
 12      AND o.subname IS NULL
 13      AND o.namespace = 1 AND o.remoteowner IS NULL AND o.linkname IS NULL
 14      AND bitand(o.flags, 128) = 0
 15  UNION ALL
 16  SELECT u.name, o.name, o.subname, NULL, 'PARTITION',
 17      decode(
 18        decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
 19        decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
 20        0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
 21    FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab
 22    WHERE o.owner# = u.user#
 23      AND o.obj# = tp.obj#
 24      AND tp.bo# = tab.obj#
 25      AND bitand(tab.property, 64) = 0
 26      AND o.namespace = 1 AND o.remoteowner IS NULL AND o.linkname IS NULL
 27      AND bitand(o.flags, 128) = 0
 28  UNION ALL
 29    SELECT u.name, o.name, o.subname, NULL, 'PARTITION',
 30      decode(
 31        decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) +
 32        decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2),
 33        0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
 34    FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab
 35    WHERE o.owner# = u.user#
 36      AND o.obj# = tp.obj#
 37      AND tp.bo# = tab.obj#
 38      AND bitand(tab.property, 64) = 64
 39      AND o.namespace = 1 AND o.remoteowner IS NULL AND o.linkname IS NULL
 40      AND bitand(o.flags, 128) = 0
 41  UNION ALL
 42    SELECT u.name, o.name, o.subname, NULL, 'PARTITION',
 43      decode(
 44        decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
 45        decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
 46        0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
 47    FROM sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp, sys.tab$ tab
 48    WHERE o.owner# = u.user#
 49      AND o.obj# = tcp.obj#
 50      AND tcp.bo# = tab.obj#
 51      AND o.namespace = 1 AND o.remoteowner IS NULL AND o.linkname IS NULL
 52      AND bitand(o.flags, 128) = 0
 53  UNION ALL
 54    SELECT u.name, po.name, po.subname, so.subname, 'SUBPARTITION',
 55      decode(
 56        decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) +
 57        decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2),
 58        0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL')
 59    FROM sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp, sys.tabsubpartv$ tsp, sys.tab$ tab
 60    WHERE so.obj# = tsp.obj#
 61      AND po.obj# = tcp.obj#
 62      AND tcp.obj# = tsp.pobj#
 63      AND tcp.bo# = tab.obj#
 64      AND u.user# = po.owner#
 65      AND bitand(tab.property, 64) = 0
 66      AND po.namespace = 1 AND po.remoteowner IS NULL AND po.linkname IS NULL
 67      AND bitand(po.flags, 128) = 0
 68    ;
VIEW created.
SQL> SELECT TABLE_NAME, partition_name, object_type, stattype_locked 
  2  FROM dba_tab_statistics_lock
  3  WHERE owner = 'TEST'
  4  AND TABLE_NAME = 'T_PART';
TABLE_NAME                     PARTITION_NAME                 OBJECT_TYPE  STATT
------------------------------ ------------------------------ ------------ -----
T_PART                                                        TABLE
T_PART                         P1                             PARTITION    ALL
T_PART                         P2                             PARTITION
T_PART                         PMAX                           PARTITION

SQL> CREATE OR REPLACE VIEW DBA_TAB_STATISTICS_LOCK 2 (OWNER, TABLE_NAME, PARTITION_NAME, 3 SUBPARTITION_NAME, OBJECT_TYPE, STATTYPE_LOCKED) 4 AS 5 SELECT u.name, o.name, NULL, NULL, 'TABLE', 6 decode(bitand(t.trigflag, 67108864) + bitand(t.trigflag, 134217728), 7 0, NULL, 67108864, 'DATA', 134217728, 'CACHE', 'ALL') 8 FROM sys.user$ u, sys.obj$ o, sys.tab$ t 9 WHERE o.owner# = u.user# 10 and o.obj# = t.obj# 11 and bitand(t.property, 1) = 0 12 and o.subname IS NULL 13 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 14 and bitand(o.flags, 128) = 0 15 UNION ALL 16 SELECT u.name, o.name, o.subname, NULL, 'PARTITION', 17 decode( 18 decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) + 19 decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2), 20 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL') 21 FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab 22 WHERE o.owner# = u.user# 23 and o.obj# = tp.obj# 24 and tp.bo# = tab.obj# 25 and bitand(tab.property, 64) = 0 26 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 27 and bitand(o.flags, 128) = 0 28 UNION ALL 29 SELECT u.name, o.name, o.subname, NULL, 'PARTITION', 30 decode( 31 decode(bitand(tab.trigflag, 67108864) + bitand(tp.flags, 32), 0, 0, 1) + 32 decode(bitand(tab.trigflag, 134217728) + bitand(tp.flags, 64), 0, 0, 2), 33 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL') 34 FROM sys.user$ u, sys.obj$ o, sys.tabpartv$ tp, sys.tab$ tab 35 WHERE o.owner# = u.user# 36 and o.obj# = tp.obj# 37 and tp.bo# = tab.obj# 38 and bitand(tab.property, 64) = 64 39 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 40 and bitand(o.flags, 128) = 0 41 UNION ALL 42 SELECT u.name, o.name, o.subname, NULL, 'PARTITION', 43 decode( 44 decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) + 45 decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2), 46 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL') 47 FROM sys.user$ u, sys.obj$ o, sys.tabcompartv$ tcp, sys.tab$ tab 48 WHERE o.owner# = u.user# 49 and o.obj# = tcp.obj# 50 and tcp.bo# = tab.obj# 51 and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL 52 and bitand(o.flags, 128) = 0 53 UNION ALL 54 SELECT u.name, po.name, po.subname, so.subname, 'SUBPARTITION', 55 decode( 56 decode(bitand(tab.trigflag, 67108864) + bitand(tcp.flags, 32), 0, 0, 1) + 57 decode(bitand(tab.trigflag, 134217728) + bitand(tcp.flags, 64), 0, 0, 2), 58 0, NULL, 1, 'DATA', 2, 'CACHE', 'ALL') 59 FROM sys.user$ u, sys.obj$ po, sys.obj$ so, sys.tabcompartv$ tcp, sys.tabsubpartv$ tsp, sys.tab$ tab 60 WHERE so.obj# = tsp.obj# 61 and po.obj# = tcp.obj# 62 and tcp.obj# = tsp.pobj# 63 and tcp.bo# = tab.obj# 64 and u.user# = po.owner# 65 and bitand(tab.property, 64) = 0 66 and po.namespace = 1 and po.remoteowner IS NULL and po.linkname IS NULL 67 and bitand(po.flags, 128) = 0 68 ; View created. SQL> select table_name, partition_name, object_type, stattype_locked 2 from dba_tab_statistics_lock 3 where owner = 'TEST' 4 and table_name = 'T_PART'; TABLE_NAME PARTITION_NAME OBJECT_TYPE STATT ------------------------------ ------------------------------ ------------ ----- T_PART TABLE T_PART P1 PARTITION ALL T_PART P2 PARTITION T_PART PMAX PARTITION

使用新创建的这个视图，就可以解决锁定分区的统计信息显示问题。

Posted in ORACLE | Tagged 10.2, dba_tab_statistics, DBMS_STATS, lock_partition_stats, partition, stattype_locked | Leave a comment

10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空

Posted on 2012/07/18 by yangtingkun

Oracle10g的DBA_TAB_STATISTICS视图的STATTYPE_LOCKED列没有正确的显示结果。
看一个简单的例子：

SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------
Oracle DATABASE 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS FOR Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> CREATE TABLE t_part (id NUMBER, name varchar2(30))
  2  partition BY range (id)
  3  (partition p1 VALUES less than (10), 
  4  partition p2 VALUES less than (20), 
  5  partition pmax VALUES less than (maxvalue));
TABLE created.
SQL> SELECT TABLE_NAME, partition_name, stattype_locked FROM all_tab_statistics WHERE owner = USER AND TABLE_NAME = 'T_PART';
TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T_PART
T_PART                         P1
T_PART                         P2
T_PART                         PMAX
SQL> EXEC dbms_stats.lock_partition_stats(USER, 'T_PART', 'P1')
PL/SQL PROCEDURE successfully completed.
SQL> SELECT TABLE_NAME, partition_name, stattype_locked FROM all_tab_statistics WHERE owner = USER AND TABLE_NAME = 'T_PART';
TABLE_NAME                     PARTITION_NAME                 STATT
------------------------------ ------------------------------ -----
T_PART
T_PART                         P1
T_PART                         P2
T_PART                         PMAX
SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART')
PL/SQL PROCEDURE successfully completed.
SQL> SELECT TABLE_NAME, partition_name, last_analyzed, stattype_locked FROM all_tab_statistics WHERE owner = USER AND TABLE_NAME = 'T_PART';
TABLE_NAME                     PARTITION_NAME                 LAST_ANAL STATT
------------------------------ ------------------------------ --------- -----
T_PART                                                        16-JUL-12
T_PART                         P1
T_PART                         P2                             16-JUL-12
T_PART                         PMAX                           16-JUL-12

SQL> select * from v$version; BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production SQL> create table t_part (id number, name varchar2(30)) 2 partition by range (id) 3 (partition p1 values less than (10), 4 partition p2 values less than (20), 5 partition pmax values less than (maxvalue)); Table created. SQL> select table_name, partition_name, stattype_locked from all_tab_statistics where owner = user and table_name = 'T_PART'; TABLE_NAME PARTITION_NAME STATT ------------------------------ ------------------------------ ----- T_PART T_PART P1 T_PART P2 T_PART PMAX SQL> exec dbms_stats.lock_partition_stats(user, 'T_PART', 'P1') PL/SQL procedure successfully completed. SQL> select table_name, partition_name, stattype_locked from all_tab_statistics where owner = user and table_name = 'T_PART'; TABLE_NAME PARTITION_NAME STATT ------------------------------ ------------------------------ ----- T_PART T_PART P1 T_PART P2 T_PART PMAX SQL> exec dbms_stats.gather_table_stats(user, 'T_PART') PL/SQL procedure successfully completed. SQL> select table_name, partition_name, last_analyzed, stattype_locked from all_tab_statistics where owner = user and table_name = 'T_PART'; TABLE_NAME PARTITION_NAME LAST_ANAL STATT ------------------------------ ------------------------------ --------- ----- T_PART 16-JUL-12 T_PART P1 T_PART P2 16-JUL-12 T_PART PMAX 16-JUL-12

可以看到在10.2环境中，LOCK_PARTITION_STATS过程是正常工作的，但是DBA_TAB_STATISTICS视图的STATTYPE_LOCKED列并没有正确的显示分区被锁定的结果。
而对于表来说，LOCK_TABLE_STATS过程执行后，STATTYPE_LOCKED的结果显示是正常的：

SQL> EXEC dbms_stats.lock_table_stats(USER, 'T_PART') 
PL/SQL PROCEDURE successfully completed.
SQL> SELECT TABLE_NAME, partition_name, last_analyzed, stattype_locked FROM all_tab_statistics WHERE owner = USER AND TABLE_NAME = 'T_PART';
TABLE_NAME                     PARTITION_NAME                 LAST_ANAL STATT
------------------------------ ------------------------------ --------- -----
T_PART                                                        16-JUL-12 ALL
T_PART                         P1                                       ALL
T_PART                         P2                             16-JUL-12 ALL
T_PART                         PMAX                           16-JUL-12 ALL

这说明在10.2中，Oracle对于分区列的锁定的支持是存在问题的。查询了一下MOS，Oracle将这个问题确认为内部BUG：7240460，这个问题在11.1.0.7中被FIXED。
而在11.2中，这个问题以及不存在了：

SQL> SELECT * FROM v$version;
BANNER
----------------------------------------------------------------------------
Oracle DATABASE 11g Enterprise Edition 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 Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> SELECT owner, TABLE_NAME, partition_name, stattype_locked
  2  FROM dba_tab_statistics
  3  WHERE owner = 'TEST'
  4  AND TABLE_NAME = 'T_PART';
OWNER      TABLE_NAME   PARTITION_NAME  STATT
---------- ------------ --------------- -----
TEST       T_PART
TEST       T_PART       P2
TEST       T_PART       P3
TEST       T_PART       P4
TEST       T_PART       P5
TEST       T_PART       PMAX
6 ROWS selected.
SQL> EXEC dbms_stats.lock_partition_stats('TEST', 'T_PART', 'P2')
PL/SQL PROCEDURE successfully completed.
SQL> SELECT owner, TABLE_NAME, partition_name, stattype_locked
  2  FROM dba_tab_statistics
  3  WHERE owner = 'TEST'
  4  AND TABLE_NAME = 'T_PART';
OWNER      TABLE_NAME   PARTITION_NAME  STATT
---------- ------------ --------------- -----
TEST       T_PART
TEST       T_PART       P2              ALL
TEST       T_PART       P3
TEST       T_PART       P4
TEST       T_PART       P5
TEST       T_PART       PMAX
6 ROWS selected.

Posted in ORACLE | Tagged 10.2, dba_tab_statistics, DBMS_STATS, lock_partition_stats, stattype_locked | Leave a comment

ORA-4031导致CJQ进程出现ORA-1003错误

Posted on 2012/07/17 by yangtingkun

客户数据库出现ORA-4031错误，随后出现了大量的ORA-1003和ORA-604错误。
数据库版本为10.2.0.3 RAC for HP-UX，详细的报错信息为：

Mon Jul 16 15:30:30 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2389.trc:
ORA-00603: ORACLE server SESSION TERMINATED BY fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 32 bytes OF shared memory ("shared pool","select name,online$,contents...","sql area","tmp")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 32 bytes OF shared memory ("shared pool","select name,online$,contents...","sql area","tmp")
Mon Jul 16 15:30:32 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2878.trc:
ORA-00603: ORACLE server SESSION TERMINATED BY fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
Mon Jul 16 15:30:32 2012
Errors IN file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_10030.trc:
ORA-00603: ORACLE server SESSION TERMINATED BY fatal error
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable TO allocate 144 bytes OF shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol")
Mon Jul 16 15:30:39 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable TO allocate 32 bytes OF shared memory ("shared pool","select o.owner#,o.name,o.nam...","sql area","kobjn : kkdcchs")
Mon Jul 16 15:30:40 2012
Trace dumping IS performing id=[cdmp_20120716153040]
Mon Jul 16 15:32:19 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:33:59 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:35:39 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:37:19 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:39:00 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:40:40 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQ
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: err
Mon Jul 16 15:41:59 2012
Thread 2 advanced TO log SEQUENCE 61522
CURRENT log# 7 seq# 61522 mem# 0: +ORCL_CTL/orcl/onlinelog/group_7.263.611598065
Mon Jul 16 15:42:20 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:44:00 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
Mon Jul 16 15:45:40 2012
Errors IN file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc:
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQ
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2
ORA-01003: no statement parsed
ORA-00604: error occurred at recursive SQL level ORA-00604: err

Mon Jul 16 15:30:30 2012 Errors in file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2389.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select name,online$,contents...","sql area","tmp") ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select name,online$,contents...","sql area","tmp") Mon Jul 16 15:30:32 2012 Errors in file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_2878.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 144 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol") ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 144 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol") Mon Jul 16 15:30:32 2012 Errors in file /u01/app/oracle/admin/ORCL/udump/orcl2_ora_10030.trc: ORA-00603: ORACLE server session terminated by fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 144 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol") ORA-00604: error occurred at recursive SQL level 1 ORA-04031: unable to allocate 144 bytes of shared memory ("shared pool","select name,online$,contents...","Typecheck","coldef: qcopCreateCol") Mon Jul 16 15:30:39 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select o.owner#,o.name,o.nam...","sql area","kobjn : kkdcchs") Mon Jul 16 15:30:40 2012 Trace dumping is performing id=[cdmp_20120716153040] Mon Jul 16 15:32:19 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed Mon Jul 16 15:33:59 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed Mon Jul 16 15:35:39 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed Mon Jul 16 15:37:19 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed Mon Jul 16 15:39:00 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed Mon Jul 16 15:40:40 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQ ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: err Mon Jul 16 15:41:59 2012 Thread 2 advanced to log sequence 61522 Current log# 7 seq# 61522 mem# 0: +ORCL_CTL/orcl/onlinelog/group_7.263.611598065 Mon Jul 16 15:42:20 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed Mon Jul 16 15:44:00 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed Mon Jul 16 15:45:40 2012 Errors in file /u01/app/oracle/admin/ORCL/bdump/orcl2_cjq0_29269.trc: ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQ ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: error occurred at recursive SQL level 2 ORA-01003: no statement parsed ORA-00604: error occurred at recursive SQL level ORA-00604: err

可以看得，开始还是比较“正常”的ORA-4031错误，但是随着CJQ进程出现了ORA-4031的错误，数据库开始每1分40秒报一次ORA-1003和ORA-604的错误。这里CJQ进程似乎陷入了一个死循环中，持续不停的报错。查询MOS发现，在9.2中Oracle存在类似的BUG：Bug 3289063 ORA-1003 every 5 seconds after CJQ hits ORA-4031。这个bug已经在9.2.0.5和10.1.0.2中被FIXED，但是这个bug的现象和当前的错误完全一致，从这点上看，这个问题显然在10.2.0.3中仍然存在。
Oracle给出的解决方案也很简单，重启CJQ进程既可。除了在操作系统去kill -9杀掉cjq0对应的进程之外，直接通过alter system set job_queue_processes=0，然后在设置回原值，也有可能解决该问题。此外，尽量避免ORA-4031错误的产生是最根本的问题解决之道。

Posted in BUG | Tagged CJQ, JOB, ORA-1003, ORA-4031, ora-604 | Leave a comment

ORA-7445(plcurClose)错误

Posted on 2012/07/16 by yangtingkun

11.2.0.2 RAC环境，碰到这个错误。
详细错误信息为：

Tue Mar 13 14:25:17 2012
Exception [TYPE: SIGSEGV, SI_KERNEL(general_protection)] [ADDR:0x0] [PC:0x37434AD, plcurClose()+27] [flags: 0x0, COUNT: 1]
Errors IN file /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29472.trc  (incident=279439):
ORA-07445: exception encountered: core dump [plcurClose()+27] [SIGSEGV] [ADDR:0x0] [PC:0x37434AD] [SI_KERNEL(general_protection)] []
Incident details IN: /u01/app/oracle/diag/rdbms/orcl/orcl2/incident/incdir_279439/orcl2_ora_29472_i279439.trc
USE ADRCI OR Support Workbench TO package the incident.
See Note 411.1 at My Oracle Support FOR error AND packaging details.
Tue Mar 13 14:25:19 2012
Dumping diagnostic DATA IN directory=[cdmp_20120313142519], requested BY (instance=2, osid=29472), summary=[incident=279439].
Tue Mar 13 14:25:21 2012
Sweep [inc][279439]: completed
Sweep [inc2][279439]: completed

查询MOS发现问题属于ORA-07445 [PLCURCLOSE()+24] at Session Cleanup [ID 1422764.1]描述的情况，在会话清理过程中引发的bug。
这个问题确认影响的版本是11.2.0.2，Oracle通过专门的补丁11786776解决这个问题。

Posted in BUG | Tagged 11.2.0.2, ORA-7445, PLCURCLOSE, session cleanup | Leave a comment

利用tar向磁带备份恢复文件

Posted on 2012/07/15 by yangtingkun

可以利用tar命令向磁盘备份文件，同时可以利用tar从磁带中恢复指定文件。
备份的命令为：

tar cvf /dev/rmt/0m filename…

恢复的命令为：

tar xvf /dev/rmt/0m filename…

从磁带上恢复文件的例子：

> tar xvf /dev/rmt/0m /archive/orcl_exp_report_1.dmp /archive/orcl_exp_report_2.dmp
x /archive/temp_exp/acdb_exp_report_1.dmp, 3198353408 bytes, 6246784 tape blocks
x /archive/temp_exp/acdb_exp_report_2.dmp, 3078950912 bytes, 6013576 tape blocks

恢复时指定的文件名必须是备份到磁带的文件名。

Posted in OPERATING SYSTEM | Tagged /dev/rmt/0m, tape, tar | Leave a comment

密码即将过期提示的影响

Posted on 2012/07/14 by yangtingkun

当用户密码即将过期时，在登录时Oracle会提示ORA-28002错误，但是并不会影响正常的登录。
本来认为这个信息并没有太大的影响，但是没想到这个错误会导致SET AUTOTRACE功能失效：

solaris*orcl-/home/oracle$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production ON Fri Jul 13 11:27:28 2012
Copyright (c) 1982, 2011, Oracle. ALL rights reserved.
ERROR:
ORA-28002: the password will expire WITHIN 1 days
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, Oracle Label Security AND REAL Application Testing options
SQL> SET autot ON
ERROR:
ORA-28002: the password will expire WITHIN 1 days
 
SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
SQL> ALTER USER test IDENTIFIED BY test;
USER altered.
SQL> SET autot ON
ERROR:
ORA-24315: illegal attribute TYPE
 
SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report
SQL> exit
Disconnected FROM Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, Oracle Label Security AND REAL Application Testing options
solaris*orcl-/home/oracle$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production ON Fri Jul 13 11:27:52 2012
Copyright (c) 1982, 2011, Oracle. ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, Oracle Label Security AND REAL Application Testing options
SQL> SET autot ON
SQL> INSERT INTO t VALUES (1, 'a');
1 ROW created.
 
Execution Plan
----------------------------------------------------------
---------------------------------------------------------------------------------
| Id  | Operation                | Name | ROWS  | Bytes | Cost (%CPU)| TIME     |
---------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |      |     1 |    12 |     1   (0)| 00:00:01 |
|   1 |  LOAD TABLE CONVENTIONAL | T    |       |       |            |          |
---------------------------------------------------------------------------------
 
Statistics
----------------------------------------------------------
         71  recursive calls
          9  db block gets
         75  consistent gets
         10  physical reads
       1284  redo SIZE
        829  bytes sent via SQL*Net TO client
        785  bytes received via SQL*Net FROM client
          3  SQL*Net roundtrips TO/FROM client
          9  sorts (memory)
          0  sorts (disk)
          1  ROWS processed

solaris*orcl-/home/oracle$ sqlplus test/test SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 13 11:27:28 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. ERROR: ORA-28002: the password will expire within 1 days Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security and Real Application Testing options SQL> set autot on ERROR: ORA-28002: the password will expire within 1 days SP2-0619: Error while connecting SP2-0611: Error enabling STATISTICS report SQL> alter user test identified by test; User altered. SQL> set autot on ERROR: ORA-24315: illegal attribute type SP2-0619: Error while connecting SP2-0611: Error enabling STATISTICS report SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security and Real Application Testing options solaris*orcl-/home/oracle$ sqlplus test/test SQL*Plus: Release 11.2.0.3.0 Production on Fri Jul 13 11:27:52 2012 Copyright (c) 1982, 2011, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security and Real Application Testing options SQL> set autot on SQL> insert into t values (1, 'a'); 1 row created. Execution Plan ---------------------------------------------------------- --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | INSERT STATEMENT | | 1 | 12 | 1 (0)| 00:00:01 | | 1 | LOAD TABLE CONVENTIONAL | T | | | | | --------------------------------------------------------------------------------- Statistics ---------------------------------------------------------- 71 recursive calls 9 db block gets 75 consistent gets 10 physical reads 1284 redo size 829 bytes sent via SQL*Net to client 785 bytes received via SQL*Net from client 3 SQL*Net roundtrips to/from client 9 sorts (memory) 0 sorts (disk) 1 rows processed

很显然，由于ORA-28002错误导致了SET AUTOTRACE ON功能启用时碰到了错误。当修改了当前的用户密码，则ORA-28002不再出现后，SET AUTOTRACE ON的功能恢复正常。
根据上面的信息其实可以判断，在启用SET AUTOTRACE ON功能时，sqlplus会自动创建一个新的会话来记录当前会话的统计信息。
而启用的新的会话会使用当前会话登录时保存的密码来进行登录，因此可以通过下面的例子来验证这个推论：

solaris*orcl-/home/oracle$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production ON Sun Jul 15 01:28:38 2012
Copyright (c) 1982, 2011, Oracle. ALL rights reserved.
 
Connected TO:
Oracle DATABASE 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
WITH the Partitioning, Oracle Label Security AND REAL Application Testing options
SQL> SET autot ON
SQL> SET autot off
SQL> ALTER USER test IDENTIFIED BY test1;
USER altered.
SQL> SET autot ON
ERROR:
ORA-01017: invalid username/password; logon denied
 
SP2-0619: Error while connecting
SP2-0611: Error enabling STATISTICS report

果然，在用户登录后，如果当前的密码被修改，是会导致SET AUTOTRACE ON启用时报错不正确的用户名密码错误的。

Posted in ORACLE | Tagged ora-1017, ora-24315, ora-28002, password, profile, SET AUTOT ON | Leave a comment

分区表部分分区不可用导致统计信息收集失效

Posted on 2012/07/13 by yangtingkun

一个客户碰到的具体需求，分区表中有些分区所在的表空间被OFFLINE，导致在删除统计信息时报错。
下面通过例子来说明这个问题：

SQL> CREATE TABLE t_part_read (id NUMBER)
2 partition BY range (id)
3 (partition p1 VALUES less than (10) tablespace ts1, 
4 partition p2 VALUES less than (20) tablespace ts2, 
5 partition pmax VALUES less than (maxvalue) tablespace users);
TABLE created.
SQL> INSERT INTO t_part_read SELECT rownum FROM tab;
54 ROWS created.
SQL> commit;
Commit complete.
SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART_READ')
PL/SQL PROCEDURE successfully completed.
SQL> ALTER tablespace ts1 READ ONLY;
Tablespace altered.
SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART_READ')
PL/SQL PROCEDURE successfully completed.
SQL> ALTER tablespace ts1 offline;
Tablespace altered.
SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART_READ')
BEGIN dbms_stats.gather_table_stats(USER, 'T_PART_READ'); END;
*
ERROR at line 1:
ORA-00376: file 6 cannot be READ at this TIME
ORA-01110: DATA file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_7w8l5fz1_.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1

如果将表空间只读，并不会影响到表空间上的表或分区的统计信息的收集，因为收集过程只是读取，而收集的结果信息是写到SYSTEM表空间的。
但是如果分区所在的表空间处于OFFLINE状态，那么在统计信息收集的过程中就会报错。
有一个很简单的方法可以解决这个问题，就是将被OFFLINE影响的分区的统计信息锁定，这样Oracle在收集统计信息时就会跳过锁定的分区，通过这个办法就可以避免统计信息收集过程中的报错：

SQL> EXEC dbms_stats.lock_partition_stats(USER, 'T_PART_READ', 'P1')
PL/SQL PROCEDURE successfully completed.
SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART_READ')
BEGIN dbms_stats.gather_table_stats(USER, 'T_PART_READ'); END;
*
ERROR at line 1:
ORA-00376: file 6 cannot be READ at this TIME
ORA-01110: DATA file 6: '/u01/app/oracle/oradata/ORCL/datafile/o1_mf_ts1_7w8l5fz1_.dbf'
ORA-06512: at "SYS.DBMS_STATS", line 23829
ORA-06512: at "SYS.DBMS_STATS", line 23880
ORA-06512: at line 1
 
SQL> EXEC dbms_stats.gather_table_stats(USER, 'T_PART_READ', granularity => 'PARTITION') 
PL/SQL PROCEDURE successfully completed.

即使锁定分区后，尝试收集统计信息仍然报错，这是因为Oracle默认除了要收集分区上的统计信息以外，还要收集表级的统计信息，而这就会造成被OFFLINE影响的分区也要被读取。
解决方法就是在收集统计信息的时候指定收集的粒度是分区，不收集表上的GLOBAL信息。

Posted in ORACLE | Tagged DBMS_STATS, granularity, lock_partition_stats, offline, ORA-1110, ora-376, partition | Leave a comment

yangtingkun

分区表UNUSED列后的EXCHANGE PARTITION操作

ORA-600(kccida_kccsgfsz)错误

ORA-600(KSSRMP2)错误

10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空的解决

10g中DBA_TAB_STATISTICS的STATTYPE_LOCKED列对分区锁定显示为空

ORA-4031导致CJQ进程出现ORA-1003错误

ORA-7445(plcurClose)错误

利用tar向磁带备份恢复文件

密码即将过期提示的影响

分区表部分分区不可用导致统计信息收集失效

Recent Posts

Recent Comments

Archives

Categories

Meta