- 3,741,452 Users
- 2,248,430 Discussions
- 7,861,817 Comments
- 381.2K All Categories
- 2.1K Data
- 204 Big Data Appliance
- 1.9K Data Science
- 446.6K Databases
- 220.6K General Database Discussions
- 23 Multilingual Engine
- 511 MySQL Community Space
- 462 NoSQL Database
- 7.7K Oracle Database Express Edition (XE)
- 2.8K ORDS, SODA & JSON in the Database
- 448 SQLcl
- 3.9K SQL Developer Data Modeler
- 185.6K SQL & PL/SQL
- 20.8K SQL Developer
- 291.8K Development
- 7 Developer Projects
- 117 Programming Languages
- 288.6K Development Tools
- 94 DevOps
- 3K QA/Testing
- 645.3K Java
- 18 Java Learning Subscription
- 36.9K Database Connectivity
- 150 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.7K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 12 Java Essentials
- 141 Java 8 Questions
- 85.9K Java Programming
- 79 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.2K Java SE
- 13.8K Java Security
- 197 Java User Groups
- 193 LiveLabs
- 34 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 165 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 225 Portuguese
How to deactivate the UNDO and REDO ?
I have some performance issues due to UNDO logs.
I am working under Oracle DB 12.2 and OLE 7.4
I have a UNDO tablespace at the moment of 9 GB it is increasing all the time as it is managed by Oracle.
The tablespace for data where the tables are saved is set to NOLOGING.
The big inserts are used with the hint /*+ APPEND */ on GTT although I do not deactivate and reactivate the Indexes. does this have an impact on the UNDO or only on performance ?
I have a lot of INSERT of 1 row only but it should not be generate a huge Undo
I have modified the DELETE statement by TRUNCATE
I have UPDATE that updates between 10 and 200 rows on tables that have between 500 and 4000 rows. In my opinion this should not require a UNDO tablespace of 9 GB.
As I was getting a lot of expired undo I have increased the default value of undo retention from 900s (15 mins) to 20027s (5.5 hours)
alter system set "undo_retention"=20027 scope=both sid='*';
- How can I identify the SQL statements that are creating so big UNDOs ?
- Is is possible to deactivate this functionality as I do not need to recover the DB all the data is calculated and loaded at any time I needed in case of failure ?
- If it is not possible to deactivate it what other modifications can I do in my code to avoid creating UNDO ?