Sessions and related system processes for Oracle to locate and kill lock tables and objects

Sessions and related system processes for Oracle to locate and kill lock tables and objects

Sessions and related system processes for Oracle to locate and kill lock tables and objects

I. handling process

1. Find the locked database table and related sid, serial # And spid:

Select object_name as object name, s. sid, s. serial #, p. spid as system process number
From v $ locked_object l, dba_objects o, v $ session s, v $ process p
Where l. object_id = o. object_id and l. session_id = s. sid and s. paddr = p. addr;

2. Disable related sessions in the database:

Alter system kill session 'sid, serial #';

-- Sid and serial # data found in step 1

3. Kill the process corresponding to the session from the system:

Kill-9 spid;

-- Spid is the system process number found in step 1.

After the above operations, you can re-operate the previously locked object.

Ii. Basic Introduction to related tables

Description of columns in V $ LOCKED_OBJECT:
XIDUSN: rollback segment number
XIDSLOT: Slot Number
XIDSQN: serial number
OBJECT_ID: ID of the locked object
SESSION_ID: sessionID of the lock
Oracle_USERNAME: The Oracle user name holding the lock
OS _USER_NAME: operating system username holding the lock
PROCESS: Operating System PROCESS number
LOCKED_MODE: Lock mode
Dba_objects column description (I am too lazy to translate it online)
OWNER
Username of the owner of the object
OBJECT_NAME
Name of the object
SUBOBJECT_NAME
Name of the sub-object (for example, partititon)
OBJECT_ID
Object number of the object
DATA_OBJECT_ID
Object number of the segment which contains the object
OBJECT_TYPE
Type of the object
CREATED
Timestamp for the creation of the object
LAST_DDL_TIME
Timestamp for the last DDL change (including GRANT and REVOKE) to the object
TIMESTAMP
Timestamp for the specification of the object
STATUS
Status of the object
TEMPORARY
Can the current session only see data that it place in this object itself?
GENERATED
Was the name of this object system generated?
SECONDARY
Is this a secondary object created as part of icreate for domain indexes?
V $ session Description
V $ SESSION is the basic information view, used to find the user SID or SADDR
Common columns:
SID: SESSION ID
SERIAL #: If a SID is used by another session, the value is automatically increased (when one SESSION ends, the other SESSION starts and uses the same SID ).
AUDSID: Reviews session ID uniqueness and confirms that it is usually used when looking for a parallel query mode
USERNAME: the USERNAME of the current session in oracle.
STATUS: This column is used to determine the session STATUS:
Achtive: SQL statement being executed (waiting for/using a resource)
Inactive: waiting for the operation (that is, waiting for the SQL statement to be executed)
Killed: marked as deleted
V $ process view
The v $ process view contains information about all processes running in Oracle. It is often used to establish a connection between the operating system process ID of an Oracle or service process and a database session.
Common columns:
ADDR: Process object address
PID: oracle process ID
SPID: operating system process ID