How to cancel a session with connection_id=-1

[Former Member]

I want to use

• ALTER SYSTEM DISCONNECT SESSION '<connection_id>'

to cancel a session with connection_id=-1.  However, there are many sessions with connection_id=-1.

There are other fields in M_TRANSACTIONS, can I use as follows:

• ALTER SYSTEM DISCONNECT SESSION '<connection_id>' where transaction_id = '...'

I try on  a sandbox but see no effect.

What is the best way?

Thanks!

[lbreddemann · Accepted solution]

For ALTER SYSTEM you cannot use a WHERE clause  - it's just not part of the syntax.

Connections with connection_id = -1 are internal connections and cannot be disconnected.

Instead you need to find the external session that spawned this internal thread and cancel that one.

- Lars

[Former Member · Accepted solution]

Instead you need to find the external session that spawned this internal thread and cancel that one.

Could you please provide an SQL to identify the parent session? 

Thanks!

[lbreddemann · Accepted solution]

Check the PARENT_CONNECTION_ID in M_CONNECTIONS.