sql - 使用 SQL 语句调试 Shell 脚本时出错到 V_$UNDOSTAT

Question

我想每 6 小时在一张表中存储 UNDO 表空间的统计信息。我创建了简单的表:

CREATE TABLE SYS.TB_UNDOSTAT (
 MAX_UNDOBLKS NUMBER,
 MAX_QUERY_LENGTH NUMBER,
 MAX_QUERY_ID VARCHAR2(13),
 DATE_OF_STAT DATE,
 DMY_OF_STAT VARCHAR2(30),
 TIME_OF_STAT VARCHAR2(30)); 

之后我创建了 Oracle 外部作业:

BEGIN
   DBMS_SCHEDULER.CREATE_JOB
        (job_name=>'ACCUMULATE_UNDOSTAT',
        repeat_interval =>'FREQ=DAILY; BYHOUR=05,11,17,23',
        job_type=>'EXECUTABLE',
        job_action=>'/home/oracle/scripts/UNDOSTAT/accumulate_undostat_111.bsh',
        enabled =>TRUE,
        auto_drop=>FALSE,
        comments=>'Take accumulate statistics from V$UNDOSTAT to 
        SYS.TB_UNDOSTAT one time through 6 hours On 111 Server'
        );
END;

accumulate_undostat_111.bsh 文件的内容是:

#!/bin/bash
export ORACLE_HOME=/u01/home/oracle/product/11.2.0/db_1
export ORACLE_SID=parustest
export PATH=$ORACLE_HOME/bin:$PATH 
sqlplus -s << EOF
/ as sysdba
INSERT INTO FGA_OWNER.TB_UNDOSTAT (MAX_UNDOBLKS, MAX_QUERY_LENGTH,
MAX_QUERY_ID, DATE_OF_STAT, DMY_OF_STAT, TIME_OF_STAT)
SELECT max(undoblks), max(maxquerylen), maxqueryid, sysdate, to_char(sysdate,'DD.MM.YYYY'), 
to_char(sysdate,'HH24:MI:SS') FROM SYS.V_$UNDOSTAT GROUP BY maxqueryid;
COMMIT;
exit;
EOF
exit 0

工作创建没有任何问题。已授予所有必要的权限。但是当我调试我的 shell 脚本时，我遇到了一些问题:

[oracle@parustest111 UNDOSTAT]$ bash -o xtrace accumulate_undostat_111.bsh            + export ORACLE_HOME=/u01/home/oracle/product/11.2.0/db_1
+ ORACLE_HOME=/u01/home/oracle/product/11.2.0/db_1
+ export ORACLE_SID=parustest
+ ORACLE_SID=parustest
+ export PATH=/u01/home/oracle/product/11.2.0/db_1/bin:/u01/home/oracle/product/11.2.0/db_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
+ PATH=/u01/home/oracle/product/11.2.0/db_1/bin:/u01/home/oracle/product/11.2.0/db_1/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin
+ sqlplus -s
to_char(sysdate,'HH24:MI:SS') FROM V_ GROUP BY maxqueryid
                                   *
ERROR at line 4:
ORA-00942: table or view does not exist


Elapsed: 00:00:00.00

Commit complete.

Elapsed: 00:00:00.00
+ exit 0
[oracle@parustest111 UNDOSTAT]$

有人可以解释并帮助我吗？谢谢!

Answer 1

在 unix shell 中，$ 是一个变量的开始，所以你的语句

FROM SYS.V_$UNDOSTAT 

由 shell 解释，以便 $UNDOSTAT 查找名为 UNDOSTAT 的 unix 变量。为防止这种情况，您必须转义语句

FROM SYS.V_\$UNDOSTAT 

例如:

$ cat foo.bash
#!/bin/bash

sqlplus /<<EOF
select count(*) from v$session;
EOF

$ ./foo.bash

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 28 12:56:43 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> select count(*) from v
                     *
ERROR at line 1:
ORA-00942: table or view does not exist

对比:

$ cat foo2.bash
#!/bin/bash

sqlplus /<<EOF
select count(*) from v\$session;
EOF

$ ./foo2.bash

SQL*Plus: Release 11.2.0.2.0 Production on Mon Jan 28 12:56:49 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL>
  COUNT(*)
----------
       184

更好的是，如果我是你，我会将 SQL 文件分开并直接调用它

sqlplus -s << EOF
/ as sysdba
@yoursql.sql
COMMIT;
exit;
EOF

yoursql.sql 文件中包含所有的 SQL。那时就不用担心转义了。