Skip to Main Content
  • Questions
  • Import database objects to new tablespace.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Michael.

Asked: June 26, 2006 - 5:19 pm UTC

Answered by: Tom Kyte - Last updated: February 25, 2012 - 5:04 am UTC

Category: Database - Version: 9.2.0.7

Viewed 10K+ times! This question is

You Asked

I am trying to move objects from one tablespace to another tablespace using export and import.

Is there any way to avoid the errors below?

ORA-01950: no privileges on tablespace 'USERS'

Thank you for your time.

C:\>exp userid=imptest/imp file=exp.dmp

Export: Release 9.2.0.7.0 - Production on Mon Jun 26 14:09:39 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses US7ASCII character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user IMPTEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user IMPTEST
About to export IMPTEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export IMPTEST's tables via Conventional Path ...
. . exporting table DEMO_IOT 6091 rows exported
. . exporting table MYDBAOBJECTS 0 rows exported
. . exporting table NEWS_TEST 0 rows exported
. . exporting table T 100 rows exported
. . exporting table TESTUNDO 0 rows exported
. . exporting table TT 0 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
EXP-00091: Exporting questionable statistics.
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Jun 26 14:09:55 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

SQL> drop user imptest cascade;
drop user imptest cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected


SQL> drop user imptest cascade;

User dropped.

SQL> create user imptest identified by imp default tablespace moveto temporary tablespace temp;

User created.

SQL> grant connect, resource to imptest;

Grant succeeded.

SQL> revoke unlimited tablespace from imptest;

Revoke succeeded.

SQL> alter user imptest quota unlimited on moveto;

User altered.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

C:\>imp userid=imptest/imp file=exp.dmp ignore=y full=y

Import: Release 9.2.0.7.0 - Production on Mon Jun 26 14:10:37 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses US7ASCII character set (possible charset conversion)
. importing IMPTEST's objects into IMPTEST
. . importing table "DEMO_IOT" 6091 rows imported
. . importing table "MYDBAOBJECTS" 0 rows imported
IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "NEWS_TEST" ("ID" NUMBER, "BODY" CLOB) PCTFREE 10 PCTUSED 40 "
"INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"
") TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("BODY") STORE AS (TABLESPACE "
""USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STO"
"RAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "T" ("X" NUMBER(*,0), "Y" CLOB) PCTFREE 10 PCTUSED 40 INITRAN"
"S 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLE"
"SPACE "USERS" LOGGING NOCOMPRESS LOB ("Y") STORE AS (TABLESPACE "USERS" EN"
"ABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STORAGE(INITI"
"AL 65536 FREELISTS 1 FREELIST GROUPS 1))"
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace 'USERS'
. . importing table "TESTUNDO" 0 rows imported
. . importing table "TT" 0 rows imported
Import terminated successfully with warnings.

C:\>

and we said...

IMP-00017: following statement failed with ORACLE error 1950:
"CREATE TABLE "NEWS_TEST" ("ID" NUMBER, "BODY" CLOB) PCTFREE 10 PCTUSED 40 "
"INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1"
") TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("BODY") STORE AS (TABLESPACE "
""USERS" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING STO"
"RAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1))"

that is one of those "multi-segment" objects.

Here is a quote from expert one on one Oracle regarding this:

<quote>
Multi-Tablespace CREATES

In the beginning, CREATE TABLE statements were relatively simple. Over the years, they have gotten progressively more and more complex. The 'train tracks' or 'wire diagram' for the simple CREATE TABLE now spans eight pages. One of the newer features of tables is the ability for bits and pieces of them to exist in various tablespaces. For example, a table with a CLOB column will have a table segment, CLOB index segment, and CLOB data segment. We can specify the location of the table and the locations of the CLOB data. An index-organized table (IOT) can have the index segment, and an overflow segment. Partitioned tables of course may have many partitions, each in a separately specified tablespace.

With this complexity comes some confusion for EXP/IMP. It used to be that if you tried to import an object and it failed due to the tablespace either not existing, or because you exceeded your quota on that tablespace, IMP would rewrite the SQL for you to create the object in your DEFAULT tablespace. IMP will not do this with multi-tablespace objects like it will with single tablespace object, even if all of the tablespaces specified in the CREATE are the same. An example will demonstrate the problem, and then I'll describe how we can work around the situation.

First, we'll start with a schema that has a couple of multi-tablespace objects and a simple single tablespace table in a tablespace:

tkyte@TKYTE816> create tablespace exp_test
2 datafile 'c:\oracle\oradata\tkyte816\exp_test.dbf'
3 size 1m
4 extent management local
5 uniform size 64k
6 /
Tablespace created.

tkyte@TKYTE816> alter user tkyte default tablespace exp_test
2 /
User altered.

tkyte@TKYTE816> create table t1
2 ( x int primary key, y varchar2(25) )
3 organization index
4 overflow tablespace exp_test
5 /
Table created.

tkyte@TKYTE816> create table t2
2 ( x int, y clob )
3 /
Table created.

tkyte@TKYTE816> create table t3
2 ( x int,
3 a int default to_char(sysdate,'d')
4 )
5 PARTITION BY RANGE (a)
6 (
7 PARTITION part_1 VALUES LESS THAN(2),
8 PARTITION part_2 VALUES LESS THAN(3),
9 PARTITION part_3 VALUES LESS THAN(4),
10 PARTITION part_4 VALUES LESS THAN(5),
11 PARTITION part_5 VALUES LESS THAN(6),
12 PARTITION part_6 VALUES LESS THAN(7),
13 PARTITION part_7 VALUES LESS THAN(8)
14 )
15 /
Table created.

tkyte@TKYTE816> create table t4 ( x int )
2 /
Table created.

So, we started by creating a tablespace and making this our DEFAULT tablespace. We then created an IOT with two segments – the index and overflow. We created a table with a CLOB that has three segments. Then we have a partitioned table with seven segments. Lastly, we have the normal, simple 'table'. We export this schema:

tkyte@TKYTE816> host exp userid=tkyte/tkyte owner=tkyte

and proceed to drop that tablespace:

tkyte@TKYTE816> drop tablespace exp_test including contents;
Tablespace dropped.

tkyte@TKYTE816> alter user tkyte default tablespace data;
User altered.

When we import the schema we discover most of the tables won't come back in:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y

Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:18 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.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 WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T2" ("X" NUMBER(*,0), "Y" CLOB) PCTFREE 10 PCTUSED 40 INITRA"
"NS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOB "
"("Y") STORE AS (TABLESPACE "EXP_TEST" ENABLE STORAGE IN ROW CHUNK 8192 PCT"
"VERSION 10 NOCACHE STORAGE(INITIAL 65536))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T3" ("X" NUMBER(*,0), "A" NUMBER(*,0)) PCTFREE 10 PCTUSED 40"
" INITRANS 1 MAXTRANS 255 LOGGING TABLESPACE "EXP_TEST" PARTITION BY RANGE ("
""A" ) (PARTITION "PART_1" VALUES LESS THAN (2) PCTFREE 10 PCTUSED 40 INIT"
"RANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, P"
"ARTITION "PART_2" VALUES LESS THAN (3) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "
""PART_3" VALUES LESS THAN (4) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 25"
"5 STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_4" "
"VALUES LESS THAN (5) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE"
"(INITIAL 65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_5" VALUES LE"
"SS THAN (6) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL "
"65536) TABLESPACE "EXP_TEST" LOGGING, PARTITION "PART_6" VALUES LESS THAN ("
"7) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TA"
"BLESPACE "EXP_TEST" LOGGING, PARTITION "PART_7" VALUES LESS THAN (8) PCTFR"
"EE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536) TABLESPACE "
""EXP_TEST" LOGGING )"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
. . importing table "T4" 0 rows imported
IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "T1" ("X" NUMBER(*,0), "Y" VARCHAR2(25), PRIMARY KEY ("X") EN"
"ABLE) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOG"
"GING STORAGE(INITIAL 65536) TABLESPACE "EXP_TEST" PCTTHRESHOLD 50 OVERFLOW "
"PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING STORAGE(INITIAL 6553"
"6) TABLESPACE "EXP_TEST""
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'EXP_TEST' does not exist
Import terminated successfully with warnings.

Specifically, the only table that came back in without an error was the simple 'normal' table. For this table, IMP rewrote the SQL, blanking out the first TABLESPACE EXP_TEST that it came across and retried the CREATE. This rewritten CREATE succeeded. The other CREATES, when likewise rewritten, did not succeed. The only solution to this is to create the tables beforehand and then import with IGNORE=Y. If you do not have the DDL for the CREATE TABLES, you can retrieve it of course, from the DMP file with INDEXFILE=Y to modify it. In this case, since I had the DDL readily handy, I just created the three tables with new tablespaces specified where necessary:

tkyte@TKYTE816> create table t1
2 ( x int primary key, y varchar2(25) )
3 organization index
4 overflow tablespace data
5 /
Table created.

tkyte@TKYTE816> create table t2
2 ( x int, y clob )
3 /
Table created.

tkyte@TKYTE816> create table t3
2 ( x int,
3 a int default to_char(sysdate,'d')
4 )
5 PARTITION BY RANGE (a)
6 (
7 PARTITION part_1 VALUES LESS THAN(2),
8 PARTITION part_2 VALUES LESS THAN(3),
9 PARTITION part_3 VALUES LESS THAN(4),
10 PARTITION part_4 VALUES LESS THAN(5),
11 PARTITION part_5 VALUES LESS THAN(6),
12 PARTITION part_6 VALUES LESS THAN(7),
13 PARTITION part_7 VALUES LESS THAN(8)
14 )
15 /
Table created.

and was able to import cleanly:

tkyte@TKYTE816> host imp userid=tkyte/tkyte full=y ignore=y

Import: Release 8.1.6.0.0 - Production on Tue Mar 20 19:03:20 2001

(c) Copyright 1999 Oracle Corporation. All rights reserved.


Connected to: Oracle8i Enterprise Edition Release 8.1.6.0.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 WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
. importing TKYTE's objects into TKYTE
. . importing table "T2" 0 rows imported
. . importing partition "T3":"PART_1" 0 rows imported
. . importing partition "T3":"PART_2" 0 rows imported
. . importing partition "T3":"PART_3" 0 rows imported
. . importing partition "T3":"PART_4" 0 rows imported
. . importing partition "T3":"PART_5" 0 rows imported
. . importing partition "T3":"PART_6" 0 rows imported
. . importing partition "T3":"PART_7" 0 rows imported
. . importing table "T4" 0 rows imported
. . importing table "T1" 0 rows imported
Import terminated successfully without warnings.

</quote>

and you rated our response

  (11 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

July 10, 2006 - 5:53 am UTC

Reviewer: Naji Ghanim from Amman - Jordan

I want ask if this problem only in Oracle Version 9.2.0.7 or in all releases?

and will Oracle solve it in the future releases?
Or if there is any other way to solve it.

Thanks
Naji

Tom Kyte

Followup  

July 10, 2006 - 7:54 am UTC

for import - the solution provided is going to be it forever more (pre-create the multi-segment object if the tablespaces are NOT THE SAME on the system to be imported into)

for impdp (the data pump) more sophisticated methods exist (but that is not imp/exp anymore - that is a completely different tool)

ORA-00959: tablespace '_$deleted$6$0' does not exist

June 09, 2010 - 4:35 pm UTC

Reviewer: Ravi B from Bay Are,CA

Hi Tom,

I am encountering following error while running import. Any suggestions from you is greatly appreciated.

ORA-00959: tablespace '_$deleted$6$0' does not exist

I am importing to a brand new schema with pre created user/tables/indexes/tablespaces. I got the DDL's from running INDEXFILE=Y option. The CREATE statements have storage options inculding tablespace name. But import logs shows something like this (please see tablespace is missing, there is black space before LOGGING NOCOMPRESS)


Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

"CREATE TABLE "SUPPSTAGES" ("_rid" NUMBER, "_inst" NUMBER, "_enddate" DATE, "
""ORGS_RID" NUMBER, "SUPPORT_RID" NUMBER, "NAME" VARCHAR2(60), "STAGEORDER" "
"NUMBER, "ISFINAL" NUMBER, "_createdate" DATE, "LSD" VARCHAR2(32), "EOL" VAR"
"CHAR2(32), "_src" VARCHAR2(60), "_srccols" VARCHAR2(4000), "DEF" VARCHAR2(2"
"000), "ENDFULL" NUMBER, "OBSOLETE" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS "
"1 MAXTRANS 255 STORAGE(INITIAL 720896 FREELISTS 1 FREELIST GROUPS 1 BUFFER_"
"POOL DEFAULT) LOGGING NOCOMPRESS"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace '_$deleted$6$0' does not exist
IMP-00017: following statement failed with ORACLE error 959:


Tom Kyte

Followup  

June 10, 2010 - 12:20 pm UTC

what is "black space"?

ORA-00959: tablespace _$deleted$ does not exist

June 10, 2010 - 1:05 pm UTC

Reviewer: Ravi B from Bay Area,CA

I mean to say blank.

To Ravi B

June 11, 2010 - 4:05 am UTC

Reviewer: Patrick from Hong Kong

Ravi
Here is my guess as to what is going on. As you can see from the CREATE STATEMENT listed, there is no tablespace defined, so it will be using the current users default tablespace. Check that it is valid. There are cases, especially when you perform tasks such as renaming tablespaces, that it can get messed up.
Tom Kyte

Followup  

June 11, 2010 - 7:36 am UTC

but the create was already run - they used indexfile to do that, so the create should just be skipped with object already exists.

I don't know what this _deleted tablespace is - it is a strange name, I'm not familiar with it.

Also, the IMP log should have the tablespace in it since it should be failing with object already exists - so this shouldn't be happening.


Unless - the object DOES NOT exist - to the original poster - are you sure THIS OBJECT exists and what is the default tablespace for this user?

Ravi's problem

June 11, 2010 - 11:54 am UTC

Reviewer: A reader

Please see Metalink note ORA-959 Tablespace '_$deleted$0$0' Does Not Exist Error Executing Some Code [ID 604648.1]

Thanks

June 11, 2010 - 1:11 pm UTC

Reviewer: Ravi B from Bay Area, CA

Thanks Patrick.

Here is what i found out yesterday.

I saw the default tablespace for the user defined as _$deleted$6$0 in DBA_USERS view. I have no clue where this came from. This is a brand new, clean schema created with valid default tablespace. Anyways, i reassinged the correct default tablespace and import worked fine without any errors.

But the question remains:

1. where _$deleted$6$0 came from?

2. Why the import log doesn't show/find the tablespace name? whereas, the same import dump is used to created the objects in the schema along with storage clauses(INDEXFILE=<filename>)

3. Even if the import utility *somehow* doesn't find the tablespace name, it shouldn't matter becuase the object already exists and it should ignore the error (ignore=y) and import the rows.

Here is what i think (may be i'm wrong):

ignore=y only works when for "object already exists" errors not any syntax/symantic errors that comes from CREATE <object> statements.

Thanks,
Ravi

To: Ravi B

June 11, 2010 - 1:24 pm UTC

Reviewer: A reader

Did you read the note ORA-959 Tablespace '_$deleted$0$0' Does Not Exist Error Executing Some Code [ID 604648.1]?

yes I did read metalink notes

June 11, 2010 - 1:37 pm UTC

Reviewer: Ravi B from Bay Area, CA

The metalink notes talks about renaming tablespace which might cause this error. In my case, it is a brand new database with new schema and new tablespace.
Tom Kyte

Followup  

June 22, 2010 - 7:45 am UTC

please utilize support then, it should not be happening.

importing table with clob datatype from one server to other server

February 24, 2012 - 12:40 am UTC

Reviewer: santosh challa from INDIA

Hi Tom,

Firstly thanks for helping us when we fall in trouble.

I am importing a dump file which has a table with clob datatype from oralce 10g server to oracle 11g server I get this following error. Is there a working around for this?

Note: If there is no clob table the import goes fine.

IMP-00017: following statement failed with ORACLE error 959:
"CREATE TABLE "TST_CLOB" ("SYSCODE" NUMBER, "TST_CLOB" NCLOB) PCTFREE 10 PC"
"TUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST"
" GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PDM_SANO_HEAD" LOGGING NOCOMPRES"
"S LOB ("TST_CLOB") STORE AS (TABLESPACE "PDM_SANO_HEAD" ENABLE STORAGE IN "
"ROW CHUNK 8192 RETENTION NOCACHE LOGGING STORAGE(INITIAL 65536 FREELISTS 1"
" FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'PDM_SANO_HEAD' does not exist


Thanks in advance.
Santosh Challa.
Tom Kyte

Followup  

February 25, 2012 - 5:04 am UTC

why are you using the slowest possible tools in the world when you have datapump you could be using???


with the old, obsolete exp/imp tools - you will have to precreate this table. IMP did not handle tables with multiple tablespace clauses nicely (partitioned table, tables with lobs, tables with overflow segements - etc). It would only rewrite the FIRST tablespace clause out of the create statement.

So, either

a) use a much faster set of tools - datapump expdp and impdp, they can remap tablespaces *easily*

b) precreate these tables and use IGNORE=Y and wait a really long time for imp to run.

importing table with clob datatype from one server to other server

February 26, 2012 - 6:43 am UTC

Reviewer: Shoupeng yan from China

To santosh challa:


You should be carefully read the above that Tom reply.

Tom has said very clearly.

Becase the table TST_CLOB have "multi-segment",the exp tools can't rewrite the create statement.

you should be create the table TST_CLOB with new tablespace in target database server(your oracle 11g server),
and then import with the option ,igonre=y.





for example:


assume the table TST_CLOB should be place in tablespace DATATBS in your oracle 11g server.

Before import,you must be create the table TST_CLOB as follows:

CREATE TABLE "TST_CLOB" ("SYSCODE" NUMBER, "TST_CLOB" NCLOB)  
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) 
TABLESPACE "DATATBS" 
LOGGING NOCOMPRESS LOB ("TST_CLOB") STORE AS  
(TABLESPACE "DATATBS" ENABLE STORAGE IN ROW CHUNK 8192 
RETENTION NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 
FREELIST GROUPS 1 BUFFER_POOL DEFAULT))



and then ,

imp userid=xxxx/xxxx file=xxxx igonre=y  full=y



with datapump tools,you easily to achieve goal with the following command:

impdp xxxx/xxxx directory=xxxx dumpfile=xxxx full=y remap_tablespace=PDM_SANO_HEAD:DATATBS

you  need not create table TST_CLOB beforehand.

wish you good luck.

   Shoupeng yan

June 15, 2012 - 7:01 am UTC

Reviewer: A reader

Followup February 25, 2012 - 5am Central time zone:

why are you using the slowest possible tools in the world when you have datapump you could be using???


with the old, obsolete exp/imp tools - you will have to precreate this table. IMP did not handle tables with multiple tablespace clauses nicely (partitioned table, tables with lobs, tables with overflow segements - etc). It would only rewrite the FIRST tablespace clause out of the create statement.

So, either

a) use a much faster set of tools - datapump expdp and impdp, they can remap tablespaces *easily*

b) precreate these tables and use IGNORE=Y and wait a really long time for imp to run

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.