This discussion is archived
8 Replies Latest reply: Mar 20, 2013 10:29 AM by rp0428 RSS

alter user password with first character as numeric

Sivaprasad S Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Oracle ACE Director
    Currently Being Moderated
    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!!! Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    >
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points