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

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 … Continue reading ADDING DATAFILE TO TABLESPACE IN ASM

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 … Continue reading ORA-39142: incompatible version number in dmp file

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 … Continue reading ORA-01940: cannot drop a user that is currently connected

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 … Continue reading How to find table size?

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    

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 … Continue reading ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired.