Using FROMUSER/TOUSER Fails to Generate Tables With LOBs into TOUSER Tablespace

(Doc ID 91969.1)

Last updated on JUNE 09, 2009

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.5.0
This problem can occur on any platform.

Symptoms

In 8.1.5, an import FROMUSER/TOUSER including a table with LOB clauses cannot import the table and its LOB segments into the TOUSER's tablespace though the Import utility can be forced into placing objects into the TOUSER's tablespace using the traditionnal method:

1. Set QUOTA=0 on the FROMUSER's tablespace for the schema which will own the objects after import.

2. Set that TOUSER's DEFAULT TABLESPACE to the intended destination.

3. Make sure the user has sufficient QUOTA on the destination tablespace.

4. Perform the import.
The import utility will still try to import those objects into the original tablespace.

Even if the TOUSER has no QUOTA on the FROMUSER's tablespace, the operation will fail and the utility will not check the TOUSER's default tablespace and will not try to import the objects into the TOUSER's tablespace.

Example:

SQL> CREATE TABLE SCOTT.CIRCUL  
(CIRCUL_ID VARCHAR2(8),  
FIC_CIRCUL BLOB)  
TABLESPACE USERS  
LOB (FIC_CIRCUL)  
STORE AS (  
TABLESPACE USERS ENABLE STORAGE IN ROW CHUNK 2048  
PCTVERSION 10 NOCACHE LOGGING  
STORAGE (INITIAL 10240 NEXT 10240  
MINEXTENTS 1 MAXEXTENTS 121  
PCTINCREASE 50 FREELISTS 1)  
);  
$exp system/manager tables=scott.circul
SQL> DROP TABLE SCOTT.CIRCUL;

Before the import, the user U1 has:

* the tablespace NEW_TBS as default tablespace
* a quota of 0 bytes on SCOTT's default tablespace USERS
* an unlimited quota on the tablespace NEW_TBS

$imp system/manager fromuser=scott touser=u1 tables=\(circul\)  

Import: Release 8.1.5.0.0 - Production on Fri Dec 17 08:58:20 1999  
(c) Copyright 1999 Oracle Corporation. All rights reserved.  

Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production  
With the Partitioning and Java options  
PL/SQL Release 8.1.5.0.0 - Production  

Export file created by EXPORT:V08.01.05 via conventional path  
import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set  
import server uses WE8ISO8859P1 character set (possible charset conversion)  
IMP-00017: following statement failed with ORACLE error 1536:  
"CREATE TABLE "CIRCUL" ("CIRCUL_ID" VARCHAR2(8), "FIC_CIRCUL"  
"" BLOB) STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121  
PCTINCREASE 50 FREELISTS 1"  
" FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOB ("FIC_CIRCUL"  
"") STORE AS (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 2048 PCTVERSION"  
" 10 NOCACHE LOGGING STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTEN"  
"TS 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"  
IMP-00003: ORACLE error 1536 encountered  
ORA-01536: space quota exceeded for tablespace 'USERS'  
Import terminated successfully with warnings. 

Before the import, the user U1 has:

* the tablespace NEW_TBS as default tablespace
* no quota on SCOTT's default tablespace USERS
* an unlimited quota on the tablespace NEW_TBS

$imp system/manager fromuser=scott touser=u1 tables=\(circul\) 
Import: Release 8.1.5.0.0 - Production on Fri Dec 17 17:20:07 1999 

(c) Copyright 1999 Oracle Corporation. All rights reserved. 
Connected to: Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production 
With the Partitioning and Java options 
PL/SQL Release 8.1.5.0.0 - Production 

Export file created by EXPORT:V08.01.05 via conventional path 
import done in US7ASCII character set and WE8ISO8859P1 NCHAR character set 
import server uses WE8ISO8859P1 character set (possible charset conversion) 
IMP-00017: following statement failed with ORACLE error 1950: 
"CREATE TABLE "CIRCUL" ("CIRCUL_ID" VARCHAR2(8), "FIC_CIRCUL" BLOB) 
TABLESPACE "USERS" LOB ("FIC_CIRCUL") STORE AS (TABLESPACE "USERS" ENABLE 
STORAGE IN ROW CHUNK 2048 PCTVERSION 10 NOCACHE LOGGING 
STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121 
PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))" 
IMP-00003: ORACLE error 1950 encountered 
ORA-01950: no privileges on tablespace 'USERS' 
Import terminated successfully with warnings. 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms