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.

COMMIT Causes ORA-03113

A colleague had a problem with an Oracle 11.2.0.1 database today. It affected the following table:

SQL> desc bepe.sd_national_holiday

Name                       Null?    Type

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

SD_NATIONAL_HOLIDAY_ID     NOT NULL NUMBER(15)

DAY_DATE                            DATE

DESCRIPTION                         VARCHAR2(500)

CREATED_BY                 NOT NULL VARCHAR2(100)

CREATION_DATE              NOT NULL TIMESTAMP(6)

ACTIVITY_BY                NOT NULL VARCHAR2(100)

ACTIVITY_DATE              NOT NULL TIMESTAMP(6)

TCN                        NOT NULL NUMBER

 

SQL>

The table was empty:

SQL> select count(*) from bepe.sd_national_holiday

  2  /

 

  COUNT(*)

----------

         0

 

SQL>

… but, when I tried to add data to it, the COMMIT, which followed, failed with an ORA-03113. Typing EXIT, without doing a COMMIT, caused the same problem:

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('15-APR-2015')

  3  /

 

1 row created.

 

SQL> commit

  2  /

commit

     *

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 7037

Session ID: 133 Serial number: 6339

 

SQL> conn /

Connected.

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('15-APR-2015')

  3  /

 

1 row created.

 

SQL> exit

ERROR:

ORA-03113: end-of-file on communication channel

Process ID: 0

Session ID: 133 Serial number: 6343

 

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options (with complications)

APXDEV1 /export/home/oracle/andrew >

The table remained empty:

SQL> l

  1  select count(*)

  2* from bepe.sd_national_holiday

SQL> /

 

  COUNT(*)

----------

         0

 

SQL>

… and trying to move it did not work either:

SQL> l

  1  alter table bepe.sd_national_holiday

  2* move tablespace apex_13482906934262524

SQL> /

alter table bepe.sd_national_holiday

                 *

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 11685

Session ID: 156 Serial number: 21804

 

SQL>

I was able to reproduce this error again and again. I thought there might be some corruption in the underlying data file but dbverify did not show anything. I looked at the messages in the alert log:

ORA-07445: exception encountered: core dump [qesrcRseObj_Invalidate()+4] [SIGSEGV] [ADDR:0x18] [PC:0x1029AFAC4] [Address not mapped to object] []

The closest match I could find for these on My Oracle Support was unpublished bug 9025861. In Oracle 11.2, results from functions run against tables can be cached. Then Oracle can access these cached values rather than rerun the functions. The suggested fix for this bug is to upgrade to Oracle 11.2.0.2 or set result_cache_max_size to zero, which stops cached results being used. I set the parameter to zero:

SQL> l

  1  select value from v$parameter

  2* where name = 'result_cache_max_size'

SQL> /

 

VALUE

----------

2621440

 

SQL> alter system set result_cache_max_size = 0

  2  /

 

System altered.

 

SQL>

… and the problem went away:

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('15-APR-2015')

  3  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

Strangely enough, resetting the parameter to its previous value did not make the problem return:

SQL> alter system set result_cache_max_size = 2621440

  2  /

 

System altered.

 

SQL> select value from v$parameter

  2  where name = 'result_cache_max_size'

  3  /

 

VALUE

----------

2621440

 

SQL> insert into bepe.sd_national_holiday

  2  (day_date) values ('16-APR-2015')

  3  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> 

However, this is not a permanent cure. I did the same thing just over a month ago and the problem went away then too but, as you can see, it returned today for no apparent reason.

We noticed that an Oracle 11.2.0.2 database, which is being used for similar work, has not been affected by this problem so upgrading to this version might be the safest option.

SHUTDOWN TRANSACTIONAL

If you run this command, Oracle waits until all users have committed or rolled back any outstanding transactions before closing the database. To test this out in Oracle 12.1, I tried to close a database from a session with an outstanding transaction. This failed with an ORA-01097. Once I had committed the transaction, I was able to close the database successfully:

SQL> show user

USER is "SYS"

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> shutdown transactional

ORA-01097: cannot shutdown while in a transaction - commit or rollback first

SQL> commit

  2  /

 

Commit complete.

 

SQL> shutdown transactional

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 1720328192 bytes

Fixed Size                  2403496 bytes

Variable Size            1056965464 bytes

Database Buffers          654311424 bytes

Redo Buffers                6647808 bytes

Database mounted.

Database opened.

SQL>

 

For the next example, I logged in as user ANDREW, created a table, inserted a row but did not commit it:

 

SQL> show user

USER is "ANDREW"

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL>

 

When user SYS tried to close the database, nothing happened:

 

SQL> show user

USER is "SYS"

SQL> shutdown transactional

 

After a while, user ANDREW rolled back his transaction:

 

SQL> show user

USER is "ANDREW"

SQL> create table tab1(col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL>

 

… then the database closed down a few seconds later:

 

SQL> show user

USER is "SYS"

SQL> shutdown transactional

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

AFTER DELETE Triggers do not Run After a Truncate

In this example, I want to show that Oracle does not execute an AFTER DELETE trigger after doing a TRUNCATE. I tested it on Oracle 11.2. First I created a table:

SQL> create table tab1 (my_name varchar2(10))
  2  /

Table created. 

SQL>

Then I inserted a row into it: 

SQL> insert into tab1 values ('Andrew')
  2  /

1 row created.

SQL> commit
  2  /

Commit complete.

SQL> select * from tab1
  2  /

MY_NAME
----------
Andrew

SQL>

Next I created a second table:

SQL> create table tab2 (my_name varchar2(10))
  2  /

Table created.

SQL>

I created a trigger to insert rows into TAB2 after deleting them from TAB1:

SQL> create or replace trigger trig1
  2  after delete on tab1
  3  for each row
  4  begin
  5  insert into tab2 (my_name) values (:old.my_name);
  6  end;
  7  /

Trigger created.

SQL>

To test the trigger, I ran a DELETE to remove the row from TAB1 and it appeared in TAB2:

SQL> delete tab1
  2  /

1 row deleted.

SQL> select * from tab1
  2  /

no rows selected

SQL> select * from tab2
  2  /

MY_NAME
----------
Andrew

SQL>

I did a ROLLBACK and the row returned from TAB2 to TAB1:

SQL> rollback
  2  /

Rollback complete.

SQL> select * from tab1
  2  /

MY_NAME
----------
Andrew

SQL> select * from tab2
  2  /

no rows selected

SQL>

I repeated the test with TRUNCATE:

SQL> truncate table tab1
  2  /

Table truncated.

SQL> select * from tab1
  2  /

no rows selected

SQL>

... but Oracle did not execute the trigger and the row did not appear in TAB2:

SQL> select * from tab2
  2  /

no rows selected

SQL>

... and after doing a ROLLBACK, the row did not return to TAB1 because TRUNCATE is DDL and includes a COMMIT:

SQL> rollback
  2  /

Rollback complete.

SQL> select * from tab1
  2  /

no rows selected

SQL> select * from tab2
  2  /

no rows selected

SQL>

ORA-01456 (SET TRANSACTION READ ONLY - Part 3)

This was tested on an Oracle 11.2 database. If you try to INSERT, UPDATE or DELETE rows in a table during a READ ONLY transaction, you get an ORA-01456:

 

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> update tab1 set col1 = 2

  2  /

update tab1 set col1 = 2

       *

ERROR at line 1:

ORA-01456: may not perform insert/delete/update

operation inside a READ ONLY transaction

 

SQL>

 

If you do a COMMIT, this terminates the READ ONLY transaction:

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

 

… and the UPDATE statement works OK:

 

SQL> update tab1 set col1 = 2

  2  /

 

1 row updated.

 

SQL> commit

  2  /

 

Commit complete.

 

SQL>

 

The example below is similar to the one above. However, this time a ROLLBACK is used to terminate the READ ONLY transaction:

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> update tab1 set col1 = 3

  2  /

update tab1 set col1 = 3

       *

ERROR at line 1:

ORA-01456: may not perform insert/delete/update

operation inside a READ ONLY transaction

 

SQL> rollback

  2  /

 

Rollback complete.

 

SQL> update tab1 set col1 = 3

  2  /

 

1 row updated.

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL>

ORA-01453 (SET TRANSACTION READ ONLY - Part 2)

Continuing with my investigation of SET TRANSACTION READ ONLY on an Oracle 11.2 database, I noticed that it fails with an ORA-01453 if you try to run it from a session with pending transactions. Once you have used COMMIT (or ROLLBACK) to deal with the pending transactions, the statement works OK:

 

SQL> create table tab1 (col1 number)

  2  /

 

Table created.

 

SQL> insert into tab1 values(1)

  2  /

 

1 row created.

 

SQL> set transaction read only

  2  /

set transaction read only

*

ERROR at line 1:

ORA-01453: SET TRANSACTION must be first statement of

transaction

 

SQL> commit

  2  /

 

Commit complete.

 

SQL> host sleep 1

 

SQL> set transaction read only

  2  /

 

Transaction set.

 

SQL> select * from tab1

  2  /

 

      COL1

----------

         1

 

SQL> drop table tab1

  2  /

 

Table dropped.

 

SQL>

ORA-02020

Tested on Oracle 9. I reconfigured several database links then ran a test script to check them all:

SQL> conn andrew/reid

Connected.

SQL> select * from dual@link1

  2  /

D

-

X

SQL> select * from dual@link2

  2  /

D

-

X

SQL> select * from dual@link3

  2  /

D

-

X

SQL> select * from dual@link4

  2  /

D

-

X

SQL> select * from dual@link5

  2  /

select * from dual@link5

                   *

ERROR at line 1:

ORA-02020: too many database links in use

SQL>

The 5th query failed. This was because the value of the open_links parameter was too low:

SQL> conn / as sysdba

Connected.

SQL> select value from v$parameter

  2  where name = 'open_links'

  3  /

VALUE

----------

4

SQL>

Once you have run a query down a database link, it is retained until you do a commit or rollback. In the example below, a commit after the 1^st query allows the 5^th to work. Changing the value of the open_links parameter is also an option. I will try to look at this in a future post:

SQL> conn andrew/reid

Connected.

SQL> select * from dual@link1

  2  /

D

-

X

SQL> commit

  2  /

Commit complete.

SQL> select * from dual@link2

  2  /

D

-

X

SQL> select * from dual@link3

  2  /

D

-

X

SQL> select * from dual@link4

  2  /

D

-

X

SQL> select * from dual@link5

  2  /

D

-

X

SQL>