我在入数据之前去oracle查询了下,大数据量(大概130多万的数据)的时候,老是报ORA-01555: snapshot too old: rollback segment number 11 with name "_SYSSMU11$" too small或是couldn't perform the operation commit: You can't perform any operations on this connection. It has been automatically closed by Proxool for some reason (see logs).这一类的错误,查询中我也用到了索引,只是怎么还是老错误的。请问下大家有没有好的解决方法的。
估计里面有长查询,别的事务提交又很慢
可以把undo调大点看看
SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME like '%UNDO%';FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------
D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
2 UNDOTBS1SQL> alter database datafile 2 resize 1000M;Database altered.
当某一个事务回退数据大于回退段所容纳的数量时,oracle根据回退段的存储参数next进行区扩展,如果所有区的数量等于存储参数maxnextents仍不够用时,则产生"快照太旧"(Snapshot Too Old)错误。解决办法:
增大回退段容量参考:
http://blog.csdn.net/hdhai9451/archive/2008/11/23/3356937.aspx
2 /oracle_backup/oradata/cmccdnms/undotbs1_5.dbf 12 UNDOTBS1
3 /oracle_data01/oradata/cmccdnms/undotbs1_2.dbf 56 UNDOTBS1
4 /oracle_data01/oradata/cmccdnms/undotbs1_3.dbf 110 UNDOTBS1
5 /oracle_data02/oradata/cmccdnms/undotbs1_4.dbf 144 UNDOTBS1这是我根据你提供的sql查出来的。我看我的sql主要是查询过程吧,从130万里找几万条数据进行匹配的,估计这个过程得非常的慢。