{"id":4313,"date":"2025-06-18T23:51:43","date_gmt":"2025-06-18T23:51:43","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&#038;p=4313"},"modified":"2026-09-26T13:07:22","modified_gmt":"2026-09-26T07:37:22","slug":"restore_tables_stats_v1-sql","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/restore_tables_stats_v1-sql\/","title":{"rendered":"restore_tables_stats_v1.sql"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code>-------------------------------------------------------------------------------\n-- Script:     RESTORE_TABLES_STATS_BY_SQLID_v1.sql\n-- Purpose:    Generates DBMS_STATS.RESTORE_TABLE_STATS commands for all tables\n--             involved in the execution plan of a given SQL ID.\n--\n-- Description:\n--             This script scans V$SQL_PLAN for a specified SQL ID,\n--             extracts all referenced tables, and prints EXEC statements to\n--             restore their optimizer statistics as of a user-provided timestamp.\n--\n-- Usage:\n--             Run in SQL*Plus or SQL Developer.\n--             Prompts for:\n--               - SQL ID (e.g., 9f3ac2b7k1mxy)\n--               - Restore timestamp in format: DD\/MM\/YYYY HH24:MI\n--\n-- Output:\n--             A list of executable DBMS_STATS.RESTORE_TABLE_STATS commands.\n--             These can be manually executed or spooled to a script file.\n--\n-- Example:\n--             Enter SQL ID: f7gq9x4h8lm2a\n--             Enter restore timestamp: 18\/06\/2025 11:30\n--\n--             Output:\n--             EXEC DBMS_STATS.RESTORE_TABLE_STATS('HR', 'EMPLOYEES', as_of_timestamp => TO_DATE('18\/06\/2025 11:30', 'DD\/MM\/YYYY HH24:MI'), no_invalidate => FALSE);\n--             EXEC DBMS_STATS.RESTORE_TABLE_STATS('HR', 'DEPARTMENTS', as_of_timestamp => TO_DATE('18\/06\/2025 11:30', 'DD\/MM\/YYYY HH24:MI'), no_invalidate => FALSE);\n--             EXEC DBMS_STATS.RESTORE_TABLE_STATS('HR', 'JOBS', as_of_timestamp => TO_DATE('18\/06\/2025 11:30', 'DD\/MM\/YYYY HH24:MI'), no_invalidate => FALSE);\n--\n-- Author:     W3Buddy\n-- Version:    v1.0\n-- Date:       2025-06-18\n-------------------------------------------------------------------------------\n\nSET SERVEROUTPUT ON\nACCEPT v_sqlid CHAR PROMPT 'Enter SQL ID: '\nACCEPT v_asof_ts CHAR PROMPT 'Enter restore timestamp (DD\/MM\/YYYY HH24:MI): '\n\nDECLARE\n  v_sqlid   VARCHAR2(20) := '&amp;v_sqlid';\n  v_ts_str  VARCHAR2(30) := '&amp;v_asof_ts';\n  v_asof_ts DATE;\nBEGIN\n  v_asof_ts := TO_DATE(v_ts_str, 'DD\/MM\/YYYY HH24:MI');\n\n  FOR rec IN (\n    SELECT DISTINCT OBJECT_OWNER, OBJECT_NAME\n    FROM   V$SQL_PLAN\n    WHERE  SQL_ID = v_sqlid\n    AND    OBJECT_TYPE = 'TABLE'\n    AND    OBJECT_NAME IS NOT NULL\n  )\n  LOOP\n    DBMS_OUTPUT.PUT_LINE(\n      'EXEC DBMS_STATS.RESTORE_TABLE_STATS(''' ||\n      rec.OBJECT_OWNER || ''', ''' ||\n      rec.OBJECT_NAME || ''', as_of_timestamp => TO_DATE(''' || v_ts_str || ''', ''DD\/MM\/YYYY HH24:MI''), no_invalidate => FALSE);'\n    );\n  END LOOP;\nEXCEPTION\n  WHEN OTHERS THEN\n    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);\nEND;\n\/<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225,992],"tags":[],"class_list":["post-4313","post","type-post","status-publish","format-standard","hentry","category-database","category-performance-tuning"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=4313"}],"version-history":[{"count":8,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4313\/revisions"}],"predecessor-version":[{"id":4331,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/4313\/revisions\/4331"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=4313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=4313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=4313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}