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