Re: Help! Db table reorg problem...
Date: Tue, 8 Jan 2008 14:33:21 -0800 (PST)
One thing to note (although probably have no bearing):
- PCTFREE is not ignored with ASSM segments, that only applies to PCTUSED
and a couple questions:
How were the number of blocks and free blocks derived?
If via dba_tab_partitions, how were the analyzes done in each environment and with what options (analyze, vs dbms_stats). Have you used DBMS_SPACE to look at the real statistics? Were the rows inserted into the new partitions in parallel or serially?
- Original Message ---- From: Ben Wittmeier <Ben.Wittmeier_at_gov.ab.ca> To: mark.powell_at_eds.com; oracle-l_at_freelists.org Sent: Tuesday, January 8, 2008 3:44:27 PM Subject: RE: Help! Db table reorg problem...
Mark - thanks for the reply:
>> What type of space management was used in the old tablespaces verse
the new ones?
>> Dictionary vs auto-allocate? ASSM?
As per the details section, the original tablespace was created as 'NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128M BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' and the new tablespace was created the same way with the poor storage results. A 2nd attempt with tablespace setup as 'NOLOGGING ONLINE PERMANENT EXTENT MANAGEMENT LOCAL AUTOALLOCATE BLOCKSIZE 8K SEGMENT SPACE MANAGEMENT AUTO FLASHBACK ON' resulted in a bit better storage usage but still not close to the original.
>>What do you mean by did not change anything important?
I changed the name of the tablespaces, added new partitions (which should not have increased the size of old partitions) and changed the names of the partitions - none of these should have had any bearing on space usage that I can see. The create table and tablespace statements are basically what the originals were.