8 Replies Latest reply: Mar 20, 2013 12:29 PM by rp0428 RSS

    alter user password with first character as numeric

    Sivaprasad S
      SQL> alter user user01 identified by 1arkspur
                                              *
      ERROR at line 1:
      ORA-00922: missing or invalid option
      Then I tried with double quotes;
      alter user sim01 identified by "1arkspur"
      The issue is password first character is numeric one "1".

      Question:
      ------------------------------------------------------------------------------------------------------------------------------------------------------

      How to use the double quotes on the SQL Script file for the password? The issue is password first character is numeric one "1".

      Example
      sqlplus -s << _proc 2>&1
         sys/password@TestDB as sysdba
         set lines 200
         set pages 60
      
         spool pwd_change.lst
      
         set heading off
         select 'Oracle Server and Curr Date: '|| instance_name||' '||'${CNTY_CD}'||' '
            ||to_char(sysdate, 'yyyymmdd_HH24MISS')||'.' from v\$instance;
      
      NW_USER01_PASSWD='1arkspur'
      NW_USER02_PASSWD='1arkspur'
      
         *alter user user01 identified by ${NW_USER01_PASSWD};*
         *alter user user02 identified by ${NW_USER02_PASSWD};*
      
      spool off;
         exit
      Edited by: Sivaprasad S on Mar 18, 2013 5:08 PM
        • 1. Re: alter user password with first character as numeric
          rp0428
          >
          The issue is password first character is numeric one "1".
          >
          No - the issue is that the first character can NOT be numeric unless you quote it.

          This works for me:
          SQL> alter user scott identified by "1tiger";
          
          User altered.
          
          SQL> alter user scott identified by tiger;
          
          User altered.
          
          SQL>
          So for this
          >
          How to use the double quotes on the SQL Script file for the password?
          >
          Add double quotes around the password.
          >
          NW_USER01_PASSWD='1arkspur'
          >
          Do you see any double quotes in there anywhere?
          • 2. Re: alter user password with first character as numeric
            Srini Chavali-Oracle
            Passwords starting with numeric characters MUST be enclosed in quotes - http://docs.oracle.com/cd/E11882_01/network.112/e16543/guidelines.htm#DBSEG10005

            This is a scripting issue, not an Oracle issue

            HTH
            Srini
            • 3. Re: alter user password with first character as numeric
              Pradap!!!
              SQL> alter user hr identified by "1hr";
              
              User altered.
              
              SQL> alter user hr identified by hr;
              
              User altered.
              
              SQL> 
              Regards,

              Pradap
              • 4. Re: alter user password with first character as numeric
                Sivaprasad S
                My question is on scripting how to use the double quote.

                I never state that it is a Oracle issue !
                • 5. Re: alter user password with first character as numeric
                  rp0428
                  >
                  My question is on scripting how to use the double quote.
                  >
                  As I ask above: do you see any double quotes in this?
                  NW_USER01_PASSWD='1arkspur'
                  If you want double quotes YOU have to include them.
                  • 6. Re: alter user password with first character as numeric
                    John Spencer
                    As others have already pointed out, the first thing you need to do is actually double quote the password, After that, you need to get the shell assignment to NW_USER01_PASSWD out of the sqlplus here document and into the shell script proper.
                    SQL> grant create session to a identified by a;
                    
                    Grant succeeded.
                    
                    SQL> exit
                    Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    
                    /home/oracle> cat t.sh
                    sqlplus  / << EOF
                    NW_USER01_PASSWD='1arkspur'
                    alter user a identified by "${NW_USER01_PASSWD}";
                    exit;
                    
                    /home/oracle> ./t.sh
                    
                    SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 19 13:57:43 2013
                    
                    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                    
                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    
                    SQL> SP2-0734: unknown command beginning "NW_USER01_..." - rest of line ignored.
                    SQL> alter user a identified by ""
                                               *
                    ERROR at line 1:
                    ORA-01741: illegal zero-length identifier
                    
                    SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    More like:
                    /home/oracle> cat t.sh
                    NW_USER01_PASSWD='1arkspur'
                    sqlplus  / << EOF
                    alter user a identified by "${NW_USER01_PASSWD}";
                    exit;
                    
                    /home/oracle> ./t.sh
                    
                    SQL*Plus: Release 11.2.0.3.0 Production on Tue Mar 19 13:59:22 2013
                    
                    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                    
                    
                    Connected to:
                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    
                    SQL>
                    User altered.
                    
                    SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                    John
                    • 7. Re: alter user password with first character as numeric
                      Sivaprasad S
                      John,

                      Thanks for answering it.

                      I am new to Oracle, I would be glad if you can explain difference between the two snippet of code you have given.
                      • 8. Re: alter user password with first character as numeric
                        rp0428
                        >
                        I am new to Oracle, I would be glad if you can explain difference between the two snippet of code you have given.
                        >
                        The difference is that you used this:
                        alter user user01 identified by ${NW_USER01_PASSWD};
                        Do you see any double quotes in there anywhere? I don't.

                        John used this:
                        alter user a identified by "${NW_USER01_PASSWD}";
                        Do you see any double quotes in there anywhere? I do.