Shulou(Shulou.com)06/01 Report--

The management of UNDO tablespaces is one of the most important daily tasks of Oracle DBA. UNDO tablespaces are used to temporarily store data for DML operations, and their main functions are:

1. Transaction rollback

2. Instance recovery

3. Read consistency

4. Flashback

Here are some operations on UNDO tablespaces

1. Check what tablespaces an instance has: select tablespace_name, contents from dba_tablespaces where contents = 'UNDO'

We can also view it with the show parameter undo_tablespace name, and this command can only view the current default UNDO tablespace:

Of course, we can also look at the table space and its corresponding data file: select substr (file_name,1,60) UNDO_FILES from dba_data_files where tablespace_name = 'UNDOTBS1' order by 1

View the size and free space of the UNDO tablespace:

Select * from (select

A.tablespace_name

Sum (a.bytes) / (1024024) total_space_MB

Round (b.freefree2) Free_space_MB

Round (b.free/ (sum (a.bytes) / (1024) (1024)) * 100pion2) percent_free

From dba_data_files a

(select tablespace_name,sum (bytes) / (1024024) free from dba_free_space

Group by tablespace_name) b

Where a.tablespace_name = b.tablespace_name (+)

Group by a. Tablespacehammer name.free)

Where tablespace_name = 'UNDOTBS1'

2. Create a UNDO tablespace:

Create undo tablespace undotbs3 datafile'/ data1/oradata/undotbs03_1.dbf' size 100m autoextend on next 20m maxsize 500m

In the above command, specify the name of the UNDO tablespace, the corresponding data file, the initial size, the automatic extension, the size of each extension, and the maximum extension

Add a data file to the UNDO tablespace:

ALTER TABLESPACE UNDOTBS3 ADD DATAFILE'/ data1/oradata/undotbs03_2.dbf' SIZE 1024M AUTOEXTEND ON NEXT 100M MAXSIZE 2048m

3. Switch the default UNDO tablespace:

Alter system set undo_tablespace = UNDOTBS3

4. Delete unused UNDO tablespaces:

First find out what UNDO tablespaces are available and their names

Then look for the default UNDO tablespace

Then find the data file of the UNDO tablespace to be deleted

Delete unused UNDO tablespaces: drop tablespace UNDOTBS2

This is not over. After the tablespace is deleted in the database, its data file still exists on the physical disk and needs to be deleted:

5. We can view the status of UNDO tablespaces through dba_undo_extents:

There are three states of STATUS: UNEXPIRED, EXPIRED, and ACTIVE

ACTIVE indicates that there are a large number of DML operations writing the UNDO,UNEXPIRED state at this time, which is converted by the data of the ACTIVE state. As for when the UNEXPIRED state is converted to EXPIRED, it is determined by the undo retention of the current system.

We can also view detailed UNDO information through 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') ND_TIME

UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON" FROM v$UNDOSTAT WHERE rownum

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.