ora-1950 on import

Chuck

unread,

May 15, 2008, 9:33:34 AM5/15/08

to

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?

StefanKapitza

unread,

May 15, 2008, 12:09:51 PM5/15/08

to

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

Mark D Powell

unread,

May 15, 2008, 12:29:01 PM5/15/08

to

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

joel garry

unread,

May 15, 2008, 2:03:45 PM5/15/08

to

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.

Don't know if this has anything to do with your problem (are you
partitioning?), but that seems strange to me.

Anyways, there's also some left-field stuff on metalink about how
revoking certain grants from roles can have the effect of undoing
unlimited ts, if you've done anything like revoke resource, very odd
for a privilege you can't grant to a role.

jg
--
@home.com is bogus.
But what if someone gave me a PDA and it just sits on the shelf?
http://www.signonsandiego.com/uniontrib/20080515/news_1n15savvy.html

Chuck

unread,

May 16, 2008, 10:07:12 AM5/16/08

to

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

Mark D Powell

unread,

May 19, 2008, 10:33:09 AM5/19/08

to

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?

Chuck

unread,

May 21, 2008, 8:43:58 AM5/21/08

to

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.