- 382.6K All Categories
- 2.1K Data
- 213 Big Data Appliance
- 1.9K Data Science
- 448.5K Databases
- 221.1K General Database Discussions
- 25 Multilingual Engine
- 542 MySQL Community Space
- 469 NoSQL Database
- 7.8K Oracle Database Express Edition (XE)
- 2.9K ORDS, SODA & JSON in the Database
- 503 SQLcl
- 3.9K SQL Developer Data Modeler
- 186.3K SQL & PL/SQL
- 21.1K SQL Developer
- 294K Development
- 9 Developer Projects
- 131 Programming Languages
- 290.7K Development Tools
- 97 DevOps
- 3K QA/Testing
- 645.6K Java
- 24 Java Learning Subscription
- 36.9K Database Connectivity
- 151 Java Community Process
- 104 Java 25
- 22.1K Java APIs
- 137.9K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 17 Java Essentials
- 147 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
- 201 Java User Groups
- 301 LiveLabs
- 36 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 168 Deutsche Oracle Community
- 1.2K Español
- 1.9K Japanese
- 235 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 ?