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

This article focuses on "how to reduce UNDO table space and capacity in Oracle 19c". Interested friends may wish to take a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "how to reduce UNDO table space and capacity in Oracle 19c".

1. Create a new UNDO table space and specify it as the current UNDO table space

SQL > CREATE UNDO TABLESPACE UNDO_TBS DATAFILE'+ DATA' SIZE 1G

SQL > ALTER SYSTEM SET undo_tablespace=UNDO_TBS

In systems with relatively large transactions, specifying a new UNDO tablespace may cause the entire database to be unable to make any changes, and any DDL or DML operations will report errors. If allowed, you can restart the database to solve the problem. After the database restart, the default UNDO tablespace parameters that have just been changed will become invalid or are still old. You should re-specify the new UNDO tablespace in the NOMOUNT state, and then open the database to continue with the following operations.

2. Delete the original UNDO tablespace (usually UNDOTBS1)

SQL > DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES

3. Create a new UNDO table space (UNDOTBS1) with the same name as the original UNDO table space, and specify it as the current UNDO table space, and delete the UNDO table space created in the first step.

SQL > CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE'+ DATA' SIZE 200m AUTOEXTEND ON

SQL > ALTER SYSTEM SET undo_tablespace=UNDOTBS1

SQL > DROP TABLESPACE UNDO_TBS INCLUDING CONTENTS AND DATAFILES

Note: there is no need to restart the database during the whole process. If the database is restarted, the parameter undo_tablespace will become the default value (UNDOTBS1), so our final UNDO tablespace is still called UNDOTBS1.

UNDO tablespaces in Oracle 19c are independent, and CDB and each PDB have their own UNDO tablespaces, so the actual operation is the same as 11g. Here you can see that there are three UNDOTBS1 tablespaces that belong to different CON_ID.

At this point, I believe you have a deeper understanding of "Oracle 19c how to reduce UNDO table space and capacity". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue 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.