on 01-18-2013 12:34 PM
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,
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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,
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,
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
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
Yes, I check the note and it appears:
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,
User | Count |
---|---|
81 | |
25 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.