Re: recreate tablespace - Titi

I'd only add this to Uwe's advice,

As an addendum, select count(*), (bytes/(1024*1024)) group by (bytes/(1024*1024))
order by (bytes/(1024*1024))
from dba_extents [or is it dba_segments?! I chronically mix up that view's name]
where tablespace_name = 'xxxx' (you may have to join dba_data_files on the file number to get tablespace_name, I forget offhand if that is in dba_extents)

well whatever, it's close anyhow

to see what kind of distribution and number of extents of varying sizes you have already, as obviously this is of concern already; if the distribution of sizes varies wildly and you have a lot of extents, you may wish to consider taking steps to consolidate them into a few or one single extent, providing of course the result of that wouldn't end up with an extent or extents that can't fit

also, doing this is a mixed blessing depending on your storage mechanism; if you've striped the tablespace across multiple disks by hand (like I do especially under raw i/o), crunching will lose you the i/o benefits gained by spindle diversity

Alllsooo, don't know what version/release you're running, but I assume you know about the 0 PCTINCREASE / coalescing problem; I hope Oracle's done something about that by now, unless there is some ethereal Codd/Date/Ken Jacobs reason, and there is a REASON for the way it works; anyway, PCTINCREASE of 0 means coalescing won't be done by SMON in the background when it has free time to fool around, so at least, in the past, you had to have it at at least 1, which I thought was rather kludgy; I'm sure you've heard that, it's been in the manuals and 3rd party books for years. Though I've never yet seen a reason WHY Oracle implemented it this way.

RSH. "Uwe Schneider" <uwe_at_richard-schneider.de> wrote in message news:3C7D5ADD.ABFA73E0_at_richard-schneider.de...
> Titi wrote:
> >
> > Hi,
> >
> > What is the easiest way to recreate a tablespace with PCTINCREASE = 0 ?
> > Thanks ...
> >
>
> For new segments:
> ALTER TABLESPACE foo DEFAULT STORAGE (PCTINCREASE 0);
>
>
> For existing segments:
>
> spool doit.sql;
> SELECT 'ALTER TABLE ' || table_name || ' STORAGE (PCTINCREASE 0);' FROM
> USER_TABLES WHERE tablespace_name = 'FOO';
> spool off;
> @doit;
>
> The same applies to indexes.
>
> Uwe
>
> --
> Uwe Schneider | Telefon +49 7244 / 609504
> Haydnstr. 1 | Mail uwe_at_richard-schneider.de
> DE-76356 Weingarten | http://www.richard-schneider.de/uwe
> Linux - OS al dente!
Received on Thu Feb 28 2002 - 03:28:24 CST