| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 10g & tablespaces
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. --
 |
 |