Re: imports - which tablespaces will tables go into?

I agree that indexfile is much easier, David -it's just that it's also seriously incomplete, in the sense that it won't contain procedures and triggers and (from memory) Users etc etc etc. I use indexfile all the time, provided my requirements are straightforward -but if I'm migrating an entire database, missing out all that information is going to be a bit of swine, really.

Regards
HJR

--
---------------------------------------------------------------------------
Opinions expressed are my own, and not those of Oracle Corporation
Oracle DBA Resources:               http://www.geocities.com/howardjr2000
---------------------------------------------------------------------------


"David Fitzjarrell" <oratune_at_aol.com> wrote in message
news:8urop5$6jp$1_at_nnrp1.deja.com...


> 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.