Oracle Database: Select BLOB via DB Link


Today i’vh tried to select a simple blob over da database link. I know there are some restrictions using database links but in my business case i’vh to do this. So my goal is to transparenty select blobs over a database link from a remote database.

First I havn’t found any suiteable workaround. Here my references from support.oracle.com:

  • SELECT with a LOB and DBLink Returns an ORA-22992: Cannot Use LOB Locators Selected from Remote tables (Doc ID 1234893.1)
    • “The error is expected because the use of DBLinks and LOBs via the SELECT from PL/SQL is not supported.”
  • Ora-22992 workaround (Doc ID 436707.1)
    • Getting ORA-1406 with lobs greater than 32KB – 1
  • ORA-1406: Fetched Column Value was Truncated When Selecting Remote Column into Local BLOB Variable (Doc ID 459557.1)
    • “This means that we are not able to retrieve BLOBs columns greater than 32KB – 1 in size through a database link.”

Summarized we are not able to select a blob nativ over a database link if this blob is greater than 32KB-1.

The interesting thing is, that you are able to use DBMS_LOB operators on local and remote site. My favourite is the DBMS_LOB.SUBSTR function. The name is a little bit missleading because you can retrieve BLOB and CLOB. Here is my approach to select blob via a database link transparently to application:

VERSION 1 (chunk method):

create or replace function GETBLOBVIADBLINK
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,rwid in urowid)
return blob
is
  retval blob;
  tmpraw raw(2000);  
  tmplen number;
  tmpchk number;
  chksize number;
begin
  --preset vars
  chksize:=2000;
  dbms_lob.createtemporary (retval,true);
  execute immediate 'select dbms_lob.getlength@'||dblnk||' ('||col||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmplen using rwid;
  
  -- precalc  
  tmpchk:=floor(tmplen/chksize);

  -- applicate frist chunks  
  for i in 0 .. tmpchk-1
  loop  
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||chksize||','||((i*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
    dbms_lob.append(retval,tmpraw);
  end loop;
  
  -- applicate last entry
  if (tmplen-(tmpchk*chksize)) > 0 then
    execute immediate 'select dbms_lob.substr@'||dblnk||'('||col||','||(tmplen-(tmpchk*chksize))||','||((tmpchk*chksize)+1)||') from '||tbl||'@'||dblnk||' where rowid=:rwid' into tmpraw using rwid;
    dbms_lob.append(retval,tmpraw);
  end if;
  return retval;
end;
/

The explanation of the function is simple:

  1. Create a temp lob at local site
  2. The limitation of DBMS_LOB.SUBSTR is a RAW(2000) as maximum chunk size
  3. Copy chunk (2000 bytes max.) by chunk over the database link and append it to our local temporary blob. So we generate a local copy
  4. return local blob to upper caller

 

Now create a view with the new defintion:

CREATE OR REPLACE FORCE VIEW TESTVW1 (ID, MYLOB) AS 
SELECT id
       ,getblobviadblink('ARCHIV','MYLOBTABLE','MYLOB',rowid) MYLOB
FROM  MYLOB@archiv;

It’s done. I’m able to select a blob via dblink even it is greater than 32KB, now!

VERSION 2 (temporary table method):

create global temporary table tmplob (tmplob blob) ON COMMIT PRESERVE ROWS;
create or replace function getblobviadblink2
( dblnk in varchar2
  ,tbl  in varchar2
  ,col  in varchar2
  ,rwid in urowid)
return blob
is
  PRAGMA AUTONOMOUS_TRANSACTION;
  retval blob;
begin

  execute immediate 'insert /*+ NOLOGGING */ into tmplob select '||col||' from '||tbl||'@'||dblnk||' where rowid=:rwid' using rwid;
  select tmplob into retval from tmplob;
  delete /*+ NOLOGGING */ from tmplob;
  commit;
  return retval;
end;
/

Both methods are suitable for selecting on  it, but the VERSION2 Method is significant faster on network.

These are just two of many other ways. I would appreciate it if you share your implementation and experience with lob selection.

Oracle Security Patch October 2013 has been released


On 15. October 2013 Oracle released the quarterly Security Patch for October 2013.

How to patch, see here.

At same time the following PSUs for Database and Clusterware/Grid Infrastructure has been released:

Unix/Linux Systems:

  • 12.1.0.1.0 PSU 1 (12.1.0.1.1)  (DB: 17027533, GI: 17272829)
    actual available for:
    • Linux x86-64
    • Solaris x86-64
    • Solaris SPARC (64Bit)
  • 11.2.0.3.0 PSU 8 (11.2.0.3.8)  (DB:  16902043, GI: 17272731)
  • 11.2.0.2.0 PSU 12 (11.2.0.2.12)  (DB:  17082367, GI: 17272753)
  • 11.1.0.7.0 PSU 16 (11.1.0.7.16) (DB:  17082366, CRS: 11724953)

Windows Systems:

For 10g Customers:

The PSU July 2013 was the final PSU for Oracle 10gR2 Database (10.2.0.4.0 & 10.2.0.5.0). So the final version is:

Unix/Linux

  • 10.2.0.5.0 PSU 12
  • 10.2.0.4.0 PSU 17

Windows

  • 10.2.0.5 BP 23 (32 Bit)
  • 10.2.0.5 BP 22 (64 Bit) (Maybe there will be a BP 23, Documentation is inconsistent)
  • 10.2.0.4 BP 50 (32 Bit)
  • 10.2.0.4 BP 49 (64 Bit) (Maybe there will be a BP 50, Documentation is inconsistent)

Common Vulnerabilities and Exposures (CVE) fixed in these patches:

  • CVE-2011-3389
  • CVE-2013-0169
  • CVE-2013-3762
  • CVE-2013-5766
  • CVE-2013-5827
  • CVE-2013-5828

Limitations:

  • Patch Set Update (PSU) patches are cumulative.
  • This patch is Oracle RAC Rolling Installable.
  • This patch is Data Guard Standby-First Installable.

IMPORTANT:
This patch contains a security fix due to which a SELECT query’s plan MAY change under the following conditions:

  • The SELECT queries a table protected with a Fined Grained Auditing policy
  • And the policy condition is NULL

Refer to My Support notice for more information:

  • Bug 17027533 – 12.1.0.1.1 (Oct 2013) Database Patch Set Update (PSU) [ID 17027533.8]
  • Oracle Database Patch Set Update 12.1.0.1.1 Known Issues (Doc ID 1571651.1)
  • Bug 16902043 – 11.2.0.3.8 (Oct 2013) Database Patch Set Update (PSU) [ID 16902043.8]
  • Oracle Database Patch Set Update 11.2.0.3.8 Known Issues (Doc ID 1571650.1)
  • Patch Set Update and Critical Patch Update October 2013 Availability Document (Doc ID 1571391.1)

The new release, new Bugs here the list of the Multitenant Bugs, which has been fixed:

16427054 - SR12.1.0.2PX_HYBRID_LOAD - TRC - KPDBIDTONAME
16443657 - CDB: OCITRANSCOMMIT() IS ABLE TO COMMIT FROM WRONG CONTAINER
16457621 - W2K8_12.1_CDB: ORA-600 [KKAEGEN_GET_EDITION_NAME_3] TERMINATE INSTANCE
16459685 - CDB (NON RAC) : ORA-44310 AND ORA-07445:[KSPGIP()+106] [SIGSEGV]
16483559 - CDB:COMMON USER NOT SYNCED ON PDB OPEN WITH FORCE OPTION
16485876 - FIRE LOGON TRIGGER DOING DDL IN OTHER CONTAINER GIVES ORA-600 [KTSSCNI1],
16603924 - XSTRM CDB W/ UPG'D PDBS, CREATE_OUTBOUND => ORA-600 [KKAEGEN_GET_EDITION_NAME_1]
16660558 - CDB: ORA-7445 [KSFD_IO] & [KSLWS_DMP_SESS_WAITSTACK] IN CREATE PDB UNDO CALLBACK
16663303 - SR12.1UPD-PLUGIN:DBMS_EDITIONS_UTILITIES FAILS WITH ORA-38817: INSUFFICIENT PRIV
16663465 - SR12.1UPD-PLUGIN -TRC -ORA-600KKDLGETBASEUSER2:AUTHIDTYPE/ORA-04024 SELF-DEADLOC
16675710 - CDB: ORA-7445 [KSUSDIINPROGRESS()+47] [SIGSEGV] [ADDR:0X18] [PC:0XB35190F]
16689109 - INVALID OBJECTS OCCUR WHEN UPGRADING IN A CDB
16697600 - CDB: "ALTER PDB ALL OPEN INSTANCES = ALL" ERRORS IF ALL OPEN ALREADY
16698577 - FA + REDACT: ORA-10387 AND ORA-600 [KGLRELEASEHANDLEREFERENCE1]
16705020 - LNX64-12.1-CDB: HIT ORA-7445 [KSP_PDB_SPFILE_INSERT] WHEN CREATING PDB
16707927 - PKT : ORA-600 [2130] - TRC - KCCUGG
16712618 - CDB:ORPHANED USER CAN BE UNLOCKED
16715647 - CDB-ADG:RESTRICTED OPEN FORCE FOR MULTIPLE PDBS DOES NOT WORK
16730813 - CDB:ORA-65144 WHEN DISABLING RESTRICTED SESSION IN ROOT
16772060 - TT12.1SQLFUZZ2: DBMS_PDB.SYNC_PDB THROWS ORA-600 [KGHSTACK_UNDERFLOW_INTERNAL_1]
16784167 - CDB(NON-RAC):ORA-00600: INTERNAL ERROR CODE, ARGUMENTS: [2801], [], [], [], [],
16784901 - TT12.1SQLFUZZ2: DESC ON RECREATED SUPPLIED OBJ THROWS ORA-7445 [KQLPRFD()+121]
16795944 - TT12.1SQLFUZZ2: PDB OPEN CRASHES AFTER LICENSE_MAX_USERS SET TO LESS THAN CURREN
16825779 - COMMON PROFILE RESOURCE LIMIT FOR PASSWORD VERIFY FUNCTION DISPLAYS FROM ROOT
16836849 - PHSB: CDB ORA-00600:[KTCALLOCXCB] DURING DB OPEN
16859937 - CDB: LOCAL USER CONVERTED TO COMMON
16902138 - RAC: ORA-7445 [RPIDRV] AFTER DROPPING A PDB
16921340 - CDB EXIT: NON-CDB TO PDB PLUGIN HANGS
16935643 - GOT ORA-600 [KQLUDP2] , [0X16EB753A8], [4] WHILE UPGRADING A PDB
16946613 - TT12.1SQLFUZZ2: CMN USER WHICH WAS LOCAL PRIOR TO SYNC NOT SYNCED ON PDB OPEN
16946990 - UNABLE TO INSTALL APEX IN LOCAL PDB 12C DATABASE.RAISES ORA-65050
16993424 - CDB: ORA-600 [KKAEGEN_GET_EDITION_NAME_3], [3]
16994576 - CDB(NON-RAC): ORA-600 [KQLBBOTADD:3]
17000176 - HANG INSIDE CATCDB_INT.SQL CALLED USING CATCON.PL
16911800 - Fix for bug 16911800
16919176 - Fix for bug 16919176

AOUG Migration Day: Save the date 19th September 2013


Austrian Oracle User Group (AOUG) announced the oracle “Migration Day”.

Save the date:

19th September 2013, starting 15:30

Tech Gate Vienna
Donau City Strasse 1
A-1220 Wien

Check this out and save the date here

Database Migration Assistant for Unicode (DMU)


Since 12c csscan and csalter is not supported anymore see here. Therefore the Database Migration Assistant (DMU) has to be used right now. DMU is an external tool with is not shipped with the database software. This tool is suitable for UNICODE migrations (UTF8 or AL32UTF8) only.

So our first step is to download the software

  • Link to download Database Migration Assistant for Unicode (DMU) here
  • This is a java tool like sqlpdeveloper and need a jdk here.

After first start, there may be a warning due to the java 7 version, just ignore it.

dmu2

Notice: Only the convert of a Non-CDB is supported by this tool, otherwise you get an error like this:

dmu3

1. Preparing Database for convert

1.1 Create repository

dmu4

1.2 Choose character set you want to migrate to

dmu5

1.2 Choose tablespace where you want to store the migration repository. This does’t need much space so SYSAUX is possible.

dmu6

1.3 Finish this

dmu7

2. Scan database data

Like csscan you have to fill your migration repository with data to get what should be converted:

dmu8

2.1 Choose parameters to start scan

dmu9

2.2 Choose data you want to scan

dmu10

2.3 Choose action for scan and start it

dmu11

2.4 Here an example output of the scanning process

dmu12

2.5 Finish

dmu13

3. Start convertion of the database

3.1 Make sure that you have a database backup!

3.2 Start convert wizard

dmu14

3.3 Check scan result timestamp is ok

dmu16

3.4 If not have done right now, than take a backup before start

dmu17

3.4 Scan wirzard will start now

dmu18

3.5 Choose conversion parameters

dmu19

3.6 Check details and start process

dmu20

3.7 Here an example output of the conversion process

dmu21

3.8 Finish

dmu22

4. Uninstall migration repository

If the migration was successful you can deinstall the migration repository

dmu23

dmu24

dmu25

 

Overall finished. Now your database has been migrated to UNICODE.

Oracle Security Patch July 2013 has been released


(Is now superseeded by October 2013 PSU -> See here)

On 16. July 2013 Oracle Released the quarterly Security Patch for July 2013.

At same time the following PSUs for Database and Clusterware/GridInfrastructure has been released:

  • 11.2.0.3.0 PSU 7 (11.2.0.3.7)
  • 11.2.0.2.0 PSU 11 (11.2.0.2.11)
  • 11.1.0.7.0 PSU 16 (11.1.0.7.16)
  • 10.2.0.5.0 PSU 12 (10.2.0.5.12)
  • 10.2.0.4.0 PSU 17 (10.2.0.4.17) for several platforms

Refer to My Support notice for more information:

  • Bug 16619892 – 11.2.0.3.7 (Jul 2013) Database Patch Set Update (PSU) [ID 16619892.8]
  • Oracle Database Patch Set Update 11.2.0.3.7 Known Issues [ID 1546433.1]

IMPORTANT: Update from 2th August 2013:
Patch 17230530 Is a Recommended Patch for PSU 11.2.0.3.7

  • Bug 17230530 – ORA-600 [kkzqid2fro] after apply 11.2.0.3.7 psu patch
  • PSU 11.2.0.3.8 onwards includes a fix for bug 17230530 [ID 17230530.8]