Oracle tablespace query and maintenance command Daquan II (undo tablespace), oracleundo

Oracle tablespace query and maintenance command Daquan II (undo tablespace), oracleundo
-- Undo tablespace Summary
-- View All tablespace names
Select name from v $ tablespace;
-- Create a new UNDO tablespace and set automatic expansion parameters;
Create undo tablespace undotbs2 datafile 'd: \ ORACLE \ PRODUCT \ 10.1.0 \ ORADATA \ ORCL \ UNDOTBS02.DBF 'size 10 m reuse autoextend on next 100 m maxsize unlimited;
-- Note: In the OPEN state, only one UNDO tablespace can be used at some time. To use a new tablespace, you must switch to the tablespace:
Alter system set undo_tablespace = undo2;
-- Change to automatic management
Alter system set undo_management = auto scope = spfile;

Change the tablespace Management Mode of undo to manual.
Alter system set undo_management = manual scope = spfile;
-- Modify

-- Wait for all undo segment offlines of the original UNDO tablespace;
Select usn, xacts, status, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks from v $ rollstat order by rssize;
-- Delete a tablespace
Drop tablespace undo1 including contents and datafiles;
-- Change the size of the uodo tablespace
Alter database datafile '/u2/oradata/spring/undotbs01.dbf'

Resize 1024 M;

Uodo is a roll-back data that can be reused. to roll back data for as long as possible, increase the undo tablespace.

Next, explain the temporary tablespace

Oracle decides the size of the UNDO tablespace

The UNDO tablespace is used to store UNDO data. When you perform the DML operation, oracle writes the old data of these operations to the UNDO segment to ensure rollback or consistent read, temporary tablespace is mainly used to query and store some buffer data. You have heard that UNDO is also a temporary table, probably because neither of the two tablespaces saves data permanently.
-------------------------------------------------------------------------
Oracle undo tablespace

The undo tablespace is used to store undo data. When you perform DML operations (insert, update, delete), oracle writes the old data of these operations to the undo segment.

Functions of undo data

1. roll back the transaction

After the DML operation is executed to modify the data, the old data is stored in the undo segment. Old data can be rolled back if the data is submitted, the rollback segment is not full, or the rollback segment is time-out.

2. read consistency

Before the data operated through DML is submitted, the data read by other users is old data in the rollback segment.

Use undo Parameters

1. undo_management

This initialization parameter is used to specify the undo data management mode. If you want to use the automatic management mode, you must set it to auto. If you want to use the manual management mode, you must set this parameter to manual. When you use the automatic management mode, oracle uses the undo tablespace management, in manual management mode, oracle uses rollback segments to manage undo data. Note that when the automatic management mode is used, if the initialization parameter UNDO_TABLESPACE is not configured, oracle will automatically select the first available UNDO tablespace to store UNDO data. If there is no available UNDO tablespace, oracle uses the SYSTEM rollback segment to store UNDO records and records warnings in the ALTER file.

2, UNDO_TABLESPACE

This initialization parameter is used to specify the UNDO tablespace to be used by the routine. When using the automatic UNDO management mode, you can configure this parameter to specify the UNDO tablespace to be used by the routine.

In the RAC (Real Application Cluster) structure, because an UNDO tablespace cannot be used by multiple routines at the same time, each routine must be configured with an independent UNDO tablespace.

3, UNDO_RETENTION

This initialization parameter is used to control the maximum retention time of UNDO data. Its default value is 900 seconds. From 9i, you can specify the retention time of undo data by configuring this initialization parameter, so as to determine the earliest time point that can be viewed by the Flashback Query feature (Flashback Query.

Manage rollback segments manually:

SQL> show parameter undo;

NAME TYPE VALUE

-----------------------------------------------------------------------------

Undo_management string AUTO

Undo_retention integer 900

Undo_tablespace string UNDOTBS1

SQL> show parameter transactions;

NAME TYPE VALUE

------------------------------------ ---------- ...... The remaining full text>

Oracle UNDO tablespace and temporary tablespace

First, it is certainly not a concept. The UNDO tablespace is used to store UNDO data. When a DML operation is executed, oracle will write the old data of these operations to the UNDO segment, to ensure rollback or consistent reading, the temporary tablespace is mainly used for querying and storing some buffer data. You have heard that UNDO is also a temporary table, probably because neither of the two tablespaces will store data permanently.