ORACLE 11g默认不区分大小写
我在这篇文章中发现，从ORACLE 10g开始，有一种方法可以使用 ALTER SESSION 使特定的连接会话比较字符串不区分大小写，而不需要任何疯狂的 SQL 函数。
有谁知道，在 11g 中，是否有一种方法可以使数据库默认为所有新连接会话始终在此模式下运行，从而消除每次运行 ALTER SESSION 的需要你连接吗？
或者，您可以在连接字符串上指定一个附加参数来打开相同的功能？
I found in this article, that since ORACLE 10g, there is a way to make a particular connection-session compare strings case-insensitive, without needing any crazy SQL functions, using an ALTER SESSION.
Does anyone know if, in 11g, there might be a way to make the database to always operate in this mode by default for all new connection-sessions, thereby eliminating the need for running ALTER SESSIONs every time you connect?
Or perhaps, an additional parameter you could specify on your connection string that would turn the same on?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您只需使用
alter system set将文章中提到的 子句。NLS_SORT、NLS_COMP参数设置为 Oracle init 文件中的值即可。 = <值>;有关使用 alter system 命令的信息可以在 此处找到。
这是一个关于正确用法的很好的链接
NLS_*参数。请注意，NLS_SORT 参数的某些设置可能会导致性能问题，即当它未设置为 BINARY 时。 Oracle 文档指出：You could just set the
NLS_SORT,NLS_COMPparameters mentioned in the article as the values in the the Oracle init file using thealter system set <parameter> = <value>;clause.Info on using the alter system commands can be found here.
Here is a good link on the correct usage of the
NLS_*parameters. Note that some settings of of the NLS_SORT parameter can/could cause performance issues, namely when it is not set to BINARY. The Oracle docs state:当然可以！
让您友好的 DBA 设置这些参数：
这摘自我关于 如何使 Oracle 不区分大小写
Sure you can!
Get your friendly DBA to set these parameters:
This is taken from my short article on How to make Oracle Case Insensitive
我尝试使用登录触发器来发出这些命令来获取不区分大小写的查询：
虽然这确实给了我 CI，但它也给我带来了令人难以置信的糟糕性能问题。我们有一个特别的表，如果没有这些设置，插入需要 2 毫秒。完成这些设置后，插入需要 3 秒秒。我通过多次创建和删除触发器确认了这一点。
我不知道在系统级别执行此操作（与使用触发器的会话级别相反）是否会产生影响。
I tried using a
logon triggerto issue these commands to get case-insensitive queries:And while that did give me CI, it also gave me unbelievably bad performance issues. We have one table in particular that, without those settings, inserts take 2 milliseconds. With those settings in place, inserts took 3 seconds. I have confirmed this by creating and dropping the trigger multiple times.
I don't know if doing it at the system level, as opposed to the session level with a trigger, makes a difference or not.
我在 11g r2 中发现插入和 nls 也存在相同的性能问题！幸运的是，对我来说，性能影响还不够大，不需要更改应用程序。
如果您可以在插入时不使用binary_ci，那么我会在插入之前和之后进行更改会话，这样您就不必删除触发器
I found the same performance issue with inserts and nls in 11g r2! Luckily for me the performance hit was not significant enough requiring an app change.
If you can do without binary_ci for the INSERT, then I would do an alter session just before the insert and afterwards, so you don't have to drop the trigger