An example of using temporary tables in Oracle to solve table conflicts

Platform: windows 2000 server sp3

Database: Oracle 9.0.1.1.1

Field Description: The user complained that data cannot be processed for multiple units at the same time. The error message displayed during execution is:

SQL> exec sp_hyb_da_ryxx_dwbh_test ('20140901 ')

BEGIN sp_hyb_da_ryxx_dwbh_test ('20140901'); END;

* ERROR is located in row 1st:

ORA-02055: distributed update operation failure; rollback required

ORA-06502: PL/SQL: Number or value error: string buffer is too small

ORA-06512: In "SIDB. SP_HYB_DA_RYXX_DWBH_TEST", line 55

ORA-06502: PL/SQL: Number or value error: string buffer is too small

ORA-06512: In "SIDB. SP_HYB_DA_RYXX", line 145

ORA-06502: PL/SQL: Number or value error: string buffer is 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 waiting for Lock

Contact the system administrator. 1

ORA-06512: In line 1

After checking, it is found that the 'temporary 'tables are used in the background storage process. These temporary tables are used to temporarily save data in some formal tables. The code of the stored procedure of the application is as follows:

DELETE ;

INSERT SELECT * FROM WHERE ...;

This method causes Lock wait, and only one unit of data processing can be performed simultaneously.

You can use temporary tables in Oracle to eliminate temporary table locking.

The basic definition of a temporary table is that all sessions in the database can access the temporary table, but only sessions inserted into the temporary table can see the inserted data. You can specify a temporary table as the transaction-related default) or session-related:

(