Original posts by
QingyunPublished on 2010-6-21 15:44
When writing PL/SQL, there is a very important point of attention;
Like what:
Begin
Update a SQL Server table @dblink name ...;
Update a table for an oracle ...;
End
This PL/SQL execution will error:
The error message is:
-----------------------------------------------------------------
Execution failed: ORA-02054: Transaction processing 2.12.27634 a problem
ORA-28500: This information is returned when connecting Oracle to a non-Oracle system:
[Oracle] [ODBC SQL Server Driver] [SQL Server] Object name ' RECOVER. Hs_transaction_log ' is invalid.
ORA-02063: Immediately thereafter 2 lines (starting from Dblink name)
-----------------------------------------------------------------
Then you perform:
SELECT * from a SQL Server table @dblink name
This is no problem;
Re-execution:
SELECT * FROM an Oracle table
Finished! Results hint:
Execution failure: ORA-01591: Lock is held by problematic distributed transaction processing 2.12.27634
At this time, you query:
SELECT * from dba_2pc_pending
Will find that the information is locked;
Then, you use
Commit/rollback force ' 2.12.27634 '; Unlock
Of course, the 2.12.27634 here are random. Each time it is locked, it is a different value;
How to solve this problem?
Begin
Update a SQL Server table @dblink name ...;
Commit --very important;
Update a table for an oracle ...;
End
That's OK, because I think this is true: Oracle is actually two transactions, one for SQL Server, one for its own oracle;
Therefore, the two ends of the transaction must be independent;
The following wording is OK:
Begin
Update a SQL Server table @dblink name ...;
Update a SQL Server table @dblink name ...;
Commit --very important;
Update a table for an oracle ...;
Update a table for an oracle ...;
End;
Finally to solve the problem, this problem, throat, troubled me for half a year, now the customer's system immediately to formally use;
I was forced to grope again and finally took care of it:
Workaround:
1. Modify the Initdg4msql.ora file;
Last two lines:
Hs_fds_recovery_account=recover
Hs_fds_recovery_pwd=recover
Modified into
Hs_fds_recovery_account=dbo
Hs_fds_recovery_pwd=sa's password;
This is the simplest method, of course, can also be built in MSSQL recover users, I think the simpler the better;
2. Run on the SQL Server database:
DROP TABLE Hs_transaction_log
Go
CREATE TABLE Hs_transaction_log (
global_tran_id char (+) is not NULL,
Tran_comment char (255) NULL
)
Go
Grant all on Hs_transaction_log to public
Go
So everything is done; no more worrying about the two database transaction switching issues; |
|
| Http://www.daizhicun.com |
[Oracle] [ODBC SQL Server Driver] [SQL Server] Object name ' RECOVER. Hs_transaction_log ' Invalid (RPM)
