Oracle 19c中Shell变量传入存储过程后未正常收集统计信息

1. 变量传递失效：SQL脚本里DEFINE schema='$owner'使用了单引号，Shell不会解析单引号内的$owner变量，导致存储过程接收的参数是字符串'$owner'而非实际的SCOTT——这就是统计信息未执行的核心原因，因为不存在名为$owner的schema。
2. 输出未正常显示：虽然设置了SET SERVEROUTPUT ON，但一是参数错误导致输出的消息指向无效schema，二是-S静默模式下需确保输出缓冲区足够，建议添加SIZE UNLIMITED避免截断。

方法一：通过sqlplus命令行传参（最简洁） #

调整gstat.sql： #

SET SERVEROUTPUT ON SIZE UNLIMITED
-- 直接引用命令行传入的第一个参数&1
EXEC gather_schema_stats_proc('&1');

调整Shell脚本： #

#!/bin/bash
owner="SCOTT"
export ORACLE_SID=abcdb
export ORACLE_HOME=/u01/app/oracle/product/db_home
# 将owner作为参数传递给SQL脚本
sqlplus -S '/ as sysdba' @gstat.sql "$owner"

方法二：通过Here Document直接注入变量 #

如果不想修改SQL脚本，可在Shell里用Here Document直接编写PL/SQL调用：

#!/bin/bash
owner="SCOTT"
export ORACLE_SID=abcdb
export ORACLE_HOME=/u01/app/oracle/product/db_home
sqlplus -S '/ as sysdba' <<EOF
SET SERVEROUTPUT ON SIZE UNLIMITED
EXEC gather_schema_stats_proc('$owner');
EOF

验证方法 #

1. 执行脚本后，应看到如下输出：

   Gathering statistics for schema SCOTT...
   Statistics gathering for schema SCOTT completed.
   PL/SQL procedure successfully completed.
   

2. 确认统计已收集：
   登录SQL*Plus执行：

   SELECT last_analyzed FROM all_tables WHERE owner = 'SCOTT' AND table_name = 'EMP';
   

   查看last_analyzed字段是否为当前时间附近。

内容的提问来源于stack exchange，提问作者Kishan