RE: Coalescing tablespace

Thanks Tim.
Yes, for some it is a blast from the past! Unfortunately for me, for one of our systems still using this database, I am sort of stuck in the past. On the subject of coalescing, is the SYSTEM tablespace safe to be coalesced as well? I was thinking of putting this SQL statement you've suggested in a loop for all tablespaces.
Thanks again and to others for the prompt reply. Rgds,
Ross

> -----Original Message-----
> From: Tim Gorman [mailto:tim_at_sagelogix.com]
> Sent: Monday, 27 October 2003 2:44 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Coalescing tablespace
>
>
> 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ross Collado
  INET: Ross.Collado_at_techpac.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).