In our last gripping episode ashish25_at_my-deja.com wrote:
> 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.
>
Without generating a log file of the import session (using the log
parameter) the errors generated are most likely lost. Try importing
the partitioned table again, this time generating a log of the import
activity:
imp user/pass file=filename.dmp tables=(<partitioned table name>)
buffer=8192000 commit=y log=<partitioned table name>.log
Any errors generated by the import will also be saved to the log file.
Once the errors can be determined the behaviour can be deduced.
--
David Fitzjarrell
Oracle Certified DBA
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 14 2000 - 13:10:49 CST
