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
a. Create a new table with the same structure, set the new table to
NOLOGGING
b. Use INSERT /*+ APPEND */ to copy the rows to preserve into the new
table (INSERT APPEND avoids Undo and the NOLOGGING avoids Redo)
c. Add indexes (CREATE INDEX .. NOLOGGING) to the new table as desired
d. Drop or rename the old table
e. 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@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
