At first, check the sessions
SQL>
SELECT DISTINCT spid, b.username, osuser
FROM sys.dba_ddl_locks a, sys.v_$session B, v$process c
WHERE B.SID = a.session_id
AND owner NOT IN ('SYS',
'WMSYS',
'CTXSYS',
'DBSNMP')
AND B.TYPE <> 'BACKGROUND'
AND B.status = 'INACTIVE'
AND (c.addr = b.paddr)
ORDER BY username, osuser;
Kill the session
SQL>
SELECT DISTINCT
'ALTER SYSTEM KILL SESSION '''
|| b.sid
|| ','
|| b.serial#
|| ''' IMMEDIATE;' AS KILL_THEM_ALL
FROM sys.dba_ddl_locks a, sys.v_$session B
WHERE B.SID = a.session_id
AND owner NOT IN ('SYS',
'WMSYS',
'CTXSYS',
'DBSNMP')
AND B.TYPE <> 'BACKGROUND'
AND B.status = 'INACTIVE'
SQL>
SELECT DISTINCT spid, b.username, osuser
FROM sys.dba_ddl_locks a, sys.v_$session B, v$process c
WHERE B.SID = a.session_id
AND owner NOT IN ('SYS',
'WMSYS',
'CTXSYS',
'DBSNMP')
AND B.TYPE <> 'BACKGROUND'
AND B.status = 'INACTIVE'
AND (c.addr = b.paddr)
ORDER BY username, osuser;
Kill the session
SQL>
SELECT DISTINCT
'ALTER SYSTEM KILL SESSION '''
|| b.sid
|| ','
|| b.serial#
|| ''' IMMEDIATE;' AS KILL_THEM_ALL
FROM sys.dba_ddl_locks a, sys.v_$session B
WHERE B.SID = a.session_id
AND owner NOT IN ('SYS',
'WMSYS',
'CTXSYS',
'DBSNMP')
AND B.TYPE <> 'BACKGROUND'
AND B.status = 'INACTIVE'