SEO

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

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

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.

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@yahoo.com> a écrit
dans le message de news:
1164227053.666471.323630@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.
>

Sylvestre wrote:
> "Charles Hooper" <hooperc2000@yahoo.com> a écrit
> dans le message de news:
> 1164227053.666471.323630@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...ments_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...1/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...s.htm#i1106606

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc.

Sylvestre wrote:
> 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
> ?

Why would you think this is desirable?
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Hello Thank yu for answer, I will run some test.
Yes my customer want to keep existing DMTS
because, they are very big
and they think reorg will cost money...

"DA Morgan" <damorgan@psoug.org> a écrit dans le
message de news:
1164644798.145975@bubbleator.drizzle.com...
> Sylvestre wrote:
>> 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 ?
>
> Why would you think this is desirable?
> --
> Daniel A. Morgan
> University of Washington
> damorgan@x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Group
> www.psoug.org

Sylvestre wrote:
> Hello Thank yu for answer, I will run some test.
> Yes my customer want to keep existing DMTS
> because, they are very big
> and they think reorg will cost money...
>
> "DA Morgan" <damorgan@psoug.org> a écrit dans le
> message de news:
> 1164644798.145975@bubbleator.drizzle.com...
>> Sylvestre wrote:
>>> 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 ?
>> Why would you think this is desirable?
>> --
>> Daniel A. Morgan
>> University of Washington
>> damorgan@x.washington.edu
>> (replace x with u to respond)
>> Puget Sound Oracle Users Group
>> www.psoug.org

Please don't top post. Scroll to the bottom to reply in this forum.

Your customer is incorrect. The improved efficiency of LMT more than
offsets the change-over.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org