Thanks Jesper, yet another one to add to the list of LMT benefits.
Thanks again for the heads up.
Cheers
Richard
----- Original Message -----
From: "Jesper Haure Norrevang" <jhn.aida@xxxxxx>
To: <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, February 19, 2004 11:18 PM
Subject: 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
----------
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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
