{"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":"2025-06-19T00:45:00","modified_gmt":"2025-06-19T00:45:00","slug":"restore_tables_stats_v1-sql","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/performance-tuning\/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},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,992],"class_list":["post-4313","cposts","type-cposts","status-publish","hentry","category-notes","category-performance-tuning"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/4313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"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"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}