Re: MV logs with Oracle's auto extent control
- From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
- To: tomdaytwo@xxxxxxxxx, oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 05 Oct 2007 23:25:03 +0800
A Truncate doesn't actually do a "drop and recreate". It drops all
extents *after* the first one.
Apparently, your first extent for those MVLog Tables has been
8MB. Why ? Not sure yet.
But here's a suggestion. Try dropping and recreating the MV logs.
(DROP SNAPSHOT LOG ON TABLE <tablename> ; followed by the CREATE
SNAPSHOT LOG ... )
Hemant
At 09:26 PM Friday, Thomas Day wrote:
We're using materialized view logs for data capture. There are no
materialized views being refreshed from these logs. Instead our ETL
team reads the logs (we capture all columns) and duplicates the
insert, update, or delete. After they're done they truncate the logs.
We're also letting Oracle automatically handle the next extent size.
The problem is that the extent size of the single extent remaining
after the truncate is around 8M while the single extent for a newly
created table is 62K.
My question is, is Oracle not resetting the number of times extended
counter when the ML log is truncated? You'd think that a smart
algorythm would notice when the table had been truncated. On the
other hand, we're using this mechanism in a way that Oracle never
intended it to be used.
I know how the auto extension sizing is supposed to work. I'm just
wondering if there's someone on the list who knows enough about the
internals of the algorythm to shed some light on why the MV Log
isn't dropping back to the smallest extent size?
Hemant K Chitale
http://hemantoracledba.blogspot.com
ohandas_gandhi.html
--
//www.freelists.org/webpage/oracle-l
Other related posts:
