Home » SQL & PL/SQL » SQL & PL/SQL » Regular expression to find commits in all Oracle source (Oracle 10g)
Regular expression to find commits in all Oracle source [message #598386] |
Mon, 14 October 2013 09:00 |
Buchas
Messages: 101 Registered: March 2006
|
Senior Member |
|
|
Hello,
I would like to write a select that would return all places in DB that are commiting transaction.
E.g. package for testing:
CREATE OR REPLACE PACKAGE test.TEST_COMMIT AS
PROCEDURE THE_ONLY_COMMIT_IN_DB ;
END TEST_COMMIT;--not a match
CREATE OR REPLACE PACKAGE BODY test.TEST_COMMIT AS
PROCEDURE THE_ONLY_COMMIT_IN_DB IS
BEGIN
COMMIT; --ok
COMMIT; -- ok
commit ; --ok spaces between t and ;
commit
; -- ok comma on a different line
END;
END TEST_COMMIT; --not a match
The select should return 4 rows with --ok.
My best try so far (I am new in regexp):
SELECT *
FROM ALL_SOURCE ASO
WHERE
REGEXP_LIKE(ASO.TEXT,'commit(\s*);','i' )
AND name = 'TEST_COMMIT'
Help please.
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598393 is a reply to message #598386] |
Mon, 14 October 2013 09:16 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
from ALL_SOURCE you can get 3 of them but not the one with ';' on another line as it is not on the same row:
SQL> SELECT line, text
2 FROM ALL_SOURCE ASO
3 WHERE REGEXP_LIKE(ASO.TEXT,'(^|\s)commit(\s*);','i')
4 AND name = 'TEST_COMMIT';
LINE TEXT
---------- --------------------------------------------------------------------------------
5 COMMIT; --ok
6 COMMIT; -- ok
7 commit ; --ok spaces between t and ;
[Updated on: Tue, 15 October 2013 00:08] Report message to a moderator
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598440 is a reply to message #598386] |
Mon, 14 October 2013 19:26 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Looking for word commit will not find all the places where code commits, just the explicit ones. Code can have dynamic SQL that executes DDL which will commit implicitly. Or code can be wrapped. But even looking for explicit commits in not wrapped code isn't that simple. Did you open SQL Language Reference manual to check commit syntax:
COMMIT [ WORK ]
[ [ COMMENT string ]
| [ WRITE [ WAIT | NOWAIT ] [ IMMEDIATE | BATCH ]
]
| FORCE { string [, integer ]
| CORRUPT_XID string
| CORRUPT_XID_ALL
}
] ;
As you can see, there can be quite a few clauses between commit and semi-colon and this means word commit and semi-colon can be on different lines. But someone might write code where commit and semi-colon are on different lines even without using the above clauses. Like this:
SQL> create or replace
2 procedure p1
3 is
4 begin
5 commit
6 ;
7 end;
8 /
Procedure created.
SQL> SELECT line,
2 text
3 FROM USER_SOURCE
4 WHERE REGEXP_LIKE(text,'(^|\s)commit(\s*);','i')
5 AND name = 'P1'
6 /
no rows selected
SQL>
You might want to use DBMS_METADATA to account on word commit and semi-colon being on different lines:
SQL> with t as (
2 SELECT object_name,
3 dbms_metadata.get_ddl('PROCEDURE',object_name) text
4 FROM USER_OBJECTS
5 WHERE object_name = 'P1'
6 )
7 SELECT object_name
8 FROM t
9 WHERE REGEXP_LIKE(text,'(^|\s)commit(\s*);','i')
10 /
OBJECT_NAME
----------------------------------------------------------------------------
P1
SQL>
You will also need to adjust REGEXP pattern to account for all additional clauses that can be used in commit.
SY.
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598464 is a reply to message #598440] |
Tue, 15 October 2013 03:19 |
Buchas
Messages: 101 Registered: March 2006
|
Senior Member |
|
|
Thank you for the valuable insights. However I have run into strange problem - REGEXP_LIKE throws ora-31603 exception.
I have modified the Solomon Yakobson's suggestion into the following:
with t as (
SELECT OWNER,object_name,OBJECT_TYPE
FROM ALL_OBJECTS
WHERE OBJECT_TYPE IN ( 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'JOB','TRIGGER','TYPE BODY')
AND OWNER IN ('DWPIPE' )
)
SELECT OWNER,object_name,OBJECT_TYPE, dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER) as source
FROM t
WHERE REGEXP_LIKE(dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER),'(^|\s)commit(\s*);','i') --commenting this line gives no error
It gives an error: ora-31603: object "I_SNAP$ADJUST_FACT_MV" of type INDEX not found in schema "DWPIPE".
The strange thing is that if I comment the condition "WHERE REGEXP_LIKE(dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER),'(^|\s)commit(\s*);','i')", no error is given, one row is retrieved and I can see the source code of a package body in a column "source".
I tried googling and granted SELECT_CATALOG_ROLE to the user I connect with as was suggested in
http://sshailesh.wordpress.com/2010/05/08/dbms_metadata-get_ddl-avoid-ora-31603-error-by-using-select_catalog_role/
but it didn't help.
Another strange thing is that Oracle goes crazy about INDEX type, though those types are filtered out.
Any ideas?
|
|
|
|
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598490 is a reply to message #598479] |
Tue, 15 October 2013 05:37 |
Solomon Yakobson
Messages: 3269 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Lalit Kumar B wrote on Tue, 15 October 2013 06:13I am not sure how did the MATERIALIZE hint worked for you. It seems to be an error when dbms_metadata.get_ddl could not get details about object "I_SNAP$ADJUST_FACT_MV" of type INDEX in schema "DWPIPE".
Check the status of the index I_SNAP$ADJUST_FACT_MV. Is it really being used by the MV?
And how safe is it to use undocumented hint?
I agree on undocumented part, but the rest of it is quite obvious. Look at my code. You will notice DBMS_METADATA in with clause and in any case not in WHERE clause. WHERE clause is a culprit. Optimizer decides to merge with clause as a result we have:
WHERE OBJECT_TYPE IN ( 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'JOB','TRIGGER','TYPE BODY')
AND OWNER IN ('DWPIPE' )
AND REGEXP_LIKE(dbms_metadata.get_ddl(REPLACE(OBJECT_TYPE,' ','_'),object_name,OWNER),'(^|\s)commit(\s*);','i')
So now we are at the mercy of in what order optimizer will decide to apply predicates. If it applies DBMS_METADATA before OBJECT_TYPE IN ( 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE', 'JOB','TRIGGER','TYPE BODY') we will end up with very bad performance at best waisting all that time or running not so fast DBMS_METADATA for objects we don't need. But more likely (and that's what OP experienced) we will end up running dbms_metadata against some dbms_metadata in some other schema like SYS and will obviously get:
SQL> select dbms_metadata.get_ddl('INDEX','I_SNAP$ADJUST_FACT_MV','SYS') from dual
2 /
ERROR:
ORA-31603: object "I_SNAP$ADJUST_FACT_MV" of type INDEX not found in schema
"SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1
no rows selected
SQL>
SY.
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598494 is a reply to message #598487] |
Tue, 15 October 2013 05:49 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 15 October 2013 15:57
With "materialize" hint you force Oracle to FIRST select the objects from the wanted types to build a memory table
Yes, I am aware of how it works. The memory table you mentioned is a GTT where the subquery result is resolved. What I am wondering is, why to hint the optimizer, it may decide to whether or not to materialize WITH tables regardless of the hint. We can overcome OP's error by handling the objects that throws the error.
I am absolutely open for correction on this.
[update : Saw SY's post above after I posted my comments]
[Updated on: Tue, 15 October 2013 05:53] Report message to a moderator
|
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598507 is a reply to message #598504] |
Tue, 15 October 2013 07:30 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 15 October 2013 17:45
In addition, the intermediate table is not a GTT but a memory table.
Since this is undocumented, I had to experiment it on my own to understand step-by-step some time ago. I followed Tim Hall's demo. In this link Tim says, If the contents of the WITH clause is sufficiently complex, Oracle may decide to resolve the result of the subquery into a global temporary table. I have followed him a lot, hope I understood his explanation properly.
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598592 is a reply to message #598507] |
Tue, 15 October 2013 17:00 |
Lalit Kumar B
Messages: 3174 Registered: May 2013 Location: World Wide on the Web
|
Senior Member |
|
|
Hi Michel,
Since I quoted Dr. Tim Hall's link about the materialize optimizer hint. And that we differ about the temporary table being a GTT or a memory table, I posted a question to Tim, and here is what he explained.
I had a conversation with him, and this is what he finally quoted " This is an undocumented feature. I've given you an example that uses a global temporary table, but perhaps there are other circumstances that don't."
As I stated that, "I believe that GTT are not supposed to be created and dropped on the fly, rather once created, it resides in the DB. A memory table, however, is used to hold the sub-result set while the query is under execution." based on his test case he quoted, "Yes. Like any DDL operation, you should not create and drop tables (including GTTs) on the fly, but this is an internal action of the optimizer, so however they do it, I can only assume it is efficient."
Here is the forum thread from Oracle-Base which I posted, MATERIALIZE optimizer hint in WITH Clause:Subquery Factoring
And, Dr. Hall took out from his precious time to update the same in his blog too, MATERIALIZE Hint
Since it is an undocumented feature, it's a privilege to have an explanation along with a test case about the subject. Many thanks to Dr. Tim Hall.
Regards,
Lalit
[Updated on: Tue, 15 October 2013 17:25] Report message to a moderator
|
|
|
Re: Regular expression to find commits in all Oracle source [message #598606 is a reply to message #598592] |
Wed, 16 October 2013 00:53 |
|
Michel Cadot
Messages: 68624 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
I get not exactly the same thing with:
SQL> EXEC DBMS_MONITOR.session_trace_enable;
PL/SQL procedure successfully completed.
SQL>
SQL> WITH query1 AS (
2 SELECT /*+ MATERIALIZE */ * FROM t
3 )
4 SELECT * FROM query1;
C
-
Y
2 rows selected.
SQL> EXEC DBMS_MONITOR.session_trace_disable;
PL/SQL procedure successfully completed.
Abstract of the trace file:
...
PARSING IN CURSOR #5 len=171 dep=1 uid=0 oct=1 lid=0 tim=340880475664 hv=2308051955 ad='6b50499c'
CREATE GLOBAL TEMPORARY TABLE "SYS"."SYS_TEMP_0FD9D6600_20844BC" ("C0" VARCHAR2(1 CHAR) ) IN_MEMORY_METADATA CURSOR_SPECIFIC_SEGMENT STORAGE (OBJNO 4254950912 ) NOPARALLEL
END OF STMT
PARSE #5:c=0,e=13625,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=340880475654
....
Note the "IN_MEMORY_METADATA" and storage clause.
So both are correct there is GTT but not a stored one.
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Mar 28 09:30:53 CDT 2024
|