Ross,
Yup. I don't recall ever excluding SYSTEM, though I generally work hard to
minimize I/O in that tablespace (i.e. make sure not being used as temporary,
no schemas except SYS, move AUD$ table to another TS if necessary, cache
sequences, etc).
Most folks put that statement into a loop, to go through all the
tablespaces, excluding tablespaces where COUNT(*) comes back as 0...
Good luck!
-Tim
on 10/26/03 9:54 PM, Ross Collado at Ross.Collado_at_techpac.com wrote:
> 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: 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 Mon Oct 27 2003 - 07:24:24 CST
