Home » SQL & PL/SQL » SQL & PL/SQL » Dictionary table to see the operation of a procedure
Dictionary table to see the operation of a procedure [message #326992] Fri, 13 June 2008 05:38 Go to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
I was running a huge PL/SQL script through shell script and cron job. But, then I figured out that there was an unnecessary
insertion in a log table.
So, I changed the script so that the procedure skips the insertion.
To bring the change into effect, I had to kill the process.
I found out the process id and killed it with following command:

kill -KILL processId.


I queried the v$session table. But, there was no hanging user session.

Now, when I re-ran the script, everything seemed to go fine:
# ps command shows that the process is running.
# v$session shows the running session

Now, can I see what the procedure is doing; which operation it is stuck with, etc
(Maybe there are some kind of dictionary tables for this.)
Because I don't see any effect in other tables by the procedure.

I hope I was clear.

Thanks.
Re: Dictionary table to see the operation of a procedure [message #326994 is a reply to message #326992] Fri, 13 June 2008 05:42 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can look at v$session_waits to see what the session is currently waiting on.

I don't really understand what you're asking.
Re: Dictionary table to see the operation of a procedure [message #326997 is a reply to message #326994] Fri, 13 June 2008 05:57 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
I am asking for the dictionary views so that I can find out what the procedure is doing actually.

Ok, I will see the v$session_waits.

I also found out a query that shows currently running sql.
Select sql_text
from   v$sqlarea
where  (address, hash_value) in
(select sql_address, sql_hash_value 
        from v$session
        where username like 'my_user');


This showed that the queries inside the procedure are running.

Anyway, thanks.

I will try other things too; and ask you if there is any other problem.
Re: Dictionary table to see the operation of a procedure [message #326998 is a reply to message #326992] Fri, 13 June 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You should first investigate BEFORE killing.
After that it is too late to investigate, you can only have limited information.

Regards
Michel
Re: Dictionary table to see the operation of a procedure [message #327003 is a reply to message #326998] Fri, 13 June 2008 06:09 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
With the previous query that I posted,
I can see an INSERT statement like this:

INSERT INTO my_table(col1,col2,col3,col4) values(:B4,:B3,:B2,:B1);


I think the :B* stands for the memory address of the variables that I passed through the statement.

Can't I see the actual value being inserted?

I don't see the values being inserted inside the table my_table even there is a commit after the insert statement.
Re: Dictionary table to see the operation of a procedure [message #327026 is a reply to message #327003] Fri, 13 June 2008 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can't I see the actual value being inserted?

It depends on you Oracle version that you didn't post when you should.

Regards
Michel
Re: Dictionary table to see the operation of a procedure [message #327292 is a reply to message #327026] Sun, 15 June 2008 23:47 Go to previous messageGo to next message
sarwagya
Messages: 87
Registered: February 2008
Location: Republic of Nepal
Member
My oracle version is:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Re: Dictionary table to see the operation of a procedure [message #327296 is a reply to message #327292] Mon, 16 June 2008 00:03 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
V$SQL_BIND% views

Regards
Michel

[Updated on: Mon, 16 June 2008 00:04]

Report message to a moderator

Previous Topic: Bitmap Index
Next Topic: Viewing formatted data from a table
Goto Forum:
  


Current Time: Sun Dec 11 08:29:18 CST 2016

Total time taken to generate the page: 0.16202 seconds