This article describes an example of using a temporary table to resolve table conflicts in Oracle, with an example of creating a temporary table
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;
INSERT into SELECT *from WHERE ...;
This approach results in simultaneous execution of data processing at the same time due to waiting for the lock.
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.
See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/