升级到11.2.0.4的一些发现-2-其他发现

升级到11.2.0.4的一些发现-1-catupgrd.sql大致解读
升级到11.2.0.4的一些发现-3-catalog.sql的主要内容

1，如果当前连接的用户不是SYS，那么会报ORA-01722: invalid number错误：

SQL> conn / as sysdba
Connected.
SQL> show user
USER is "SYS"
SQL> SELECT TO_NUMBER('MUST_BE_AS_SYSDBA') FROM DUAL
  2  WHERE USER != 'SYS';

no rows selected

SQL> conn lunar/lunar
Connected.
SQL> show user
USER is "LUNAR"
SQL> SELECT TO_NUMBER('MUST_BE_AS_SYSDBA') FROM DUAL
  2  WHERE USER != 'SYS';
SELECT TO_NUMBER('MUST_BE_AS_SYSDBA') FROM DUAL
                 *
ERROR at line 1:
ORA-01722: invalid number


SQL> 

那么判断是否当前连接用户为LUNAR，就可以使用下面的语句：

SQL> conn lunar/lunar
Connected.
SQL> show user
USER is "LUNAR"
SQL> SELECT TO_NUMBER('MUST_BE_AS_LUNAR') FROM DUAL
  2  WHERE USER != 'LUNAR';

no rows selected

SQL> 
SQL>

同样道理，判断当前数据库版本是否为11.2.0.4:

SQL> SELECT TO_NUMBER('MUST_BE_11_2_0_3') FROM v$instance
  2  WHERE substr(version,1,8) != '11.2.0.4';

no rows selected

SQL>
2,利用11.2 的新特性，记录SQLPLUS错误日志：
CREATE TABLE sys.registry$error(username   VARCHAR(256),
                                timestamp  TIMESTAMP,
                                script     VARCHAR(1024),
                                identifier VARCHAR(256),
                                message    CLOB,
                                statement  CLOB);
                                         
DELETE FROM sys.registry$error;

set errorlogging on table sys.registry$error identifier 'RDBMS';

commit;

然后，通过下面的命令查看sqlplus的错误日志：

col timestamp format a15
col username format a15
col script format a10
col identifier format a15
col statement format a20
col message format a20
 select * from REGISTRY$ERROR;
 
SQL> CREATE TABLE LUNAR.registry$error(username   VARCHAR(256),
  2                                  timestamp  TIMESTAMP,
  3                                  script     VARCHAR(1024),
  4                                  identifier VARCHAR(256),
  5                                  message    CLOB,
  6                                  statement  CLOB);

Table created.

SQL> DELETE FROM LUNAR.registry$error;

0 rows deleted.

SQL> set errorlogging on table LUNAR.registry$error identifier 'RDBMS';
SQL> COMMIT;

Commit complete.

SQL> conn lunar/lunar
Connected.
SQL> select * from REGISTRY$ERROR;

no rows selected

SQL> insert into REGISTRY$ERROR as select * from dba_users;
insert into REGISTRY$ERROR as select * from dba_users
                           *
ERROR at line 1:
ORA-00926: missing VALUES keyword


SQL> select count(*) from REGISTRY$ERROR;

  COUNT(*)
----------
         0

SQL>

这里我们看到，并没有记录下来sqlplus的操作错误，仔细看一下，原来set errorlogging on table命令必须在当前用户下执行，例如：

SQL> set errorlogging on table LUNAR.registry$error ;
SQL> insert into REGISTRY$ERROR as select * from dba_users;
insert into REGISTRY$ERROR as select * from dba_users
                           *
ERROR at line 1:
ORA-00926: missing VALUES keyword


SQL> set linesize 167
SQL> set pages 999
SQL> col timestamp format a15
SQL> col username format a15
SQL> col script format a10
SQL> col identifier format a15
SQL> col statement format a20
SQL> col message format a20
SQL>  select * from REGISTRY$ERROR;

USERNAME        TIMESTAMP       SCRIPT     IDENTIFIER      MESSAGE              STATEMENT
--------------- --------------- ---------- --------------- -------------------- --------------------
LUNAR           03-AUG-14 05.45                            ORA-00926: missing V insert into REGISTRY
                .13.000000 PM                              ALUES keyword        $ERROR as select * f
                                                                                rom dba_users

LUNAR           03-AUG-14 05.50                            SP2-0042: unknown co g
                .25.000000 PM                              mmand "g" - rest of
                                                           line ignored.


SQL>

看，错误信息，一目了然
3, auto-bulkification by setting event 10933

Bug:6275368 PL/SQL FOR UPDATE cursor may be positioned on wrong row
              Component: RDBMS
              Fixed Ver(s): 10205 11107 112
               Symptom(s):
                - If a FOR LOOP iterates over a cursor declared in a different package, auto-bulkification occurs. This
                  may be inappropriate if the cursor's SQL statement (which would appear in the package body) contains 
                  a FOR UPDATE clause as the "CURRENT OF" may then be incorrect.
               Available Workaround(s): 
               					Manually turn off auto-bulkification by setting event 10933, level 16384 
               					and recompiling affected library units.

4，catupgrd.sql会调用catupstr.sql， 这个脚本执行过程中中，还需要依次调用：
catupses.sql
i0902000.sql——重整 props$，dependency$，mon_mods$。之后，该脚本还调用i1001000.sql。i1001000调用i1002000.sql。
在i1002000.sql有有一个有意思的操作：

Rem clear 0×00200000 (read-only table flag) in trigflag during upgrade
update tab$ set trigflag = trigflag – 2097152
where bitand(trigflag, 2097152) <> 0;
commit;
— 0×00200000转换成10进制是2097152
—-bitand，顾名思义，就是按位与操作，即：

SQL> select bitand(1,0) from dual;

BITAND(1,0)
-----------
          0

1 row selected.

SQL> select bitand(0,1) from dual ;

BITAND(0,1)
-----------
          0

1 row selected.

SQL> select bitand(1,1) from dual ;

BITAND(1,1)
-----------
          1

1 row selected.

SQL> 

SQL> select bitand(trigflag, 2097152) ,trigflag,count(*) from tab$ group by bitand(trigflag, 2097152) ,trigflag;

BITAND(TRIGFLAG,2097152)   TRIGFLAG   COUNT(*)
------------------------ ---------- ----------
                       0  201326592         18
                       0          0       1957

2 rows selected.

SQL> 
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

Session altered.

select object_id,SUBOBJECT_NAME,object_name,CREATED,LAST_DDL_TIME,STATUS from dba_objects
where object_id in(select obj# from tab$ where TRIGFLAG=201326592) order by object_id ;

SQL> select object_id,SUBOBJECT_NAME,object_name,CREATED,LAST_DDL_TIME,STATUS from