analyze_all.sql

oracle dba 常用的管理脚本, 覆盖日常的系统管理.

--* File Name    : Analyze_All.sql
--* Author       : DR Timothy S Hall
--* Description  : Outdated script to analyze all tables for the specified schema.
--* Comment      : Use DBMS_UTILITY.ANALYZE_SCHEMA or DBMS_STATS.GATHER_SCHEMA_STATS if your server allows it.
--* Call Syntax  : @Ananlyze_All (schema-name)
--* Last Modified: 26/02/2002
SET PAGESIZE 0
SET FEEDBACK OFF
SET VERIFY OFF

SPOOL temp.sql

SELECT 'ANALYZE TABLE "' || table_name || '" COMPUTE STATISTICS;'
FROM   all_tables
WHERE  owner = Upper('&1')
ORDER BY 1;

SPOOL OFF

-- Comment out following line to prevent immediate run
@temp.sql

SET PAGESIZE 14
SET FEEDBACK ON
SET VERIFY ON