Re: locally managed tablespaces

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 17 Apr 2002 22:18:15 +0200
Message-ID: <ubrm30qi86qr73@corp.supernews.com>

"farah nur" <gargaare_at_yahoo.com> wrote in message news:257c0775.0204171046.61c065b6_at_posting.google.com...
> Hello,
>
> I am trying to fully migrate oracle applications tablespaces (apps
> 1155, db 817) to fully Locally managed tablespaces. The database is
> 200 Gig.
>
> Here is what I was planning to do:
>
> -exp schema
> -exp data
>
> -create new database with LMT tbs
>
> -imp schema
> -drop indexes
> -disable primary keys
> -imp data
> -enable primary keys
> -recreate indexes
>
> However, I am finding out since the apps modules are bundled now,
> there thousands of indexes to deal with, which will increase down time
> when dropping and recreating.
>
> Has anyone done a better way?
>
> Thanks

The dbms_space_admin package is capable of converting dictionary managed tablespaces to lmt and vice versa
Instead of

-imp schema
-drop indexes
-disable primary keys
-imp data
-enable primary keys
-recreate indexes

You should
imp ... indexfile=<your indexfile>
imp indexes=n
sqlplus @your indexfile
Indexfile is probably in need to be edited to add the nologging clause everywhere, the change the tablespace , and to delete the storage parameters For PKs simply use
alter index <index_name> rebuild
tablespace <new_tablespace>

or even
alter index <index_name> move

--
Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address

Received on Wed Apr 17 2002 - 15:18:15 CDT