| How to export/import data with LOB type from one user/schema to another? [ID 464558.1] | |||||
|
| |||||
| Modified 12-NOV-2010 Type HOWTO Status PUBLISHED | |||||
In this Document
Goal
Solution
References
Applies to:
Oracle Server - Standard Edition - Version: 9.2.0.1 to 11.1.0.8 - Release: 9.2 to 11.1Oracle Server - Enterprise Edition - Version: 9.0.1.0 to 11.1.0.8 [Release: 9.0.1 to 11.1]
Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.1.0.8 [Release: 9.2 to 11.1]
Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.2 [Release: 10.2 to 11.2]
Information in this document applies to any platform.
Oracle Server Enterprise Edition - Version: 9.0.1.0 to 11.2.x
Oracle Server Enterprise Edition - Version: 9.2.0.1 to 11.2.x
Oracle Server Enterprise Edition - Version: 10.2.0.1 to 11.2.x
This note applies to Database versions 9.X and 10.X for standard export and import usage and 10.1.0.X to 11.2.X for datapump export and import usage.
Goal
Checked for relevance 11-12-2010How to export/import data with LOB type from one user/schema to another user
Solution
1. Use Data Pump to Export and Import in 10g and newer versions:
---------------------------------------------------
It is very easy to use Data Pump to export and import tables with or without the LOB clause. The following applies for all kinds of tables (with or without the LOB clause):
-- export schema user1 with Export Data Pump:
$ expdp user1/**** DIRECTORY=my_dir DUMPFILE=expdp.dmp \
LOGFILE=expdp.log SCHEMAS=user1
-- import objects in schema user2 and remap the tablespaces to different ones:
$ impdp user2/**** DIRECTORY=my_dir DUMPFILE=expdp.dmp \
LOGFILE=impdp.log REMAP_SCHEMA=user1:user2 REMAP_TABLESPACE=users1_t:users2_t \
REMAP_TABLESPACE=lobs1_t:lobs2_t REMAP_TABLESPACE=x:y
you can review the following note for additional details:
Note 260225.1 "Import DataPump Parameter REMAP_SCHEMA - How to Move Database Objects from one Schema to Another"
2. Use Original Export and Import in 10g and 9i:
---------------------------------------------------
In Original Export and Import, you can use the FROMUSER and TOUSER parameters of the classic export and import clients.
1) If table with LOB exported and imported points to the same tablespace:
$ exp user1/*** FILE=exp.dmp LOG=exp.log OWNER=user1
$ imp user2/*** FILE=exp.dmp LOG=imp.log FROMUSER=user1 TOUSER=user2
The procedure is the same for tables with a LOB column and without a LOB column.
Note: This import will attempt to create the objects in the user2 schema in the same tablespace as they were stored in the user1 schema. If the tablespace needs to be changed, then you can use the method that is described in:
Note 1012307.6 "Moving Tables Between Tablespaces Using EXPORT/IMPORT"
2) If a table with a LOB is exported and import points to a different tablespace:
If the table has a LOB column which has a storage clause that points to tablespace A and needs to be imported into a tablespace B which is not the default tablespace of the TOUSER, then you have to pre-create the table in the target database with the correct storage clause, and import with IGNORE=Y. or you will receive the following error:
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'
There are 2 ways to solve the IMP-00017, IMP-00003, ORA-01950
a.
step1: In order to obtain the original DDL of the table in the source database you can use dbms_metadata.get_ddl in the source database:
SQL>set long 200000000
SQL>select dbms_metadata.get_ddl('TABLE','LOBTAB','USER1') from dual;
step2: You can modify the DDL and use it to pre-create the table in the target database with the correct tablespaces storage clauses.
SQL> create table ...
step3:
$ imp user2/*** FILE=exp.dmp LOG=imp_tab.log FROMUSER=user1 TOUSER=user2 TABLES=test IGNORE=y
OR
b.
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
Refer to Note 91969.1 - Using FROMUSER/TOUSER Fails to Generate Tables With LOBs into TOUSER Tablespace.
References
NOTE:1012307.6 - Moving Tables Between Tablespaces Using EXPORT/IMPORTNOTE:260225.1 - Import DataPump Parameter REMAP_SCHEMA - How to Move Database Objects from one Schema to Another
NOTE:91969.1 - Using FROMUSER/TOUSER Fails to Generate Tables With LOBs into TOUSER Tablespace
|
Products
| |||
Related
扫一扫
1745
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
