Oracle uses a temporary table to solve an example of conflict (reposted)

Platform: Windows 2000 Server SP3

Database: Oracle 9.0.1.1.1

Site Description: The user complains that the data processing is not possible at the same time, and the error message that executes the error is:

SQL> EXEC SP_HYB_DA_RYXX_DWBH_TEST ('331028')

Begin sp_hyb_da_ryxx_dwbh_test ('331028'); END;

*

Error is located on the first line:

ORA-02055: Distributed update operation failed;

ORA-06502: PL / SQL: Digital or value error: String buffer is too small

ORA-06512: "SIDB.SP_HYB_DA_RYXX_DWBH_TEST", LINE 55

ORA-06502: PL / SQL: Digital or value error: String buffer is too small

ORA-06512: "SIDB.SP_HYB_DA_RYXX", LINE 145

ORA-06502: PL / SQL: Digital or value error: String buffer is too small

ORA-06512: "SIDB.SP_HYB_DA_RYXX_YZ1", LINE 25

ORA-20901: Process sp_hyb_daxx error; grbh = 1000607288 ORA-02049: Timeout:

Distributed transaction processing waiting to be locked

Please contact the system administrator; 1

ORA-06512: in line 1

After checking, it was found that the 'temporary' table was used in the background storage process. These temporary tables were used to temporarily save part of the formal table data, and the application stored procedure code

Delete ;

INSERT INTO Select * from Where ...

This method has caused only one unit's data processing due to the wait for locking at the same time.

You can eliminate the locking of the temporary table by using a temporary table in the true sense of Oracle.

The basic definition of the temporary table is:

All sessions in the database can access the temporary table, but only the session inserted into the temporary table can see the data inserted. You can specify the temporary table as transaction-related (default) or a session:

On Commit Delete Rows: Specifies the temporary table is transaction-related, Oracle cut off after each submission.

On Commit Preserve Rows: Specifies the temporary table is the session, and Oracle has truncated the table after the session is aborted.

By using Oracle's temporary table, the data can be processed in parallel to improve the speed of data processing.

Attachment: An example of creating a temporary table

Create Global Temporary Table

AS

Select * from Where 1 = 2;

Create Index ON ();