Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the use scenarios and functions of retention guarantee in sql. I hope you will get something after reading this article. Let's discuss it together.

The SQL > alter tablespace undo_samll retention guarantee; tablespace has changed.

SQL > select tablespace_name,block_size,extent_management segment_space_management,contents,retention from dba_tablespaces

It should be noted that this guarantee schema is not applicable only for other undo tablespace tablespaces.

In Oracle 10g, we can also set undo_retention=0 to allow Oracle to automatically adjust the time it takes to retain undo information after submission.

The role of RETENTION GUARANTEE in 10g

1. Explain undo_retention first.

Set undo_retention to ensure how long the data after commit will be retained in undo segment. However, there is no guarantee that the undo information after commit will not be overwritten within the time of undo_retention. When there is not enough undo segment, it will still overwrite the undo information that has been commit.

2. If you need to ensure that the undo information will not be overwritten in undo_retention time, you can set RETENTION GUARANTEE to undo segment. However, this parameter is limited by undo_retention and undo size. If undo size is too small and undo_retention is set for too long, an error will be reported when setting retention guarantee:

ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS2'

3. Set this parameter

Alter tablespace undotbs2 retention guarantee

Undo the parameter

Alter tablespace undotbs2 retention noguarantee

After reading this article, I believe you have a certain understanding of "what are the usage scenarios and functions of retention guarantee in sql". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.