Q
Problem solve Get help with specific problems with your technologies, process and projects.

Insert not showing in v$sqlarea

If a stored PL/SQL procedure is executed, all the DML in the procedure should show up in v$sqlarea, right? My test case shows it's not there. Why don't I see the insert?

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);

Table created.

SQL> create or replace procedure jgtest1
is
begin
insert into jgtb values (123);
end;
/ 

Procedure created.

SQL> exec jgtest1;

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select sql_text from v$sqlarea where sql_text like '%jgtb%' and
sql_text not like '%sql_text%';

SQL_TEXT
------------------------------
create table jgtb (x int)

SQL> exit;
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.

This was last published in October 2006

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

  • How do I size a UPS unit?

    Your data center UPS sizing needs are dependent on a variety of factors. Develop configurations and determine the estimated UPS ...

  • How to enhance FTP server security

    If you still use FTP servers in your organization, use IP address whitelists, login restrictions and data encryption -- and just ...

  • 3 ways to approach cloud bursting

    With different cloud bursting techniques and tools from Amazon, Zerto, VMware and Oracle, admins can bolster cloud connections ...

SearchContentManagement

SearchHRSoftware

Close