Commit from a Procedure/Function Executed Through a Database Link, ORA-02064: distributed operation not supported

It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.

Here is a test case, working fine:

1-) At remote database create following procedure:

create or replace procedure erkan_trunc

is

begin

insert into XXX values(1,1,'erkan saka'); -- Create a transaction at remote db

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- This is a DDL! which also autocommits

end;

/

2-) At source database create following procedure

create or replace procedure erkan_dene

is

begin

insert into erkan.A values(1); -- Create a transaction at local (source) db

erkan_trunc@{db_link}; -- remote procedure call

rollback; -- End local transaction

end;

/

3-) Working fine:

SQL>exec erkan_dene

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Here is an other test case with ORA-02064: distributed operation not supported:

1-) At remote database create following procedure:

create or replace function erkan_trunc2

RETURN NUMBER  -- This is the reason causing ORA-02064

is

begin

insert into XXX values(1,1,'erkan saka');

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';

RETURN (0);

end;

/

2-) At source run following PL/SQL

SQL>declare

  2     de_return number;

  3  begin

  4     de_return := erkan_trunc2@{db_link};

  5             dbms_output.put_line('de_return='||de_return);

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-02064: distributed operation not supported

ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6

ORA-06512: at line 4

Elapsed: 00:00:00.01

ORA-02064: distributed operation not supported

If a commit/rollback or autocommit(DDL) operation is executed within a procedure or function which is called remotely via database link then ORA-02064 may occur. Exact explanation is:

It is not supported to issue a commit from a procedure with OUT parameters or a function with return value, when the procedure/function is executed through a database link.

I tested following two senarios. First works fine while second fails with ORA-02064:

Senario 1-)

* Create following procedure at remote database:

create or replace procedure erkan_trunc

is

begin

insert into XXX values(1,1,'erkan'); -- in order to create a local transaction at remote database

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX'; -- autocommits

end;

/

* Create following procedure at source database:

create or replace procedure erkan_dene

is

begin

insert into erkan.A values(1); --in order to create a local transaction in source database

BEGIN

erkan_trunc@remote_tns;

EXCEPTION

       WHEN OTHERS

       THEN

dbms_output.put_line('Error while truncate: ' || SUBSTR (SQLERRM, 1, 100););

END;

rollback;

end;

/

* Works fine:

SQL>exec erkan_dene

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

if i hold lock on table XXX at remote database...

SQL>exec erkan_dene

Error while truncate: ORA-00054: resource busy and acquire with NOWAIT specified

ORA-06512: at "ERKAN.ERKAN_TRUNC", line 5

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.03

Senario 2-)

* Create following function at remote database:

create or replace function erkan_trunc2

RETURN NUMBER

is

begin

insert into XXX values(1,1,'erkan');

EXECUTE IMMEDIATE 'TRUNCATE TABLE ERKAN.XXX';

RETURN (0);

end;

/

* Run following pl/sql script at source database:

SQL>declare

  2     de_return number;

  3  begin

  4     de_return := erkan_trunc2@remote_tns;

  5             dbms_output.put_line('de_return='||de_return);

  6  end;

  7  /

declare

*

ERROR at line 1:

ORA-02064: distributed operation not supported

ORA-06512: at "ERKAN.ERKAN_TRUNC2", line 6

ORA-06512: at line 4

Elapsed: 00:00:00.01

ORA-38029: object statistics are locked

If this error occurs while trying to analyze a table then run following to unlock the statistics:

exec DBMS_STATS.UNLOCK_TABLE_STATS('{owner}','{table name}');

You can see list of all locked tables by running following query:

select owner, table_name, stattype_locked from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM');

or

select 'exec DBMS_STATS.UNLOCK_TABLE_STATS('''||owner||''','''||table_name||''');' from dba_tab_statistics where stattype_locked is not null and owner not in ('SYS','SYSTEM')

For further detail, refer to metalink doc id 433240.1

LOCALLY MANAGED TABLESPACE with SPACE MANAGEMENT AUTO

CREATE TABLESPACE DATA_1M DATAFILE
'/data01/dbfs/data_1m01.dbf' SIZE 2048M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
SEGMENT SPACE MANAGEMENT AUTO;
/

alter tablespace DATA_1M add datafile '/data01/dbfs/data_1m02.dbf' SIZE 1024M AUTOEXTEND ON NEXT 512M MAXSIZE 2048M;


alter database datafile '/data01/dbfs/data_1m02.dbf' AUTOEXTEND ON NEXT 1024M MAXSIZE 3072M;

ARCHIVED_SEQ# column of v$ARCHIVE_DEST_STATUS is not being updated

Check and correct fal_client and fal_server system parameters at standby database:

alter system set fal_client= scope=both;
alter system set fal_server= scope=both;

Dataguard Swithover

switchover commands:

Prod Site:
ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY WITH SESSION SHUTDOWN;

Standby Site:
alter database commit to switchover to primary;
alter database open ;

Old Prod Site:
SHUTDOWN;
STARUP MOUNT;
alter database recover managed standby database using current logfile disconnect from session;

Identity (Autoincrement) Column on Oracle

You can do this by using a sequence and before insert trigger on oracle:

create table ABC (id int, data varchar2(200));

create sequence INCABC
start with 1
increment by 1
maxvalue 999999999999
minvalue 1
nocyclecache 10
order;

create or replace trigger ABC_IDENTITY
before insert on ABC
FOR EACH ROW
begin
select INCABC.nextval into :new.id from dual;
end;
/

However in order to insert table, all column names except "id" should be provided in insert statement like:

insert into ABC (data) values ('abc');

or a dummy value should be provided for "id" column:

insert into abc values('abc',5);

other case following error occurs:

SQL> insert into abc values('abc');
insert into abc values('abc')
ERROR at line 1:
ORA-00947: not enough values