Re: Mig from DMTS to LMTS

From: Sylvestre <Sylvestre_at_world.com>
Date: Sun, 26 Nov 2006 22:01:11 +0100
Message-ID: <456a009a$0$31685$426a34cc@news.free.fr>

Thank you for answer.

There is two questions non solved :
1) Is it possible to migrate a database(9.2) to 10g with dictionary managed tbs, keeping them dmts ?
2)Is it possible , in 10g, to use datapump to migrate from dmts to lmts ?

Yes it is true that by default 10g create the system tbs in lmts, but it is also true that oracle continue
to support dmts in 10g..

Thank yu

"Charles Hooper" <hooperc2000_at_yahoo.com> a écrit dans le message de news:
1164227053.666471.323630_at_k70g2000cwa.googlegroups.com...
> 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...
>>
>> Thank you for advices..
>

> A method that I have used is to create the new
> locally managed
> tablespace, and issue ALTER TABLE MOVE commands
> to move the tables to
> the newly created tablespace. You then need to
> move/rebuild the
> indexes for the affected tables. It is possible
> to create a SQL
> statement which builds other SQL statements to
> assist with the task.
> For example, the following:
> SELECT
> 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||'
> REBUILD TABLESPACE
> INDEX_DATA2 NOLOGGING;'
> FROM
> DBA_INDEXES
> WHERE
> OWNER='MY_USER_HERE'
> AND TABLESPACE_NAME='INDEX_DATA'
> ORDER BY
> TABLE_NAME,
> INDEX_NAME;

>

> The above creates SQL statements that will move
> each index owned by the
> user MY_USER_HERE which is currently in the
> INDEX_DATA tablespace into
> the INDEX_DATA2 tablespace, while minimizing the
> amount of redo
> generated. Create a backup of the database
> before and after the
> changes. The move should take place when there
> is minimal activity in
> the database.

>

> Another option is to export the data using exp
> or expdp (if in 10g),
> build a new database instance, precreate all
> tablespaces as locally
> managed, and then import the data. This is
> obviously not the best
> approach.

>

> Dictionary managed tablespaces are _potentially_
> dead in 10g, if the
> SYSTEM tablespace is created as locally managed,
> which is the default
> for the graphical database creation utility for
> Oracle.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
>
Received on Sun Nov 26 2006 - 15:01:11 CST