STATSPACK spcpkg.sql PLS-00103

ERP DB (8.1.7.3 -> 9.2.0.8)업그레이드로 인해 성능정보 수집을 위해 statspack install 시에 발생한 오류.

[ID 236068.1]

fact: Oracle Applications Release 11.5

fact: Oracle Server - Enterprise Edition 9.2

symptom: Error compiling Statspack

symptom: PLS-00103: Encountered the symbol "%s" when expecting one of the following "%s"

cause: Bug:2393958 STATSPACK PACKAGE DOES NOT COMPILE ON ORACLE*APPS/CRM INSTANCE

An Oracle Apps database typically has the following event set:
EVENT = "10933 trace name context level 512"
This event disables the common SQL-PL/SQL parser introduced in 9i. Per Bug:2393958 Oracle*Apps 11i version 11.5.7 does not use event 10933.

fix:
1. Connect to PERFSTAT schema using Sql*Plus
2. Execute: ALTER SESSION SET EVENTS '10933 trace name context off';
3. Recompile statspack package: @?/rdbms/admin/spcpkg
4. Once the package is recompiled, it can be executed with event 10933 set to level 512


Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Warning: Package Body created with compilation errors.

Errors for PACKAGE BODY STATSPACK:

LINE/COL ERROR
-------- -----------------------------------------------------------------
1468/18  PLS-00103: Encountered the symbol "ORDER" when expecting one of
         the following:
         . ( * @ % & - + ; / at mod rem return returning
         <an exponent (**)> and or group having intersect minus start
         union where connect ||

1471/13  PLS-00103: Encountered the symbol "L_INSERT_DONE"
1476/13  PLS-00103: Encountered the symbol "LOOP" when expecting one of
         the following:
         ;

LINE/COL ERROR
-------- -----------------------------------------------------------------
1634/14  PLS-00103: Encountered the symbol "ORDER" when expecting one of
         the following:
         . ( , * @ % & - + ; / at mod rem return returning
         <an exponent (**)> group having intersect minus start union
         where connect ||

1637/14  PLS-00103: Encountered the symbol "L_INSERT_DONE"
1644/13  PLS-00103: Encountered the symbol "LOOP" when expecting one of
         the following:
         ;

LINE/COL ERROR
-------- -----------------------------------------------------------------
1650/10  PLS-00103: Encountered the symbol "IF" when expecting one of the
         following:
         ; <an identifier> <a double-quoted delimited-identifier>
         delete exists prior <a single-quoted SQL string>

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

SQL> conn perfstat/xxxxxx
Connected.
SQL> ALTER SESSION SET EVENTS '10933 trace name context off';

Session altered.
Creating Package STATSPACK...

Package created.

No errors.
Creating Package Body STATSPACK...

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.