Re: Mig from DMTS to LMTS

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

> 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
>

Question #1:
It is still possible to create new tablespaces as dictionary managed tablespaces in Oracle 10g:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7003.htm

"Specify LOCAL if you want the tablespace to be locally managed. Locally managed tablespaces have some part of the tablespace set aside for a bitmap. This is the default for permanent tablespaces. Temporary tablespaces are always automatically created with locally managed extents.

AUTOALLOCATE specifies that the tablespace is system managed. Users cannot specify an extent size. You cannot specify AUTOALLOCATE for a temporary tablespace.

UNIFORM specifies that the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE. You cannot specify UNIFORM for an undo tablespace.

Specify DICTIONARY if you want the tablespace to be managed using dictionary tables.

Restriction on Dictionary-managed Tablespaces You cannot specify DICTIONARY if the SYSTEM tablespace of the database is locally managed or if you have specified the temporary_tablespace_clause."

Migrating a 9.2 database to 10g will not convert the existing dictionary managed tablespaces to locally managed.

According to the documentation, DBMS_SPACE_ADMIN. TABLESPACE_MIGRATE_TO_LOCAL could be used to migrate a dictionary managed tablespace to locally managed.

http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/tspaces.htm

I believe that Sybrand commented in this thread that DBMS_SPACE is unreliable, so maybe this is not the best approach.

Question #2:
Yes, it is possible to convert from dictionary managed tablespaces to locally managed using datapump. Export the data (expdp), drop the tablespaces including contents, pre-create the tablespaces as locally managed, and then import the data (impdp). I would strongly advise against using this method, because if something goes wrong (and the back ups are no good), all of the data may be unusable.

If you are moving the database instance from one server to another as part of the 9.2 to 10g conversion, consider using exp on the old server, precreate the tablespaces as locally managed on the new server, and use imp to read the data into the new database. If something goes wrong (create and check the export/import logs), fix the problem, then repeat as many times as necessary.

If you decide to just migrate from 9.2 to 10g on the same server, consider creating additional locally managed tablespaces. At a later time, you can move the tables, indexes, and other objects to those locally managed tablespaces. Note: if you move a table, you must recreate/rebuild the table's indexes.
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14231/tables.htm#i1106606

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