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 […]

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 […]

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 […]

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 […]

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 […]

Compiles all invalid views for specified schema

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT ‘ALTER VIEW ‘ || a.owner || ‘.’ || a.object_name || ‘ COMPILE;’ FROM all_objects a WHERE a.object_type = ‘VIEW’ AND a.status = ‘INVALID’ AND a.owner = Decode(Upper(‘&&1’), ‘ALL’,a.owner, Upper(‘&&1’)); SPOOL OFF — Comment out following line to prevent immediate run @temp.sql SET PAGESIZE […]

Compile all İnvalid Trigger

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT ‘ALTER TRIGGER ‘ || a.owner || ‘.’ || a.object_name || ‘ COMPILE;’ FROM all_objects a WHERE a.object_type = ‘TRIGGER’ AND a.status = ‘INVALID’ AND a.owner = Decode(Upper(‘&&1’), ‘ALL’,a.owner, Upper(‘&&1’)); SPOOL OFF — Comment out following line to prevent immediate run @temp.sql SET PAGESIZE […]

Compile all invalid Procedure

SET PAGESIZE 0 SET FEEDBACK OFF SET VERIFY OFF SPOOL temp.sql SELECT ‘ALTER PROCEDURE ‘ || a.owner || ‘.’ || a.object_name || ‘ COMPILE;’ FROM all_objects a WHERE a.object_type = ‘PROCEDURE’ AND a.status = ‘INVALID’ AND a.owner = Decode(Upper(‘&&1’), ‘ALL’,a.owner, Upper(‘&&1’)); SPOOL OFF — Comment out following line to prevent immediate run @temp.sql SET PAGESIZE […]