| Export Clob Column [message #167550] |
Thu, 13 April 2006 15:53  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi
I want to do export and import of a table from one schema to another, this table has CLOB column, my colleague said it is failing , i also tried it gave this message
EXP - 00003 NO STORAGE DEFINATION FOUND
I tried from command prompt as well as from Toad,
How can i do any way either by COPY or export/Import., please let me know.
Thanks
|
|
|
|
|
|
| Re: Export Clob Column [message #167859 is a reply to message #167858] |
Mon, 17 April 2006 09:37  |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Thanks for replying
Versions are same, Oracle 9i Rel2 with in a same database from one schema to another exp import is failing when trying to do for 1 table with CLOB column then i used TOAD to export the data in .xls file and them import into that table,
but still if you any solution why Export import fail with CLOB can we do or not with CLOB column,
Thanks
|
|
|
|
|
|
|
|
| Re: Export Clob Column [message #167869 is a reply to message #167867] |
Mon, 17 April 2006 10:14 |
 |
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
copy your $ORACLE_HOME/rdbms/admin/catexp.sql to something.sql. secure the file.
edit catexp.sql
Look for these lines.
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
change it to
CREATE OR REPLACE VIEW exu9tne (
tsno, fileno, blockno, length) AS
SELECT ts#, segfile#, segblock#, length
FROM sys.uet$
WHERE ext# = 1
UNION ALL
SELECT * from sys.exu9tneb
now run catexp.sql logged in as sys.
Try your exp session again.
If it works, rename the something.sql to catexp.sql
or
Ofcourse, apply the highest patchset for this release.
[Updated on: Mon, 17 April 2006 10:18] Report message to a moderator |
|
|
|
| Re: Export Clob Column [message #167876 is a reply to message #167869] |
Mon, 17 April 2006 11:53 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Hi
Thanks Mahesh for replying,
The message i get is while Export. this is while i use from command prompt.
Microsoft(R) Windows DOS
(C)Copyright Microsoft Corp 1990-2001.
C:\>exp
Export: Release 9.2.0.1.0 - Production on Mon Apr 17 11:46:25 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: xxxx/xxx@db1
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > c:\tab_xmit.dmp
(2)U(sers), or (3)T(ables): (2)U > 3
Export table data (yes/no): yes >
Compress extents (yes/no): yes > y
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > xmit_lyout_ver
. . exporting table XMIT_LYOUT_VER
EXP-00003: no storage definition found for segment(13, 12011)
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
version is Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.5.0 - Production
I couldn't find VIEW exu9tne in my catexp.sql file
other views like exu9tab, exu9tabsu .... others are there.
I will try to find that view in other Boxes like TEST and PROD and let you know what is there.
Thanks a lot
|
|
|
|
|
|
| Re: Export Clob Column [message #167881 is a reply to message #167879] |
Mon, 17 April 2006 12:41 |
faiz_hyd
Messages: 294
Registered: February 2006
|
Senior Member |
|
|
Sorry for misleading
Yes versions is same ,
when connected to SQL >
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 - Production
When i type EXP at command prompt for taking export
Export: Release 9.2.0.1.0 - Production on Mon Apr 17 11:46:25 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: xxxx/xxx@db1
Connected to: Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Here is shows Release 9.2.0.1.0 but it says connected to 9.2.0.5.0.
Sorry for confusion.
Regards
|
|
|
|
| Re: Export Clob Column [message #167883 is a reply to message #167881] |
Mon, 17 April 2006 12:52 |
|
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
No. VERSIONS are NOT same ( Anyhow it is not relevant anymore for this problem you have).
Your own posting shows that,
Your Client intallation is 9.2.0.1.0 and your EXP version is the same.
Your Database server ( to which you are connecting ) is of version 9.2.0.5.0 (actually core patched to 9.2.0.6.0).
Here in this session, i am connect from a client 9.2.0.4.0 to a database version 9.2.0.1.0.
oracle@mutation#sqlplus dbadmin@lawd
SQL*Plus: Release 9.2.0.4.0 - Production on Mon Apr 17 13:48:03 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Enter password:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
dbadmin@republic_lawd1 > select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for Solaris: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
