Re: Coelesce Tablespace in 7.2?

Install the following pl/sql procedure in the SYS schema, grant execute on it to the appropriate people and then you can:

SQL> exec sys.coalesce( 'SomeTableSpaceName' )

or

SQL> exec sys.coalesce

to coalesce free space in 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;

On 14 Aug 1996 16:37:21 -0600, doc_at_balr.balr.com (David R. Gilbert) wrote:

>Is there any way to force Oracle 7.2 to coelesce a
>tablespace right away?
>
>I know it's available in 7.3, but that's not an option
>for us right now.
>
>Thanks in advance.
>
>doc_at_balr.com
>
>--
> David Gilbert Internet: doc_at_balr.com, xanadu_at_mcs.net
> Consultant BALR Corporation - Information Technologies
> Web - PERSONAL: http://www.mcs.net/~xanadu WORK: http://www.balr.com

Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com                          

http://govt.us.oracle.com -- Check out our web site! Brand new, uses Oracle Web Server and Database

statements and opinions are mine and do not necessarily reflect the opinions of Oracle Corporation Received on Wed Aug 14 1996 - 00:00:00 CEST