Oracle Locks have the following modes: 0: None1: Null Empty 2: Row-S Ring Sharing (RS): Shared table lock 3: Row-x line dedicated (RX): Modify 4: Share shared lock (S): Prevent other DML operations 5: S / ROW-X shared line dedicated (SRX): Block other transaction operations 6: Exclusive dedicated (X): Separate access to use the number, the higher the lock level, the more affected operations . A general query statement such as Select ... from ...; is less than 2 locks, sometimes appears in V $ locked_Object. Select ... from ... for update; is 2 lock. When the conversation uses the for Update substring to open a cursor, all data rows in the return set will be in the row level (ROW-X) single lock, and other objects can only query these data lines, and Update, Delete or SELECT cannot be performed. .for Update operation. INSERT / UPDATE / Delete ...; is a lock. There is no response before inserting the same record until the next 3 lock will wait for the last 3 lock, we must release the next to continue working. 3, 4 locks are generated when creating an index. Locked_mode is 2, 3, 4 does not affect DML (INSERT, DELETE, UPDATE, SELECT), but DDL (ALTER, DROP, etc.) will prompt ORA-00054 errors. Update / delete ... may result in 4, 5 locks when there is a primary key constraint. DDL statements are 6 locks. With DBA roles, see the current database locks to use the following SQL statement: select object_id, session_id, lockd_mode from v $ locked_object; select t2.user, t2.logon_time from v $ locked_Object T1 , V $ session t2 where t1.session_id = t2.sid order by t2.logon_time;
Select a.owner, A. Object_name, B.xidusn, B.xidslot, B.XIDSQN, B.Session_ID, B. Oracle_username, B.OS_USER_NAME, B.PROCHINE, C.STATUS, C.MACHINE, C.STATUS, C .Server, C.ProgramFrom All_Objects a, v $ locked_object b, sys.gv_ $ session cwhere (a.Object_id = B.Object_id) and (b.Process = C.Process) Order By 1, 2;
If there is a long-term list, it may be no lock. We can use the following SQL statement to kill the long-term non-normal lock: Alter System Kill Session 'SID, Serial #'; if there is a lock problem, a DML operation may wait for a long time. When you use the way to connect the database, don't terminate the user connection with the OS system command $ KILL Process_num or $ KILL -9 Process_num, because a user process may generate more than one lock, kill the OS process and cannot completely clear the lock The problem. Remember to use Alter System Kill Session 'SID, Serial #'; kill an abnormal lock.