OraFAQ Forum: Server Utilities » Oracle10g imp tables containing LOB

Home » RDBMS Server » Server Utilities » Oracle10g imp tables containing LOB (Oracle 10.2.0.4 linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Oracle10g imp tables containing LOB [message #483603]

Tue, 23 November 2010 07:29

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

Hi there, I am still very new to Oracle and I have an unsolved issue, google a lot but still can't figure it out. Would appreciate any thoughts.

A full database exported from Oracle 10.2.0.4 and import back to the same instance with different user/schema name. The whole database is in single tablespace, SOURCE_TS. Since SOURCE_TS already exist in the same instance. A brand new user and its default tablespace will be created before the import. SOURCE_TS cannot be access by this new user.

Since my database has many tables containing LOB column, those LOB tables will be pre-create before the import start. Initially, those LOB tables imported successfully but at one stage, it fails. Subsequent LOB tables all failed since then.

Quote:

IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "I43NAV_LOC_VEC" ("VID" NUMBER NOT NULL ENABLE, "BLK_NO" N"
"UMBER NOT NULL ENABLE, "DATATYPE" NUMBER, "VEC_STORAGE_TYPE" VARCHAR2(12) N"
"OT NULL ENABLE, "REL_PATH" VARCHAR2(256), "BYTES_USED" NUMBER, "BLOB_DAT"
"A" BLOB, "BFILE_DATA" BFILE) PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 "
"STORAGE(INITIAL 81920 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TA"
"BLESPACE "SOURCE_TS" LOGGING NOCOMPRESS LOB ("BLOB_DATA") STORE AS ("
"TABLESPACE "SOURCE_TS" ENABLE STORAGE IN ROW CHUNK 16384 PCTVERSION 1"
"0 NOCACHE LOGGING STORAGE(INITIAL 81920 FREELISTS 1 FREELIST GROUPS 1 BUFF"
"ER_POOL DEFAULT))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'SOURCE_TS'

I am not able to find out any reason why it fails, since out of 150, 80 of my tables containing LOB column were imported correctly suggested that my imp parameters were correct.

It doesn't seems to be my backup file problem too, I tried twice and the first LOB table failed is not the same with the previous attempts.

Initially I thought might be quota, but unlimited quota to the new tablespace is granted. Even if it is not granted, the error returned would be complaining about quota limit reached in the new tablespace, not SOURCE_TS.

If this is not about disk space or quota issue, in what condition the imp will tries to create table in SOURCE_TS, instead of my new tablespace? I will need to understand the IMP utility logic to sort this out.

Thanks for your time.

Attachment: log.txt
(Size: 0.78KB, Downloaded 1706 times)

[Updated on: Tue, 23 November 2010 07:31]

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483606 is a reply to message #483603]

Tue, 23 November 2010 07:45

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Sounds quite obvious.

user (who has privileges on SOURCE_TS tablespace) (who also owns those tables) created an export file
another user, who does not have access to SOURCE_TS is performing import - that's what you said:Quote:

SOURCE_TS cannot be access by this new user.
table is supposed to end up in SOURCE_TS:Quote:

IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "I43NAV_LOC_VEC" ...
"TABLESPACE "SOURCE_TS"...
So I'd say that Oracle is right when saying
Oracle
ORA-01950: no privileges on tablespace 'string'

Cause: User does not have privileges to allocate an extent in the specified tablespace.

Action: Grant the user the appropriate system privileges or grant the user space resource on the tablespace.

Or did I get it wrong?

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483607 is a reply to message #483606]

Tue, 23 November 2010 07:51

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

Hi thanks for your reply Smile

A new user and a new tablespace is created beforehand to import those tables.

The new user default tablespace is the new tablespace.

By right, imp utility should import those tables into my new tablespace, no? I use IGNORE=Y option in imp.

Most of the tables has been imported to the new tablespace correctly, except half my of tables containing LOB column.

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483610 is a reply to message #483607]

Tue, 23 November 2010 08:03

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

It is the intended behavior dictated by
Quote:

LOB ("BLOB_DATA") STORE AS ("
"TABLESPACE "SOURCE_TS" ENABLE

During import, Oracle will try to write to Source_TS for lobsegments.
Extract the ddl, pre-create the tables with tablespace_name you want.
Import with ignore=y.
or
just use datapump with remap options.

[Updated on: Tue, 23 November 2010 08:04]

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483612 is a reply to message #483610]

Tue, 23 November 2010 08:08

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

Hi Manesh,

Thanks for your opinion, I know this is how imp handle multi segments or LOB tables, but the application had done that.

As I mentioned earlier, more than half of my LOB tables are imported successfully, suggested that tables have been pre-created, and yes, I run with IGNORE=Y.

You think the only reason, the other half of my LOB tables have not been pre-created? This is the only possibility imp utility will attempts to create the table at SOURCE_TS?

It is 10pm and I am still in office trying to figure this out. Pathetic. I have to go now.

Thanks for all your kind replies and thoughts. Appreciated.

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483613 is a reply to message #483612]

Tue, 23 November 2010 08:14

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>You think the only reason, the other half of my LOB tables have not been pre-created?
Pre-created, yes. But with the correct tablespace?
I dont think so, unless your import sessions is lying Smile

.
Your import session is looking for SOURCE_TS.

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483646 is a reply to message #483613]

Tue, 23 November 2010 18:53

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

Can you please correct me if I am wrong in the following concept?

When I exp my tables, the oracle dump file contained DDL to re-create ALL those tables, constraints, indexes etc, with destination tablespace SOURCE_TS. (well, I see that in DDL produced with INDEXFILE)

When I import the Oracle dump file to another tablespace, with IGNORE=Y and pre-created LOB tables in new tablespace (not old tablespace, user should not write into SOURCE_TS, so no permission granted), it should ignore that DDL to create in SOURCE_TS, instead it should creates those tables in default tablespace, which is my new tablespace.

That is happening with all my simple tables and some of my LOB tables, except the other half of my LOB tables. The entire tables are in a single tablespace.

One possible cause is, of course, like you said, those failed LOB tables, have not been pre-created in my New Tablespace. That is very much possible, and is the only reason I can thought of.

But since it is created by a program, I have no way to intercept in between to check if half of tables are pre-created before the import starts (the program pre-create those tables and run the import in a single session).

I am pursuing the issue with the program vendor currently, but would like to know any other possible reason imp tries to create table in original tablespace as in DDL, apart from pre-create LOB tables.

I am trying to learn how the IMP tool logic works, like "if no pre-create LOB table lies around for default tablespace, I will try to create the table in original tablespace, but I will fail if that tablespace doesn't exist or I have not granted create table role".

Manish, what you were saying is, only when LOB tables were not pre-created in default tablespace, the imp session will tries to create LOB tables in its original tablespace.

Imp session not lying, so that is the only cause, I guess?

[Updated on: Tue, 23 November 2010 19:08]

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483647 is a reply to message #483646]

Tue, 23 November 2010 19:21

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

With ignore=y, Oracle will just ignore any errors due to existence of tables and proceed.
If the table already exists, the error is suppressed and data is loaded.
else
table will be created (based on DDL in dumpfile) and data will be loaded.
Seems you cannot use datapump.
If you can afford,
why not just let import process do the job the way it does?
I mean, open up SOURCE_TS to new user (write permissions).
After import job is done, some lob objects will be in SOURCE_TS.
Just move those tables/lob segments to NEW_TS.

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483648 is a reply to message #483647]

Tue, 23 November 2010 19:44

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

Thank you very much for your explanation. I wanted to get a particular archived dump file loaded, and it was dump by EXP utility. So I am not considering datapump here.

Too bad I cannot mess with existing SOURCE_TS. SOURCE_TS is an active ongoing project database.

So IMP will just use DDL in dumpfile unless we/program explicitly modify it? IMP is not going to create even simple tables in default tablespace when SOURCE_TS not available? I always thought it can, looks like I was wrong.

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483649 is a reply to message #483648]

Tue, 23 November 2010 19:55

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Data segment or lobsegment/lobindex,
the user/schema needs write privs on corresponding tablespace.
>>SOURCE_TS is an active ongoing project database.
It should not mess with anything, except storage.
you need the SOURCE_TS big enough to hold objects to data from both schema.

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #483727 is a reply to message #483649]

Wed, 24 November 2010 05:13

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

Thanks for your alternative solution, but won't go that way. My database model forbid me from doing so. SOURCE_TS might be drop anytime and this is not an intended workflows to move object from a project to another.

Actually I would like to figure out why part of LOB tables works, and the other half not, so I can fix that issue.

Anyway, thank you very much for all your thoughts.

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #484024 is a reply to message #483727]

Fri, 26 November 2010 07:17

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

Interesting, with the same Oracle dump file, some import attempts works.

Same program to pre-create LOB tables before start import. In the successful attempts, all LOBS table created beforehand. I manage to peep at the number of tables (around 245 LOB tables precreated in the target tablespace before import starts)

In all failed attempts, those LOB tables are not in the tablespace, means something went wrong when creating 245 LOB tables in a go.

Still trying to work it out, will share findings if relevant to Oracle. Smile

Report message to a moderator

Re: Oracle10g imp tables containing LOB [message #487773 is a reply to message #483603]

Fri, 31 December 2010 01:04

junwen
Messages: 12
Registered: November 2010
Location: Malaysia

Junior Member

I got the issue sorted out. This is not an Oracle issue, rather Java Development issue.

Some of the LOB tables indeed didn't get created, while parsing from DDL file obtained via SHOW=Y import.

The parsing was done with Java string buffer.

Thanks for all the hands and guidance.

Report message to a moderator

Previous Topic:	Getting "'localconfig' is not recognized as an internal or external command,"
Next Topic:	SQL Loader REG - Number values

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 10:47:07 CST 2024