Re: UNTO TBS behavior in 9i
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: veeeraman@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 23 Jan 2007 17:28:29 +0800
1. If you are doing multiple commits during the insert run, each
committed batch remains
in UNDO for the duration of 2 hours. You should reduce UNDO_RETENTION.
2. UNDO is maintained for both Table Rows and Index updates -- eg
when you INSERT,
you are adding entries to indexes on the table as well. So UNDO is
maintained for that
as well.
3. You can monitor the size of your transaction by querying
V$TRANSACTION -- that
would show the number of undo "records" (table+index entries) and
undo blocks used
between every commit.
4. You could avoid undo and redo both -- eg
alter table target_table nologging ;
insert /*+ APPEND */ into target_table ....
<finally> commit ; alter table target_table logging;
At 02:19 PM Tuesday, Ram Raman wrote:
Hi all,
We are running a batch process to insert data from one table to
another. The data inserted is over than 10 million rows. We had
about 20GB of UNDO tablespace. UNDO_RETENTION is 7200. When we start
the insert process no other process runs and all the time during the
insert process nothing else runs. We insert about 5.5 Gb of data
including indexes.
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
and
http://hemantoracledba.blogspot.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
