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...
>
> 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 Wed Nov 22 2006 - 14:24:13 CST