ORA Errors

This ORA Error post will update frequently for same page 

ORA-00031: session marked for kill.

The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.

No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.

Solution

Find the thread in OS level

select vs.sid,vs.username,vs.osuser, vs.process,vp.spid from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username='<User_name>' and vs.osuser='<applmgr>';

Will get output like this

SID    USERNAME     OSUSER   PROCESS   SPID

123        AP              applmgr     456          8765

Kill the session from OS level

kill –9 spid

Login database server

[oracle@san ~]$ ps -ef |grep 8765

oracle 87651 64 03:00 ? 02:03:43 oraclevis (LOCAL=NO)

oracle 62744 62411 0 06:11 pts/4 00:00:00 grep 8765

kill -9 8765

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Please see from v$lock if the object is being used by other sessions for below query

select obect_id1,object_id2 from dba_objects where owner='schemanname' and object_name='table_name';

1)   select sid from v$lock where id1='id1_obtained from previous query' and id2='obtained from previous query';

2)select serial# from v$session where sid='sid obtained from previous query';

3) select lo.session_id,lo.oracle_username,lo.os_user_name,

lo.process,do.object_name,

decode(lo.locked_mode,0, 'None',1, 'Null',2, 'Row Share (SS)',

3, 'Row Excl (SX)',4, 'Share',5, 'Share Row Excl (SSX)',6, 'Exclusive',

to_char(lo.locked_mode)) mode_held

from v$locked_object lo, dba_objects do

where lo.object_id = do.object_id

order by 1,5

/

SID    Oracle_User  OS_User    Process          Object_name

442       APPS            applmgr    22539       FND_CONCURRENT_REQUESTS

1199    APPS            applmgr    23004        FND_CONCURRENT_QUEUES

Kill those sessions or wait until user complete those sessions

alter system kill session 'serial#,sid';

ORA-65011: Pluggable database TEST does not exist.

Recently I am facing this error in 19c Database lets see the solution

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 TEST READ WRITE NO

SQL> alter session set container="TEST";

ERROR:

ORA-65011: Pluggable database TEST does not exist.

Check below parameter

SQL> show parameter case

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

_pdb_name_case_sensitive boolean FALSE

sec_case_sensitive_logon boolean FALSE

If _pdb_name_case_sensitive parameter is FALSE Make it true.

SQL> alter system set "_pdb_name_case_sensitive"=TRUE scope=spfile;

System altered.

SQL>

SQL> shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 5.3687E+10 bytes

Fixed Size 30386096 bytes

Variable Size 3.1407E+10 bytes

Database Buffers 2.2146E+10 bytes

Redo Buffers 103829504 bytes

Database mounted.

Database opened.

SQL>

SQL> alter session set container="TEST";

Session altered.

SQL>

Reference:

ORA-65011: Pluggable Database Does Not Exist (Doc ID 2642230.1)