Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Finding similar statements in database for ORA-4031
Ajay
Three thoughts
select REGEXP_REPLACE(SQL_TEXT, '[Tt][0-9]*\.', 't@.') transformed_sql) FROM ...
eg:
C:\>sqlplus test/test
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Sep 20 20:27:56 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
SQL> select regexp_replace('t1.a t2.b t1.c t123.d','t[0-9]*\.', 't@.') from dual
2 /
REGEXP_REPLACE('T1.
HTH Regards Nigel
In my database there is a big problem of shared pool getting fragmented(ORA-4031). I know its due to some sql statements not getting shared. I found some statements which are potential candidate causing fragmentation.
There are about 6000 versions of same sql not shared due to use of table aliases which makes statement different. Below is one of the example. 00000005ECD192B0 SELECT t3608.CURRENT_BUSDATE, t3608.BOM_DATE FROM deldate0 t360800000005ECD192B0 WHERE t3608.NDAY_REGION = :"SYS_B_0" 00000005ED1E0C90 SELECT t66732.CURRENT_BUSDATE, t66732.BOM_DATE FROM deldate0 t6600000005ED1E0C90 732 WHERE t66732.NDAY_REGION = :"SYS_B_0" 00000005ED5B2AE0 SELECT t110049.CURRENT_BUSDATE, t110049.BOM_DATE FROM deldate0 t00000005ED5B2AE0 110049 WHERE t110049.NDAY_REGION = :"SYS_B_0" 00000005ED6C55C0 SELECT t310076.CURRENT_BUSDATE, t310076.BOM_DATE FROM deldate0 t00000005ED6C55C0 310076 WHERE t310076.NDAY_REGION = :"SYS_B_0"
<etc>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Sep 20 2007 - 14:33:01 CDT