Hi Richard, Unfortunately "minimum extent" limitations is ignored when using ALTER TABLE DEALLOCATE UNUSED KEEP. A quick example below. Minumum extent size is 320 K, but my table extent is only 48 K after deallocation. A similar problem exists in 10g with the "online segment shrink" feature. SQL> create tablespace test_ts 2 datafile '/tmp/test_ts.dbf' size 10248 K 3 extent management dictionary 4 minimum extent 320 K 5 / Tablespace created. SQL> create table scott.emp2 2 (id number) 3 tablespace test_ts 4 storage (initial 5120 K) 5 / Table created. SQL> select bytes 2 from dba_extents 3 where owner =3D 'SCOTT' 4 and segment_name =3D 'EMP2' 5 / BYTES ---------- 5242880 SQL> select bytes 2 from dba_free_space 3 where tablespace_name =3D 'TEST_TS' 4 / BYTES ---------- 5242880 SQL> alter table scott.emp2 2 deallocate unused keep 40 K 3 / Table altered. SQL> select bytes 2 from dba_extents 3 where owner =3D 'SCOTT' 4 and segment_name =3D 'EMP2' 5 / BYTES ---------- 49152 SQL> select bytes 2 from dba_free_space 3 where tablespace_name =3D 'TEST_TS' 4 / BYTES ---------- 10436608 SQL> exit Regards Jesper Norrevang -----Oprindelig meddelelse----- Fra: oracle-l-bounce@xxxxxxxxxxxxx = [mailto:oracle-l-bounce@xxxxxxxxxxxxx] P=E5 vegne af Richard Foote Sendt: 19. februar 2004 14:56 Til: oracle-l@xxxxxxxxxxxxx Emne: Re: Deallocate Unused Hi Vidya, "deallocate unused" will only deallocate extents up to the size = specified by your initial/minexents storage allocation. "deallocate unused keep blah" allows you to go below your initial = allocation although it won't violate uniform extent size (LMT), minimum extent size (DMT), etc. limitations. Cheers Richard ----- Original Message ----- From: "vidya kalyanaraman" <vidya.kalyanaraman@xxxxxxxxxx> To: <oracle-l@xxxxxxxxxxxxx> Sent: Thursday, February 19, 2004 10:22 PM Subject: Deallocate Unused Hi While alterering the table to deallocate the extents which are above the HWM, we did use the following command. alter table <tab> deallocate unused; Another DBA here feels that appending the DDL with "KEEP' keyword is more beneficial. But he could not give any solid reason for doing = so. alter table <tab> deallocate unused keep 10M; Can any one tell me how these two commands are different and which is more efficient? Scanned through Metalink, but could not find the correct answer for this question. Thanks in Advance Vidya ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------