Example of using temporary tables to resolve table conflicts in Oracle

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>);