In our last gripping episode ashish25_at_my-deja.com wrote:
I am going to make what I hope is an educated guess on this one, since
I cannot find any information in the documentation.
My feeling is that since the tablespaces holding the partitions do not
exist the creation of the partitioned table fails. It may be a 'bug'
that reports the PLDMD1TS (the initial tablespace for the table)
missing; my estimation is that the first tablespace imp is trying to
access doesn't exist and the table create fails there.
Maybe someone else has experience with this.
> Hi David,
> Here is the log file.
> onnected to: Oracle8i Enterprise Edition Release 8.1.6.1.0 -
Production
> With the Partitioning option
> JServer Release 8.1.6.0.0 - Production
>
> Export file created by EXPORT:V08.01.06 via conventional path
> import done in US7ASCII character set and US7ASCII NCHAR character set
> . importing NOKAPP's objects into NOKAPP
> IMP-00017: following statement failed with ORACLE error 959:
> "CREATE TABLE "BOUND_MEASUREMENT" ("LATITUDE" NUMBER(10,
> 0), "LONGITUDE" NUM"
> "BER(10, 0), "MEASUREMENT_FILE_ID" NUMBER(12, 0) NOT NULL ENABLE)
> PCTFREE 10 PCTUSED 40 INITRANS 1"
> " MAXTRANS 255 LOGGING STORAGE(INITIAL 267386880 NEXT 267386880
> MINEXTENTS 2"
> " MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 8)
> TABLESPACE "PLDMD1TS" PAR"
> "TITION BY HASH ("MEASUREMENT_FILE_ID" ) (PARTITION "P1"
> TABLESPACE "PLDMD"
> "ATS", PARTITION "P2" TABLESPACE "PLDMDATS", PARTITION "P3"
> TABLESPACE "PL"
> "DMDATS", PARTITION "P4" TABLESPACE "PLDMDATS", PARTITION "P5"
> TABLESPACE "
> ""PLDMDATS", PARTITION "P6" TABLESPACE "PLDMDATS", PARTITION "P7"
> TABLESPA"
> "CE "PLDMDATS", PARTITION "P8" TABLESPACE "PLDMDATS" )"
> IMP-00003: ORACLE error 959 encountered
> ORA-00959: tablespace 'PLDMD1TS' does not exist
> Import terminated successfully with warnings.
>
> The interesting thing is user which is being imported has default
> tablespace pldmd1ts but table doesn't have any partition in that
> tablespace.
> Thanks,
> Ashish
>
> In article <8us2nh$g2r$1_at_nnrp1.deja.com>,
> David Fitzjarrell <oratune_at_aol.com> wrote:
> > 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.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
--
David Fitzjarrell
Oracle Certified DBA
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Tue Nov 14 2000 - 15:22:50 CST
