|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: export LOB and CLOB from one to another database [message #254974 is a reply to message #254968] |
Mon, 30 July 2007 04:12   |
 |
Michel Cadot
Messages: 68664
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 #255011 is a reply to message #254991] |
Mon, 30 July 2007 07:17 |
 |
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
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
