Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Coalescing tablespace

Re: Coalescing tablespace

From: Tim Gorman <tim_at_sagelogix.com>
Date: Mon, 27 Oct 2003 05:24:24 -0800
Message-ID: <F001.005D4AE6.20031027052424@fatcity.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US