Comments below
HJR
--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources: http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------
<dmnwork_at_my-deja.com> wrote in message news:8urhqu$21$1_at_nnrp1.deja.com...
>
>
> We're migrating from Oracle 8.1.5 on NT to Oracle 8.1.6.
>
> Rather than upgrade (we'd like this to be clean, and apparently Oracle
> takes it upon itself to leave a lot of junk/directories from the version
> being upgraded from), we'd like to do an export, uninstall 8.1.5,
> install a fresh copy of 8.1.6, then rebuild our database.
>
> But that looks like it could be a nightmare, necessitating a bunch of
> scripts to reconstruct things. Primarily, I think, because - so far as I
> know - the import utility won't know what tablespaces to put tables
> into.
>
'Course it does. It puts 'em into exactly the same tablespace as they came
from. And I mean *exactly* the same. If you've got a DATA1 tablespace now,
and create a tablespace DATA1 in the new database, you'll be fine. If you
create a tablespace DATA01, you'll be out of luck -not exactly the same
tablespace name, so in that case, Oracle will stick the table in the default
tablespace of the User performing the import (and I bet you do this logged
on as SYS or SYSTEM, and I bet you forget to change their default
tablespaces, and accordingly I'll bet that your entire database ends up
sitting in the SYSTEM tablespace. What fun!)
> What's the default tablespace for creating tables, and can import be
> instructed where the correct tablespaces are?
>
See above. Actually, it's not as bad as all of that. A complete export
*does* have the instructions in it to actually create all the relevant
tablespaces, and hence names *will* be exact. The normal problem with that
is that the paths to the tablespaces' data files are also included in the
dump file, and clearly you can't therefore use import as a way of cloning a
database, because it will be trying to create files which already exist. In
your case, since you are wiping the thing out totally, you'll probably be
fine. "Probably" being the keyword.
There is an alternative (apart from the obvious one of creating your own
tablespaces with exactly the same names as the old ones). Create your new
databases with whatever tablespaces you like, and when you run import, say
full=y and show=y, and spool the resulting monster output out to a text
file -you can then edit that to change the old names into the current ones,
and then run the resulting script to re-create all your objects. Finally,
run import once more with ignore=y and rows=y, and you should be in
business. And after reading all of that, you might consider that it would
be far easier to be extremely careful about the naming of your new
tablespaces after all!
> I'd like to make this as painless as possible, and having a script for
> generating each object doesn't make a lot of sense to me. I'd like to
> use the export file as much as possible.
>
> Any insights appreciated.
>
>
> Thanks very much.
>
> - dana
>
>
Select * from v$tablespace before you start blowing the old database away,
and keep the list handy for when you create new tablespaces, and you'll be
fine. Incidentally, the physical size and location of the *data files* is
utterly irrelevant for these purposes -it's the *name* of the tablespace
that counts.
Best of luck
HJR
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Nov 14 2000 - 09:50:28 CST
