CODEWITHSUNDEEP
sql-serveroracle-databasedatabase-linkheterogeneous-services
Mackers
Mackers

Reputation: 1050

Unable to create objects with db link; ORA - 0251: another session or branch in same transaction failed or finalized

I have a Heterogeneous Link between Oracle 11.2.0.3 and SQL Server 2008. When I attempt to create views, or procedures that reference the Heterogeneous Services views for system tables, I get 4 errors listed below:

ORA-00604: error occured at recursive SQL level 1

ORA-02051: another session or branch in same transaction failed or finalized

ORA-06512: at "SYS.HS$_DDTF_SQLTABLES", line 58

ORA-06512: at line 1

As an example, these errors are created when I try to execute the following code:

CREATE VIEW ALLMYTABLES AS
SELECT * FROM ALL_TABLES@DBLINKNAME;

I am however able to create views on any normal table:

CREATE VIEW RANDOMTABLE AS
SELECT * FROM RANDOMTABLE@DBLINKNAME;

I have done some research to issues similar to this one, but have not found any references to this specific issue. I believe it may have something to do with the conflict between the concurrent operations of creating the view and the select * statement call to the SQL server database. Or perhaps that the records are not entirely fetched when the view is created.

I do not know the specifics of the HS link configuration and properties.

Upvotes: 3

Views: 1063

Answers (0)

Related Questions