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).
