Partitions expanding above the HWM

From: Thomas Roach <troach_at_gmail.com>
Date: Mon, 11 Jan 2010 17:55:52 -0500
Message-ID: <b86ffce61001111455y5469505dta40a544f2f4b88f4_at_mail.gmail.com>



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!

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 11 2010 - 16:55:52 CST

Original text of this message