My Oracle Support Banner

Rolling Cursor Invalidations with DBMS_STATS.AUTO_INVALIDATE (Doc ID 557661.1)

Last updated on JUNE 13, 2023

Applies to:

Oracle Database - Personal Edition - Version and later
Oracle Database Cloud Schema Service - Version N/A and later
Gen 1 Exadata Cloud at Customer (Oracle Exadata Database Cloud Machine) - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.


Starting with Oracle10g, the DBMS_STATS package offers the AUTO_INVALIDATE option for the NO_INVALIDATE parameter of its GATHER_xxx_STATS and DELETE_xxx_STATS procedures. This parameter allows the user to specify when to invalidate dependent cursors i.e. cursors cached in the library cache area of the shared pool which reference a table, index, column or fixed object whose statistics are modified by the procedure call.

According to the documentation the values NO_INVALIDATE can take are:

  • TRUE: does not invalidate the dependent cursors
  • FALSE: invalidates the dependent cursors immediately
  • AUTO_INVALIDATE (default): have Oracle decide when to invalidate dependent cursors

This document describes details of how AUTO_INVALIDATE works.



This article may be of interest to DBAs interested in the behaviour of DBMS_STATS with respect to cursor invalidations.


To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document
 Cursor Invalidations on Gathering Statistics prior to Oracle10g
 Cursor Invalidations with Oracle10g and AUTO_INVALIDATE
 Testcase for Cursor Invalidations using AUTO_INVALIDATE

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.