SEO

#1 (**permalink**) 05-16-2008, 01:39 PM

What would cause this? I am exporting data from schema S1 in database
D1, and importing into schema S2 in database D2. In S1, everything is in
the USERS tablespace. I set up the default tablespace in S2 to be TS2,
grant unlimited quota on TS2 and no quota on USERS. S2 btw is starting
out empty. Out of 100 tables, 96 of them correctly loaded into
tablespace TS2, however on 4 of them I got ora-1950 - no privileges on
tablespace 'USERS'. Why?

#2 (**permalink**) 05-16-2008, 01:39 PM

On May 15, 3:33 pm, Chuck <skilover_nos...@bluebottle.com> wrote:
> What would cause this? I am exporting data from schema S1 in database
> D1, and importing into schema S2 in database D2. In S1, everything is in
> the USERS tablespace. I set up the default tablespace in S2 to be TS2,
> grant unlimited quota on TS2 and no quota on USERS. S2 btw is starting
> out empty. Out of 100 tables, 96 of them correctly loaded into
> tablespace TS2, however on 4 of them I got ora-1950 - no privileges on
> tablespace 'USERS'. Why?

4 Tables are different than the others (obvious answer).

IIRC blobs or lobs or something like that are
showing this behaviour, Solution is to create
the Tables in advance with the desired tablespace and
do an import with ignore=yes.

regards.

s.kapitza

#3 (**permalink**) 05-16-2008, 01:39 PM

On May 15, 12:09*pm, StefanKapitza <skapi...@volcanomail.com> wrote:
> On May 15, 3:33 pm, Chuck <skilover_nos...@bluebottle.com> wrote:
>
> > What would cause this? I am exporting data from schema S1 in database
> > D1, and importing into schema S2 in database D2. In S1, everything is in
> > the USERS tablespace. I set up the default tablespace in S2 to be TS2,
> > grant unlimited quota on TS2 and no quota on USERS. S2 btw is starting
> > out empty. Out of 100 tables, 96 of them correctly loaded into
> > tablespace TS2, however on 4 of them I got ora-1950 - no privileges on
> > tablespace 'USERS'. Why?
>
> 4 Tables are different than the others (obvious answer).
>
> IIRC blobs or lobs or something like that are
> showing this behaviour, Solution is to create
> the Tables in advance with the desired tablespace and
> do an import with ignore=yes.
>
> regards.
>
> s.kapitza

Chuck, you should always include the full Oracle database version and
explicitly name the utility being used (since there are two different
Oracle import utilities) as well as include the version of the Oracle
cleint when the import is not done on the database server.

There is as Stefan mentioned a bug where LOB tables will not pick up
the importing touser default tablespace so if the LOB table tablespace
does not exist the table fails to import. I cannot remember the error
message. Stefan provided the solution in this case.

You should be able to use the show=y feature to extract the table DDL,
pre-create the table, and do a tables= import to bring the data.

HTH -- Mark D Powell --

#4 (**permalink**) 05-18-2008, 10:01 PM

joel garry wrote:

> Poking about on metalink, I noticed bug 7013409 (for 11g). It seems
> to be saying unlimited tablespace does not apply to partitioned
> tables??? Maybe I'm misreading into it.

Bingo. At least for one table anyway. It is partitioned. I'm still
scratching my head for the other 3.

FYI the source DB version is 9.2.0.5. The target DB is 10.2.0.3. The
utility is import, (data datapump).

#5 (**permalink**) 05-20-2008, 05:55 PM

On May 16, 10:07*am, Chuck <skilover_nos...@bluebottle.com> wrote:
> joel garry wrote:
> > Poking about on metalink, I noticed bug 7013409 (for 11g). *It seems
> > to be saying unlimited tablespace does not apply to partitioned
> > tables??? *Maybe I'm misreading into it.
>
> Bingo. At least for one table anyway. It is partitioned. I'm still
> scratching my head for the other 3.
>
> FYI the source DB version is 9.2.0.5. The target DB is 10.2.0.3. The
> utility is import, (data datapump).

Datapump would be impdp which is a 10g utility and if the export was
made on 9.2 I would expect it to be made using exp so imp should be
the utility used for the import.

Exactly how as the export made and which utility by name is being used
to perform the import?

If you grant quota and rerun the import for the failed tables what
happens?

-- Mark D Powell --

#6 (**permalink**) 05-24-2008, 06:08 AM

Mark D Powell wrote:

>
> Exactly how as the export made and which utility by name is being used
> to perform the import?

Like you said, the source DB is 9i so the utility used was exp. It had
to be.

> If you grant quota and rerun the import for the failed tables what
> happens?

If I grant quota on the tablespace with the same name as they were in on
the source db, they load just fine.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

ora-1950 on import