Re: Oracle is using lot of UNDO tablespace (Oracle 12c)

Hi Joe,

​​
13.000.000 of lines is part of the table, I cannot execute the truncate.

I created a new tablespace with more space and I will monitor the execution.

Thanks for answers.

Regards
Eriovaldo

2017-01-15 18:40 GMT-02:00 Sweetser, Joe <JSweetser_at_icat.com>:

> 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_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Eriovaldo Andrietta
> *Sent:* Sunday, January 15, 2017 1:34 PM
> *To:* ORACLE-L <oracle-l_at_freelists.org>
> *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_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
>
>
> 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.
>

--
http://www.freelists.org/webpage/oracle-l