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

The error is raised when the following statement is executed against the Oracle Database.

SQL> truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table WRI$_OPTSTAT_HISTHEAD_HISTORY
               *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Cause: 

The statement requires an exclusive lock on the table.If the table is active and has DML or DDL transactions against it the transaction will not be able to acquire the lock quickly. This will cause the transaction to fail and return the resource busy error to the session.

Solution:

Using DDL_LOCK_TIMEOUT

You can increased the timeout by modifying the DDL_LOCK_TIMEOUT parameter in your session. Once this is set Oracle will wait for the new TIMEOUT before returning the “ORA-00054: resource busy and acquire with NOWAIT specified” error.

[ora@sonu ~]$ sqlplus / as sysdba

SQL> ALTER SESSION SET ddl_lock_timeout=900;
Session altered.

ORA-28365: wallet is not open encountered below issue while Starting the Database

SQL> startup
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size                 12170960 bytes
Variable Size            2818574640 bytes
Database Buffers         1.8589E+10 bytes
Redo Buffers               54935552 bytes
Database mounted.
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-28365: wallet is not open
Process ID: 12599
Session ID: 260 Serial number: 21611


                                   Solution:
Start the instance in mount mode

[oracle@ora]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jul 10 17:02:32 2020

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area 2.1475E+10 bytes
Fixed Size                 12170960 bytes
Variable Size            2818574640 bytes
Database Buffers         1.8589E+10 bytes
Redo Buffers               54935552 bytes
Database mounted.

Check Wallet status
SQL> select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;

STATUS
------------------------------
WALLET_DIR
--------------------------------------------------------------------------------
WALLET_TYPE
--------------------
CLOSED
/apps/DEV/product/12.2.0/dbhome_1/admin/DEV/wallet/
UNKNOWN


SQL> administer key management set keystore open identified by "tde1986";

keystore altered.

SQL> select * from v$encryption_wallet;

WRL_TYPE   WRL_PARAMETER  STATUS  WALLET_TYPE   WALLET_OR ULLY_BAC
    CON_ID    FILE
/apps/DEV/product/12.2.0/dbhome_1/admin/DEV/wallet/   OPEN   PASSWORD    SINGLE    NO       0


SQL> alter database open;


Database altered.

EXP-00008: ORACLE error 1455 encountered

I an facing below error when attempting to export schema level on 12.1 oracle database.

[oracle@sonu EXPIMP]$ exp system/password owner=DEV file=DEV.dmp log=DEV.log statistics=none

EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00056: ORACLE error 1403 encountered
ORA-01403: no data found


solution :-


Try adding below parameter to resolved this issue.

constraints=n indexes=n statistics=none

[oracle@sonu EXPIMP]$ exp system/password owner=DEV file=DEV.dmp log=DEV.log constraints=n indexes=n statistics=none

issue resolved.

JOB QUEUE PROCESSES parameter in 12C database

JOB QUEUE PROCESSES is used for background process in Oracle such as DBMS JOBS or scheduler job is running from this parameter.

If you set the value to zero then no scheduler or DBMS jobs is running in background. all jobs disabled to run.

Note:
Up to 12c, JOB_QUEUE_PROCESSES is set to a value in the range of 0 to 1000.
For 18c, JOB_QUEUE_PROCESSES is set to a value in the range of 0 to 4000.

Check the value of JOB QUEUE PROCESSES

[oracle@ora ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue Jul 7 10:23:35 2020

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL>

SQL> Show parameter job_queue_processes

NAME                   TYPE     VALUE
---------------------- -------- -------
job_queue_processes    integer  4000

SQL> alter system set job_queue_processes=0;

System altered.

SQL> Show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     0