• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How long does Oracle retain the statistics for

by admin

Beginning with Oracle10G, when statistics are gathered for a table, the old statistics are retained so should there be any problem with performance of queries dependent on those statistics, the old ones can be restored.

This post outlines the facility by which statistics may be restored following their removal. This facility is available from Orale 10g onwards.

How long does Oracle retain the statistics for?

The default period for which statistics are retained is 31 days but this can be altered with:

execute DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (xx)

– where xx is the number of days to retain them.

Note: There is an overhead of storage in the SYSAUX tablespace with statistics so care should be taken not to cause the tablespace to fill with the statistics.

How do I know how many days the statistics are available for?

Below query will return the number of days stats are currently retained for.

sql> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

Below query will return the date statistics have been purged up to (so only dates newer then this can possibly be restored to). Any request to restore stats from this date or older will fail with: “ORA-20006: Unable to restore statistics, statistics history not available”

sql> select DBMS_STATS.GET_STATS_HISTORY_AVAILABILITY from dual;

How do I find the statistics history for a given table?

Below query will show the times statistics were regathered for a given table:

sql> select TABLE_NAME, STATS_UPDATE_TIME from dba_tab_stats_history

Filed Under: oracle

Some more articles you might also be interested in …

  1. Oracle Database: Redo log operations (Add/Drop/Change Location)
  2. Script/Queries to Monitor Temporary (TEMP) Tablespace Usage in Oracle Database
  3. How to Install and configure OSWatcher Black Box (OSWbb)
  4. How to Monitor Process Memory Usage on Oracle Pluggable Databases
  5. ORA-354 ORA-353 and ORA-312: Possible corruption in Online Redo Log File Members in a Redo Log Group
  6. New Connections to the Database lead to ORA-12518 or TNS-12518
  7. List of OCFS2 threads
  8. How to Rename the Default JSESSIONID in WebLogic
  9. How to Clear a Redo Log file in Oracle Database
  10. PL/SQL Nested Blocks

You May Also Like

Primary Sidebar

Recent Posts

  • vgextend Command Examples in Linux
  • setpci command – configure PCI device
  • db_load command – generate db database
  • bsdtar command – Read and write tape archive files

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright