DBA Sensation

November 6, 2008

how to export/import data with LOB type from one user/schema to another user?

Filed under: [backup and recovery] — zhefeng @ 2:05 pm

When you are using exp/imp moving data from one user/schema to another, if the schema contains some table with lob data type, then you will get error like this:

IMP-00017: following statement failed with ORACLE error 1950:
“CREATE TABLE “TEST” (“ID” NUMBER, “NAME” CLOB) ……..
IMP-00003: ORACLE error 1950 encountered
ORA-01950: no privileges on tablespace ‘USERS’

So how to export/import data with LOB type from one user/schema to another user?

Here are the solutions:

1. for oracle 10g and 11g, directly using datapump can avoid this issue. Datapump support lob pretty well:

export:

expdp jehan directory=DATA_PUMP_DIR schemas=(LOUVRE_121307,DENALI10G) dumpfile=cart_dp.dmp logfile=cart_expdp.log

import:

impdp system DIRECTORY=DATA_PUMP_DIR DUMPFILE=CART_DP.DMP LOGFILE=cart_impdp.log REMAP_SCHEMA=LOUVRE_121307:cartesis_LOUVRE_121307 REMAP_SCHEMA=DENALI10G:cartesis_DENALI10G REMAP_TABLESPACE=users:cartesis REMAP_TABLESPACE=TBS_DATA_DENALI10G:cartesis REMAP_TABLESPACE=CARTESIS_DATA:cartesis REMAP_TABLESPACE=TBS_DATA_LOUVRE_121307:cartesis

2. for 9i, you have to extract the lob table DDL and create it manually, then do the importing

step1: Import with INDEXFILE parameter to generate the script that can be used to modify the LOB’s tablespace clause.
$imp system/manager fromuser=user1 tables=\(test\) indexfile=create_lob_table

step2: Edit the generated script file create_lob_table.sql:

REM CREATE TABLE “USER1″.”TEST” (“ID” VARCHAR2(8) NOT NULL
REM ENABLE, “FIC_CIRCUL” BLOB) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
REM 255 LOGGING STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS
REM 121 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
REM TABLESPACE “USERS” LOB (“FIC_CIRCUL”) STORE AS (TABLESPACE “USERS”
REM ENABLE STORAGE IN ROW CHUNK 2048 PCTVERSION 10 NOCACHE LOGGING
REM STORAGE(INITIAL 10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS 121
REM PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
REM … 0 rows

> Remove the REM comments
> Change the owner USER1 by the new owner USER2
> Change the tablespace USERS by the TOUSER’s tablespace USER2 in the whole statement.

step3. Run the script create_lob_table.sql to create the USER2.TEST table.

step4. Import the data only in the created table, ignoring the CREATE TABLE statement failure.

$imp user2/*** FILE=exp.dmp LOG=imp_tab.log FROMUSER=user1 TOUSER=user2 TABLES=test IGNORE=y

Advertisements

3 Comments »

  1. Great article for importing lob data to another schema. Changing tablespace can only thing to help. Thanks for sharing.Keep it buddy.

    Comment by Jack Nicholson — September 23, 2010 @ 11:45 am

  2. Wonderful, what a weblog it is! This webpage presents
    useful information to us, keep it up.

    Comment by ontario pay day lottery canada — September 8, 2012 @ 8:59 am

  3. exactly what i wanted !!

    Comment by Vish — September 8, 2014 @ 2:39 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: