RE: [External] Oracle is using lot of UNDO tablespace (Oracle 12c)

12.1 has only Shared Undo (Local Undo in a PDB becomes available in 12.2)

DELETE statements will *always* generate Undo. You cannot disable Undo generation.

When deleting a very large percentage of rows, one method is to

1. Create a new table with the same structure, set the new table to NOLOGGING
2. Use INSERT /*+ APPEND */ to copy the rows to preserve into the new table (INSERT APPEND avoids Undo and the NOLOGGING avoids Redo)
3. Add indexes (CREATE INDEX .. NOLOGGING) to the new table as desired
4. Drop or rename the old table
5. Rename the new table This does require an outage as you can’t do this concurrently with other transactions against the same table.

Hemant K Chitale

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Eriovaldo Andrietta Sent: Monday, January 16, 2017 4:34 AM
To: ORACLE-L
Subject: [External] Oracle is using lot of UNDO tablespace (Oracle 12c)

Hi,

I have a container and I have one pdb in Oracle 12c.

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE  12.1.0.2.0     Production                                                                0
TNS for Linux: Version 12.1.0.2.0 - Production                                            0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

Connected as sysdba and as sysdba_at_instpdb I see these parameters related to undo.

SQL> show parameter undo

NAME                                 TYPE        VALUE


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


temp_undo_enabled                    boolean     FALSE
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2

Note : Look that I don´t see the local_undo_enabled parameter .

I am doing a delete * from table and 7gb of tablespace undo is not being enough. It delete 13.000.000 lines and takes more then 15 minutes. It is not a production environment and I don´t want to save all data to recover in the future.

I would like to know how to check if the UNDO option is on or off and
How to disable the UNDO option in order to Oracle write the minimum in the undo tablespace.

Regards
Eriovaldo

This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html
--

http://www.freelists.org/webpage/oracle-l Received on Tue Jan 17 2017 - 04:32:44 CET