Hi,
I took the export of a user from one database
and imported that user in the second database.I didn't have all the
same tablespaces.All the tables were created in the default tablespace
of the user.Only table which didn't get created was a partitioned
table??Is it a bug?? or it behaves differently in case of a partitioned
table.
Thanks,
Ashish
In article <8urop5$6jp$1_at_nnrp1.deja.com>,
David Fitzjarrell <oratune_at_aol.com> wrote:
> 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.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 14 2000 - 10:53:46 CST
