Correct Password Gives ORA-01017

This post replicates a real-life situation where Oracle returned an ORA-01017 when the correct password was used. First I created a user in an Oracle 11 database and checked that I could connect to it:

Oracle 11: sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 10:55:39 2019

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

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

SQL> create user blah identified by secret_password
  2  /

User created.

SQL> grant create session to blah
  2  /

Grant succeeded.

SQL> conn blah/secret_password
Connected.
SQL>

Then I connected to that user from a server which only had Oracle 9 software installed:

Oracle 9: sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:02:32 2019

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

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

SQL>

The user had a hashed password created using the pre Oracle 11 routine:

Oracle 11: sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 11:05:08 2019

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

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

SQL> select password from sys.user$ where name = 'BLAH'
  2  /

PASSWORD
------------------------------
92D633C444E0CD1A

SQL>

It also had a hashed password created using the Oracle 11 routine:

SQL> select spare4 from sys.user$ where name = 'BLAH'
  2  /

SPARE4
--------------------------------------------------------------------------------
S:A19249C8E39996F37B62E3DD40A49EC9ADF171BD5B4036462304E353E384

SQL>

In the real-life situation, the user's password expired because the PASSWORD_LIFE_TIME was set to 180 in the DEFAULT profile. For the purposes of this test I expired it manually:

SQL> alter user blah password expire
  2  /

User altered.

SQL> select account_status from dba_users
  2  where username = 'BLAH'
  3  /

ACCOUNT_STATUS
--------------------------------
EXPIRED

SQL>

In the real-life situation I did not know the user's password so I reinstated the Oracle 11 hash to preserve the password's case sensitivity:

SQL> alter user blah identified by values
  2  'S:A19249C8E39996F37B62E3DD40A49EC9ADF171BD5B4036462304E353E384'
  3  /

User altered.

SQL>

This set the ACCOUNT_STATUS to OPEN:

SQL> select account_status from dba_users
  2  where username = 'BLAH'
  3  /

ACCOUNT_STATUS
--------------------------------
OPEN

SQL>

It did not occur to me at the time but this also removed the Oracle 10 hash presumably because Oracle had no way of knowing if it was still correct:

SQL> select nvl(password,'NULL') from sys.user$
  2  where name = 'BLAH'
  3  /

NVL(PASSWORD,'NULL')
------------------------------
NULL

SQL>

I spoke to the developer concerned. He confirmed that he knew the user's password so I told him he could login again. A couple of hours later he told me that he could login to the user using SQL*Plus:

C:\>sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 23 11:42:58 2019

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

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

SQL>

...but he could not connect using JDBC. I am not in a position to replicate that now but I can show the same problem by trying to login from the server with the Oracle 9 software:

Oracle 9: sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:45:18 2019

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

I guess this was sending a hash of the password created using the pre Oracle 11 routine.
The Oracle 11 database had nothing to compare this with so it returned an ORA-01017. The developer told me the password and I reset it. This reinstated the pre Oracle 11 hash of the password:

Oracle 11: sqlplus /

SQL*Plus: Release 11.1.0.6.0 - Production on Wed Jan 23 11:49:12 2019

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

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

SQL> alter user blah identified by secret_password
  2  /

User altered.

SQL> select password from sys.user$ where name = 'BLAH'
  2  /

PASSWORD
------------------------------
92D633C444E0CD1A

SQL> select spare4 from sys.user$ where name = 'BLAH'
  2  /

SPARE4
--------------------------------------------------------------------------------
S:784AAC15DBA436AA97653EE6C3868F7B87B434793F2BD69DCEDF55C2EDDB

SQL>

This allowed me to login again from the server with the Oracle 9 software:

Oracle 9: sqlplus blah/secret_password@flwdpt1

SQL*Plus: Release 9.2.0.7.0 - Production on Wed Jan 23 11:54:01 2019

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

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

SQL>

...and the developer was able to set up his JDBC connection.

db_writer_processes

I wrote the first part of this example in 2012.

 

The database writer copies data blocks from the buffer cache onto disk. The db_writer_processes initialization parameter determines how many processes will do this task. Its default value is 1 or cpu_count / 8, whichever is greater. I found an Oracle 9 database on a Tru64 server with cpu_count set to 1:

 

SQL> l

  1  select value from v$parameter

  2* where name = 'cpu_count'

SQL> /

 

VALUE

------------------------------

1

 

SQL>

 

The database used the default value for db_writer_processes, which Oracle had calculated as 1:

 

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'db_writer_processes'

  4  /

 

VALUE                          ISDEFAULT

------------------------------ ---------

1                              TRUE

 

SQL>

 

(However, since I first wrote this, I have found documentation suggesting that db_writer_processes always defaulted to 1 in Oracle 9 and took no notice of cpu_count.)

 

I looked for this process in the operating system as follows (I had to squash the ps output a bit to make it fit the screen):

 

UNIX > ps -ef|grep dbw|grep TEST9

oracle 250424 1 0.0 02:07:18 ?? 0:00.87 ora_dbw0_TEST9

UNIX >

 

I found an Oracle 11 database on a Solaris server with cpu_count set to 16:

 

SQL> select value from v$parameter

  2  where name = 'cpu_count'

  3  /

 

VALUE

------------------------------

16

 

SQL>

 

It also used the default value for db_writer_processes, which Oracle had calculated as 2:

 

SQL> select value, isdefault

  2  from v$parameter

  3  where name = 'db_writer_processes'

  4  /

 

VALUE                          ISDEFAULT

------------------------------ ---------

2                              TRUE

 

SQL>

 

When I found these processes in the operating system, I saw that Oracle had given them consecutive numbers i.e. dbw0 and dbw1 (the ps output was squashed again to fit the screen):

 

UNIX > ps -ef|grep dbw|grep PROD11

oracle 12230 1 0 19:42:36 ? 1:24 ora_dbw0_PROD11

oracle 12232 1 0 19:42:36 ? 1:21 ora_dbw1_PROD11

UNIX >

 

If you don’t have enough database writer processes, you can apparently have problems with free buffer waits. I searched all our production databases and could only find two with any of these at all. This was one of them:

 

  1  select time_waited from v$system_event

  2* where event = 'free buffer waits'

SQL> /

 

TIME_WAITED

-----------

71

 

SQL>

 

As this figure is given in hundredths of a second, I decided to do nothing about it.

 

I created an Oracle 11.2.0.4 database in 2015 and set db_writer_processes to 36:

 

SQL> alter system set db_writer_processes = 36

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database (this is not shown). When I looked for the database writer processes in the operating system, I saw that Oracle had called them dbw0 through dbw9 then dbwa through dbwz:

 

UNIX > ps -ef|grep dbw|grep BECHEDV1

oracle 50594 50565 0 17:27:03 ? 0:00 ora_dbwh_BECHEDV1

oracle 50583 50565 0 17:27:02 ? 0:00 ora_dbw6_BECHEDV1

oracle 50578 50565 0 17:27:02 ? 0:00 ora_dbw1_BECHEDV1

oracle 50611 50565 0 17:27:03 ? 0:00 ora_dbww_BECHEDV1

oracle 50589 50565 0 17:27:02 ? 0:00 ora_dbwc_BECHEDV1

oracle 50593 50565 0 17:27:03 ? 0:00 ora_dbwg_BECHEDV1

oracle 50600 50565 0 17:27:03 ? 0:00 ora_dbwn_BECHEDV1

oracle 50615 50565 0 17:27:03 ? 0:00 ora_dbwz_BECHEDV1

oracle 50612 50565 0 17:27:03 ? 0:00 ora_dbwx_BECHEDV1

oracle 50582 50565 0 17:27:02 ? 0:00 ora_dbw5_BECHEDV1

oracle 50580 50565 0 17:27:02 ? 0:00 ora_dbw3_BECHEDV1

oracle 50608 50565 0 17:27:03 ? 0:00 ora_dbwt_BECHEDV1

oracle 50588 50565 0 17:27:02 ? 0:00 ora_dbwb_BECHEDV1

oracle 50610 50565 0 17:27:03 ? 0:00 ora_dbwv_BECHEDV1

oracle 50579 50565 0 17:27:02 ? 0:00 ora_dbw2_BECHEDV1

oracle 50602 50565 0 17:27:03 ? 0:00 ora_dbwp_BECHEDV1

oracle 50595 50565 0 17:27:03 ? 0:00 ora_dbwi_BECHEDV1

oracle 50614 50565 0 17:27:03 ? 0:00 ora_dbwy_BECHEDV1

oracle 50598 50565 0 17:27:03 ? 0:00 ora_dbwl_BECHEDV1

oracle 50609 50565 0 17:27:03 ? 0:00 ora_dbwu_BECHEDV1

oracle 50590 50565 0 17:27:03 ? 0:00 ora_dbwd_BECHEDV1

oracle 50581 50565 0 17:27:02 ? 0:00 ora_dbw4_BECHEDV1

oracle 50586 50565 0 17:27:02 ? 0:00 ora_dbw9_BECHEDV1

oracle 50596 50565 0 17:27:03 ? 0:00 ora_dbwj_BECHEDV1

oracle 50584 50565 0 17:27:02 ? 0:00 ora_dbw7_BECHEDV1

oracle 50599 50565 0 17:27:03 ? 0:00 ora_dbwm_BECHEDV1

oracle 50604 50565 0 17:27:03 ? 0:00 ora_dbwr_BECHEDV1

oracle 50587 50565 0 17:27:02 ? 0:00 ora_dbwa_BECHEDV1

oracle 50585 50565 0 17:27:02 ? 0:00 ora_dbw8_BECHEDV1

oracle 50591 50565 0 17:27:03 ? 0:00 ora_dbwe_BECHEDV1

oracle 50603 50565 0 17:27:03 ? 0:00 ora_dbwq_BECHEDV1

oracle 50601 50565 0 17:27:03 ? 0:00 ora_dbwo_BECHEDV1

oracle 50606 50565 0 17:27:03 ? 0:00 ora_dbws_BECHEDV1

oracle 50597 50565 0 17:27:03 ? 0:00 ora_dbwk_BECHEDV1

oracle 50592 50565 0 17:27:03 ? 0:00 ora_dbwf_BECHEDV1

oracle 50577 50565 0 17:27:02 ? 0:00 ora_dbw0_BECHEDV1

UNIX >

 

I read somewhere that the maximum value for db_writer_processes is 36 but Oracle allowed me to change it to 37:

 

SQL> alter system set db_writer_processes = 37

  2  scope = spfile

  3  /

 

System altered.

 

SQL>

 

Then I bounced the database again:

 

SQL> shutdown

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area  521936896 bytes

Fixed Size                  2252448 bytes

Variable Size             306184544 bytes

Database Buffers          205520896 bytes

Redo Buffers                7979008 bytes

Database mounted.

Database opened.

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

UNIX >

 

However, when I checked in the operating system, there were still only 36 database writer processes:

 

UNIX >  ps -ef|grep dbw|grep BECHEDV1|wc -l

      36

UNIX >

 

… and there was a message in the alert log telling me that db_writer_processes had been adjusted:

 

NOTE: db_writer_processes has been changed from 37 to  36ue to NUMA requirements.

OPTIMIZER_MODE = FIRST_ROWS_N

I have known about the first_rows optimizer mode for some time. This tells Oracle to use an execution path which will return the first few rows as quickly as possible. However, I recently read about the first_rows_n optimizer mode, which apparently appeared first in Oracle 9. This tells Oracle to use an execution path which will return the first n rows quickly, where n can be 1, 10, 100 or 1000. I decided to try it out in an Oracle 11.2 database. First I checked that Oracle would accept the expected values of n:

SQL> alter session set optimizer_mode = first_rows

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_1

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_10

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_100

  2  /

 

Session altered.

 

SQL>

…then, before checking the final option, I started to trace my SQL*Plus session:

SQL> alter session set sql_trace = true

  2  /

 

Session altered.

 

SQL> alter session set optimizer_mode = first_rows_1000

  2  /

 

Session altered.

 

SQL> select sysdate "first_rows_1000" from dual

  2  /

 

first_rows_1000

---------------

29-SEP-14

 

SQL> alter session set sql_trace = false

  2  /

 

Session altered.

 

SQL>

I ran the trace file through tkprof and looked at the explain plan for the query I had just run. It did not seem to be aware exactly which optimizer mode I had used: 

********************************************************************************

 

SQL ID: 9u1zkyyn9vbt4

Plan Hash: 1546270724

select sysdate "first_rows_1000"

from

dual

 

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        2      0.01       0.00          0          0          0           0

Execute      2      0.00       0.00          0          0          0           0

Fetch        4      0.00       0.00          0          0          0           2

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        8      0.01       0.00          0          0          0           2

 

Misses in library cache during parse: 1

Optimizer mode: FIRST_ROWS

Parsing user id: 5  (SYSTEM)

 

Rows     Row Source Operation

-------  ---------------------------------------------------

      1  FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)

 

 

Rows     Execution Plan

-------  ---------------------------------------------------

      0  SELECT STATEMENT   MODE: FIRST_ROWS

      1   FAST DUAL

 

********************************************************************************

 

Finally, I tried to use an invalid value for n. This time the error message explained exactly where I had gone wrong: 

SQL> alter session set optimizer_mode = first_rows_99

  2  /

ERROR:

ORA-00096: invalid value FIRST_ROWS_99 for parameter

optimizer_mode, must be from among first_rows_1000,

first_rows_100, first_rows_10, first_rows_1,

first_rows, all_rows, choose, rule

SQL>

ORA-01460 on PL/SQL Developer

An Oracle 9 database had the following character set:

 

SQL> l

  1  select sys_context('userenv','LANGUAGE')

  2* from dual

SQL> /

 

SYS_CONTEXT('USERENV','LANGUAGE')

----------------------------------------

ENGLISH_UNITED KINGDOM.WE8ISO8859P15

 

SQL>

 

I used PL/SQL Developer to look at the code in the database (as usual, click on the image to enlarge it and bring it into focus):

 

Somebody typed the following command in the database by mistake:

 

SQL> l

  1* create database sonar character set utf8

SQL> /

create database sonar character set utf8

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

I logged in to PL/SQL Developer again and found that I got an ORA-01460 when I tried to look at the same code in the database:

I also noticed that the database had a new character set:

 

SQL> l

  1  select sys_context('userenv','LANGUAGE')

  2* from dual

SQL> /

 

SYS_CONTEXT('USERENV','LANGUAGE')

----------------------------------------

ENGLISH_UNITED KINGDOM.UTF8

 

SQL>

I guess this is a bug as the user account which typed the create database command had no special privileges and Oracle replied with an ORA-01031. Therefore no damage should have been caused.

Three days later:

I logged in as a privileged user and tried to change the character set back but this failed, in line with the Oracle documentation:

SQL> conn / as sysdba

Connected.

SQL> alter database character set we8iso8859p15

  2  /

alter database character set we8iso8859p15

*

ERROR at line 1:

ORA-12712: new character set must be a superset of old character set

 

SQL>

 

So, as a last resort, I logged back in with an ordinary user and tried to reverse the damage like this:

 

SQL> conn andrew/reid

Connected.

SQL> create database sonar character set we8iso8859p15

  2  /

create database sonar character set we8iso8859p15

*

ERROR at line 1:

ORA-01031: insufficient privileges

 

SQL>

 

This seemed to have the desired effect, at least in the short term as the character set went back to its original value:

 

SQL> select sys_context('userenv','LANGUAGE')

  2  from dual

  3  /

 

SYS_CONTEXT('USERENV','LANGUAGE')

----------------------------------------

AMERICAN_AMERICA.WE8ISO8859P15

 

SQL>

 

I will report back as the problem develops…

GROUP BY and ORDER BY

In 1997, I was sent on a 1 day Introduction to Oracle course. The lecturer said that if you used a GROUP BY, you could not assume that the results would be returned in order. If you wanted to make sure, you should include an ORDER BY as well. From a theoretical point of view, he was correct as an RDBMS is not obliged to return results in any particular order unless you tell it to do so. But from a practical point of view, a simple way to group data is to sort it first. Up to and including version 9, Oracle seems to have used this method:

 

TEST9 > sqlplus /

 

SQL*Plus: Release 9.2.0.7.0 - Production on Fri Sep 2 18:04:16 2011

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.7.0 - Production

 

SQL> @group_by

SQL> set pages 100

SQL> col first_letter format a12

SQL> select substr(segment_name,1,1) first_letter,

  2  count(*)

  3  from dba_segments

  4  group by substr(segment_name,1,1)

  5  /

 

FIRST_LETTER   COUNT(*)

------------ ----------

1                     1

A                   118

B                    92

C                    98

D                    43

E                    17

F                    51

G                    22

H                    39

I                   293

J                     5

K                     7

L                   115

M                   116

N                     2

O                    29

P                    52

Q                    49

R                   170

S                   319

T                   121

U                    54

V                    16

W                    11

X                    14

_                   119

 

26 rows selected.

 

SQL>

 

This is no longer the case and, starting with Oracle 10, if you want to see the output of a GROUP BY in order, you have to include an ORDER BY as well:

 

TEST10 > sqlplus /

 

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Sep 2 18:15:01 2011

 

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

 

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

 

SQL> @group_by

SQL> set pages 100

SQL> col first_letter format a12

SQL> select substr(segment_name,1,1) first_letter,

  2  count(*)

  3  from dba_segments

  4  group by substr(segment_name,1,1)

  5  /

 

FIRST_LETTER   COUNT(*)

------------ ----------

P                   161

O                    37

V                    83

D                   157

_                    68

M                   448

N                    98

W                   779

Q                    40

Y                     3

C                   135

I                   550

B                   120

F                    55

U                    20

S                   717

T                   250

A                   316

J                    21

E                   143

L                   178

R                   303

H                   662

K                     9

b                     1

G                     7

X                    23

 

27 rows selected.

 

SQL> select substr(segment_name,1,1) first_letter,

  2  count(*)

  3  from dba_segments

  4  group by substr(segment_name,1,1)

  5  order by 1

  6  /

 

FIRST_LETTER   COUNT(*)

------------ ----------

A                   316

B                   120

C                   135

D                   157

E                   143

F                    55

G                     7

H                   662

I                   550

J                    21

K                     9

L                   178

M                   448

N                    98

O                    37

P                   161

Q                    40

R                   303

S                   717

T                   250

U                    20

V                    83

W                   779

X                    23

Y                     3

_                    68

b                     1

 

27 rows selected.

 

SQL>