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:[EMAIL PROTECTED] > 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 [EMAIL PROTECTED] 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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
