Coalesce?

Hi,

I check out my tablespace usage in OEM and see that the datafiles are like 5M used and the datafile is like 2GB. I have resized many of the others similarly using the resize command. There are a few however that say that the amount in the resize command is much bigger? I refresh my display and see no such thing. Does this mean that objects are scattered in different blocks and I need to coalesce?

Also if people are online etc and using, will coallesce cause any problems?

Please help,
thanks
Nirasha

Statement is Alter tablespace xxx coalesce, right?

The error is due to fragmentation of free extents. Coalesce does not cause any problems. Syntax is correct.

Thanks for the reply. Tried it, and coalesced, but still cannot perform it. Tried taking a look at the Tabelspace Map in OEM and saw 'selected' used and free (looks like a bad mess).. what does selected mean? and does this mean my only option is export/import.

Thanks again,
Nirasha

If your tablespace map is not evenly spread in OEM, thats fragmented.

How about using reorg ? Else redefine storage clasuses of initial and next extents with initial and next extents as same and pctincrease 0 and add 2 oracle blocks for datafile as over head.

Make sure your extent_size is multiple of multi_block_red_count.

That should take care of fragmentation problem. See the SAFE tech. white paper link posted in somany threads from couple of weeks.

Make sure to use comepress=n option while exporting.

[Edited by sreddy on 01-30-2001 at 06:42 PM]

Thanks, though not looking forward to a reorg without fancy tools available. What if the system tablespace has some fragmentation?

Thanks,
Nirasha

Don't worry about the system tablespace. Oracle recommends not to touch it. And even if you want, you can't change the storage clause (other than resizing the tablespace/datafile) in simple way. You have to do it hard way by editing sql.bsq script. Oracle don not encourage dba's to change it without their suppot or whatever.

For Nirasha...

What are the obejcs you have in that tablesapce ?? If those are Indexes you can simply rebuild them with proper storage parameters. If those are tables you can do a export/import to reorganise the tablesapce.

Hope it helps..

Sanjay

if they are tables and you are using 8i try

alter table table_name move tablespace_name
then resize the datafile
and finally move the tables back with above command

Thanks you guys for all that help. I just read recently about the feature of move tablespace, and I was curious whether there is anything I need to be aware of before doing this .. like people not touch the table etc???

Thanks
Nirasha
PS Does anyone know if this email notification in this forum will ever be reactivated??

Still wondering about whether I can move tablespace if anyone is working (selecting/updating it etc)?? Or is there some mode for it to be in???

Nirasha

Move tablespace? Are you looking at alter table move tablespace xyz?

Oops sorry I was unclear. Yes, you are right.

alter table table_name move tablespace_name

I just need to know if people are accessing the table (either in update or select) if it is possible?

What things/tasks do I have to do to perform this and/or check out for?

Thanks in advance,
Nirasha