Home » RDBMS Server » Server Administration » Can PMON be killed? (Oracle 8.0)
Can PMON be killed? [message #295707] Wed, 23 January 2008 02:33 Go to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi guru,
I run a Package in oracle 8.0. It has many proceudres. In one stage, it is updating a table, which is in remote database. Before update statement, I Locked the table (in Exclusive mode without Nowait) also

Since it was taking more than 7 hrs to run the package, i just killed the session. But, now I couldnt even compile a procedure on the locked table.

My DBA says that the PMON is running. It will take its own time for recovering data.

How to release the lock in the killed session?
How to stop the PMON in the kiled session?

Re: Can PMON be killed? [message #295709 is a reply to message #295707] Wed, 23 January 2008 02:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The session is rolling back what you did during those seven hours. That could take up some time (even more that seven hours, maybe more, maybe less)
PMON is a database-process, you don't kill it. (It would kill the database)

You will just have to wait.
Re: Can PMON be killed? [message #295712 is a reply to message #295709] Wed, 23 January 2008 02:55 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Thanx Frank !

It will be great, if the Lock can be released in the killed session. Is it possible?

Re: Can PMON be killed? [message #295713 is a reply to message #295712] Wed, 23 January 2008 02:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, the lock is released when the data are safe.

Regards
Michel
Re: Can PMON be killed? [message #295737 is a reply to message #295713] Wed, 23 January 2008 04:18 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Michel,

Is it possible to see how much has been recovered and how much (how many rows) yet to be recovered? how long will the oracle take for recovery?

Thnx in advance.
-Lenin.
Re: Can PMON be killed? [message #295740 is a reply to message #295737] Wed, 23 January 2008 04:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$transaction.used_blk contains the number of undo blocks used by the transaction.
When a transaction rolls back or is rolled back then this number decreases.
Take 2 snapshots at interval of 1 minute then you know how much blocks are released in a minute, divide the last number by this and you will know how long it will last.

Regards
Michel
Re: Can PMON be killed? [message #295758 is a reply to message #295740] Wed, 23 January 2008 05:45 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Hi Michel,
I checked the used_ublk 1 hr before. It was 19. Even now, it is 19. and still continues.
That means, oracle has to recover 19 records. right?

To recover 19 records, will the oracle take more than hour?

Will be there any other problem? Like dead-lock or anything else??

Sorry if I am wrong.

The SID for the killed session is 43. I gave the following query. I am using TOAD.

SELECT used_ublk, s.taddr, T.addr FROM v$session s, v$transaction T
WHERE s.taddr = T.addr
AND SID = 43

Re: Can PMON be killed? [message #295761 is a reply to message #295758] Wed, 23 January 2008 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$transaction gives the number of blocks not records.
Don't use TOAD, use SQL*Plus. I don't trust anything returned by TOAD although if you give a query it is likely that the result is correct.
Are you sure this is the same session?
Query v$lock to see if this session holds locks.
Query v$session_wait to see if this session is waiting on something.
Does the associated process still exists at OS level (query v$process)?

Regards
Michel
Re: Can PMON be killed? [message #295783 is a reply to message #295761] Wed, 23 January 2008 07:33 Go to previous messageGo to next message
catchme_lenin
Messages: 35
Registered: January 2008
Location: Dubai
Member
Michel: Are you sure this is the same session?
Lenin: Yes.
Michel:Query v$lock to see if this session holds locks.
SQL> SELECT * FROM v$lock WHERE SID = 43
2 AND addr = '0700000021638DF8';

Result:
Addr: 0700000021638DF8
SID - 43
Type - TX
ID1 - 393247
ID2 - 1095832
LOMODE - 6
REQUEST - 0
CTIME - 93550
BLOCK - 0

Michel:Query v$session_wait to see if this session is waiting on something.
Lenin: The Event - 'SQL*Net more data from dblink', Seconds in waiting - 93740, State - 'Waiting'.

Michel:Does the associated process still exists at OS level (query v$process)?
Lenin: query has no records for the particular ADDR = 0700000021638DF8.
---
Kindly let me know whether it has been dead locked or session is waiting on something,...
What I have to do to over come this issue?

Thanx a lot, Michel.
-Lenin.
Re: Can PMON be killed? [message #295790 is a reply to message #295783] Wed, 23 January 2008 07:54 Go to previous message
Michel Cadot
Messages: 64135
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ADDR in each view are different meanings, don't restrict on this with constant value use joins.
v$process.addr = v$session.paddr

Your session is waiting on db links.
I can't see in what case you are. Too much information are missing.
Sorry.

Regards
Michel
Previous Topic: How to deside the SGA size for fresh oracle installation?
Next Topic: dbms_job
Goto Forum:
  


Current Time: Thu Dec 08 05:53:11 CST 2016

Total time taken to generate the page: 0.05778 seconds