7 Replies Latest reply on Feb 26, 2008 5:09 PM by emersokl

    Help with alter syntax

    emersokl
      Could anyone point out to me what is wrong with the following syntax:

      sql> alter system set '_INDEX_JOIN_ENABLED'=FALSE scope=spfile;


      It errors after with 'MISSING OR INVALID OPTION'

      Thanks, Kathie
        • 1. Re: Help with alter syntax
          591186
          Use double quotes "_INDEX_JOIN_ENABLED".
          • 2. Re: Help with alter syntax
            emersokl
            Thanks!
            • 3. Re: Help with alter syntax
              EdStevens
              If you check the SQL Reference Manual, you'll see that, in general, there are no quotes on the ALTER SYSTEM SET .... syntax.

              And just as an aside, I hope you have a VERY good reason for setting an 'underbar' initialization parameter. Generally, these are only modified at the direction of Oracle Support in response to a specific problem worked in a Service Request.
              • 4. Re: Help with alter syntax
                stevencallan
                For what it's worth with respect to undocumented parameters (mileage may vary based on the platform, this is from Windows):
                SQL> @db10
                Connected.
                SQL> alter system set "_index_join_enabled"=false;

                System altered.

                SQL> alter system set indexjoin_enabled=false;
                alter system set indexjoin_enabled=false
                                 *
                ERROR at line 1:
                ORA-00911: invalid character


                SQL> alter system set '_index_join_enabled'=false;
                alter system set '_index_join_enabled'=false
                                 *
                ERROR at line 1:
                ORA-00922: missing or invalid option
                • 5. Re: Help with alter syntax
                  EdStevens
                  I stand corrected. I was just going by the syntax on 'normal' parameters, as shown in the ref. manual.
                  • 6. Re: Help with alter syntax
                    stevencallan
                    No, you are correct. I was only mentioning the occasional platform differences that arise when using some symbols/syntax. With "normal" things, the general case is true.
                    SQL> alter system set undo_retention=901;

                    System altered.

                    SQL> alter system set "undo_retention"=900;

                    System altered.
                    • 7. Re: Help with alter syntax
                      emersokl
                      Thanks to everyone!

                      I did contact Oracle. The parameters I changed change the cbo to act like 9i instead of the 10g. They sometimes recommend Peoplesoft (PS) database change to 9i because it performs better with PS sql (very complex sql). We have several PS databases but none in prodcution yet.

                      For this box, the bad performing sql was not fixed by changing the parameters so I put it back - worth a try.

                      Again, thanks for the help!
                      Kathie