Not exactly sure about your question but if you really want to delete all rows
in the table, using the truncate command should do the trick way better than
the delete command since it won’t write any undo. Which also means it’s a one
way street and not recoverable once you do it. But that appears to fit your
scenario.
Hth,
-joe
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Eriovaldo Andrietta
Sent: Sunday, January 15, 2017 1:34 PM
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Subject: 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
Confidentiality Note: This message contains information that may be
confidential and/or privileged. If you are not the intended recipient, you
should not use, copy, disclose, distribute or take any action based on this
message. If you have received this message in error, please advise the sender
immediately by reply email and delete this message. Although ICAT, Underwriters
at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does
not guarantee that either are virus-free and accepts no liability for any
damage sustained as a result of viruses. Thank you.
