为什么在通过 Oracle DBLINK 复制时 Char(1) 会更改为 Char(3)？
我有 2 个数据库，我想将包含 CHAR 列的现有表从数据库 A 传输到数据库 B。
数据库 A 是 Oracle 9i，编码为 WE8ISO8859P1，并包含一个带有 at 的表“foo”至少 1 列 CHAR(1 char) 类型。 我无法更改数据库 A 上的表，因为它是第三方设置的一部分。
数据库B是我自己的Oracle 10g数据库，由于各种原因使用AL32UTF8编码，我想将foo复制到这个数据库中。
我设置了从数据库 B 到数据库 A 的数据库链接。然后发出以下命令：
*create table bar as select * from #link#.foo;*
数据复制得很好，但是当我检查列的类型时，我注意到CHAR(1 char)已经被转换为CHAR(3 char)，并且在数据库B中查询数据时，全部用空格填充。
我认为在水下的某个地方，Oracle 混淆了它自己的字节和字符。 CHAR(1 byte) 与 CHAR(1 char) 等不同。我已经读过所有这些内容。
为什么数据类型更改为填充的 CHAR（3 个字符）以及如何阻止 Oracle 执行此操作？
编辑：这似乎与在 Oracle 的两个特定补丁级别之间传输 CHAR 有关9和10。看起来确实是一个bug。 一旦我发现我会发布更新。 同时：不要像我描述的那样尝试在数据库之间移动 CHAR。 VARCHAR2 工作正常（经过测试）。
编辑 2：我找到了答案并将其发布在这里： 为什么在通过 Oracle DBLINK 复制时 Char(1) 更改为 Char(3)？ 可惜我不能接受自己的答案，因为我的问题已经解决了。
I have 2 databases, and I want to transport an existing table containing a CHAR column from database A to database B.
Database A is Oracle 9i, has encoding WE8ISO8859P1, and contains a table "foo" with at least 1 column of type CHAR(1 char). I can not change the table on database A because it is part of a third party setup.
Database B is my own Oracle 10g database, using encoding AL32UTF8 for all kinds of reasons, and I want to copy foo into this database.
I setup a database link from database B to database A. Then I issue the following command:
*create table bar as select * from #link#.foo;*
The data gets copied over nicely, but when I check the types of the columns, I notice that CHAR(1 char) has been converted into CHAR(3 char), and when querying the data in database B, it is all padded with spaces.
I think somewhere underwater, Oracle confuses it's own bytes and chars. CHAR(1 byte) is different from CHAR(1 char) etc. I've read about all that.
Why does the datatype change into a padded CHAR(3 char) and how do I stop Oracle from doing this?
Edit: It seems to have to do with transfering CHAR's between two specific patchlevels of Oracle 9 and 10. It looks like it is really a bug. as soon as I find out I'll post an update. Meanwhile: don't try to move CHAR's between databases like I described. VARCHAR2 works fine (tested).
Edit 2: I found the answer and posted it here: Why does Char(1) change to Char(3) when copying over an Oracle DBLINK?
Too bad I can not accept my own answer, because my problem is solved.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
此问题是由于 Oracle 根据原始列长度定义（错误）处理不同字符集之间的字符转换的方式造成的。 当您以字节为单位定义字符类型列的大小时，Oracle 不知道如何进行转换并回避它。 解决方案是始终以字符为单位定义字符类型的长度。
要更深入地解释这个问题以及我是如何解决这个问题的，请查看
http://www.rolfje.com /2008/11/04/transporting-oracle-chars-over-a-dblink/
This problem is caused by the way Oracle (mis)handles character conversions between different character sets based on the original column length definition. When you define the size of a character type column in bytes, Oracle does not know how to do a conversion and bodges it. The solution is to always define the length of a character type in characters.
For a more in-depth explanation of the problem and how I figured this out have a look at
http://www.rolfje.com/2008/11/04/transporting-oracle-chars-over-a-dblink/
您需要了解 WE8ISO8859P1 NLS（以一个字节存储字符）和 AL32UTF8（最多以四个字节存储字符）之间的区别。 您需要花一些时间与 Oracle 国家语言支持 (NLS) 文档。 Oracle 通过数据库链接自动进行转换，试图提供帮助。
在 SQL 提示符下尝试以下操作：
YOu need to learn the difference between the WE8ISO8859P1 NLS (which stores characters in one byte) and the AL32UTF8 which stores characters in up to four bytes. You will need to spend some quality time with the Oracle National Language Support (NLS) Documentation. Oracle automatically does the conversion through the database link, in an attempt to be helpful.
Try the following from your SQL prompt:
我要尝试的第一件事是创建表，而不是作为 CTAS，而是使用列定义列表，并尝试执行前几千行的插入。 如果这没有成功，那么原因就很清楚了……而且你会很快确认托马斯·洛是准确的。
The first thing I would try is Creating the table NOT as a CTAS but with a list of column definitions and try to perform an insert of the first few thousand rows. If that didn't succeed then it would be very clear why... and you'd have quick confirmation that Thomas Low is dead on accurate.