Oracle Database常用补丁集Patch号

发表于 2012 年 3 月 24 日 由 惜分飞

Patchset / PSU Patch Number 

11.2.0.4	13390677      11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER

11.2.0.3.8	16902043      DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPUOCT2013)
11.2.0.3.7	16619892      DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPUJUL2013)
11.2.0.3.6	16056266      DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPUAPR2013) 
11.2.0.3.5	14727310      DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPUJAN2013):
11.2.0.3.4	14275605      DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012)
11.2.0.3.3	13923374      DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES CPU JUL2012):
11.2.0.3.2	13696216      DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES CPU APR2012)
11.2.0.3.1	13343438      DATABASE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES CPU JAN2012)
11.2.0.3	10404530      11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
11.2.0.2.11	16619893      DATABASE PATCH SET UPDATE 11.2.0.2.11 (INCLUDES CPUJUL2013)
11.2.0.2.10	16056267      DATABASE PATCH SET UPDATE 11.2.0.2.10 (INCLUDES CPUAPR2013) 
11.2.0.2.9	14727315      DATABASE PATCH SET UPDATE 11.2.0.2.9 (INCLUDES CPUJAN2013):
11.2.0.2.8	14275621      DATABASE PATCH SET UPDATE 11.2.0.2.8 (INCLUDES CPUOCT2012)
11.2.0.2.7	13923804      DATABASE PATCH SET UPDATE 11.2.0.2.7 (INCLUDES CPU JUL2012)
11.2.0.2.6	13696224      DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPU APR2012)
11.2.0.2.5	13343424      DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPU JAN2012)
11.2.0.2.4	12827726      DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011)
11.2.0.2.3	12419331      DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011)
11.2.0.2.2	11724916      DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011)
11.2.0.2.1	10248523      DATABASE PSU 11.2.0.2.1
11.2.0.2	10098816      11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
11.2.0.1.6	12419378       DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011)
11.2.0.1.5	11724930      DATABASE PSU 11.2.0.1.5 (INCLUDES CPUAPR2011)
11.2.0.1.4	10248516      DATABASE PSU 11.2.0.1.4 (INCLUDES CPUJAN2011)
11.2.0.1.3	9952216      DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010)
11.2.0.1.2	9654983      DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010)
11.2.0.1.1	9352237      DATABASE PSU 11.2.0.1.1
        	 
11.1.0.7.16	16619896 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.16 (INCLUDES CPUJUL2013)
11.1.0.7.15	16056268  [*]      DATABASE PATCH SET UPDATE 11.1.0.7.15 (INCLUDES CPUAPR2013) 
11.1.0.7.14	14739378 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.14 (INCLUDES CPUJAN2013)
11.1.0.7.13	14275623 [*]      DATABASE PATCH SET UPDATE 11.1.0.7.13 (INCLUDES CPUOCT2012)
11.1.0.7.12	13923474      DATABASE PATCH SET UPDATE 11.1.0.7.12 (INCLUDES CPU JUL2012)
11.1.0.7.11	13621679      DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012)
11.1.0.7.10	13343461      DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES CPU JAN2012)
11.1.0.7.9	12827740      DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011)
11.1.0.7.8	12419384      DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011)
11.1.0.7.7	11724936      DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011)
11.1.0.7.6	10248531      DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011)
11.1.0.7.5	9952228      DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010)
11.1.0.7.4	9654987      DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010)
11.1.0.7.3	9352179      DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
11.1.0.7.2	9209238      DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
11.1.0.7.1	8833297      DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
11.1.0.7	6890831      11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
10.2.0.5.12	16619894 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013):
10.2.0.5.11	16056270  [*]      DATABASE PATCH SET UPDATE 10.2.0.5.11 (INCLUDES CPUAPR2013)
10.2.0.5.10	14727319 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.10 (INCLUDES CPUJAN2013):
10.2.0.5.9	14275629 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.9 (INCLUDES CPUOCT2012)
10.2.0.5.8	13923855 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.8 (INCLUDES CPU JUL2012)
10.2.0.5.7	13632743 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.7 (INCLUDES CPU APR2012)
10.2.0.5.6	13343471 [*]      DATABASE PATCH SET UPDATE 10.2.0.5.6 (INCLUDES CPU JAN2012)
10.2.0.5.5	12827745 [*]      DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011)
10.2.0.5.4	12419392      DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011)
10.2.0.5.3	11724962      DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011)
10.2.0.5.2	10248542      DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011)
10.2.0.5.1	9952230      DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010)
10.2.0.5	8202632      10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER
        	 
10.2.0.4.17	16619897 [*]      DATABASE PSU 10.2.0.4.17 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2013):
10.2.0.4.16	16056269 [*]      DATABASE PSU 10.2.0.4.16 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2013)
10.2.0.4.15	14736542 [*]      DATABASE PSU 10.2.0.4.15 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2013):
10.2.0.4.14	14275630 [**]      DATABASE PSU 10.2.0.4.14 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2012)
0.2.0.4.13	13923851 [*]      DATABASE PSU 10.2.0.4.13 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2012)
10.2.0.4.12	12879933 [*]      
DATABASE PSU 10.2.0.4.12 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2012)
10.2.0.4.11	12879929 [*]      DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4 | INCLUDES CPUJAN2012)
10.2.0.4.10	12827778      DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2011)
10.2.0.4.9	12419397      DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2011)
10.2.0.4.8	11724977      DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2011)
10.2.0.4.7	10248636      DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2011)
10.2.0.4.6	9952234      DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2010) 
10.2.0.4.5	9654991      DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2010)    [overlay PSU]
10.2.0.4.4	9352164      DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010)
10.2.0.4.3	9119284      DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010)
10.2.0.4.2	8833280      DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009)
10.2.0.4.1	8576156      DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009)
10.2.0.4	6810189      10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.3	5337014      10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
10.2.0.2	4547817      10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
        	 
10.1.0.5	4505133      10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.4	4163362      10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER
10.1.0.3	3761843      10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER
        	 
9.2.0.8	4547809      9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.7	4163445      9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.6	3948480      9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.5	3501955      ORACLE 9I DATABASE SERVER RELEASE 2 - PATCH SET 4 VERSION 9.2.0.5.0
9.2.0.4	3095277      9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.3	2761332      9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER
9.2.0.2	2632931      9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER
        	 
9.0.1.5	3301544      9.0.1.5 PATCHSET
9.0.1.4	2517300      9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER
9.0.1.3	2271678      9.0.1.3. PATCH SET FOR ORACLE DATA SERVER
        	 
8.1.7.4	2376472      8.1.7.4 PATCH SET FOR ORACLE DATA SERVER
8.1.7.3	2189751      8.1.7.3 PATCH SET FOR ORACLE DATA SERVER
8.1.7.2	1909158      8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER

NOTE:
[*] 10.2.0.4 and 10.2.0.5 are now in extended support mode and PSU’s released after Aug 01,2011 will need ES License to download them.
参考:Quick Reference to Patchset Patch Numbers [ID 753736.1]

发表在 Oracle | 一条评论

10.2.0.5出现ORA-00600[kcblasm_1]

发表于 2012 年 3 月 22 日 由 惜分飞

今天朋友执行一条如下sql

SELECT YF,
       KHBH,
       MAX(KHXM),
       MAX(YYBDM),
       MAX(YYBMC),
       MIN(ZJZH),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZJYE, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZJYE, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZQSZ, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZQSZ, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(ZC, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(ZC, 0)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120321 THEN
              NVL(DWJZ, 1)
             ELSE
              0
           END),
       SUM(CASE
             WHEN RQ = 20120301 THEN
              NVL(DWJZ, 1)
             ELSE
              0
           END),
       MAX(NVL(ZC, 0)) ZGZC,
       SUM(NVL(ZRZC, 0)) ZRZC,
       SUM(NVL(ZCZC, 0)) ZCZC,
       SUM(NVL(FDYK, 0)) FDYK,
       SUM(NVL(ZRGF, 0)) ZRGF,
       SUM(NVL(ZCGF, 0)) ZCGF,
       AVG(NVL(ZC, 0)) PJZC,
       SUM(NVL(CJJE, 0)) CJJE,
       SUM(NVL(YJ, 0)) YJ,
       SUM(NVL(ZJFSS, 0)) ZJFSS,
       SUM(NVL(ZYJ, 0)) ZYJ,
       SUM(NVL(CJCS, 0)) CJCS
  FROM CRM_FX2_KHZJHZ P
 WHERE YF = 201203
 GROUP BY YF, KHBH;

出现ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], []错误

数据库版本

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

通过MOS发现该错误在10.2.0.5上有相关描述

This problem is hit only in 10.2.0.5, up to PSU 10.2.0.5.4 in which problem is fixed.
Problem is affecting 10.2.0.5 to 10.2.0.5.3.
Cause
This is is a known and common problem hit in 10.2.0.5, investigated and corrected in unpublished Bug 7612454.

The problem was introduced in 10.2.0.5, by the fix of Bug:7523755.
This has been corrected in PSU 10.2.0.5.4 and 11.2.

解决方法

To quickest way to the solution of this problem is to apply PSU 10.2.0.5.4, Patch 12419392 or later.
All alternative solutions for this problem are listed below: 
•	Upgrade the database to 11.2.

OR
•	Apply 10.2.0.5.4 Patch Set Update (Patch 12419392) or later PSUs where bug is fixed. 
The available PSUs are mentioned in "10.2.0.5 Patch Set Updates - List of Fixes in each PSU" (Document 1337394.1)

OR
•	Apply interim Patch 7612454 on top of 10.2.0.5 (10.2.0.5.0-10.2.0.5.3):
o	For UNIX / Linux platforms apply Patch 7612454 available for download on MOS.
o	For Windows platforms apply Patch 3 or higher. 
Please check Document 342443.1 for latest patches available for Windows on top of 10.2.0.5.

这个错误在10.2.0.5上很容易发生，官方也没有给出发生这个错误的原因，以及如何改写相关sql，给出的方案是升级到10.2.0.5.4或者更高。这里可以看出升级需要慎重，可能导致原库有小部分sql不能正常执行。

发表在 ORA-xxxxx | 标签为 , , | 一条评论

lob类型数据转换为系统文件

发表于 2012 年 3 月 21 日 由 惜分飞

1.插入一条blob数据

SQL> create directory ULTLOBDIR as '/home/oracle';

Directory created.

SQL> create table blobtest(col1 BLOB);

Table created.

SQL> declare
a_blob BLOB;
  2    3  bfile_name BFILE := BFILENAME('ULTLOBDIR','tt.txt.bak');
  4  begin
  5  insert into blobtest values (empty_blob())
  6  returning col1 into a_blob;
  7  dbms_lob.fileopen(bfile_name);
  8  dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
  9  dbms_lob.fileclose(bfile_name);
 10  commit;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select dbms_lob.getlength(col1) from blobtest;

DBMS_LOB.GETLENGTH(COL1)
------------------------
                    4829

SQL> !pwd
/home/oracle

SQL> !ls -l tt.txt.bak
-rw-r--r-- 1 oracle oinstall 4829 03-19 17:26 tt.txt.bak

2.创建存储过程

CREATE OR REPLACE PROCEDURE RETRIEVE_LOB_TO_FILE(TEMP_BLOB IN BLOB,
                                                 FILE_PATH IN VARCHAR2,
                                                 FILE_NAME IN VARCHAR2) IS
  DATA_BUFFER   RAW(32767);
  POSITION      INTEGER := 1;
  FILEHANDLE    UTL_FILE.FILE_TYPE;
  ERROR_NUMBER  NUMBER;
  ERROR_MESSAGE VARCHAR2(100);
  BLOB_LENGTH   INTEGER;
  CHUNK_SIZE    BINARY_INTEGER := 32767;
BEGIN
  BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB);
  FILEHANDLE  := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024);
  WHILE POSITION < BLOB_LENGTH LOOP
    DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER);
    UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER);
    POSITION    := POSITION + CHUNK_SIZE;
    DATA_BUFFER := NULL;
  END LOOP;
  UTL_FILE.FCLOSE(FILEHANDLE);
EXCEPTION
  WHEN OTHERS THEN
    BEGIN
      ERROR_NUMBER  := SQLCODE;
      ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100);
      DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER);
      DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE);
      UTL_FILE.FCLOSE_ALL;
    END;
END;
/

3.测试读取blob到系统

SQL> declare 
  2  tmp_blob blob default empty_blob(); 
  3  begin 
  4  dbms_lob.createtemporary(tmp_blob, true); 
  5  select col1 into tmp_blob from blobtest; 
  6  retrieve_lob_to_file (tmp_blob, 'ULTLOBDIR','xifenfei.txt'); 
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> !pwd
/home/oracle

SQL> !ls -l xifenfei.txt
-rw-r--r-- 1 oracle oinstall 4829 03-20 23:44 xifenfei.txt
发表在 Oracle 开发 | 评论关闭