Re: Coalescing tablespace

From: Tim Gorman <tim_at_sagelogix.com>
Date: Sun, 26 Oct 2003 19:44:25 -0800
Message-ID: <F001.005D4A0F.20031026194425@fatcity.com>

Ross,

Wow! That's a blast from the past! ALTER TABLESPACE ... COALESCE didn't arrive on the scene until v7.3, so prior to that version you had to do something like the following:

    alter session set events 'immediate trace name coalesce level NNN';

where the value of the level "NNN" could be set one of two ways.

First and simplest way to set "NNN" is to use the value of the column TS# in the table SYS.TS$ where NAME is the name of the tablespace. So, a query like:

    select ts# from sys.ts$ where name = 'TOOLS'

would yield the "NNN" for the level in the ALTER SESSION statement, or:

    alter session set events 'immediate trace name coalesce level 23';

if the TS# of the TOOLS tablespace was 23.

However, setting the level this way would cause the ALTER SESSION command to only coalesce a certain, predefined number of extents and then stop. At this time, 6-7 years after the last time I used it, I forget what the default was, but it was probably a very low number (maybe "1"?)...

So, if you wanted to specify to the ALTER SESSION command how many extents to coalesce before quitting, you had to specify the TS# number in the lower-order 16 bits of the "NNN" and the number of extents to coalesce in the higher-order 16 bits. This implied an upper limit of 65535 extents to be coalesced at a time.

So, generating a full ALTER SESSION statement, complete with a fully-qualified LEVEL parameter, could be performed as follows:

    select 'alter session set events ' ||

             '''immediate trace name coalesce level ' ||
             to_char((65536*least(count(*),65535))+t.ts#)||''''
    from     sys.fet$   a,
             sys.fet$   b,
             sys.ts$    t
    where    t.name = '<tablespace-name>'
    and      a.ts# = t.ts#
    and      a.ts# = b.ts#
    and      a.file# = b.file#
    and      (a.block# + a.length) = b.block#

    group by t.ts#;

The purpose of the "LEAST(COUNT(*),65535)" phrase is to prevent an overflow, due to max of 16 bits in which to specify the count...

Hope this helps...

Thanks!

-Tim

on 10/26/03 6:04 PM, Ross Collado at Ross.Collado_at_techpac.com wrote:

> Hi,
> For those like me still working on an Oracle 716 (hold the laughs), how do
> we coalesce a tablespace?
> Rgds,
> Ross

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Sun Oct 26 2003 - 21:44:25 CST