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

This article mainly introduces how to monitor and manage Oracle UNDO tablespaces, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, let the editor take you to understand it.

The monitoring and management of UNDO tablespaces in Oracle databases is one of our most important daily tasks. UNDO tablespaces are usually managed automatically by Oracle (determined by undo_management initialization parameters). UNDO tablespaces are used to store pre-mirror data for DML operations, and it is an important component of instance recovery, data rollback and consistent query features. We often ignore its monitoring, which may lead to the following problems in UNDO tablespaces:

1)。 The space utilization rate is 100%, which makes the DML operation impossible.

2)。 There are a large number of ORA-01555 alarm errors in the alarm log.

3)。 Instance recovery failed and the database could not be opened properly.

one。 Automate the management of UNDO for Oracle

Because UNDO is managed automatically, there are very few places that can be intervened, and more are monitored. Some intervention can be implemented on UNDO tablespaces through the following places:

1)。 Initialization parameter

Undo_management=AUTO means that the instance automates the management of UNDO tablespaces. Starting with Oracle 9i, Oracle introduced AUM (Automatic Undo Management).

The time in seconds that the corresponding UNDO data is retained after the undo_retention=900 transaction is committed.

The UNDO table space for the undo_tablespace=UNDOTBS1 activity.

_ smu_debug_mode=33554432

_ undo_autotune=TRUE

2). Automatic UNDO Retention

Automatic UNDO Retention is a new feature of 10g, which is enabled by default in 10g and later versions of the database.

In Oracle Database 10g, when automatic undo management is enabled, there is always a current undo retention,Oracle Database attempt to retain at least old undo information until that time. The database collects usage statistics and adjusts the time of the UNDO based on these statistics and the size of the undo retention tablespace.

Oracle Database automatically adjusts undo retention based on undo tablespace size and system activity, and specifies the minimum value of undo retention by setting UNDO_RETENTION initialization parameters.

To view the value of Oracle automatically adjusting UNDO RETENTION, you can obtain it from the following query:

SELECT TO_CHAR (BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,TUNED_UNDORETENTION FROM V$UNDOSTAT

For the automatically expanded UNDO table space, the system retains the UNDO at least until the time specified by the parameter, and automatically adjusts the UNDO RETENTION to meet the query's requirements for UNDO, which may lead to the rapid expansion of UNDO, so we can consider not setting the UNDO recovery value.

For fixed UNDO tablespaces, the system automatically adjusts to the maximum possible undo retention, and the reference adjusts based on UNDO tablespace size and usage history, which ignores UNDO_RETENTION unless the tablespace is enabled for RETENTION GUARANTEE.

Auto-tuning undo retention does not support LOB because no UNDO information about LOBs transactions can be stored in the undo tablespace.

You can turn off the Automatic UNDO Retention function by setting _ undo_autotune=FALSE.

3). The large value calculated by TUNED_UNDORETENTION leads to the rapid growth of UNDO tablespace.

When the UNDO tablespace used does not grow automatically, the tuned_undoretention is calculated based on the utilization of the UNDO tablespace size, which will calculate a larger value in some cases, especially for larger UNDO tablespaces.

To resolve this behavior, set the following instance parameters:

_ smu_debug_mode=33554432

By setting this parameter, TUNED_UNDORETENTION is not calculated based on the utilization of the undo tablespace size, but instead the setting (MAXQUERYLEN + 300) and the maximum value of UNDO_RETENTION.

4). Automatic extension of UNDO tablespace data files

If the UNDO tablespace is an automatically extended tablespace, it is likely that EXTENT with the UNDO tablespace status of EXPIRED will not be used (to reduce the chance of ORA-01555 errors), which will cause the UNDO tablespace to become very large If the UNDO tablespace is set to non-automatic extension, the EXTENT with the state of EXPIRED can be utilized, which can control the size of the UNDO tablespace to some extent, but this increases the risk of ORA-01555 errors and UNDO space underreporting errors. Reasonable non-auto-expanding UNDO table space size and reasonable UNDO_RETENTION settings can ensure stable UNDO space usage.

5). UNDO tablespace guarantee property

If the UNDO tablespace is in noguarantee state, Oracle does not guarantee that the data in the UNDO tablespace corresponding to the committed transaction will retain the length of time specified by UNDO_RETENTION. If the UNDO tablespace is insufficient, other transactions may steal the corresponding unexpired space; setting the UNDO tablespace to guarantee ensures that the data in the committed transaction corresponding to the UNDO tablespace will retain the length of time specified by UNDO_RETENTION in any case.

SQL > SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1'

TABLESPACE_NAME RETENTION

-

UNDOTBS1 NOGUARANTEE

SQL > alter tablespace undotbs1 retention guarantee

The tablespace has changed.

SQL > SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1'

TABLESPACE_NAME RETENTION

-

UNDOTBS1 GUARANTEE

6). UNDO tablespace size

For different types of business systems, we need to have sufficient UNDO table space to ensure that the system can run normally. The size of UNDO space is related to the business system as well as the GUARANTEE attribute of UNDO_RETENTION and UNDO tablespaces. Usually, we can estimate the required UNDO tablespace size from the statistical information of V$UNDOSTAT.

two。 Monitor UNDO tablespace usage.

As an administrator, what is more important for UNDO tablespaces is the daily monitoring work, which is commonly used in the following views:

A). DBA_ROLLBACK_SEGS

DBA_ROLLBACK_SEGS describes rollback segments.

B). V$ROLLSTAT

V$ROLLSTAT contains rollback segment statistics.

C) .V$TRANSACTION

V$TRANSACTION lists the active transactions in the system.

D) .V$UNDOSTAT

V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns NULL values if the system is in manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 576 rows, spanning a 4 day cycle.

E). DBA_UNDO_EXTENTS

DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database. This view shows the status and size of each extent in the undo tablespace.

DBA_UNDO_EXTENTS.STATUS has three values:

ACTIVE represents the UNDO EXTENT that the uncommitted transaction is still using. The DBA_ROLL_SEGMENTS.STATUS of the UNDO SEGMENT corresponding to this value must be ONLINE or PENDING OFFLINE state. Once no active transaction is using UNDO SEGMENT, then the corresponding UNDO SEGMENT becomes OFFLINE state.

EXPIRED represents a UNDO EXTENT that has been submitted and has exceeded the time specified by UNDO_RETENTION.

UNEXPIRED represents a UNDO EXTENT that has been submitted but has not exceeded the time specified by UNDO_RETENTION.

The principles for Oracle to reuse UNDO EXTENT are as follows:

1). EXTENT in the active state will not be occupied under any circumstances.

2)。 In the case of automatically extended UNDO tablespaces, Oracle ensures that EXTENT retains at least the time specified by UNDO_RETENTION.

3)。 If the automatic extension space is insufficient or the UNDO table space is not automatically extended, Oracle will try to reuse the EXTENT of the EXPIRED status under the same segment. If there is no such EXTENT in this segment, it will steal the EXTENT of the EXPIRED status under the other segment. If there is still no such EXTENT, the EXTENT of the UNEXPIRED of this segment will be used. If not, the EXTENT of the UNEXPIRED of the other segment will be stolen. If there is no such EXTENT, an error will be reported.

1.UNDO tablespace usage.

1). The total size of the UNDO table space.

Data is also stored as segments under the UNDO tablespace, with one segment for each transaction. This type of segment is often referred to as a rollback segment, or UNDO segment. By default, the database instance initializes 10 UNDO segments, mainly to avoid contention for UNDO segments by newly generated transactions.

The total size of the UNDO tablespace is the sum of all the datafile sizes under the UNDO tablespace:

SQL > select tablespace_name,contents from dba_tablespaces where tablespace_name='UNDOTBS1'

TABLESPACE_NAME CONTENTS

UNDOTBS1 UNDO

SQL > select tablespace_name,sum (bytes) / 1024 plus 1024 mb from dba_data_files where tablespace_name='UNDOTBS1' group by tablespace_name

TABLESPACE_NAME MB

UNDOTBS1 90

2)。 View the usage of UNDO tablespaces.

This usage can be viewed in three views:

SQL > select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS1'

OWNER SEGMENT_NAME MB

SYS _ SYSSMU12_2867006942 $.125

SYS _ SYSSMU11_3120896088 $.125

SYS _ SYSSMU10_1735367849 $2.125

SYS _ SYSSMU9_3051513041 $2.125

SYS _ SYSSMU8_2280151962 $2.125

SYS _ SYSSMU7_825858386 $.9375

SYS _ SYSSMU6_2597279618 $3.125

SYS _ SYSSMU5_247215464 $3.125

SYS _ SYSSMU4_437228663 $2.125

SYS _ SYSSMU3_3104504842 $5.125

SYS _ SYSSMU2_2464850095 $2.125

SYS _ SYSSMU1_2523538120 $3.125

12 rows have been selected.

Select sum (bytes) / 1024 Universe 1024 mb from dba_segments where tablespace_name='UNDOTBS1'

MB

-

25.4375

SQL > select segment_name, v.rssize/1024/1024 mb

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn (+)

Order by segment_name

SEGMENT_NAME MB

SYSTEM. 3671875

_ SYSSMU10_1735367849 $2.1171875

_ SYSSMU11_3120896088 $

_ SYSSMU12_2867006942 $

_ SYSSMU1_2523538120 $3.1171875

_ SYSSMU2_2464850095 $2.1171875

_ SYSSMU3_3104504842 $5.1171875

_ SYSSMU4_437228663 $2.1171875

_ SYSSMU5_247215464 $3.1171875

_ SYSSMU6_2597279618 $3.1171875

_ SYSSMU7_825858386 $.9296875

_ SYSSMU8_2280151962 $2.1171875

_ SYSSMU9_3051513041 $2.1171875

13 lines have been selected.

Through the above three queries, we can see that the values of the two views are almost the same. Usually, when patrolling, we are used to querying dba_segments views to determine the usage of UNDO tablespaces, but querying V$ROLLSTAT data is more accurate.

3)。 Query the UNDO segment and size used by the transaction.

Many customers want to know which session transactions take up more than 90% of my UNDO table space:

SQL > select s.sidwery s.serialpaperpapers.sqltrainidrecoveryv.usn.segmentationnamerecoveryr. Status, v.rssize/1024/1024 mb

From dba_rollback_segs r, v$rollstat vMagnee transaction tpgle vandalism session s

Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr

Order by segment_name

SID SERIAL# SQL_ID USN SEGMENT_NAME STATUS MB

8 163 5 _ SYSSMU5_247215464 $ONLINE 3.1171875

Through this SQL statement, you can query the name of the UNDO segment used by the active transaction corresponding to the session, and the size of the UNDO space occupied by this segment. The UNDO space occupied by the inactive transaction is automatically managed by the Oracle instance according to the parameter configuration.

two。 Adjust the UNDO parameters and size based on Oracle statistics for UNDO tablespaces.

Finally, we would like to talk about the V$UNDOSTAT view, which is used to guide the administrator to adjust the parameters and size of the UNDO table space. Each row represents 10 minutes of data, with a maximum of 576 rows and a 4-day cycle. If there is no data in this view, then UNDO may be managed manually. The meaning of the view field is explained below:

BEGIN_TIMEDATEIdentifies the beginning of the time interval interval start time. The end of the END_TIMEDATEIdentifies the end of the time interval interval. UNDOTSNNUMBERRepresents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported. The number of UNDO tablespaces active during the interval, which returns the ID number of the active UNDO tablespace. If it is larger than 1 active UNDO tablespace, the UNDO tablespace ID number activated at the end of the interval will be reported. UNDOBLKSNUMBERRepresents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system. Represents the total number of UNDO blocks consumed, and you can use this field to obtain the consumption ratio of undo blocks to estimate the size of the UNDO tablespace required to handle the system load. The total number of transactions executed by TXNCOUNTNUMBERIdentifies the total number of transactions executed within the period during this period. MAXQUERYLENNUMBERIdentifies the length of the longest query (in seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter. The length of a query is measured from the cursor open time to the last fetch/execute time of the cursor. Only the length of those cursors that have been fetched/executed during the period are reflected in the view. The maximum query time (in seconds) executed by the instance during this period, which can be used to estimate the approximate value of the UNDO_RETENTION initialization parameters. The time of the query is accurate to the time from the cursor opening to the last fetch / execution. Only when the query time of these cursors is extracted / executed during this period can it be reflected in this view. MAXQUERYIDVARCHAR2 (13) SQL identifier of the longest running SQL statement in the period the identifier of the SQL statement that has been running for the longest time during this period. The maximum number of transactions that MAXCONCURRENCYNUMBERIdentifies the highest number of transactions executed concurrently within the period executed in parallel during this period. The number of unexpired undo space intervals that UNXPSTEALCNTNUMBERNumber of attempts to obtain undo space by stealing unexpired extents from other transactions attempts to steal from other transactions. UNXPBLKRELCNTNUMBERNumber of unexpired blocks removed from certain undo segments so they can be used by other transactions removes unexpired blocks from some UNDO segments, which are used for other transactions. The number of unexpired undo blocks reused by UNXPBLKREUCNTNUMBERNumber of unexpired undo blocks reused by transactions transactions. EXPSTEALCNTNUMBERNumber of attempts to steal expired undo blocks from other undo segments attempts to steal the number of expired UNDO blocks from other UNDO segments. The number of expired UNDO blocks that EXPBLKRELCNTNUMBERNumber of expired undo blocks stolen from other undo segments stole from other UNDO segments. The number of expired UNDO blocks that EXPBLKREUCNTNUMBERNumber of expired undo blocks reused within the same undo segments reuses in the same UNDO segment. SSOLDERRCNTNUMBERIdentifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error. Identifies the number of ORA-01555 errors that occur, and you can use this statistic to determine whether the UNDO_RETENTION initialization parameter is set for a given UNDO table space. Increasing the value of UNDO_RETENTION reduces the occurrence of this error. NOSPACEERRCNTNUMBERIdentifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace. In the case of no free space activity in the UNDO tablespace, the number of space requests, and all UNDO tablespace spaces are used by active transactions, which requires adding more space to the UNDO tablespace. ACTIVEBLKSNUMBERTotal number of blocks in the active extents of the undo tablespace for the instance at the sampled time in the period the number of blocks in the active interval of the UNDO tablespace for this instance. UNEXPIREDBLKSNUMBERTotal number of blocks in the unexpired extents of the undo tablespace for the instance at the sampled time in the period the number of blocks in the UNDO tablespace that have not expired for this instance during the interval. EXPIREDBLKSNUMBERTotal number of blocks in the expired extents of the undo tablespace for the instance at the sampled time in the period the number of blocks in the expiration interval of the UNDO tablespace for this instance. TUNED_UNDORETENTIONNUMBERAmount of time (in seconds) for which undo will not be recycled from the time it was committed. At any point in time, the latest value of TUNED_UNDORETENTION is used to determine whether data committed ata particular time in the past can be recycled. The total time (in seconds) that the UNDO cannot be reclaimed after submission.

Here is an example of querying V$UNDOSTAT:

SELECT TO_CHAR (BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME

TO_CHAR (END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME

UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON"

MAXQUERYLEN, TUNED_UNDORETENTION

FROM v$UNDOSTAT

Usually when the fields UNXPSTEALCNT and EXPBLKREUCNT are non-zero values, it indicates that there is space pressure.

If the field SSOLDERRCNT is a non-zero value, the UNDO_RETENTION setting is unreasonable.

If the field NOSPACEERRCNT is a non-zero value, it indicates a series of space problems.

V$UNDOSTAT snapshot statistics are included in the 10g DBA_HIST_UNDOSTAT view.

Note: if the parameter _ undo_autotune=FALSE,X$KTUSMST2 will have no data generation, this table is the source table for the DBA_HIST_UNDOSTATS view.

three。 Free up UNDO tablespaces.

UNDO tablespaces are overstretched, and sometimes we need to release them, usually by creating a new UNDO, then setting up to use the newly created UNDO tablespace, and finally DROP the original UNDO tablespace. Here is an example to demonstrate this process:

SQL > col segment_name format A30

SQL > col tablespace_name format A30

SQL >

SQL > select segment_name, tablespace_name, r.status

(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent

Max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn (+)

Order by segment_name

SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT

-- --

SYSTEM SYSTEM ONLINE 112 56 32765 4

_ SYSSMU10_1735367849 $UNDOTBS1 ONLINE 128 64 32765 2

_ SYSSMU11_3120896088 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU12_2867006942 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU1_2523538120 $UNDOTBS1 ONLINE 128 64 32765 2

_ SYSSMU2_2464850095 $UNDOTBS1 ONLINE 128 64 32765 2

_ SYSSMU3_3104504842 $UNDOTBS1 ONLINE 128 64 32765 2

_ SYSSMU4_437228663 $UNDOTBS1 ONLINE 128 64 32765 2

_ SYSSMU5_247215464 $UNDOTBS1 ONLINE 128 64 32765 3

_ SYSSMU6_2597279618 $UNDOTBS1 ONLINE 128 64 32765 3

_ SYSSMU7_825858386 $UNDOTBS1 ONLINE 128 64 32765 9

_ SYSSMU8_2280151962 $UNDOTBS1 ONLINE 128 64 32765 3

_ SYSSMU9_3051513041 $UNDOTBS1 ONLINE 128 64 32765 2

13 lines have been selected.

All current rollback segments belong to the UNDOTBS1 tablespace.

SQL > create undo tablespace undotbs2 datafile'e:\ APP\ ORADATA\ ORCL3\ undotbs02.dbf' size 20m autoextend on next 100m

The tablespace has been created.

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

SQL > alter system set undo_tablespace='UNDOTBS2'

The system has changed.

SQL > show parameter undo

NAME TYPE VALUE

-

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS2

SQL > select segment_name, tablespace_name, r.status

(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent

Max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn (+)

Order by segment_name

SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT

-- --

SYSTEM SYSTEM ONLINE 112 56 32765 5

_ SYSSMU10_1735367849 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU11_3120896088 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU12_2867006942 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU13_3398750080 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU14_3208386744 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU15_2082453576 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU16_2746861185 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU17_3752120760 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU18_3475721077 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU19_1407063349 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU1_2523538120 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU20_910603223 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU21_1261247597 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU22_1117177365 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU2_2464850095 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU3_3104504842 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU4_437228663 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU5_247215464 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU6_2597279618 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU7_825858386 $UNDOTBS1 ONLINE 128 64 32765 9

_ SYSSMU8_2280151962 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU9_3051513041 $UNDOTBS1 OFFLINE 128 64 32765

23 lines have been selected.

Although the UNDO tablespace used by the database instance points to the new tablespace, there are still past transactions using segments under the UNDOTBS1 tablespace. At this time, you can't DROP UNDOTBS1 directly (even if you execute the DROP command), you must wait for all segments under the UNDOTBS1 tablespace to become OFFLINE before DROP.

SQL > select segment_name, tablespace_name, r.status

(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent

Max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn (+)

Order by segment_name

SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT

-- --

SYSTEM SYSTEM ONLINE 112 56 32765 5

_ SYSSMU10_1735367849 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU11_3120896088 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU12_2867006942 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU13_3398750080 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU14_3208386744 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU15_2082453576 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU16_2746861185 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU17_3752120760 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU18_3475721077 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU19_1407063349 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU1_2523538120 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU20_910603223 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU21_1261247597 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU22_1117177365 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU2_2464850095 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU3_3104504842 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU4_437228663 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU5_247215464 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU6_2597279618 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU7_825858386 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU8_2280151962 $UNDOTBS1 OFFLINE 128 64 32765

_ SYSSMU9_3051513041 $UNDOTBS1 OFFLINE 128 64 32765

23 lines have been selected.

All segments under the UNDOTBS1 tablespace become OFFLINE, which allows DROP UNDOTBS1 to free up space.

SQL > drop tablespace undotbs1 including contents and datafiles

The tablespace was deleted.

Although it can DROP, it just means that no transaction is using the old UNDO tablespace, which does not mean that all UNDO EXTENT has expired (DBA_UNDO_EXTENTS.STATUS). If some queries need to use these expired or unexpired EXTENT stored on the old UNDO tablespace, you will receive an error from ORA-01555.

SQL > select segment_name, tablespace_name, r.status

(initial_extent/1024) InitialExtent, (next_extent/1024) NextExtent

Max_extents, v.curext CurExtent

From dba_rollback_segs r, v$rollstat v

Where r.segment_id = v.usn (+)

Order by segment_name

SEGMENT_NAME TABLESPACE_NAME STATUS INITIALEXTENT NEXTEXTENT MAX_EXTENTS CUREXTENT

-- --

SYSTEM SYSTEM ONLINE 112 56 32765 5

_ SYSSMU13_3398750080 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU14_3208386744 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU15_2082453576 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU16_2746861185 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU17_3752120760 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU18_3475721077 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU19_1407063349 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU20_910603223 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU21_1261247597 $UNDOTBS2 ONLINE 128 64 32765 0

_ SYSSMU22_1117177365 $UNDOTBS2 ONLINE 128 64 32765 0

Thank you for reading this article carefully. I hope the article "how to monitor and manage Oracle UNDO tablespaces" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.