Home » RDBMS Server » Server Utilities » export LOB and CLOB from one to another database
export LOB and CLOB from one to another database [message #254901] Mon, 30 July 2007 00:59 Go to next message
somavamshi
Messages: 17
Registered: July 2007
Location: bangalore
Junior Member
Hi ...

Let me know how to export and import LOB and clob columns from database to another database..


Re: export LOB and CLOB from one to another database [message #254916 is a reply to message #254901] Mon, 30 July 2007 01:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use export/import or datapump.

Regards
Michel
Re: export LOB and CLOB from one to another database [message #254949 is a reply to message #254916] Mon, 30 July 2007 02:18 Go to previous messageGo to next message
somavamshi
Messages: 17
Registered: July 2007
Location: bangalore
Junior Member
Sorry, My question is that how to export and import using export and import commands from one database to another database having different tablespaces in oracle 9i 9.2.0.6.0
Re: export LOB and CLOB from one to another database [message #254952 is a reply to message #254949] Mon, 30 July 2007 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't. It's a bug, never fixed.

Regards
Michel
Re: export LOB and CLOB from one to another database [message #254954 is a reply to message #254952] Mon, 30 July 2007 02:27 Go to previous messageGo to next message
somavamshi
Messages: 17
Registered: July 2007
Location: bangalore
Junior Member
Then what's the way to export and import
Re: export LOB and CLOB from one to another database [message #254956 is a reply to message #254901] Mon, 30 July 2007 02:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

No way.U have to use higher version.
By Data pump using REMAP_TABLESPACE

[Updated on: Mon, 30 July 2007 02:34]

Report message to a moderator

Re: export LOB and CLOB from one to another database [message #254968 is a reply to message #254901] Mon, 30 July 2007 03:48 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
As a workaround, you should be able to create the tablespace and table containing the CLOB prior to doing the import. Ensure that the new tablespace is the user's default tablespace and use IGNORE=Y.
Re: export LOB and CLOB from one to another database [message #254974 is a reply to message #254968] Mon, 30 July 2007 04:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Unfortunalty this does not work with LOB.
SQL> select default_tablespace from user_users;
DEFAULT_TABLESPACE
------------------------------
TS_D01

1 row selected.

SQL> create table t (id integer, l clob) lob(l) store as (tablespace lob);

Table created.

SQL> host exp michel/michel file=t.dmp tables=t

Export: Release 10.2.0.3.0 - Production on Lun. Juil. 30 11:08:58 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                              T          0 rows exported
Export terminated successfully without warnings.

SQL> drop table t purge;

Table dropped.

SQL> drop tablespace lob including contents and datafiles;

Tablespace dropped.

SQL> host imp file=t.dmp full=y

Import: Release 10.2.0.3.0 - Production on Lun. Juil. 30 11:09:42 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: michel/michel

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing MICHEL's objects into MICHEL
. importing MICHEL's objects into MICHEL
IMP-00017: following statement failed with ORACLE error 959:
 "CREATE TABLE "T" ("ID" NUMBER(*,0), "L" CLOB)  PCTFREE 10 PCTUSED 40 INITRA"
 "NS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFE"
 "R_POOL DEFAULT) TABLESPACE "TS_D01" LOGGING NOCOMPRESS LOB ("L") STORE AS  "
 "(TABLESPACE "LOB" ENABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LO"
 "GGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFA"
 "ULT))"
IMP-00003: ORACLE error 959 encountered
ORA-00959: tablespace 'LOB' does not exist
Import terminated successfully with warnings.

Regards
Michel
Re: export LOB and CLOB from one to another database [message #254982 is a reply to message #254974] Mon, 30 July 2007 05:40 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Sure it does. As I indicted, you have to create the NEW tablespace prior to the import and create the table specifying the NEW tablespace name. The import is then done with IGNORE=Y.

Your example doesn't show that.
Re: export LOB and CLOB from one to another database [message #254991 is a reply to message #254982] Mon, 30 July 2007 06:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My default tablespace (TS_D01) exists, the table was created in it.
Only LOB tablespace which contained my LOB column no more exists.

"ignore=y" is irrelevant here, the table is not pre-created with or without "ignore=y" it will not be created by import.

Regards
Michel
Re: export LOB and CLOB from one to another database [message #255011 is a reply to message #254991] Mon, 30 July 2007 07:17 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
An example is worth a thousand words.

Michel Cadot wrote on Mon, 30 July 2007 07:22
"ignore=y" is irrelevant here, the table is not pre-created with or without "ignore=y" it will not be created by import.


The IGNORE=Y is totally relevant. As I explained, your example did NOT demonstrate what my initial comment iterated "create the tablespace and table containing the LOB prior to doing the import". I mentioned setting the user's default tablespace simply to ensure that the table got created in the appropriate tablespace. However, setting the default tablespace isn't mandatory in accomplishing the goal of importing a LOB into a new tablespace.

SQL> create tablespace test_lob datafile '/oracle/oradata/ora10g/testlob.dbf' size 10m;

Tablespace created.

SQL> create table testlob (a1 number, a2 clob) lob(a2) store as (tablespace test_lob);

Table created.

SQL> host exp test/test file=/tmp/tlob.dmp tables=testlob

Export: Release 10.2.0.1.0 - Production on Mon Jul 30 07:45:44 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                              TESTLOB          0 rows exported
Export terminated successfully without warnings.

SQL> drop table testlob purge;

Table dropped.

SQL> drop tablespace test_lob including contents and datafiles;

Tablespace dropped.

SQL> create tablespace new_test_lob datafile '/oracle/oradata/ora10g/newtestlob.dbf' size 10m;

Tablespace created.

SQL> create table testlob (a1 number, a2 clob) lob(a2) store as (tablespace new_test_lob);

Table created.

SQL> host imp test/test file=/tmp/tlob.dmp full=y ignore=y

Import: Release 10.2.0.1.0 - Production on Mon Jul 30 07:46:27 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username: test/test

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing TEST's objects into TEST
. importing TEST's objects into TEST
. . importing table			"TESTLOB"		0 rows imported
Import terminated successfully without warnings.

SQL> select table_name, column_name, tablespace_name from user_lobs
  2  where table_name = 'TESTLOB';

TABLE_NAME		COLUMN_NAME		TABLESPACE_NAME
----------------------- ----------------------- ---------------------
TESTLOB			A2			NEW_TEST_LOB
Re: export LOB and CLOB from one to another database [message #255033 is a reply to message #255011] Mon, 30 July 2007 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oh! there is a total mismatch between our points of view.
You pre-created the table, I thought about the whole process during import.

The answer is thus:
- you can't do it unless you precreate your table.

Regards
Michel

Re: export LOB and CLOB from one to another database [message #255050 is a reply to message #255033] Mon, 30 July 2007 09:03 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Michel Cadot wrote on Mon, 30 July 2007 09:10

The answer is thus:
- you can't do it unless you precreate your table.


Correct...and that's what I've iterate the whole time.
Re: export LOB and CLOB from one to another database [message #255090 is a reply to message #255050] Mon, 30 July 2007 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm very sorry Brian, did not see your:
Quote:
you should be able to create the tablespace and table containing the CLOB prior to doing the import

./fa/1606/0/
Regards
Michel
Re: export LOB and CLOB from one to another database [message #255097 is a reply to message #255090] Mon, 30 July 2007 11:07 Go to previous message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
No worries Michel, it happens to all of us. I appreciate the feedback from one of the senior folks like yourself.
Previous Topic: 9i to 10g upgrade through import error
Next Topic: exp/imp only data
Goto Forum:
  


Current Time: Sat Dec 03 20:05:20 CST 2016

Total time taken to generate the page: 0.04952 seconds