- 196.8K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 544 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.5K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 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 ?