oracle lock table (kill the process at the OS level)

2011-03-16  来源:本站原创  分类:Database  人气:94 

Close database
SQL> shutdown immediate
Close the database to ensure that does not kill off all the User Session
When the instance can not be closed off when not using shutdown immediate, you can use shutdown abort shut down the instance, but closed again after startup and shut down the instance using shutdown immediate, guaranteed Database synchronization

When the oracle after the next kill oracle process, sid is marked as Killed, but not yet released the lock table, kill the process on the OS level.
Main steps are as follows:
1)
select s.PROCESS,
s.SID,
s.SERIAL #,
b.object_name,
b.subobject_name,
a.LOCKED_MODE,
s.OSUSER,
s.LOGON_TIME,
s.MACHINE,
s.PROGRAM
from v $ locked_object a, dba_objects b, v $ session s
where a.OBJECT_ID = b.object_id
and a.SESSION_ID = s.SID
2)
alter system kill session 'SID, SERIAL #';

3)
At this point if the oracle session is marked as killed, but still lock the table, you need to kill the SPID corresponding to the OS, the oracle process query spid's statement is as follows:
select spid, osuser, s.program
from v $ session s, v $ process p
where s.paddr = p.addr and s.sid = here to find out the SID of the above

4) kill -9 spid

View the current user's serial # and sid
select sid, serial #, status from v $ session where audsid = userenv ('sessionid');

View user's current spid
select spid from v $ process p join v $ session s on p.addr = s.paddr and s.audsid = userenv ('sessionid');

View the current user's trace file path:

select p.value | | '\' | | t.instance | | '_ora_' | | ltrim (to_char (p.spid, 'fm99999')) | | '. trc'

from v $ process p, v $ session s, v $ parameter p, v $ thread t

where p.addr = s.paddr and s.audsid = userenv ('sessionid') and p.name = 'user_dump_dest';

相关文章
  • oracle lock table (kill the process at the OS level) 2011-03-16

    Close database SQL> shutdown immediate Close the database to ensure that does not kill off all the User Session When the instance can not be closed off when not using shutdown immediate, you can use shutdown abort shut down the instance, but closed a

  • ORA-00031: session marked for kill on oracle lock table processing 2011-07-29

    ORACLE some operations in the process is killed, the status is set to "killed", but the lock does not release the resources for a long time, no way, when it had to restart the database. But this is not the best solution, now offers a solution to

  • Oracle lock table problem simple processing techniques 2011-03-27

    Oracle lock table problem simple processing techniques http://database.51cto.com 2010-04-16 14:27 Anonymous I want to comment (0) Abstract: In the development of Oracle database, we often experience frequent operation of Oracle tables, there will be

  • oracle lock table problem 2011-04-28

    1, Select * From v $ locked_object; lock table object under sessionid, to Tools - sessions to find the appropriate sessions to see the lock table sql statement Or direct execute: Select a.inst_id, c.sid, c.serial #, d.name, b.object_name, c.username,

  • View oracle lock table 2010-04-15

    View the process of locking the table SQL statement 1: select sess.sid, sess.serial #, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v $ locked_object lo, dba_objects ao, v $ session sess where ao.object_id = lo.object_id a

  • Oracle lock table to solve the problem 2011-04-28

    Lock table to find information: select / * + RULE * / ls.osuser os_user_name, ls.username user_name, decode (ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type, o.

  • Oracle strong to kill the process, to resolve issues such as table lock 2011-10-10

    1, find the sid, serial #; SELECT / * + rule * / s.username, l.type, decode (l.type, 'TM', 'TABLE LOCK', 'TX', 'ROW LOCK', NULL) LOCK_LEVEL, o.owner, o.object_name, o.object_type, s.sid, s.serial #, s.terminal, s.machine, s.program, s.osuser, s.statu

  • View and delete oracle lock table 2010-08-20

    view table oracle check whether the table is locked locked SELECT a.sid, b.owner, object_name, object_type FROM v $ lock a, all_objects b WHERE TYPE = 'TM' and a.id1 = b.object_id; Found in the table are locked So that it can kill SELECT sid, serial

  • Oracle lock table. Relieving 2010-10-29

    View the table is locked: select p.spid, c.object_name, b.session_id, b.oracle_username, b.os_user_name from v $ process p, v $ session a, v $ locked_object b, all_objects c where p.addr = a.paddr and a.process = b.process and c.object_id = b.object_

  • Oracle Session and kill the process 2010-11-03

    Query Session: SELECT se.username, se.SID, se.serial#, pr.spid, se.status, SUBSTR (se.program, 1, 10) prog, SUBSTR (se.machine, 1, 10) mach, sq.sql_text FROM v$session se, v$sqlarea sq, v$process pr WHERE se.paddr = pr.addr(+) AND se.sql_address = sq

  • oracle lock table processing 2011-05-11

    When a database user in the database to insert, update, delete data in a table, or add a table or a table primary key index, often appear ora-00054: resource busy and acquire with nowait specified such a mistake. Mainly because there are transaction

  • Oracle lock table query [Memo] 2010-02-24

    select t2.username, t2.sid, t2.serial#, t3.object_name, t2.OSUSER, t2.MACHINE, t2.PROGRAM, t2.LOGON_TIME, t2.COMMAND, t2.LOCKWAIT, t2.SADDR, t2.PADDR, t2.TADDR, t2.SQL_ADDRESS, t1.LOCKED_MODE from v$locked_object t1, v$session t2, dba_objects t3 wher

  • lock table oracle check process, kill the lock table into the ... 2011-03-23

    Quote Process the SQL statement locks the table view 1: select sess.sid, sess.serial #, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v $ locked_object lo, dba_objects ao, v $ session sess where ao.object_id = lo.object_id

  • oracle view the process and unlock the lock table 2010-11-05

    View the process of SQL statement locks table 1: select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.sessi

  • oracle check table lock and unlock process 2010-11-05

    Process the SQL statement locks the table view 1: select sess.sid, sess.serial#, lo.oracle_username, lo.os_user_name, ao.object_name, lo.locked_mode from v$locked_object lo, dba_objects ao, v$session sess where ao.object_id = lo.object_id and lo.sess

  • oracle delete table recovery, and export and import oracle dmp file lock 2011-05-12

    A deleted recovery 1. Accidentally deleted a database table, an hour before the data recovery using the administrator login select * from table as of timestamp sysdate-1/12 - two hours before the query of a table of data! export data to rebuild the t

  • oracle can not afford to kill the process to deal with 2010-09-16

    Some of the process of being killed in ORACLE, the state is set to "killed", but the lock does not release the resources for a long time, sometimes there is really no alternative but to restart the database. But the shutdown immediate fear of de

  • oracle kill the process in dealing with 2010-09-16

    Some of the process is killed in ORACLE, the status is set to "killed", but the lock does not release the resources for a long time, sometimes really no alternative but to restart the database. But the shutdown immediate fear could not kill the

  • (Transfer) from the start to help you solve the Oracle problem deadlock kill process 2010-08-19

    http://dev.firnow.com/course/7_databases/oracle/oraclejs/20081012/150416.html Starting from the issue of deadlock to help you kill the process, Oracle www.firnow.com time: 2008-10-12 Author: Anonymous Editor: Site hits: 458 [review] Integrated Resour

  • ORACLE completely kill the process 2010-11-16

    Oracle is still relatively common, so I researched how to kill the Oracle process deadlock, come here to share with you, we hope to be useful. 1. Check which process is locking: Charles V $ DB_OBJECT_CACHE view: SELECT * FROM V $ DB_OBJECT_CACHE WHER