[Oracle]undo Table space usage is 100%

In Toad, the undo tablespace undotbs1 usage has reached 100%, but the strange thing is that the database does not hang and still works.

The use of the EM View UNDOTBS1 table space provided by Oracle also achieves 78.8

In the previous article, we described the 3 states of the Undo table space Central: active, EXPIRED, unexpired. After understanding the concept, the individual believes that without setting the undo Tablespace retention Guarantee, the active state does not cause the database to hang if it does not reach 100%.

So why Toad UNDOTBS1 use rate of 100%, and Em is 78.8?

By tracing the corresponding SQL statements of Toad and Em, it is found that the Toad calculates 3 states, while EM calculates the active and expired two states.

By querying the zone with the active status, the number found is 0:

SQL>Select*fromwhere='ACTIVE'  ; OWNER segment_name  tablespace_name  extent_id    file_id   block_ ID      BYTES     BLOCKS relative_fno commit_jtime commit_wtime         STATUS--------------- --- ---------------- ---------- ---------- ---------- ---------- ---------- ------------ ------------ ------------------ -- ---------

Therefore, it can be considered that:

1.Toad Statistics of undo table space utilization is inaccurate;

2.EM Statistics 2 States, the individual think that in the case where the undo Tablespace retention guarantee is not set, as long as the area of the active state can be counted, the SQL statement to statistical undo table space Utilization is as follows:

--Statistic the area occupancy rate of the active state how much undo table space

 withDf as(SELECTTablespace_name,SUM(BYTES) BYTES,COUNT(*) CNT,--number of data files per table spaceDECODE (SUM(DECODE (Autoextensible,'NO',0,1)),0,'NO','YES') Autoext--whether to automatically expand  fromDba_data_filesGROUP  bytablespace_name)SELECTD.tablespace_name,round(NVL (a.bytes/ 1024x768 / 1024x768 / 1024x768,0),1) ALL_TABLESPACE_GB,round(NVL (U.bytes,0)/ 1024x768 / 1024x768 / 1024x768,1) UNDO_TABLESPACE_USED_GB,round(NVL (a.bytes-NVL (U.bytes,0),0)/ 1024x768 / 1024x768 / 1024x768,1) UNDO_TABLESPACE_FREE_GB,round(NVL (u.bytes/A.bytes*  -,0),1) "Undo_tablespace_used_per%", A.autoext, D.status, a.cnt datafile_cnt, D.contents fromdba_tablespaces D,DF A, (SELECTTablespace_name,SUM(BYTES) BYTES--record unavailable Undo segment: Includes zones with active status, not including unexpired and expired      fromdba_undo_extentsWHERESTATUSinch('ACTIVE')--If the retention guarantee attribute is used, the area where the status is unexpired must also be counted: WHERE status in (' ACTIVE ', ' unexpired ') GROUP  byTablespace_name) UWHERED.tablespace_name=A.tablespace_name (+)--LEFT OUTER join andD.tablespace_name=U.tablespace_name (+) andd.contents= 'UNDO'ORDER  by 1

In this way, we can count the actual undo table space (the extent state is active) and find that the UNDOTBS1 table space is idle.

[Oracle]undo Table space usage is 100%