| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> SV: Deallocate Unused
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
SQL> exit
Regards
Jesper Norrevang
-----Oprindelig meddelelse-----
Fra: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] P=E5
vegne af Richard Foote
Sendt: 19. februar 2004 14:56
Til: oracle-l_at_freelists.org
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_at_oracle.com>
To: <oracle-l_at_freelists.org>
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
 |
 |