Re: Oracle 10g & tablespaces

"Frank van Bortel" <frank.van.bortel_at_gmail.com> wrote in message news:d6vtkp$cqi$1_at_news2.zwoll1.ov.home.nl...
> Matthias Hoys wrote:
>> Hi,
>>
>> We are planning the migration of some Oracle 8i databases to Oracle 10g
>> (both on AIX 5.2).
>> For most databases, we currently have only 2 DM tablespaces for user data
>> :
>> one for table data and one for indexes. Maximum table size is around
>> 10GB,
>> there are no objects with more than 1000 extents.
>> What would be the "best practice" for migration of those tablespaces to
>> Oracle 10g ? Use separate LM tablespaces for small, medium and big
>> objects
>> with uniform extent sizing ? Or use only 1 LM tablespace with automatic
>> extent sizing ? And what would be the best way to load the data into the
>> new
>> database ? Exp/imp ? Data pump ? Transportable tablespaces ? Can we
>> change
>> the name of the tablespaces during the import ?
>>
>> Thanks in advance,
>> Matthias
>
> yes, no, yellow, no, no, yes, 3 up, red - phew!
>
> Seriously: best practices are many, because they all depend...
> How much downtime can you afford? Do you have a 3rd party
> "maintenance" tools (like SAP)? Are there problems with spacing,
> sizing and extends you want to fix during the process?
>
> OK - you have *tables* of about 10GB - what about the total size of
> the export file? You could:
> - create the 10g database, with LMT and Auto space management, and
> create tables as CTAS_at_remote. Rebuild indexes, and analyze statistics.
> Depending on the (temporary?) network link between the two instances,
> it can be fast.
> - export (with or without indexes) without statistics(!), and import.
> Tablespace names can be "altered" by granting default tablespace, and
> revoking from others. In fact, the objects fail to be created in the
> TS mentioned in the export dump file, and get created where they can:
> in the default tablespace.
> - TTS will not work between versions (you verify that...)
> - tablespaces of LOB objects have to be named exactly as they were.
> --
> Regards,
> Frank van Bortel

• downtime : we can do it in a week-end
• no SAP
• space issues : we currently have a lot of fragmentation because of the large differences between extent sizes
• total size of full export dump for largest db is around 50GB
• no LOB objects ...

Thanks for the help,
Matthias Received on Tue May 24 2005 - 14:32:26 CDT