RE: Partitions expanding above the HWM
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <troach@xxxxxxxxx>, "'Oracle Discussion List'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 11 Jan 2010 20:46:55 -0500
Okay, ASSM has to do with which block you insert a row into in the already
allocated extents in non-bulk or direct insert operations. But if you're
bumping up form 17G to 640G, that has to do with more extents.
So, what is your style and size? We know you're locally managed since you're
ASSM. Are you system or uniform? What is your blocksize and what is your
size? System can trigger large incremental growth something like a non-zero
percent free. Uniform is uniform, so you can change the size of extents only
at create time.
There was a bug for a while where each session failing to find an eligible
block to insert into asked for a new extent without checking if another
session had already asked for a new extent, so if you had many sessions
inserting concurrently that could trigger a rapid expansion like the
behavior of the universe before Planck time. But I thought that was fixed at
least 18 months ago.
Probably it is worth looking for the support note.
mwf
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Thomas Roach
Sent: Monday, January 11, 2010 5:56 PM
To: Oracle Discussion List
Subject: Partitions expanding above the HWM
4 node RAC - 10.2.0.4
Linux x86_64 RHEL 5.3
Tablespace
ASSM on the Tablespaces (but the table has PCTFREE of 10 and PCTUSED of 0
"which should be ignored because of ASSM."
Reporting Table
Partitioned by hash into 16 partitions.
Degree of parallel is 1.
PCTUSED 0
PCTFREE 10
Staging Table
Partitioned by server name (7 partitions)
Parallelization is enabled
I am having an issue with a table that is just using way too much disk
space. As of today, it was using 640GB. I shrunk it down to about 17GB by
using the move partition command with update indexes. As data is inserted,
it appears to be
I initially thought I had it when I saw PCTUSED 0, but then I found the
tablespace was ASSM, so this value "should" be ignored. I found the package
that loads this table from a staging table.
What happens is this.
"INSERT INTO table SELECT * FROM staging_table"
THEN
"DELETE FROM table WHERE EXISTS"
So, I set up a little test case, and I can't seem to replicate the data.
This is not doing anything with APPEND in this package that does the ETL. I
tested with parallel dml enabled at the session level and that yielded no
results. Does anyone have any ideas what I might be missing? Could this be a
bug with ASSM? Any help would greatly be appreciated as I have banged my
head against the monitor all day (not literally).
Thanks!
Other related posts:
