• 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. DDL Statement Examples in SQL
  2. What roles can be set as default for a user in Oracle Database
  3. Oracle GoldenGate: How to start Extract & Replicat using Shell Script
  4. How to Rename the Default JSESSIONID in WebLogic
  5. ORA-27125: unable to create shared memory segment; Error: 28: No space left on device
  6. ORA-06512: at line num
  7. Substitution variable in PL/SQL
  8. How to Export and Import Data Guard Broker Configuration in Oracle 19c
  9. Undo Modes in 12.2 Multitenant Databases – Local and Shared Modes
  10. How do stubs work in a WebLogic Server cluster?

You May Also Like

Primary Sidebar

Recent Posts

  • aws ec2: CLI for AWS EC2 (Command Examples)
  • aws cur – Create, query, and delete AWS usage report definitions (Command Examples)
  • aws configure – Manage configuration for the AWS CLI (Command Examples)
  • aws cognito-idp: Manage Amazon Cognito user pool and its users and groups using the CLI

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright