Re: interpreting tablespace settings

You typically reorg using export and import for two space management reasons.

1. The tablespace no longer has any contiguous free space large enough for an objects next extent to fit in. (well hopefully you do it before this point !).
2. An object has 'too many' extents.

With LMT's of UNIFORM extent size issue 1 simply canot occur. Ever. (unless the tablespace itself is full up so you just need to add a datafile).

If an object has 'too many' extents you issue 'ALTER TABLE BLAH MOVE TABLESPACE <LMT WITH LARGER EXTENT SIZES>;' and rebuild the indexes. If its an index that needs moving issue 'ALTER INDEX blah REBUILD <LMT WITH LARGER EXTENT SIZES>;' NB You might still wish to reorg a database for other reasons - consolidating tablespaces is a possibility.

Nuno has raised legitimate concerns it seems to me about the use of a bitmap for extent allocation. I haven't yet had any problems that I can recall with LMT's.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************
"Charlie Edwards" <Charlie3101_at_hotmail.com> wrote in message
news:db479d88.0206130106.183f8f3e_at_posting.google.com...
> "Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message
news:<3d0513f4$0$230$cc9e4d1f_at_news.dial.pipex.com>...
> <snip>


> >                                                        If I'm right

> > there's a big market opportunity for consultants to go in export a db,

> > recreate it with LMT's and re-import, selling it as the last re-org that

the


> > db will need for space mangement reasons. (Obviously I'd be prepared to

do


> > this for a suitable fee!)

> <snip>
>
> I love the statement "the last re-org that the db will need for space
> mangement reasons".  But why, exactly, is this?  My DBA is still
> creating new databases (8.1.7) with dictionary managed tablespaces,
> and I want to persuade him otherwise (he likes having tables and
> indexes in seperate tablespaces too).  Maybe it's because we are still
> using Designer v1.3.2, which only supports 7.3, but I don't really
> know - I am barely on speaking terms with him at the moment.
>
> CE