cancel
Showing results for 
Search instead for 
Did you mean: 

Steps for compress Oracle 11g ?

Former Member
0 Kudos

Hi experts,

I have a doubt... I want compress my database with Oracle 11g, I read this notes:

Note 1436352 - Oracle 11g Advanced Compression for SAP Systems

Note 1431296 - LOB conversion and table compression with BRSPACE 7.20

But I have not clear the steps... I know I have to create a new tablespace for "send" the data compress, but I do not know what is the commands for do it.

Can anyone help me?

My system is Windows with Oracle 11.2.0.2

Thanks in advance,

Regards,

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Victor,

You can compress the database by setting the compression attribute on table and tablespace attributes then reorganizing the tablespaces. Please find the previous discussion, below;

http://scn.sap.com/thread/3212577

Please note that be sure latest database patches have been applied, before the setting compression attributes on table and tablespaces.

At the first step, create a new tablespace or change existing one attribute to "COMPRESS FOR OLTP", Then execute the script to modify tables attributes, below;

spool compress.sql

select 'alter table ', table_name, 'compress for oltp;' from dba_tables where owner='<TABLESPACE_NAME>'

spool off

connect <dbowner>

<type password>

@compress.sql

commit;

After the tablespace reorganization, the tables will be compressed.

brspace -u / -f tbreorg -s <TABLESPACE> -t "*"

Best regards,

Orkun Gedik

Former Member
0 Kudos

Please... with more steps.

I create new tablespace with the attribute "COMPRESS FOR OLTP".

After, I execute:

spool compress.sql

select 'alter table ', table_name, 'compress for oltp;' from dba_tables where owner='<TABLESPACE_NAME>'

spool off

connect <dbowner>

<type password>

@compress.sql

commit;

for mark all tables with the attribute "compress for oltp"

and the last step, execute this:

brspace -u / -f tbreorg -s <TABLESPACE> -t "*"

And what is the utility of this commands:

a. brspace -u / -f tbreorg -a long2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp> -c ctablob -lc medium -SCT ( No long row tables so wasn't executed )

b. brspace -u / -f tbreorg -a long2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp>

c. brspace -u / -f tbreorg -a lob2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp> -c ctablob -lc medium -SCT

d. brspace -u / -f tbreorg -a lob2lob -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp>

e. brspace -u / -p reorgEXCL.tab -f tbreorg -a reorg -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp> -c ctablob -SCT

f.  brspace -u / -f tbreorg -a reorg -o sap<sid> -s <src_tsp> -t allsel -n <new_tsp>

Thanks in adavance,

Regards,


Former Member
0 Kudos

These are sample commands to convert long fields and reorganization. Simply convert long type to lob and apply the steps, I noted in the previous message.

Best regards,

Orkun Gedik

Former Member
0 Kudos

So, the steps will be

1- create new tablespace with the attribute "COMPRESS FOR OLTP".

2- execute:

spool compress.sql

select 'alter table ', table_name, 'compress for oltp;' from dba_tables where owner='<TABLESPACE_NAME>'

spool off

connect <dbowner>

<type password>

@compress.sql

commit;

3- execute

brspace -u / -f tbreorg -s <TABLESPACE> -t "*"

is it ok?

Regards,

Former Member
0 Kudos

Yes, but do not forget the prerequisites; apply the database patch (in order to avoid the problem while modifying tablespace and table attribute to "compress for oltp") and convert long fields to lob. Then, you can apply the steps, above.

Best regards,

Orkun Gedik

Former Member
0 Kudos

The patch is ok.

But... how convert long fields to lob? I do not know this step...

Regards,

Former Member
0 Kudos

Hi,

Check the Note 646681 - Reorganizing tables with BRSPACE and search for "Converting the LONG fields into LOB fields online as of Oracle 10g" title

brspace -u / -f tbreorg -a long2lob -t "*" [-n <new_tsp> [-i <ind_tsp>]]     [-p <par_degree>]

After the conversion, you need to update the stats;

brconnect -u / -c -f stats -t all -f collect -p 4

Best regards,

Orkun Gedik

Former Member
0 Kudos

Hi,

If I want compress BW, is it the same process?

Thanks in advance,

Regards,

Former Member
0 Kudos

Hi Victor,

The table compression does not covered by Oracle 11g on SAP. Please check the Note 1289494 - FAQ: Oracle compression and search for "What types of compression does this note not cover?"

Best regards,

Orkun Gedik

Former Member
0 Kudos

Yes, I check the note and it appears:

  • BI compression (F fact tables -> E fact tables)

But, can I do it for other tables? Can I execute this commands?

2.1 Converting tables with LONG fields online

2.2 Converting tables with old LOB fields online

2.3 Compress tables without LONGs and without old LOBs

2.4 Reorganizing the remaining tables from the source tablespace

Thanks in advance,

Regards,

Former Member
0 Kudos

>> But, can I do it for other tables? Can I execute this commands?

Yes, you can compress them.

Best regards,

Orkun Gedik

Answers (0)