Hi gourous
I'm trying to simulate table compression with the package DBMS_COMPRESSION.GET_COMPRESSION_RATIO which runs very well on most of tables but fails on few others.
The characteristics of these few tables is that they contain more than 2000 Mega rows.
The script I run is :
Set serveroutput on
SET SERVEROUTPUT ON SIZE 1000000
declare
lv_cmp_ratio number;
lv_comptype_str varchar2(300);
lv_BLKCNT_CMP number;
lv_BLKCNT_UNCMP number;
lv_ROW_CMP number;
lv_ROW_UNCMP number;
begin
dbms_output.put_line('1. Compression Ratio.: 2. Block Count.......: 3. Compression Type.......: 4. Blk Count Compressed...:
5. Blk Count Un-compressed: 6. Row Count Compressed : 7. Row Count Un-Compressed: ');
dbms_compression.GET_COMPRESSION_RATIO(SCRATCHTBSNAME=> 'USERS',
OWNNAME=> '&owner',
TABNAME=> '&table_name',
PARTNAME=>null,
COMPTYPE=> 4,
BLKCNT_CMP =>lv_BLKCNT_CMP,
BLKCNT_UNCMP =>lv_BLKCNT_UNCMP,
ROW_CMP =>lv_ROW_CMP,
ROW_UNCMP =>lv_ROW_UNCMP,
CMP_RATIO=>lv_cmp_ratio,
COMPTYPE_STR=>lv_COMPTYPE_STR );
dbms_output.put_line('&&table_name'||'|'||lv_cmp_ratio|| '|' ||lv_blkcnt_cmp|| '|' ||lv_comptype_str|| '|' ||lv_BLKCNT_CMP|| '|
' ||lv_BLKCNT_UNCMP|| '|' ||lv_row_cmp || '|' ||lv_row_uncmp);
end;
/
Tablespace USERS that I use in the script above as scratchtablespace is 1211819 Megabytes free. !!!! <----
...and errors I've got is :
08:49:33 SQL> @compressionHCCTAB.sql
Enter value for owner: GLOP
old 12: OWNNAME=> '&owner',
new 12: OWNNAME=> 'GLOP',
Enter value for table_name: TABLE_GLOP
old 13: TABNAME=> '&table_name',
new 13: TABNAME=> 'TABLE_GLOP',
Enter value for table_name: TABLE_GLOP
old 22: dbms_output.put_line('&&table_name'||'|'||lv_cmp_ratio|| '|' ||lv_blkcnt_cmp|| '|' ||lv_comptype_str|| '|' ||lv_BLKCNT_CMP|| '|
new 22: dbms_output.put_line('TABLE_GLOP'||'|'||lv_cmp_ratio|| '|' ||lv_blkcnt_cmp|| '|' ||lv_comptype_str|| '|' ||lv_BLKCNT_CMP|| '|
1. Compression Ratio.: 2. Block Count.......: 3. Compression Type.......: 4. Blk Count Compressed...:
5. Blk Count Un-compressed: 6. Row Count Compressed : 7. Row Count Un-Compressed:
declare
*
ERROR at line 1:
ORA-20000: Compression Advisor TEMP UNCMP table created with zero rows
ORA-06512: at "SYS.PRVT_COMPRESSION", line 1121
ORA-06512: at "SYS.DBMS_COMPRESSION", line 214
ORA-06512: at line 11
Here is the physical characteristic of the server running the database.
09:14:11 SQL> 09:14:11 SQL> !prtconf|more
Machine Serial Number:
Processor Type: PowerPC_POWER8
Processor Implementation Mode: POWER 6
Processor Version: PV_6_Compat
Number Of Processors: 17
Processor Clock Speed: 3525 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 3 ZZZserver
Memory Size: 348160 MB
Good Memory Size: 348160 MB
Platform Firmware level: SV810_108
Firmware Version: IBM,SV810_108
Console Login: enable
Auto Restart: true
Full Core: true
and characteristics of Oracle Database:
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Here it is....in addition I can tell you that it runs during more than 8 hours and when there is no more space free in tablespace USERS, it fails !!!!
I beleive I need more free space in the tablespace USERS, but I don't know how much !!! or is there still the bug (
Bug 17928568 ORA-20000 from DBMS_COMPRESSION.get_compression_ratio)
even in 11.2.0.4 ?????
Thank you very much for your help
Daniel