If a stored PL/SQL procedure is executed, all the DML in the procedure should show up in v$sqlarea, right? But the test case below shows it's not there:
SQL> create table jgtb (x int);
SQL> create or replace procedure jgtest1
insert into jgtb values (123);
SQL> exec jgtest1;
PL/SQL procedure successfully completed.
SQL> select sql_text from v$sqlarea where sql_text like '%jgtb%' and
sql_text not like '%sql_text%';
create table jgtb (x int)
Why don't I see the insert? This is on 10.1.0.4.
Your select statement is selecting info from the shared SQL area. The definition of the shared SQL area is: "The shared SQL area is used for maintaining recently executed SQL commands and their execution plans. Oracle divides each SQL statement that it executes into a shared SQL area and a private SQL area. When two users are executing the same SQL, the information in the shared SQL area is used for both. The shared SQL area contains the parse tree and execution plan, whereas the private SQL area contains values for the bind variable (persistent area) and runtime buffers (runtime area). Oracle creates the runtime area as the first step of an execute request. For INSERT, UPDATE and DELETE statements, Oracle frees the runtime area after the statement has been executed. For queries, Oracle frees the runtime area only after all rows have been fetched or the query has been canceled."
The above definition is taken from the book OCA/OCP Oracle9i DBA Fundamentals I Study Guide.
In other words, the statement:
select sql_text from v$sqlarea where sql_text like
'%jgtb%' and sql_text not like '%sql_text%'
will only show DML statements if the DML statement used a bind variable.
For example, if you changed your insert statement "insert into jgtb values (123)" to "insert into jgtb values (&name)" your insert statement would then be displayed in the view v$sqlarea.