Sunday, November 07, 2021

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.

Friday, October 15, 2021

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> 

Monday, May 10, 2021

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: