Platform: Windows SP3 server
Database: Oracle 9.0.1.1.1
Site Description: Users complain that not multiple units at the same time data processing, the implementation of the error message is:
sql> exec sp_hyb_da_ryxx_dwbh_test (' 331028 ')
BEGIN sp_hyb_da_ryxx_dwbh_test (' 331028 '); End;
*error is on line 1th:
ORA-02055: The distributed update operation is invalid; request fallback
Ora-06502:pl/sql: Numeric or value error: String buffer too small
ORA-06512: In "Sidb." Sp_hyb_da_ryxx_dwbh_test ", line 55
Ora-06502:pl/sql: Numeric or value error: String buffer too small
ORA-06512: In "Sidb." Sp_hyb_da_ryxx ", line 145
Ora-06502:pl/sql: Numeric or value error: String buffer too small
ORA-06512: In "Sidb." Sp_hyb_da_ryxx_yz1 ", line 25
ORA-20901: Process Sp_hyb_daxx error; grbh = 1000607288 ORA-02049: timeout:
Distributed transaction processing waits for lock
Contact your system administrator; 1
ORA-06512: On line 1
After inspection, it is found that the ' temporary ' table is used in the background stored procedure, which is used to temporarily save the data of some formal tables, and the code of the application stored procedure is as follows:
DELETE <temp table>;
INSERT into <temp table> SELECT *from <normal table> WHERE ...;
This approach results in simultaneous data processing at the same time due to the <temp table> lock waiting while executing simultaneously.
You can eliminate locking of temporary tables by using temporary tables in the true sense of Oracle.
The basic definition of a temporary table is that all sessions in a database can access a temporary table, but only a session that inserts data into a temporary table can see the data that it inserts. Temporary tables can be specified as transaction-related (default) or session-Related:
On COMMIT DELETE ROWS: Specifies that the temporary table is transaction-related and that Oracle truncates the table after each commit.
On COMMIT PRESERVE ROWS: Specifies that the temporary table is session-related and that Oracle truncates the table after the session is aborted.
By using the Oracle temporary table, the data can be processed in parallel, and the speed of processing is improved effectively.
Attachment: Example of creating a temporary table
CREATE GLOBAL temporary TABLE <temp table>
As
SELECT *from <normal table> WHERE 1 = 2;
CREATE INDEX <index name> on <temp table> (<column name>);