ORA-00054: resource busy and acquire with NOWAIT

To check which session is holding the object:

select a.inst_id, sid, serial#, username, machine  
from gv$session a, gv$locked_object b, dba_objects c
where b.object_id = c.object_id
and a.sid = b.session_id
and a.inst_id=b.inst_id
and OBJECT_NAME='TABLENAME';


INST_ID SID   SERIAL#  USERNAME    MACHINE    OWNER      OBJECT_NAME          
------- ----- ------- ---------- -----------  ---------- ------------- 
2       3365  42069      SYS      TESTSERVER  TESTUSER   TABLENAME

Kill the session, then the error should disappear.

ORA-01950: no privileges on tablespace ‘USERS’

SQL> create table test ( id number );
create table test ( id number )
*
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘USERS’

SOLUTION

SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='SCOTT';

no rows selected

SQL> alter user scott default tablespace users quota 10m on users;



User altered.

SQL>  create table test ( id number );



Table created.

SQL> select TABLESPACE_NAME,USERNAME,MAX_BYTES,BYTES from dba_ts_quotas where USERNAME='SCOTT';



TABLESPACE_NAME      USERNAME       MAX_BYTES      BYTES

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

USERS                SCOTT      10485760      65536

ORA-12532: TNS:invalid argument

Trying to run sqlplus to logon to a database, the following error occurs:

$ sqlplus scott

SQL*Plus: Release 12.2.0.1.0 Production on Tue Nov 14 14:46:12 2022

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Enter password:
ERROR:
ORA-12532: TNS:invalid argument

CAUSE

User password contains character “@”

PRCA-1000 : ASM Disk Group DSGRP123 does not exist

Trying to start up a database, and got the following error messages:

$ srvctl start database -d testdb
PRCR-1079 : Failed to start resource ora.testdb.db
CRS-2640: Required resource 'ora.DSGRP123.dg' is missing.

$srvctl config database -d TESTDB
...
..
.
PRCD-1012 : Failed to retrieve disk group list for database TESTDB.
PRCR-1035 : Failed to look up CRS resource ora.DSGRP123.dg for TESTDB
PRCA-1000 : ASM Disk Group DSGRP123 does not exist

SOLUTION

$ srvctl modify database -d TESTDB -a "+DATA1,FRA"

$ srvctl start database -d TESTDB

ORA-01591: lock held by in-doubt distributed transaction 1.23.456789

SQL>  select local_tran_id from dba_2pc_pending;

LOCAL_TRAN_ID
----------------------
1.23.456789

SQL> select local_tran_id, state,fail_time,force_time, retry_time 
     from dba_2pc_pending;

LOCAL_TRAN_ID  STATE   FAIL_TIME   RETRY_TIME  MIXED
------------   -----   ---------   ----------  -----
1.23.456789    prepared 1-OCT-23   22-OCT-23    no

SQL> rollback force '1.23.456789';

Rollback complete.

SQL> exec dbms_transaction.purge_lost_db_entry( '1.23.456789');
BEGIN dbms_transaction.purge_lost_db_entry( '1.23.456789'); END;

*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction
ORA-06512: at "SYS.DBMS_TRANSACTION", line 74
ORA-06512: at "SYS.DBMS_TRANSACTION", line 96
ORA-06512: at line 1

SQL>exec dbms_transaction.purge_lost_db_entry('1.23.456789');

PL/SQL procedure successfully completed.

SQL> select local_tran_id from dba_2pc_pending;

no rows selected