Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Get back the allocated space for the table.

Re: Get back the allocated space for the table.

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Fri, 23 Aug 2002 16:09:15 +1000
Message-ID: <h4k99.12559$g9.40964@newsfeeds.bigpond.com>


Hi M. Balaji,

I've read this a couple of times but I'm still not sure I've got it so bear with me.

I'm not sure whether "the delete of the rows belonging to a particular branch" means the branch partition is now emptied or not. If it is, then you can simply drop the empty partition and return the extents to the tablespace.

If there are still some rows remaining in a partition but substantially less than before and you have no intention of reinserting more rows into the particular branch partition, then you could move the partition to defragment it (ALTER TABLE big_bowie MOVE PARTITION near_empty TABLESPACE wherever). You could also possibly merge the near empty partition with a fuller neighbouring partition.

Basically, if you want to deallocate space below the HWM, the partition needs to be recreated somehow. If you want to deallocate space above the HWM, then the DEALLOCATE UNUSED clause could be the go.

Hope this helps. If it doesn't, maybe if only for my somewhat slow witted benefit, if you could just re-explain exactly what the problem is, I might get closer to the mark.

Cheers

Richard

"M.Balaji" <muni_balaji_at_yahoo.com> wrote in message news:3ce48062.0208221951.7efbf67b_at_posting.google.com...
> Hi,
> We are working on a Datawarehousing Application. Where the database
> is partitioined based on the branch code. We have a seperated
> tablespace for each branch and when the rows inserted into the table,
> the data goes and sits in the corresponding partition based on the
> branch.
> Now the problem is after i delete the rows belonging to the
> particular branch, the space taken for the data is still allocated and
> it is not freed.How do i get the disk space back.
>
> Table Name :ME_CUST_AC_MASTER
> Object Type :TABLE PARTITION
> Tablespace Name :ME_DTT799
> Size Occupied :5.72
> Initial Extent :131072
> Next Extent :131072
> Extents Allocated :42
> Total Size of the Ext:6000640
> Max Extents :2147483645
>
> 1 select br_code, version_date ,count(*)
> 2 from me_cust_ac_master
> 3* group by br_code,version_date
>
> BR_C VERSION_ COUNT(*)
> ---- -------- ----------
> 0777 20020630 24896 - This is the data for another branch and
> tablespace is dtts777
>
>
> Pls help me to solve this problem
> Thanks and Regards
> M.Balaji
Received on Fri Aug 23 2002 - 01:09:15 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US