Oracle to Postgres Migration Issue with Commit

If you have some PL/SQL, Oracle allows you to include commit statements between a begin and end. Assuming this is appropriate for your application, it can produce two benefits:

(1) If the code runs for a long time, it allows you to monitor progress by running select statements from a separate session.

(2) If the code fails, and you have written it in such a way that it can be restarted, you can fix the problem and start from where you left off.

I am working on a small project at home and decided to write it in PostgreSQL, which I am trying to learn about currently. However, I found out that PostgreSQL does not allow you to include commit statements between a begin and end. You can see what I mean in the example below:

andrew=# do
andrew-# $$
andrew$# declare
andrew$#  a numeric;
andrew$# begin
andrew$#  for a in 1..1000 loop
andrew$#   insert into tab1(col1) values(a);
andrew$#   commit;
andrew$#  end loop;
andrew$# end
andrew$# $$;
ERROR:  cannot begin/end transactions in PL/pgSQL
HINT:  Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT:  PL/pgSQL function inline_code_block line 7 at SQL statement
andrew=#

I looked on some online forums and found that several other people had experienced the same problem. Unfortunately I did not understand the suggested workarounds. That's not too much of an issue for me as my project only contains around 50 lines of SQL shared among three scripts. I can quickly move it to Oracle 19 Express Edition and revisit PostgreSQL at a later date.

However, if you are working on a site which is thinking about moving from Oracle to PostgreSQL, it is something you are going to need to understand beforehand.

Minor Problem with Resumable Session

Oracle started allowing sessions with space issues to hang rather than fail in version 9. I had always assumed this only worked if a tablespace ran out of space. However, I read recently that it also works for quota failures so I decided to try this out in an Oracle 19.3 database. First I created a user without any tablespace quota:

SQL> conn / as sysdba

Connected. 

SQL> alter session set container = orclpdb 

  2  /

Session altered.

SQL> create user andrew identified by reid
  2  default tablespace users
  3  /

User created.

SQL> grant create session, create table,
  2  resumable to andrew
  3  /

Grant succeeded.

SQL>

Then I connected as this user and tried to create a table in a resumable session. Instead of hanging, it failed with an ORA-01950:

SQL> conn andrew/reid@orclpdb
Connected.
SQL> alter session enable resumable timeout 3600
  2  /

Session altered.

SQL> create table tab1
  2  (col1 varchar2(1))
  3  segment creation immediate
  4  storage (initial 8k next 8k minextents 2)
  5  /
create table tab1
*
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

SQL>

...and there was a corresponding message in the alert log:

2021-10-15T18:58:30.541244+01:00
ORCLPDB(3):statement in resumable session 'User ANDREW(125), Session 136, Instance 1' was aborted
2021-10-15T19:09:39.525424+01:00

I gave the user a quota of zero:

SQL> conn / as sysdba
Connected.
SQL> alter session set container = orclpdb
  2  /

Session altered.

SQL> alter user andrew quota 0 on users
  2  /

User altered.

SQL>

Then I ran the test again. This time it halted instead of failing:

SQL> conn andrew/reid@orclpdb
Connected.
SQL> alter session enable resumable timeout 3600
  2  /

Session altered.

SQL> create table tab1
  2  (col1 varchar2(1))
  3  segment creation immediate
  4  storage (initial 8k next 8k minextents 2)
  5  /

...and there was a different message in the alert log:

2021-10-15T19:19:51.593060+01:00
ORCLPDB(3):statement in resumable session 'User ANDREW(126), Session 147, Instance 1' was suspended due to
ORCLPDB(3):    ORA-01536: space quota exceeded for tablespace 'USERS'

In a separate session in blue, I gave the user unlimited quota:

C:\Andrew>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Oct 15 19:27:43 2021
Version 19.3.0.0.0

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


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container = orclpdb
  2  /

Session altered.

SQL> alter user andrew quota unlimited on users
  2  /

User altered.

SQL>

There was a confirmation message in the alert log:

2021-10-15T19:29:04.461911+01:00
ORCLPDB(3):statement in resumable session 'User ANDREW(126), Session 147, Instance 1' was resumed

...and the table was created moments later:

SQL> create table tab1
  2  (col1 varchar2(1))
  3  segment creation immediate
  4  storage (initial 8k next 8k minextents 2)
  5  /

Table created.

SQL> 

How Auditors Might Be Cracking Your Oracle Passwords

I have often wondered how auditors crack Oracle passwords, particularly if the user concerned has a profile which locks the account after a few unsuccessful login attempts. I just realised today how they might be doing it. First I created a user in an Oracle 11.1.0.6 database and for the purposes of this post I am going to say that this is my database which is being audited. The first thing I noticed is that if you change the password back to its original value, the value in the SPARE4 column changes each time. This has nothing to do with the title above, I just thought it was interesting:

An imaginary auditor then asked me to provide a list showing the values in the NAME and SPARE4 columns in SYS.USER$. The auditor took this list and decided to try to crack the password for user ANDREW. He didn’t have an Oracle 11.1.0.6 database at his base so he used an Oracle 11.2.0.4 database instead and created a user with the SPARE4 value I had provided:

…then he just tried values at random until he found the right one:

ORA-14306 and ORA-14400

Oracle apparently introduced list partitioning in Oracle 9. I decided to try it out in an Oracle 11 database.

In this simple example I created a table to store the names of towns in Buckinghamshire, together with the people living in each one:

SQL> create table buckinghamshire_people
  2  (town varchar2(30),
  3   first_name varchar2(30),
  4   last_name varchar2(30))
  5  partition by list (town)
  6  (partition town1 values ('AYLESBURY'),
  7   partition town2 values ('AMERSHAM'))
  8  /

Table created.

SQL>

I inserted a valid row into the table:

SQL> insert into buckinghamshire_people
  2  (town, first_name, last_name)
  3  values
  4  ('AYLESBURY', 'FRED', 'BLOGGS')
  5  /

1 row created.

SQL>

...then I inserted a row for somebody from Slough. It used to be in Buckinghamshire but is now in Berkshire so the table did not have a partition for it. This failed with an ORA-14400:

SQL> insert into buckinghamshire_people
  2  (town, first_name, last_name)
  3  values
  4  ('SLOUGH', 'JOHN', 'SMITH')
  5  /
insert into buckinghamshire_people
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any
partition
 
SQL>

I tried to recreate the table with three partitions but the first and third stored rows for the same town. Oracle would not have known where to put rows for people from Aylesbury so it returned an ORA-14306:

SQL> drop table buckinghamshire_people
  2  /

Table dropped.

SQL> create table buckinghamshire_people
  2  (town varchar2(30),
  3   first_name varchar2(30),
  4   last_name varchar2(30))
  5  partition by list (town)
  6  (partition town1 values ('AYLESBURY'),
  7   partition town2 values ('AMERSHAM'),
  8   partition town3 values ('AYLESBURY'))
  9  /
create table buckinghamshire_people
*
ERROR at line 1:
ORA-14306: List value ''AYLESBURY'' specified twice in
partitions 'TOWN1', 'TOWN3'

SQL>

Online Rebuild of Bitmap Indexes

I was reading an old Oracle 9 performance tuning book (as you do) and it said that you could not do online rebuilds of bitmap indexes.
I did not have access to an Oracle 9 database so I tried it out in an Oracle 10 one instead and it worked:

SQL> create table tab1
  2  (col1 number)
  3  /

Table created.

SQL> create bitmap index ind1 on tab1(col1)
  2  /

Index created.

SQL> alter index ind1 rebuild online
  2  /

Index altered.

SQL>

I then remembered that we had the Oracle 11.2 version of the same book by the same author in the cupboard.
It still claimed that you could not do online rebuilds of bitmap indexes.
Remember, if you read something in a book, be sure to check it yourself.

How to Calculate pi

You can use the following series to calculate pi:

4/1 - 4/3 + 4/5 - 4/7 + 4/9 - 4/11 + ...

I decided to try this out using PL/SQL. The example I created is shown below. It works OK but the series converges very slowly so you have to work out several million terms just to get pi accurate to 6 decimal places:

SQL> set timing on
SQL> declare
  2  pi          number  := 0;
  3  numerator   number  := 4;
  4  denominator number  := 1;
  5  dp          number  := 0;
  6  pi1         number;
  7  pi2         number;
  8  counter     number  := 0;
  9  finished    boolean := false;
 10  begin
 11  while not finished
 12  loop
 13   pi          := pi + (numerator / denominator);
 14   pi1         := round (pi,dp);
 15   denominator := denominator + 2;
 16   pi          := pi - (numerator / denominator);
 17   pi2         := round (pi,dp);
 18   denominator := denominator + 2;
 19   counter     := counter + 1;
 20   if pi1 = pi2 then
 21    dbms_output.put_line ('Counter = '||counter);
 22    if dp = 1 then
 23     dbms_output.put_line
 24     ('Accurate to 1 decimal place:');
 25    else
 26     dbms_output.put_line
 27     ('Accurate to '||dp||' decimal places:');
 28    end if;
 29    dbms_output.put_line ('Pi = '||pi1);
 30    dbms_output.put_line ('**********');
 31    dp := dp + 1;
 32    if dp > 6 then
 33     finished := true;
 34    end if;
 35   end if;
 36  end loop;
 37  end;
 38  /
Counter = 2
Accurate to 0 decimal places:
Pi = 3
**********
Counter = 60
Accurate to 1 decimal place:
Pi = 3.1
**********
Counter = 148
Accurate to 2 decimal places:
Pi = 3.14
**********
Counter = 5397
Accurate to 3 decimal places:
Pi = 3.142
**********
Counter = 11723
Accurate to 4 decimal places:
Pi = 3.1416
**********
Counter = 213092
Accurate to 5 decimal places:
Pi = 3.14159
**********
Counter = 3255425
Accurate to 6 decimal places:
Pi = 3.141593
**********

PL/SQL procedure successfully completed.

Elapsed: 00:00:34.53
SQL>

ORA-00439

I created a tablespace in an Oracle 12 Enterprise Edition database.

I should have used ASM but I put its datafile in ordinary disk space by mistake:

SQL> col banner format a47
SQL> l
  1  select banner from v$version
  2* where banner like 'Oracle Database%'
SQL> /

BANNER
-----------------------------------------------
Oracle Database 12c Enterprise Edition Release
12.2.0.1.0 - 64bit Production

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'ANDREW'
  3  /

FILE_NAME
--------------------------------------------------
/home/oracle/andrew/datafile1.dbf

SQL>

I moved the datafile to ASM as follows:

SQL> alter database move datafile
  2  '/home/oracle/andrew/datafile1.dbf'
  3  to '+DATA'
  4  /

Database altered.

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'ANDREW'
  3  /

FILE_NAME
--------------------------------------------------
+DATA/NLPEST1_LHR1S2/7976D62027536974E0530600000A7
C7B/DATAFILE/andrew.301.1036667943

SQL>

I tried the same thing in an Oracle 12 Standard Edition database on a different server but Oracle returned an ORA-00439:

SQL> col banner format a45
SQL> l
  1  select banner from v$version
  2* where banner like 'Oracle Database%'
SQL> /

BANNER
---------------------------------------------
Oracle Database 12c Standard Edition Release
12.2.0.1.0 - 64bit Production

SQL> select file_name from dba_data_files
  2  where tablespace_name = 'ANDREWS_TABLESPACE'
  3  /

FILE_NAME
----------------------------------------------
/home/oracle/andrew/andrews_tablespace.dbf

SQL> alter database move datafile
  2  '/home/oracle/andrew/andrews_tablespace.dbf'
  3  to '+DATA'
  4  /
alter database move datafile
*
ERROR at line 1:
ORA-00439: feature not enabled: online move datafile

SQL>

ORA-12954

I went to Techfest 2019 in Brighton. While I was there I saw a presentation by somebody from Pythian. He said that Oracle Database Express Edition is now very similar to Enterprise Edition, it just has certain resource limits applied. This makes it ideal for testing out new features. One of the resource limits is on database size, which is limited to 12 gigabytes. I wondered what would happen if I tried to exceed this. 

I checked the name of the datafile for the USERS tablespace:

SQL> l
  1  select file_name from dba_data_files
  2* where tablespace_name = 'USERS'
SQL> /

FILE_NAME
---------------------------------------------------
C:\APP\ADMIN\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF

SQL>

I checked its size:

SQL> select bytes from dba_data_files
  2  where tablespace_name = 'USERS'
  3  /

     BYTES
----------
   5242880

SQL>

...then I tried to resize it:

SQL> l
  1  alter database datafile
  2  'C:\APP\ADMIN\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF'
  3* resize 10g
SQL> /

Database altered.

SQL>

As soon as it looked like the total amount of data would exceed 12 gigabytes, Oracle returned an ORA-12954:

SQL> l
  1  alter database datafile
  2  'C:\APP\ADMIN\PRODUCT\18.0.0\ORADATA\XE\USERS01.DBF'
  3* resize 11g
SQL> /
alter database datafile
*
ERROR at line 1:
ORA-12954: The request exceeds the maximum allowed database size of 12 GB.

SQL>

It is not obvious how this calculation was done but I assume some allowance was made for data in the SYSAUX, UNDOTBS1 or TEMP tablespaces. 

How to Create a 3 by 3 Puzzle Using Oracle Database Express Edition

In 2012 I wrote a C program to create sudoku puzzles. I took a break from writing about Oracle to set up a blog for these puzzles. You can see it here. I also created a group on Facebook called Sudoku Puzzles. At the time of writing this post it has over 10,000 members.

I recently saw a new kind of puzzle in a UK newspaper. The UK agents for this puzzle are Puzzler and their offices are only a few hundred yards from my own place of work. I paid them a visit one lunchtime and they told me that the name used for these puzzles is copyright so I cannot use it here. However, they said that there is nothing to stop me creating my own puzzles as long as I call them something different. You can see the first one I made here. I used Oracle 18 Express Edition to create it. First I ran the PL/SQL below:

SQL> create table nine_digit_numbers
  2  (col1 varchar2(9))
  3  /

Table created.

SQL> declare
  2   valid_switch boolean;
  3  begin
  4  for a in 123456789..987654321 loop
  5   valid_switch := true;
  6   for b in 2..9 loop
  7    for c in 1..b-1 loop
  8     if substr(a,c,1) = substr(a,b,1) then
  9      valid_switch := false;
 10     end if;
 11    end loop;
 12   end loop;
 13   for d in 1..9 loop
 14    if substr(a,d,1) = 0 then
 15     valid_switch := false;
 16    end if;
 17   end loop;
 18   if valid_switch then
 19    insert into nine_digit_numbers(col1) values(a);
 20    commit;
 21   end if;
 22  end loop;
 23  end;
 24  /

PL/SQL procedure successfully completed.

SQL>

It ran for a couple of days on my home PC. I'm well aware that it is not efficient and at some point I hope to use it as the basis for a post entitled Your Poor Performance Could Well Be In Your Application or words to that effect. Eventually it produced a table of all possible numbers containing each of the digits 1 through 9 exactly once. There should be 9 factorial in all. I checked the first few values:

SQL> l
  1  select * from
  2  (select col1 from nine_digit_numbers
  3   order by 1 asc)
  4* where rownum <= 5
SQL> /

COL1
---------
123456789
123456798
123456879
123456897
123456978


SQL>

I checked the last few values:

SQL> l
  1  select * from
  2  (select col1 from nine_digit_numbers
  3   order by 1 desc)
  4* where rownum <= 5
SQL> /

COL1
---------
987654321
987654312
987654231
987654213
987654132


SQL>

...and I checked the number of rows:

SQL> l
  1* select count(*) from nine_digit_numbers
SQL> /

  COUNT(*)
----------
    362880


SQL>

I chose a solution at random (719368524) and decided by trial and error which extra number I would provide as a clue. Then I ran the following SQL to ensure the puzzle would only have one solution:

SQL> l
  1  select col1 from nine_digit_numbers
  2  where
  3  substr(col1,1,1) + substr(col1,2,1) +
  4  substr(col1,4,1) + substr(col1,5,1) = 17
  5  and
  6  substr(col1,2,1) + substr(col1,3,1) +
  7  substr(col1,5,1) + substr(col1,6,1) = 24
  8  and
  9  substr(col1,4,1) + substr(col1,5,1) +
 10  substr(col1,7,1) + substr(col1,8,1) = 16
 11  and
 12  substr(col1,5,1) + substr(col1,6,1) +
 13  substr(col1,8,1) + substr(col1,9,1) = 20
 14  and
 15* substr(col1,2,1) = 1
SQL> /

COL1
---------
719368524

SQL>

I put the puzzle away for a couple of days so that I would forget the answer then I solved it myself to ensure the solution could be reached using human logic.

Finally, I used Microsoft Paint to create the puzzle and the solution.

ORA-16005

I did a shutdown abort in an Oracle 19c database:

C:\Users\Admin>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Feb 9 20:52:49 2020
Version 19.3.0.0.0

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

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> shutdown abort
ORACLE instance shut down.
SQL>

A shutdown abort blocks new connections and kills current sessions whether they are doing anything or not. It does not roll back terminated transactions either. As a result, Oracle has some recovery to do when the instance is restarted to make the database consistent again.

I tried to open the database in read only mode:

SQL> startup mount
ORACLE instance started.

Total System Global Area 7717518448 bytes
Fixed Size                  9284720 bytes
Variable Size            1258291200 bytes
Database Buffers         6442450944 bytes
Redo Buffers                7491584 bytes
Database mounted.
SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

SQL>

This would have prevented the recovery from taking place so Oracle returned an ORA-16005.

I recovered the database manually:

SQL> recover database
Media recovery complete.
SQL>

I thought this might allow me to open the database in read only mode but it didn't:

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-16005: database requires recovery

SQL>

To open the database in read only mode, I had to open it normally, close it then reopen it as shown below:

SQL> alter database open;

Database altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 7717518448 bytes
Fixed Size                  9284720 bytes
Variable Size            1258291200 bytes
Database Buffers         6442450944 bytes
Redo Buffers                7491584 bytes
Database mounted.
SQL> alter database open read only;

Database altered.

SQL>

How to Skip N Rows in an Oracle SELECT Statement

I saw this question on a SQL Server blog. Apparently it was asked at an interview. Here is the suggested answer:

SELECT *

FROM [AdventureWorks2014].[Person].[Address]

ORDER BY AddressID

OFFSET 100 ROWS;

 

I don’t have access to SQL Server right now so I wondered how I might do this in Oracle. This is what I came up with. I ran it in an Oracle 11.2.0.4 database:

SQL> l

  1   select username from dba_users

  2   minus

  3   (select username from

  4    (select username from dba_users order by username)

  5    where rownum <=10)

  6* order by username

SQL> /

 

USERNAME

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

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

22 rows selected.

 

SQL>

 

It skips the first 10 rows from DBA_USERS and returns the rest. Here are all the rows, in case you are interested:

 

SQL> select username from dba_users order by 1

  2  /

 

USERNAME

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

ABCD

ANONYMOUS

APEX_030200

APEX_PUBLIC_USER

APPQOSSYS

CTXSYS

DBSNMP

DIP

EXFSYS

FLOWS_FILES

FRED

MDDATA

MDSYS

MGMT_VIEW

OLAPSYS

ORACLE

ORACLE_OCM

ORDDATA

ORDPLUGINS

ORDSYS

OUTLN

OWBSYS

OWBSYS_AUDIT

SI_INFORMTN_SCHEMA

SPATIAL_CSW_ADMIN_USR

SPATIAL_WFS_ADMIN_USR

SYS

SYSMAN

SYSTEM

WMSYS

XDB

XS$NULL

 

32 rows selected.

 

SQL>

Oracle DBA Interview Question

This Oracle DBA interview question is based on some work I did recently. I will leave you to decide whether it is too easy, too difficult or just right for your candidate.

Question:

I created two Oracle 11.2.0.4 test databases on a UNIX server some time ago. I called them GBASRDB1 and GBASRDB2. Each database has a user called ANDREW. I can connect to GBASRDB1 from my Micrososft Windows PC like this:

C:\Users\J0294094>sqlplus andrew/reid@gbasrdb1

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 8 11:29:59 2019

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

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

SQL>

I can also logon to the UNIX server and connect to GBASRDB1 as follows:

GBASRDB1: sqlplus andrew/reid

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 8 11:18:50 2019

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

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

SQL>

I can connect to GBASRDB2 from my Microsoft Windows PC like this:
 
C:\Users\J0294094>sqlplus andrew/reid@gbasrdb2

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 8 11:31:42 2019

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

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

SQL>
 
...but when I logon to the UNIX server and try to connect to GBASRDB2 as follows, I see this message:
 
GBASRDB2: sqlplus andrew/reid

SQL*Plus: Release 11.2.0.4.0 Production on Fri Mar 8 11:23:23 2019

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

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

Enter user-name: 

What could cause this to happen?

Answer:

Looking at the two sessions in GBASRDB1, you can see that the V$SESSION PROGRAM column in the first SQL*Plus session is 11 characters long. However, the PROGRAM column from the second session includes the name of the UNIX server and is 35 characters long:

SQL> select name from v$database
  2  /

NAME
---------
GBASRDB1

SQL> select program, length(program)
  2  from v$session
  3  where username = 'ANDREW'
  4  order by logon_time
  5  /

PROGRAM                             LENGTH(PROGRAM)
----------------------------------- ---------------
sqlplus.exe                                      11
sqlplus@sge-mktred-lab2 (TNS V1-V3)              35

SQL>
 
GBASRDB2 has a database level logon trigger which was created as shown below. The original trigger was much longer so I have only left enough of the code to illustrate the problem:
 
SQL> conn / as sysdba
Connected.
SQL> select name from v$database
  2  /

NAME
---------
GBASRDB2

SQL> grant select on v_$session to public
  2  /

Grant succeeded.

SQL> conn /
Connected.
SQL> show user
USER is "OPS$ORACLE"
SQL> create or replace trigger trigger1
  2  after logon on database
  3  declare
  4   current_program varchar2(30);
  5  begin
  6   select program into current_program
  7    from v$session
  8    where audsid = userenv('sessionid');
  9  end;
 10  /

Trigger created.

SQL> show errors
No errors.
SQL>

This assumes that the PROGRAM column from V$SESSION is never longer than 30 characters. It can actually be up to 48 characters long:
  
SQL> desc v$session
 Name                       Null?    Type
 -------------------------- -------- ------------------
 etc
 etc
 PROGRAM                             VARCHAR2(48)
 etc
 etc
SQL>
 
So when a user tries to connect with a PROGRAM which is greater than 30 characters long, the logon trigger fails and displays the error shown.

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.