Month: April 2019

Oracle 18c new feature: ALTER SYSTEM CANCEL SQL

arcsdegeo2 Comments

In the past when you wanted to terminate an Oracle database session that is causing a problem we used the classical SQL command:

SQL> ALTER SYSTEM KILL SESSION ‘sid,serial#’;

In 18c a new command is introduced , that will cancel the SQL statement being executed in the session without actually terminating the session:

SQL> ALTER SYSTEM CANCEL SQL ‘SID, SERIAL,SQLID’;

To simulate:

To find the 3 values SID, SERIAL, and SQLID you can use the following query:

SELECT s.inst_id,

s.sid,

s.serial#,

s.sql_id,

p.spid,

s.username,

s.program

FROM   gv$session s

JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id

WHERE  s.type != ‘BACKGROUND’;

PIC1

SQL> ALTER SYSTEM CANCEL SQL ‘874,51416,a5ks9fhw2v9s1’;

PIC2

The user session will generate the following error:

ORA-01013: user requested cancel of current operation

However, the user will still continue working fine (his session will not be terminated as shown below)

PIC3

This feature exists in 18c, 21c  (innovation release), 19c and 23ai (both long term releases).  The team at Oracle behind this feature has done a great job. I would like to credit Kishy Kumar from Oracle, who is also the lead developer and architect for reviewing this blog post.

 

ORA-00600 & ORA-48254 when upgrading to Oracle 12cR2

arcsdegeoLeave a comment

while upgrading a database from Oracle 12cR1 to 12cR2….the following errors were thrown in the alert log file:

Errors in file /oraclex/orcl/diag/rdbms/orcl/orcl/trace/orcl_m000_125839.trc (incident=708075):

ORA-00600: internal error code, arguments: [Migrate Relation Failure], [], [], [], [], [], [], [], [], [], [], []

ORA-48254: Migration Error [dbgrmmdmr_max_length_change] [PDB_SPACE_MGMT] [2] [66] [32] [9]

Incident details in: /oraclex/orcl/diag/rdbms/orcl/orcl/incident/incdir_708075/orcl_m000_125839_i708075.trc

Use ADRCI or Support Workbench to package the incident.

 

This is due to old ADR setup, so you need to reset ADR location

SQL> show parameter diagnostic_dest
SQL> ALTER SYSTEM SET diagnostic_dest=/orcl/ora23/diagnostic/ scop=spfile;