Re: coalescing free space

rblizard_at_bb.cmcsys.com (Bob Blizard) wrote:

>This is from this newgroup last November (paraphrased):
 

>Oracle 7.1x does not coalesce free space if the default pctincrease is
>set to 0 for a tablespace. Asuggested solution/workaraound is to
>issue the following sql command while connected as SYS:
 

>alter session set events 'immediate trace name coalesce level <n>';
 

>chuckh_at_ix.netcom.com (Chuck Hamilton) wrote:
 

>>
 

>Bob Blizard rblizard_at_bb.cmcsys.com | All these worthwhile
>CMC Systems, Inc. | opinions are mine, and
>175 Littleton Rd Westford, MA 01886 | not necessarily CMC's...
>Ph (508) 392-1300 Fax (508) 392-1303 |
>"If you want to park in the first row of life, you must go there first."

The following pl/sql subroutine, when installed under the SYS schema, will automate the process. Once installed, grant to anyone you want and all they have to do is:

SQL> exec sys.coalesce( 'temp' )

OR

SQL> exec sys.coalesce

The first one will merge free space in tablespace TEMP. The second one does all tablespaces.

create or replace procedure coalesce( ts_name in varchar2 default NULL ) as

    number_of_extents number default 255 * 16 * 16 * 16 * 16;     exec_cursor integer default 0;     rows_processed number default 0;     stmt varchar2( 255 );
begin  

    dbms_output.enable( 1000000 );
    for x in ( select number_of_extents + ts# ts#, name

                 from ts$
                 where ( name = upper(ts_name) OR ts_name is NULL )
                   AND (  online$ != 3 ) ) loop
 
        stmt := 'alter session set events ' ||
                '''immediate trace name coalesce level ' || x.ts# || '''';
        exec_cursor := dbms_sql.open_cursor;
        dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
        rows_processed := dbms_sql.execute(exec_cursor);
        dbms_sql.close_cursor( exec_cursor );
        dbms_output.put_line( 'Coalesced Tablespace ' || x.name );
        dbms_output.put_line( stmt );

exception

    when others then

         dbms_output.put_line( substr( stmt, 1, 200 ) );
         dbms_output.put_line( 'SQLCODE = ' || sqlcode );
         dbms_output.put_line( 'SQLERRM = ' || sqlerrm );
         if dbms_sql.is_open(exec_cursor) then
           dbms_sql.close_cursor(exec_cursor);
         end if;
         raise;

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Fri Jan 19 1996 - 00:00:00 CET