Locks in Oracle

V$LOCKED_OBJECT

SELECT LO.session_id, LO.oracle_username, LO.os_user_name, 

       OBJ.object_name , OBJ.owner
  FROM V$LOCKED_OBJECT LO, 
       DBA_OBJECTS OBJ
WHERE LO.object_id = OBJ.object_id;

V$LOCKED_OBJECT + V$SESSION

SELECT DISTINCT

       SESS.sid, SESS.serial#, SESS.schemaname, SESS.machine, 

       SESS.program, SESS.module, SESS.blocking_session,

       LO.os_user_name, OBJ.object_name , OBJ.owner,

       SESS.sql_id,

'ALTER SYSTEM KILL SESSION ''' || SESS.sid || ',' || SESS.serial# ||''';' kill_session

  FROM V$LOCKED_OBJECT LO, 

       DBA_OBJECTS OBJ, 

       V$SESSION SESS

WHERE LO.object_id = OBJ.object_id

  AND LO.session_id = SESS.sid;

V$LOCK

SELECT A.sid "blocking session", 

       B.sid "blocked session"  , 

       DECODE (A.block,1, A.sid ||' is Blocking '||B.sid,'Not Blocking'),  

       A.type "Lock Type"

FROM V$LOCK A, 

     V$LOCK B 

WHERE A.sid != B.sid 

  AND A.id1 = B.id1  

  AND A.id2 = B.id2 

  AND A.block = 1

  AND B.request > 0;

V$SESSION - lock time

SELECT 

  sid, serial#, username, machine, program,

  blocking_session "blocking_session",

  sid "blocked_session",

  serial# "blocked_serial#", 

  seconds_in_wait/60 "wait_time(minutes)"

FROM V$SESSION

WHERE blocking_session IS NOT NULL

ORDER BY blocking_session;

V$SESSION - lock time + lock info

SELECT 

  ROUND(V_SESS_LOCKED.seconds_in_wait/60) "WAIT_TIME (MIN)",

  V_SESS_LOCKED.sid       locked_sid, 

  V_SESS_LOCKED.serial#   locked_serial, 

  V_SESS_LOCKED.username  locked_username, 

  V_SESS_LOCKED.machine   locked_machine, 

  V_SESS_LOCKED.program   locked_program,

  V_SESS_LOCKED.blocking_session blocking_session,

  V_SESS_LOCKED.sql_id    locked_sql,

  V_SESS_LOCKING.sid      locking_session,

  V_SESS_LOCKING.serial#  locking_serial,

  V_SESS_LOCKING.username locking_username,

  V_SESS_LOCKING.machine  locking_machine,

  V_SESS_LOCKING.program  locking_program,

  V_SESS_LOCKING.sql_id   locking_sql

FROM V$SESSION V_SESS_LOCKED,

     V$SESSION V_SESS_LOCKING

WHERE V_SESS_LOCKED.blocking_session IS NOT NULL

  AND V_SESS_LOCKED.blocking_session = V_SESS_LOCKING.sid

ORDER BY ROUND(V_SESS_LOCKED.seconds_in_wait/60) DESC;

ORA-01873: the leading precision of the interval is too small

SELECT * FROM USER_JOBS
ORA-01873: the leading precision of the interval is too small

SELECT * FROM DBA_SCHEDULER_JOBS
ORA-01882: timezone region not found

After a removal of the suspected job, and recreating it again the issue was fixed.

"suspected job" - there was a lock in database, and the job session was terminated with ALTER SYSTEM KILL SESSION command.

BEGIN

 DBMS_JOB.remove(23);
 commit;
END;
/
SELECT * FROM USER_JOBS

Now is working!

Oracle Patch types

Oracle Patch types

Interim patches - contain a single bug fix or a collection of bug fixes provided as required

Diagnostic patches - intended to help diagnose or verify a fix

Bundle Patch Updates (BPUs) - a cumulative collection of fixes for a specific product or component

Patch Set Updates (PSUs) - a cumulative collection of high impact, low risk, and proven fixes for a specific product or component and Security Patch Updates

Security Patch Updates (SPU) - a cumulative collection of security bug fixes.  SPUs were formerly known as Critical Patch Updates (CPU).

System Patch - contains several sub-patches in a format that can be used by OPatchAuto.

Merge Label Request (MLR) - a merge of two or more fixes. MLR creation requires a label for the new set of merged code and a Patch Set Exception.

What to install?

RU - Release Update

Release Update

RU - Release Update

Oracle recommends that you keep your database software current by applying Release Updates (RUs).

RUs are listed in Primary Note for Database Proactive Patch Program (Doc ID 888.1)

Release Updates (RUs) are release quarterly: Third Tuesday of January, April, July and October. 

Each RU will be given a maximum of six Monthly Recommended Patches (MRPs), released monthly.

RUs are highly tested bundles of critical fixes which enable you to avoid known issues. 

They usually contain the following type of fixes: security, regression (bug), optimizer, and functional.

Monthly Recommended Patches (MRP)

Starting with update 19.17, Oracle is providing MRPs for Linux x86-64 to provide proactive patching between Release Updates.

Which patches to install for Oracle 19?

A. Oracle Database 19c Important Recommended One-off Patches (Doc ID 555.1)

This documents lists the latest patches for Oracle 19
for example:

19.22 DB RU - Patch 35943157

19.21 DB RU - Patch 35643107 

19.20 DB RU - Patch 35320081

19.19 DB RU - Patch 35042068

B. Primary Note for Database Proactive Patch Program (Doc ID 888.1)

This documents lists patches for All oracle versions.
for example:

ORA-12034: materialized view log on "SCHEMA"."MY_SERVICE" younger than last refresh

Issue: Refresh Materialized View is giving this error:

ERROR at line 1:

ORA-12034: materialized view log on "SCHEMA"."MY_MVIEW" younger than last refresh

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 794

ORA-06512: at "SYS.DBMS_SNAPSHOT", line 851

ORA-06512: at "SYS.DBMS_IREFRESH", line 683

ORA-06512: at "SYS.DBMS_REFRESH", line 195

ORA-06512: at "SCHEMA.REFRESH_PKG", line 19

ORA-06512: at line 2

Solution:

Complete refresh and then Fast Refresh the Materialized View

Example

BEGIN

 DBMS_MVIEW.refresh('MY_MVIEW' ,'C');

END;

/

BEGIN

 DBMS_MVIEW.refresh('MY_MVIEW' ,'F');

END;

/

Code Generator

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

--Generate DBMS_REFRESH.refresh script

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

COLUMN sql_cmd FORMAT A120 WORD_WRAP

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET NEWPAGE NONE

SET VERIFY OFF

SET ECHO OFF

SET SHOW OFF

SET TERMOUT OFF

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

--Generate DBMS_REFRESH.refresh script

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

COLUMN sql_cmd FORMAT A120 WORD_WRAP

SET LINESIZE 120

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET NEWPAGE NONE

SET ECHO OFF

SET TERMOUT OFF

spool manual_refresh_mviews.sql 

PROMPT SET FEEDBACK ON

SELECT 'spool refresh_'||name||'.log;'||CHR(10)||

'PROMPT Start DBMS_MVIEW.refresh() of '||name||CHR(10)||

'BEGIN'||CHR(10)||

' DBMS_MVIEW.refresh('''||name||''' ,''C'');' ||CHR(10)||

' DBMS_MVIEW.refresh('''||name||''' ,''F'');' ||CHR(10)||

' COMMIT;'||CHR(10)||

'END;'||CHR(10)||

'/'||CHR(10)||

'spool off;' as sql_cmd

FROM DBA_REFRESH_CHILDREN

WHERE rname = 'MASTER_GROUP'

order by name;

PROMPT spool off

spool off;

@manual_refresh_mviews.sql