Oracle Advanced Compression and BLOBs

[Former Member]

Hello,

I am using brspace 7.20 and Oracle 11.2.0.1.0 to compress

the contents of a tablespace:

brspace -f tbreorg -a long2lob -c ctablob -s PSAPOLD -t PSAPNEW
brspace -f tbreorg -a lob2lob -c ctablob -s PSAPOLD -t PSAPNEW
brspace -f tbreorg -a reorg -c ctab -s PSAPOLD -t PSAPNEW

Now some tables still remain in PSAPOLD and brspace explains it like this:

BR1914W Table SAPR3.FPLOG is a SAP compressed table

BR1111I Reorganization of table SAPR3.FPLOG will be skipped

Did I miss something? I thought SAP note 1431296 is pretty clear on

how to implement Oracle Advaced Compression. Why does brspace

skip all tables with BLOB fields?

Regards,

Mark

[Former Member]

Hi Mark,

Can you help me after oracle 11g upgrade, how compress table in SAP ECC 6.0?

Thanks,

Manas

[stefan_koehler]

Hello Mark,

compression is only possible with Secure Files .. not the basic LOB.

Have you checked if the BR*Tools have converted the basic LOBs to Secure Files?

Until now i have not investigated 11g R2 with its features on a SAP environment (planned for Q3/2010), but this should be the case. If the LOBs are still the basic LOBs .. enable tracing of the conversion for one table which contains some LOBs to get some details.

Regards

Stefan

[Former Member]

Hello Stefan,

yes I was checking what happened to the tables:

brspace -f tbreorg -a long2lob -c ctablob -s PSAPOLD -t PSAPNEW
BR1146W Table SAPR3.LDKO has no LONG column
BR1111I Reorganization of table SAPR3.LDKO will be skipped

brspace -f tbreorg -a lob2lob -c ctablob -s PSAPOLD -t PSAPNEW
BR1914W Table SAPR3.LDKO is a SAP compressed table
BR1111I Reorganization of table SAPR3.LDKO will be skipped

brspace -f tbreorg -a reorg -c ctab -s PSAPOLD -t PSAPNEW
BR1914W Table SAPR3.LDKO is a SAP compressed table
BR1111I Reorganization of table SAPR3.LDKO will be skipped

The first and the third command skip the tables with BLOB columns which is perfectly ok,

but the second command also skips the tables with BLOB columns which is not ok,

this one should do perform the conversion from LOB to Securefiles. Should I better

open an OSS message for this? I believe some other people will be puzzled by

this brspace behaviour as well.

Regards,

Mark

[stefan_koehler]

Hello Mark,

> but the second command also skips the tables with BLOB columns which is not ok

.. sure i did understand your problem, but as i already wrote compression is only available with secure files.

Regarding to your command options "-a lob2lob -c ctablob", you try to convert a basic lob into secure files and compress it in one move. Maybe this is not possible or the BRTools are not designed that way .. that's why i wrote that you need to trace the BRTool calls .. to see what's going on inside (BR_TRACE 15).

Did you try to convert the basic lob into a secure file only and compress it afterwards?

Unfortunately i have no 11g R2 database in a SAP environment here to test it by my own.

Regards

Stefan

[Former Member]

Hello Stefan,

I finally sorted out what was going wrong. I underestimated the number of SAP pool and cluster tables.

There are 949 pool and cluster tables in my ECC 6.0 system, and the command line option -SCT

excludes them correctly for both (table and LOB) compression.

Now everything is fine and the correct brspace usage is clear for me.

Regards,

Mark

Edited by: Mark Foerster on Jun 22, 2010 10:25 AM