I. Status

Current Status:

• The current system when executing batch runs as scheduled, sometimes it generates an error ORL-01555

エラーメッセージ：ORA-01555: スナップショットが古すぎます: ・セグメント番号***、名前”_SYSSMU[segment-name]$”が小さすぎます

Reason:

• After a while of google English, em, Japanese, Vietnamese, ask friends, ask me, ask colleagues, the cause can be one of the two below:
  • When performing update/insert/delete a large number of records in a transaction that exceeds the memory limit of UNDO-TableSpace –> error
  • Youth B is updating/insert/delete some records and commits in a short period of time, when committing, Oracle will delete unnecessary records in the rollback segment. But now there is a young man A running Select with consistent View before committing –> data being read is changed –> error

II. Define

As above appeared 2 strange definitions (for me when I learned this error) such as UNDO-TableSpace and Rollback Segment. So it’s convenient to gg and then note it down so you don’t forget.

1. UNDO

• As the name implies, the intent was created to return the previous one.
• One more thing for easy understanding is Undo-data – is the data created by Oracle when performing transactions that change data.
• The purpose of Rollback is to retain the data of the Transactions as needed.
• It lasts until the transaction ends (commit, rollback)

2. Rollback Segment

• Are database structures, used to track UNDO information (like the concept mentioned above)

III. Explain the phenomenon

1. Cause 1

• This can be understood as when you update too many records in a transaction, here the Undo-Record starts to be initialized.
• Then when the Undo-tableSpace is full but the Transaction is not finished, what will happen? –> then the next guy will sit on top of the previous guy (overwrite)
• Oracle created UNDO_RETENTION to manage the time that UNDO can be saved, it’s kind of like the time you can write/store new UNDOs. The smaller the value of this variable, the shorter the storage time, the smaller the UNDO-TableSpace.
• Then what if the Transaction was rolled back today??? The old data is no longer available because it was sitting on top –> rollback data was changed —> Error returned

2. Cause 2

• To get to cause 2 we have to come to the concept:
  • Mechanism of read consistency: simply understood that when performing long running SQL that it is executed at 10 am, it always ensures that the rows it receives are always the same state as at 10 am even if the SQL statement is run. to the next day.
    • Through the above definition, it can be understood that it is not possible to read and write 1 record at a time.
    • If both read and write, it will lead to the phenomenon of locking each other so that it ensures the consistency of its own query
• Thereby it can be seen that when the SELECT statement is running, but the data in the Rollback segment (which contains the Undos) is changed, the consistency is not guaranteed. So besides the reason that UNDO-TableSpace is not enough, what can cause the UNDO data change??? That’s when Transaction it COMMIT. Looking back at the definition of UNDO, it can be seen that if the transaction commits, the UNDO data is released, which also means that it has been changed –> Leads to an error on the side using the SELECT statement.

Quote from Oracle

It all boils down to the size of the undo tablespace and the undo retention, in the end…just as manual management boiled down to the size, amount, and usage of rollback segments. Committing frequently is a peroxide band-aid: it covers up the problem, tries to clean it, but in the end it just hurts and causes problems for otherwise healthy processes.

Through this will arise more Transaction and how SELECT works in the Database will study and write more about it.