Wide Undo Tablespace

[marciob]

Environment Information: Oracle 9i
* Oracle version: 9i
* Operating system: Solaris 10 (UNIX)


Sirs,
I am deleting data from a table that is very large, and with that, it ends up the space on the Undo tablespace. She is cleaning but it takes a day to the other practically. I would like to know how I can force the cleaning of this tablespace undo.


I'm waiting

[diegolenhardt]

Undo does not need to clean, according to the UNDO_RETETION parameter if it is configured it will eliminate,

what you can do is, if you are cleaning the entire table without specifying clause WHERE, is to change the Instruction for Truncate Table, which will not generate undo.

Select all

 
TRUNCATE TABLE SUATABELA; 

See the possibility of by the table in Nologging.

[marciob]

Well the command I'm using is as follows:

Select all

delete EMAIL_MESSAGES WHERE EXISTS (SELECT ID FROM EMAIL_MESSAGES_OLD) AND ROWNUM <= 1000000 and create_date <= '31/12/2009 23:59;59'; 
 
commit;

Can I use Truncate?

I await.

[diegolenhardt]

Can not, could do a loop in the table, and then go excluding 10 thousand in 10 thousand, because then you would be committing 10 thousand in 10 thousand, so your undo has not grown for the control, this if the guarantee of retention of your Undo tablespace is disabled,

If you are in the Undo retention policy, decrease the time for the Retetion Undo, standard 900 seconds, 15 minutes,

[marciob]

Is what is happening he is retaining and for a period of 5 hours, that is, 18000 minutes

[diegolenhardt]

You can change to not guarantee retention, then you can by fixed size on the Undo tablespace.

[CarlosStetner]

Friend as the undo being full, it does not mean that you can not use it.

As Oracle said to try to keep the information in the Undo according to the value of the retention, but he tries, if any other process needs oracle releases what he was guarding because of the retention.

As you are in 9i, it is sure that Oracle does not guarantee the retention, already in 10g to configure for retention to be guaranteed, but as you are in 9i can continue to perform your independent process From free space in undo.