In our last gripping episode "Howard J. Rogers" <howardjr_at_www.com>
wrote:
> 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.
>
>
Pretty much what I stated in a parallel post, however yours has much
more pinache -- must be that British vocabulary and vernacular.
One slight niggle -- use the indexfile parameter instead of show.
You'll receive a script of CREATE TABLE/CREATE INDEX statements that is
much easier to edit than the monstrosity generated by show=y.
--
David Fitzjarrell
Oracle Certified DBA
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 14 2000 - 10:21:03 CST
