Troubleshooting

ORA-00054: resource busy and acquire with NOWAIT specified

When I get an ORA-00054, it’s usually impossible to do the DDL unless I spend hours inconveniencing end-users by nuking their sessions.

Solutions :

alter session set ddl_lock_timeout= 900

Now, DDL will wait 900 seconds before aborting with a ORA-00054 error.

Advertisements
Standard
ASM

ADDING DATAFILE TO TABLESPACE IN ASM

Datafile scripts :

CREATE TABLESPACE USERS DATAFILE
‘+DATA’ SIZE 33511680K AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED,
‘+DATA’ SIZE 32724M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 33554416K AUTOEXTEND ON NEXT 512M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 30040M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 1010M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 910M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 910M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 910M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 910M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 910M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 910M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED,
‘+DATA’ SIZE 910M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

Add datafile to tablespace  ASM diskgroup  DATAGROUP.

Scripts sysntax:

ALTER TABLESPACE Table_Space_Name ADD DATAFILE ‘ASM_DISK_GROUP’ SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED

Example :

SQL> ALTER TABLESPACE USERS  ADD DATAFILE
‘+DATA’ SIZE 33511680K AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED;

Standard
Troubleshooting

ORA-39142: incompatible version number in dmp file

I am getting th ORA-39142 error while using an export (expdp) from 10.2.0.4.to import into an 11.2.0.6 database:

 

Why does the import (impdp) give me this ORA-39142 error and how to I overcome the ORA-39142 error?

Export Scripts :

expdp \”/ as sysdba\” DIRECTORY= DATA_PUMP_DIR DUMPFILE=expdp_full_%u.dmp LOGFILE=expdp_full.log FULL=Y  parallel=8

Error Messages :

ORA-39001: invalid argument value

ORA-39000: bad dump file specification

ORA-39142: incompatible version number 2.1 in dump file “/u01/exports/cust.dmp”

Solutions :

Source  Database :

oracle@p1dhsdb:~/scripts] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 – Production on Fri Dec 22 20:28:48 2017

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

 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select version from v$instance;

VERSION
—————–
10.2.0.1.0

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
[oracle@p1dhsdb:~/scripts] $cat impKDTAH.sh
impdp userid=\”/ as sysdba\” full=y DIRECTORY=EXP_DIR DUMPFILE=exp_kdtah_full.dmp logfile=ktah_ex.log
[oracle@p1dhsdb:~/scripts] $

Target Database :

[oracle@uatdhsdb:~] $sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 – Production on Fri Dec 22 20:28:03 2017

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

 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
select version from v$instance
SQL> 2
SQL> select * from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
————— —————-
HOST_NAME
—————————————————————-
VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT
—————– ——— ———— — ———- ——- —————
LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO
———- — —————– —————— ——— —
1 kdtah
uatdhsdb
11.1.0.7.0 15-DEC-17 OPEN NO 1 STOPPED
ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMAL NO

 

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@uatdhsdb:~] $cd scripts/
[oracle@uatdhsdb:~/scripts] $ls
expKDTAH.sh nohup.out

Source database export scripts

[oracle@uatdhsdb:~/scripts] $cat expKDTAH.sh
expdp userid=\”/ as sysdba\” full=y DIRECTORY= EXPIMP DUMPFILE=exp_kdtah_full.dmp LOGFILE=exp_kdtah_full.log version=10.2
[oracle@uatdhsdb:~/scripts] $

Target Database Import Scripts :

oracle@p1dhsdb:~/scripts] $cat impKDTAH.sh
expdp userid=\”/ as sysdba\” full=y DIRECTORY= EXPIMP DUMPFILE=exp_kdtah_full.dmp LOGFILE=imp_kdtah_full.log

 

 

Standard
Troubleshooting

ORA-01940: cannot drop a user that is currently connected

Recently when i was trying to drop one of the schema then the drop command was failing with below error:

SQL> drop user expDbfm cascade;
drop user expDbfm cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

The Error message itself is very clear the the user is connected so it cannot be dropped. Now we must find out the session for this specific users and kill those sessions in order to drop this user.

Find out the session for the connected user

SQL> SELECT sid, serial#, username, logon_time, (last_call_et / 60 / 60 / 24 ) "Days Inactive" FROM v$session WHERE username LIKE 'FINTEST5';

SID         SERIAL#              USERNAME            LOGON_TIME   Days Inactive
---------- ---------- ------------------------------ ----------   -------------
3             28047             expDbfm              22-Dec-17    .001597222

Kill all listed sessions

SQL> ALTER SYSTEM KILL SESSION '3,28047';

System altered.

SQL>

Check any active session again before dropping the user

SQL> SELECT sid, serial#, username, logon_time, (last_call_et / 60 / 60 / 24 ) "Days Inactive" FROM v$session WHERE username LIKE 'expDbfm';

no rows selected

>> Now we are read to drop the user

SQL> drop user expDbfm cascade;

User dropped.

SQL>

This can be done using TOAD or Oracle enterprise manager  as well. We need to check all connected users for the user which we want to drop and then kill all sessions belonging to that users.

Standard
Sql

How to find table size?

Hello,

if you want to see all table sizes in oracle database you can use this code.

SELECT owner, table_name, ROUND(sum(bytes)/1024/1024/1024) GB
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND   s.owner = i.owner
AND   s.segment_type IN ('INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
and   s.owner = l.owner
AND   s.segment_type in ('LOBSEGMENT', 'LOB PARTITION', 'LOB SUBPARTITION')
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND   s.owner = l.owner
AND   s.segment_type = 'LOBINDEX')
GROUP BY table_name, owner
order by sum(bytes) desc;
Standard
Troubleshooting

Error 45 initializing SQL*Plus

My command :

$nohup sqlplus "/ as sysdba" @script.sql &

Error Messages :

Error 45 initializing SQL*Plus

Solutions:

Go to end of the script, You have to add another line with a lone “/” after it, or you will get this error.

Bug 3039738 – Error 45 Initializing Sql*Plus Running A Shell Script

 

 

Standard
Troubleshooting

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

Problem :

ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.

Solutions :

+ (Check the datafile size)

  • Show Oracle Session;
SELECT
    O.OBJECT_NAME,
    S.SID,
    S.SERIAL#,
    P.SPID,
    S.PROGRAM,
    SQ.SQL_FULLTEXT,
    S.LOGON_TIME
FROM
    V$LOCKED_OBJECT L,
    DBA_OBJECTS O,
    V$SESSION S,
    V$PROCESS P,
    V$SQL SQ
WHERE
    L.OBJECT_ID = O.OBJECT_ID
    AND L.SESSION_ID = S.SID
    AND S.PADDR = P.ADDR
    AND S.SQL_ADDRESS = SQ.ADDRESS;

Output like this,

Ekran Resmi 2017-12-06 14.40.37

kill like

alter system kill session 'SID,SERIAL#';

(For example) ,

alter system kill session '198,473'😉

Standard