Oracle使用regexp_like报错ORA-12733 正则表达式太长

注：此篇内容并没有解决正则表达式太长的问题。

在命令行窗口连接数据库：

>sqlplus username/password@hostname:port/SID或service_name

其中：

• username 是你的数据库用户名。

• password 是你的数据库密码。

• hostname 是数据库服务器的主机名或IP地址。

• port 是监听端口，默认是1521。

• SID 是系统标识符，是数据库实例的唯一名称。

• service_name 是数据库的服务名，这是在Oracle Net Configuration中配置的。

ORA-12733错误通常发生在Oracle数据库中，当正则表达式的长度超过了系统允许的最大限制时。Oracle的REGEXP_LIKE函数确实有一个可配置的最大长度限制，以防止内存溢出或性能问题。当你的正则表达式超过了这个值，就会触发这个错误。

解决这个问题的方法有：

1. 减少正则表达式长度： 确保你的正则表达式尽可能简洁。如果可能，试着分解或重构你的查询，将长表达式拆分成几个较小的表达式。

2. 增加数据库配置： 你可以增加数据库的REGEXP_LIKE_LIMIT参数来扩大允许的最大长度，但这可能会影响到性能。执行如下命令（以SQL*Plus为例）：

   SQL> ALTER SESSION SET REGEXP_LIKE_LIMIT = your_new_limit;

3. 确保提供的新值是小于或等于数据库有效内存的一个合理数量。

4. 使用查询优化： 通过审核你的SQL查询，看看是否有其他方法可以更有效或间接地实现你的需求，可能不需要直接使用长正则表达式。

5. 增加硬件资源： 如果以上方法都无法解决问题，可能是数据库的硬件限制。在这种情况下，升级数据库服务器的硬件或考虑使用分区、索引等数据库优化技术。

如何查看REGEXP_LIKE_LIMIT值的大小？

在Oracle数据库中，你可以通过以下步骤查看当前的REGEXP_LIKE_LIMIT值：

1. 使用SQL Plus： 打开SQL Plus，运行以下命令：

   SQL> SELECT DBMS_METADATA.get_database_attribute(null, 'REGEXP_LIKE_LIMIT') as max_regexp_length FROM dual;

2. 这将返回当前设置的最大正则表达式长度。

3. 通过动态性能视图： 你还可以通过动态性能视图来查看：

SQL> SELECT * FROM V$SESSION WHERE SERIAL#, TYPE = 'P' 

然后，查找列REGEXP_LIMIT，它显示了每个会话的当前REGEXP_LIKE_LIMIT值。

请注意，这两个方法可能会返回应用的会话级或数据库全局的值，具体取决于你的配置和安全上下文。如果你能够修改这个限制，通常需要足够的管理员权限。

oracle11g提示DBMS_METADATA.get_database_attribute这个方法不存在

对于较早版本的Oracle数据库，如11g，你可能无法直接使用DBMS_METADATA.get_database_attribute这个特定函数来获取REGEXP_LIKE_LIMIT的值。

如果你使用的是Oracle 11g，你可以尝试查询动态性能视图或其他方式，尽管这些选项可能不如新版本直观。以下是一个不使用DBMS_METADATA的方法：

1. 查看所有会话的限制： 在SQL Plus中，运行：

   SELECT NVL(session_id, 'global') AS session_id,
          resource_name, resource_limit
   FROM dba_resources
   WHERE resource_name = 'REGEXP_LIKE_LIMIT';

   如果结果显示的是global，那么这可能是全局的最大值。

2. 查询每个会话的用户定义的限制： 如果某个用户或会话设置了个体限定，你可以查看：

   SELECT username, upper_level_resource, MAX(resource_limit) AS max_limit
   FROM dba_users_reservations
   WHERE upper_level_resource = 'REGEXP_LIKE_LIMIT'
   GROUP BY username, upper_level_resource;

   请确保你有足够的权限来执行这些查询。如果数据库集群或共享服务器模式下，你可能需要找到可以代表所有会话用户的角色来查看。

   如果你确定并没有其他的会话设置，那么你可能需要联系你的DBA或者查阅Oracle文档以获取更多关于如何查看或修改此限制的特定信息。

对于Oracle 11g，dba_resources视图可能不存在，因为它是Oracle 12c或更高版本引入的

针对11g，你可以尝试使用以下不同的方法：

1. 查看系统全局区（SGA）的初始化参数： SQL Plus 中输入：

   SELECT name, value 
   FROM v$parameter 
   WHERE name = 'REGEXP_LIKE_LIMIT';

   这将返回名为REGEXP_LIKE_LIMIT的系统参数及其当前值。

2. 查看动态性能视图： 对于会话级别的限制，使用：

   SELECT username, resource_name, resource_limit 
   FROM v$session_stats 
   WHERE upper_level_resource = 'REGEXP_LIKE_LIMIT';

   如果这个视图没有明确显示你期望的限制，它可能表示全局配置。

3. 如果您是SYS或者具有足够权限的DBA，请尝试：

   SELECT upper_level_resource, max(resource_limit) 
   FROM dba_reservations
   WHERE upper_level_resource = 'REGEXP_LIKE_LIMIT'
   GROUP BY upper_level_resource;

   请注意，由于Oracle版本的限制，一些视图和函数可能在不同的版本之间不兼容，或者不存在。如果以上方法仍然不能提供你需要的信息，建议查阅你的Oracle数据库版本对应的文档。
   对于Oracle 11g，确实没有v$session_stats视图。在这种情况下，你可以通过查询其他视图和系统表来找到REGEXP_LIKE_LIMIT相关的信息。然而，限制信息可能不会直接以这样的方式存储，尤其是在无监控的情况下。

   如果你确定REGEXP_LIKE_LIMIT这一限制与限制查询的数量有关，并且你想要查看全局配置，可能需要查看以下几个潜在的位置：
   1.初始化参数： 可以查询v\$parameter视图来查找REGEXP_LIKE_LIMIT参数，如下：

   SELECT name, value 
   FROM v$parameter 
   WHERE name = 'REGEXP_LIKE_LIMIT';

   2.动态性能视图： 对于较老版本的Oracle，你可能需要通过包装查询来获取这个信息。例如，检查当前会话的动态SQL统计：

   SELECT * 
   FROM v$sql 
   WHERE parameter = 'SQL_SQLLOBSIZE' OR 
         parameter = 'SQL_NEW_SQLTHRU觐arsql';

   这些参数可能与限制查询数量有关，虽然不一定直接等同于REGEXP_LIKE_LIMIT。
   3.视图v$sql_plan注释： 看看SQL执行计划可能包含的资源限制（如果你有查询计划缓存的话）：

   SELECT SQL_ID, accumulated_cost, resource_name 
   FROM v$sql_plan 
   WHERE resource_name LIKE '%LIKE_LIMIT%'

   如果找到相关资源名称，你可以联系你的DBA来确认那个确切的限制。如果上述查询都无法确定确切的限制，可能需要查询数据库的日志文件或者做更深入的系统检查，这通常需要数据库管理员的帮助。

   如果查询v$parameter时提示"未选中行"，这意味着在当前会话中没有找到相应的参数。这通常发生在默认安装情况下，可能因为REGEXP_LIKE_LIMIT这个参数不是必须显式设置的，或者你的数据库配置不包括这个设置。

   为了确定这个限制是否存在，你可以：
   1.检查系统全局区（SGA）的初始化参数是否包含：

   SELECT name, value 
   FROM v$parameter 
   WHERE name LIKE 'DB_NAME';

   2.如果有一个系统参数叫REGEXP_LIKE_LIMIT，它通常会被纳入这个查询结果。
   3.如果上面的查询结果没有直接返回REGEXP_LIKE_LIMIT，你可以分析数据库的启动参数文件（init.ora或spfile），它通常包含了所有默认和你手动设置的参数。如果是在生产环境中，可能需要遵循最佳实践来保证性能，而不仅仅是寻找一个潜在的限制参数。
   4.如果还是没有找到，可能需要联系系统管理员或者DBA查询数据库的配置，并查看配置文件来确定是否有这样的限制。
   如果默认配置文件中没有设置，那么REGEXP_LIKE_LIMIT可能是被禁止或置为了Oracle的一个默认值，这可能意味着没有设置明确的限制。