[20140814]oerr for windows.txt

[20140814 ]oerr for windows.txt

--今天系統出現錯誤,我不小心在windows執行.

d:\tools\rlwrap>oerr ora 4031
ORACLE_HOME not set.  Contact Oracle Support Services.

--說明windows下存在oerr命令.我使用的是12c,按照道理以前的版本都沒有在windows下oerr版本.

E:\>set ORACLE_HOME=E:\app\Administrator\product\12.1.0\dbhome_1

E:\>oerr ora 4031
ORACLE_HOME not set.  Contact Oracle Support Services.

--我檢查檔案的位置:

E:\app\Administrator\product\12.1.0\dbhome_1\BIN>ls -l oerr*.*
-rwxrwxrwx   1 user     group         971 Oct  9  2011 oerr.bat
-rw-rw-rw-   1 user     group        5114 Nov 10  2011 oerr.pl

--檢查oerr.bat檔案發現呼叫如下:
set ORACLE_HOME=%s_OracleHome%
....

--我顯然沒有設定s_OracleHome這個變數,我直接修改檔案變成.
rem set ORACLE_HOME=%s_OracleHome%
set ORACLE_HOME=E:\app\Administrator\product\12.1.0\dbhome_1

E:\app\Administrator\product\12.1.0\dbhome_1\BIN>oerr ora 4031
oerr: Cannot access the message file E:\app\Administrator\product\12.1.0\dbhome_1\rdbms\mesg\oraus.msg
No such file or directory

--檢查發現確實沒有這個檔案,存在oraus.msb檔案,這個應該是從msg編譯過來的.難道12c for windows的版本編譯後刪除msg檔案,或者根本沒有copy.
--我沒有安裝12c的linux版本,我從11g for linux(64bit)複製一個過來.

E:\app\Administrator\product\12.1.0\dbhome_1\BIN>oerr ora 4031
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause:  More shared memory is needed than was allocated in the shared
//          pool or Streams pool.
// *Action: If the shared pool is out of memory, either use the
//          DBMS_SHARED_POOL package to pin large packages,
//          reduce your use of shared memory, or increase the amount of
//          available shared memory by increasing the value of the
//          initialization parameters SHARED_POOL_RESERVED_SIZE and
//          SHARED_POOL_SIZE.
//          If the large pool is out of memory, increase the initialization
//          parameter LARGE_POOL_SIZE.
//          If the error is issued from an Oracle Streams or XStream process,
//          increase the initialization parameter STREAMS_POOL_SIZE or increase
//          the capture or apply parameter MAX_SGA_SIZE.

--順便學習一下msg如何轉換msb的.檢查bin目錄,猜測應該是lmsgen命令.

$ lmsgen
NLS Binary Message File Generation Utility: Version 11.2.0.3.0 - Production
Copyright (c) Oracle 1979, 2004.  All rights reserved.
CORE    11.2.0.3.0      Production
Incorrect number of arguments specified!
Syntax:
LMSGEN [language] [-i indir] [-o outdir]

Where is a message text file
         the name of the product
        the name of the facility
      [language]  optional message language in
                  _. format
                  This is required if message file is not tagged properly
                  with language
      [-i indir]  optional directory where to locate the text file
      [-o outdir] optional directory where to put the generated binary file.

--試著修改sqlplus返回提示看看
$ echo $ORACLE_HOME
/u01/app/oracle11g/product/11.2.0/db_2

$ grep sp1 facility.lis
sp1:sqlplus:*:

--可以確定目錄在/u01/app/oracle11g/product/11.2.0/db_2/sqlplus/mesg
--修改sp1us.msg如下:
00005,0, "1 row selected."
--換成
00005,0, "1 row selected.^_^!!!"

--在編譯前做一個備份,儲存原來的msb檔案.
$ cd /u01/app/oracle11g/product/11.2.0/db_2/sqlplus/mesg
$ cp sp1us.msb sp1us.msb.ORG
# lmsgen sp1us.msg sqlplus sp1 american
NLS Binary Message File Generation Utility: Version 11.2.0.3.0 - Production
Copyright (c) Oracle 1979, 2004.  All rights reserved.
CORE    11.2.0.3.0      Production

--測試看看是否有效.

SCOTT@test> select * from dept where rownum=1;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

--看不見是因為沒有開啟feedback.

SCOTT@test> set feedback  on
SCOTT@test> select * from dept where rownum=1;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

1 row selected.^_^!!!

--OK,確實有效!