Whether the Oracle database query object is locked and unlocked

Source: Internet
Author: User

Whether the Oracle database query object is locked and unlocked

For details about whether the Oracle database object is locked, see the following SQL statement and the unlock process:

-- Query the locked objects of the current user
Select B. OBJECT_NAME, B. OWNER, C. SID, C. SERIAL #, C. OSUSER, C. MACHINE, C. TERMINAL
From v $ LOCKED_OBJECT A, DBA_OBJECTS B, V $ SESSION C
Where a. OBJECT_ID = B. OBJECT_ID
And a. SESSION_ID = C. SID
AND c. STATUS = 'inactive'
And c. TYPE = 'user'
Order by 1;
 

-- Unlock statement
DECLARE
V_ SQL VARCHAR2 (500 );
CURSOR C_SESSION IS
Select B. OBJECT_NAME,
B. OWNER,
C. sid as SESSION_ID,
C. SERIAL # as serial #,
C. OSUSER,
C. MACHINE,
C. TERMINAL
From v $ LOCKED_OBJECT A, DBA_OBJECTS B, V $ SESSION C
Where a. OBJECT_ID = B. OBJECT_ID AND A. SESSION_ID = C. SID
And c. STATUS = 'inactive' and c. TYPE = 'user'
Order by 1;
BEGIN
FOR GET_SESSION IN C_SESSION
LOOP
BEGIN
V_ SQL: = 'alter SYSTEM KILL SESSION ''' | GET_SESSION.SESSION_ID | ',' | GET_SESSION.SERIAL # | '''immediate ';
Execute immediate V_ SQL;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
End loop;
END;
/

Installing Oracle 12C in Linux-6-64

RHEL6.4 _ 64 install a single instance Oracle 12cR1

New Features of Oracle 12C: Paging Query

12 new features of Oracle 12C

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.