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.

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:

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>

How to Diagnose a Locking Issue

A user ran an update statement and noticed that it did not seem to be doing anything:

 

SQL> conn gordon/bennett

Connected.

SQL> update andrew.emp

  2  set ename = 'COLIN'

  3  where ename = 'BRIAN'

  4  /

 

I looked up his SID in V$SESSION:

 

SQL> select sid from v$session

  2  where username = 'GORDON'

  3  /

 

       SID

----------

      393

 

SQL>

I ran the following SQL, waited 10 seconds and ran it again:

 

SQL> l

  1  select event, time_waited/100

  2  from v$session_event

  3  where sid = 393

  4  and wait_class != 'Idle'

  5* order by 2 desc

SQL> /

 

EVENT                          TIME_WAITED/100

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

enq: TX - row lock contention           249.58

Disk file operations I/O                   .01

SQL*Net message to client                    0

 

SQL> /

 

EVENT                          TIME_WAITED/100

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

enq: TX - row lock contention            258.6

Disk file operations I/O                   .01

SQL*Net message to client                    0

 

SQL> 

This showed me that there was an ongoing locking issue with this user. I looked up the SID of the blocking session like this:

 

SQL> l

  1  select blocking_session from v$session

  2* where sid = 393

SQL> /

 

BLOCKING_SESSION

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

             101

 

SQL>

 

I saw that it belonged to Fred:

 

SQL> l

  1  select username from v$session

  2* where sid = 101

SQL> /

 

USERNAME

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

FRED

 

SQL>

I found some SQL on the Internet and hoped that it would tell me what Fred was doing but it did not work:

 

SQL> l

  1  select b.sql_text

  2  from v$session a, v$sqlarea b

  3  where a.sql_address = b.address

  4* and a.sid = 101

SQL> /

 

no rows selected

 

SQL>

 

I wondered if Fred had run his SQL but not commited it and changed my SQL as follows:

 

SQL> l

  1  select b.sql_text

  2  from v$session a, v$sqlarea b

  3  where a.prev_sql_addr = b.address

  4* and a.sid = 101

SQL> /

 

SQL_TEXT

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

update andrew.emp set enum = 2 where enum = 1

 

SQL> 

Then I looked in the EMP table and saw that both Fred and Gordon were trying to update the same row:

 

SQL> select * from andrew.emp

  2  /

 

ENUM  ENAME

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

1     BRIAN

 

SQL>

 

I asked Fred to COMMIT his UPDATE statement:

 

SQL> update andrew.emp

  2  set enum = 2

  3  where enum = 1

  4  /

 

1 row updated.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

This allowed Gordon’s UPDATE to finish:

 

SQL> conn gordon/bennett

Connected.

SQL> update andrew.emp

  2  set ename = 'COLIN'

  3  where ename = 'BRIAN'

  4  /

 

1 row updated.

 

SQL>

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list

  2  as select * from dba_objects

  3  /

Table created.

SQL>

When you create a table it has no statistics so the num_rows column is null:

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------

NULL

                                                  

SQL>

When you calculate statistics, the num_rows column is updated:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

----------------------------------------
7932

SQL>

Moving the table deletes the statistics so num_rows is null afterwards:

SQL> alter table object_list move

  2  /

Table altered.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT

---------------------------------------- NULL                                       

SQL>

To reinstate the statistics, simply analyze the table again:

SQL> analyze table object_list

  2  compute statistics

  3  /

Table analyzed.

SQL> select nvl(to_char(num_rows), 'NULL')

  2  as row_count

  3  from dba_tables

  4  where table_name = 'OBJECT_LIST'

  5  /

ROW_COUNT
----------------------------------------
7932

SQL>

Can You Use BETWEEN and a Subquery Together?

I saw a colleague about to throw away his copy of Oracle 7 The Complete Reference so I rescued it from the bin and started to read it (as you do). It said that you could not use BETWEEN and a subquery in the same SQL statement and I wondered if this restriction still applied. The oldest version of Oracle I have access to is Oracle 9 so I decided to try it out there. The examples I came up with are a bit artificial but they show that you can now use BETWEEN and a subquery in the same SQL statement. Furthermore, the subquery can be:

 

(1)  Before the AND.

(2)  After the AND.

(3)  Before and after the AND.   

 

SQL> select count(*) from dba_users

  2  where created between

  3  (select created from dba_users

  4   where username = 'DATALOAD')

  5  and '31-DEC-2013'

  6  /

 

  COUNT(*)

----------

        73

 

SQL> select count(*) from dba_objects

  2  where object_id between

  3  100 and

  4  (select max(object_id) from dba_objects

  5   where owner = 'SYSTEM')

  6  /

 

  COUNT(*)

----------

      8850

 

SQL> select count(*) from dba_tables

  2  where initial_extent between

  3  (select min(initial_extent)

  4   from dba_tables

  5   where owner = 'SYSTEM')

  6  and

  7  (select max(initial_extent)

  8   from dba_tables

  9   where owner = 'SYSTEM')

10  /

 

  COUNT(*)

----------

      1196

 

SQL>

ORA-00028

This example shows how to kill a user session. You can view sessions in your database as follows:

SQL> col username format a10
SQL> l
  1  select username, sid, serial#, status
  2  from v$session
  3* where username = 'ANDREW'
SQL> /

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 INACTIVE

SQL>

You can then use the SID and SERIAL# displayed to kill a session as shown below. The username you are interested in may have more than one session. If this happens, you will need to use one or more of the other columns in V$SESSION (e.g.OSUSER, MACHINE, LOGON_TIME etc) to see exactly which session you want to get rid of:

SQL> alter system kill session '143,79';
 

System altered.
 

SQL>

The killed session stays in V$SESSION with a status of KILLED:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

USERNAME          SID    SERIAL# STATUS
---------- ---------- ---------- --------
ANDREW            143         79 KILLED

SQL>

Until the user tries to access it again (the connection was made earlier). He will then get an ORA-00028:

SQL> conn andrew/reid@test10
Connected.
SQL> select sysdate from dual;
select sysdate from dual
*
ERROR at line 1:
ORA-00028: your session has been killed

SQL>

... and the session will disappear from V$SESSION:

SQL> select username, sid, serial#, status
  2  from v$session
  3  where username = 'ANDREW';

no rows selected

SQL>

Logons Cumulative

The logons cumulative statistic in V$SYSSTAT shows how many sessions have connected since the database was opened. If this value is too high, there could be shell scripts looping round and connecting then disconnecting from the database. This can have a detrimental effect on performance.

SQL> col name format a20
SQL> select * from v$sysstat where name = 'logons cumulative'
  2  /

STATISTIC# NAME                 CLASS      VALUE      STAT_ID
---------- -------------------- ---------- ---------- ----------
0          logons cumulative    1          44         2666645286

SQL>

If you reconnect to the database, the value should increase by 1.

SQL> conn system/manager@adhoc
Connected.
SQL> select * from v$sysstat where name = 'logons cumulative'
  2  /

STATISTIC# NAME                 CLASS      VALUE      STAT_ID
---------- -------------------- ---------- ---------- ----------
0          logons cumulative    1          45         2666645286

SQL>

ORA-00360

Oracle returns an ORA-00360 if you try to drop a redo logfile member which does not exist: 

SQL> select group#, member from v$logfile

  2  order by 1, 2;

 

    GROUP# MEMBER

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

         1 /disk1/redo/log1a.rdo

         1 /disk2/redo/log1b.rdo

         2 /disk1/redo/log2a.rdo

 

SQL> alter database drop logfile member

  2  '/disk2/redo/log2b.rdo';

alter database drop logfile member

*

ERROR at line 1:

ORA-00360: not a logfile member: /disk2/redo/log2b.rdo

 

SQL>

SQL MINUS Operator

If you join two SELECT statements together with a MINUS, the output from the second SELECT is removed from the output from the first SELECT before the results are displayed. As always, it is easier to explain by using an example. First create a table for odd and even numbers and another table just for odd numbers:

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

Table created.

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

Table created.

SQL>

Now add some odd and even numbers to the first table then insert the contents again so that each number is stored twice (you will see why later):

SQL> insert into odd_and_even values (1);

1 row created.

SQL> insert into odd_and_even values (2);

1 row created.

SQL> insert into odd_and_even values (3);

1 row created.

SQL> insert into odd_and_even values (4);

1 row created.

SQL> insert into odd_and_even select * from odd_and_even;

4 rows created.

SQL> select * from odd_and_even

  2  /

      COL1

----------

         1

         2

         3

         4

         1

         2

         3

         4

8 rows selected.

SQL>

Now insert some odd numbers into the second table but do not replicate the values:

SQL> insert into odd values (1);

1 row created.

SQL> insert into odd values (3);

1 row created.

SQL> select * from odd

  2  /

      COL1

----------

         1

         3

SQL>

Now display the contents of the first table but before displaying the results, use the MINUS statement to remove any values which appear in the second table:

SQL> select col1 from odd_and_even

  2  minus

  3  select col1 from odd

  4  /

      COL1

----------

         2

         4

SQL>
  

Note that there were four odd numbers in the first table i.e. 1, 3, 1, 3 but only two odd numbers in the second table i.e. 1, 3. Even so, all the odd numbers have been subtracted from the output. This is equivalent to either of the following two SQL statements: 

SQL> select distinct col1 from odd_and_even

  2  where col1 not in

  3  (select col1 from odd)

  4  /

      COL1

----------

         2

         4

SQL> select distinct col1 from odd_and_even x

  2  where not exists

  3  (select col1 from odd

  4   where col1 = x.col1)

  5  /

      COL1

----------

         2

         4

SQL>