April 9, 2010 at 2:53 am
Hello,
I am using SQL Server 2008 on XP machine and want to Replicate a database to an Oracle Database on a Unix machine. The two machines are connected to each other on network. I am successfully able to perform snapshot replication for small tables. But there is one table with a 2 columns of datatype VARCHAR(5000) and few other columns of datatype of VARCHAR(smaller than 4000). This table has over 1 million recrods. The distribution agent fails to apply the snapshot on the Oracle database for this table.
It says:
" Error 20203: There is not Enough Storage to Complete this Operation". I tried to repair my MDAC as well and update it, that too didn't worked.
Can some please help me.
Thanks and Regards
Sameer Kumar
Associate DBA
April 9, 2010 at 4:50 am
you need to speak to an oracle dba on this
i'm not 100% sure on this but i beleive you need to change the target column in the oracle database to VARCHAR2
MVDBA
April 9, 2010 at 4:52 am
actually - i will just correct myself
you need to store the target column as text or LOB
varchar2 has a limit of 4000 byes
varchar has a length of 2000 bytes in oracle
MVDBA
April 10, 2010 at 12:55 pm
For SQL Server varchar() not larger than 4000 bytes Oracle's varchar2() would do the trick.
For SQL Server varchar() larger than 4000 bytes receiving Oracle column should be a CLOB.
Since CLOB - LOBs in general - large than 4000 have to be manipulated via dbms_lob system package I'm not sure how SQL Server would manage it.
Would you consider telling us the outcome?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 11, 2010 at 10:03 pm
Thanks to all for a prompt reply.
For SQL Server varchar() larger than 4000 bytes receiving Oracle column should be a CLOB.
SQL Server Replication to Oracle Subscriber converts VARCHAR2(greater than 4000) to CLOB. I need not do it explicitly (in fact there is no choice for VHARCHAR datatype, only timestamp type can be mapped to binary).
Would you consider telling us the outcome?
Still, I tried to create the table (with LOB) in Oracle and set the if EXISTS to "truncate the tables" (That is if the tables already exists in target Oracle DB they will be truncated). This too did not worked.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply