Archivelog mysteries

Hi all,

I've noticed a strange occurence on my database this weekend with archive logs growing much, much larger than my database itself.

I'm running 10.2.0.1 on Windows Server 2003. I'm the only one on this weekend running any processes, and the one I'm running is an update to a table in it's own tablespace defined with "NOLOGGING". Once complete, the table will be approximately 11.6 GB, the tablespace itself is 15 GB. The tablespace has an 8k blocksize, and the average size of the XML field is 8.7k.

My program that I'm running takes the first master id and then loops through approximately 30 child tables and populates a field in the table with XML output. This is the only 'change' being made to any table. (Before running the program, I re-created the table by truncating it and then inserting the master id's for all my master records. At this point, the table was approximately 1.7 GB.)

For some reason, this has generated approximately 70 GB of archive logs already, and I'm only about 2/3 of the way through all the records.

Obviously there's a lot more being stored in the archive logs than just the data change, but what? My PL/SQL program is only 12k, so even if it's storing a before and after version of the table's record for each change, and the entire PL/SQL program that changed it, the numbers don't add up.

Any idea why a table that's only 11.6 GB (when complete) would have generated approximately 100 GB of archive logs? It doesn't seem logical to me (at this point), that the archive log data would be approximately 10 times the size of the data.

--

• Bill Ferguson

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