Re: Mig from DMTS to LMTS

Sylvestre wrote:
> Hello
>
> Our problem is simple and certainly have been
> already seen
> here, we upgrade our 9.2.0.6 databases to 10gr2,
> we have
> some big dictionary managed tablespace with
> thousands of tables,
> wich are heavily fragmented, finding the best
> practice to migrate them to LMTS with
> ASSM, I have found the classical practices :
> 1) create the new lmts tablespace, move the
> objects to the new one,
> but with thousand of tables how to proceed, by
> script ?, is there
> a tool in 10g wich done automaticaly the work
> ?
>
> 2)use DBMS_SPACE, but we know that we can't have
> ASSM, and
> the fragmentation remain, can we use SHRINK in
> 10g to eliminate it ?
>
> And one of interest, but I was not able to found
> confirmation it work :
>
> 3) migrate to 10g with keeping the dmts, create a
> new 10g instance with lmts tbs,
> and use datapump to export objects from the
> first(dmts) to the new(lmts), and then
> using transportable tablespace, back to
> original instance...I know that datapump
> allow to select objects of a tbs, but I also
> read that we can get error ORA-25150 doing
> that...
>

1. IIRC correctly 9i OEM has such a tool. In 10g this would be probably relocated to DB control or Grid Control. It may be in the Change Management Pack and it that case you need to pay for it. The full version of Toad has also a tool for it. Alternatively you could write some PL/SQL. It is not that difficult! If you don't do it online you only need to alter table move and alter index rebuild

The basic algorithm would be something like

for i in (select table_name from user_tables)

   collect a list of index in a pl/sql table    execute immediate 'alter table '||i.table_name||' move ...';    Loop through the pl//sql table and rebuild indexes end loop;
/

2) DBMS_SPACE is also inreliable.

3 In 10g DMT are DEAD.

-- 
Sybrand Bakker
Senior Oracle DBA





> Thank you for advices..