Another useful trick is to call rebuild freepools. This is done automatically in 11.2, but you would need to do it yourself in 22.214.171.124.
set serverout on size unlimited GLOBAL > select round(sum(DBMS_LOB.GETLENGTH(AWLOB))/1024,0) kb from aw$global; KB ---------- 167699 GLOBAL > declare 2 aw_name varchar2(30); 3 begin 4 aw_name := 'GLOBAL'; 5 dbms_aw.execute('aw attach ' || aw_name || ' rwx;define junkvar int;update;commit'); 6 for i in 1..3 loop 7 dbms_aw.execute('aw reattach ' || aw_name || ' rwx;junkvar=junkvar+1;update;commit'); 8 end loop; 9 dbms_aw.execute('delete junkvar;update;commit;aw detach ' || aw_name || ''); 10 end; 11 / PL/SQL procedure successfully completed. GLOBAL > select round(sum(DBMS_LOB.GETLENGTH(AWLOB))/1024,0) kb from aw$global; KB ---------- 147019
Here is a quote from the Oracle documentation. Note that you should not run this if you are using SECUREFILES.
alter table aw$global modify lob (awlob) (rebuild freepools);
The REBUILD FREEPOOLS clause removes all the old data from the LOB column. This clause is useful only if you reverting to PCTVERSION for management of LOBs. You might want to do this to manage older data blocks.Even after all this there is still an unadvoidable growth of the AW$ table. As a rule of thumb you can expect the table to grow three times its size after its original load. This is mostly a side effect of using LOBS to store the AW since the LOB subsystem doesn't like giving up memory once it has obtained it.
2. It is very difficult to reclaim space used by an AW. I tried deleting cubes but it does not work. The only possible way that I have figured out is to drop and recreate the complete AW using the xmls. Is there a better way?This is a bug and was discussed (and indeed discovered) in a recent thread: Releasing Storage space occupied by Cube
3. At times incremental data loads take up more space than the full loads. Does this mean the old data is still not deleted?Incremental data loads, or more specifically inremental aggregations, can take existing lists of child nodes and extend them. This can lead to fragmentation similar to a fragmented disk drive. After a complete load the child lists will be stored together again. This is, incidentally, why incremental performance can degrade over time and why a periodic complete aggregation can improve performance.
To truncate a table and then reload in one operation you can say
exec dbms_cube.build('SALES_CUBE USING(CLEAR)', add_dimensions=>false, parallelism=>X)
This is related to the notion of 'build script' in AWM. The AWM default is "LOAD, SOLVE", which means the cube does not get truncated before the cube is rebuilt. The result of this choice is that all AWM cubes are aggregated incrmentally.
exec dbms_cube.build('SALES_CUBE USING(CLEAR, LOAD, SOLVE)', add_dimensions=>false, parallelism=>X)
Once this is defined you can change the script as follows.
CREATE OR REPLACE PROCEDURE SET_CUBE_BUILD ( CUBE_NAME VARCHAR2, SCRIPT VARCHAR2, CUBE_OWNER VARCHAR2 DEFAULT USER ) AUTHID CURRENT_USER AS xmlvc VARCHAR2(4000); BEGIN -- Construct the XML xmlvc := ' <Metadata Version="1.0"> <Cube Name="' || CUBE_NAME || '" Owner="' || CUBE_OWNER || '"> <Organization> <AWCubeOrganization> <DefaultBuild> <![CDATA[BUILD SPEC SYS_DEFAULT( ' || SCRIPT || ')]]> </DefaultBuild> </AWCubeOrganization> </Organization> </Cube> </Metadata>'; -- Import the XML DBMS_CUBE.IMPORT_XML(xmlvc); END; /
exec set_cube_build('UNITS_CUBE', 'CLEAR, LOAD, SOLVE')