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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Slick tricks for extent size reduction in LMTS via LMTS=>DM=> LMTS ?

RE: Slick tricks for extent size reduction in LMTS via LMTS=>DM=> LMTS ?

From: Mohan, Ross <RMohan_at_arbinet.com>
Date: Wed, 21 Jul 2004 11:41:47 -0400
Message-ID: <CC74E7E10A8A054798B6611BD1FEF4D30625D0A1@vamail01.thexchange.com>


Ray,
Tried. Repeatedly. Gave me ora- error each time I tried to shrink below the previous uniform size. "Invalid Size". No joy.

-----Original Message-----

From: Feighery Raymond [mailto:Raymond.Feighery_at_churchill.com] Sent: Wednesday, July 21, 2004 10:02 AM
To: 'oracle-l_at_freelists.org'
Subject: RE: Slick tricks for extent size reduction in LMTS via LMTS=>DM=> LMTS ? Ross

You can do it in the way your subject line suggests using:

1) dbms_space_admin.tablespace_migrate_from_local
2) alter tablespace (change default storage extent size and minimum extent)
3) dbms_space_admin.tablespace_migrate_to_local

However, already existing tables will retain their existing storage parameters (unless you change them at step 2) and you will have to have a dictionary managed SYSTEM tablespace to change the LMT to dictionary-managed at step 1.

dbms_space_admin.tablespace_migrate_to_local has some side effects. Although you will get the performance benefits of an LMT you will no longer get the policy enforcement of extent sizes (i.e. the ALLOCATION_TYPE column in dba_tablespaces will show as USER not UNIFORM or SYSTEM). Another is that the next extent storage attribute does not get reset on deallocation of extents (this only matters if PCTINCREASE > 0).

Ray

-----Original Message-----

From: Mohan, Ross [mailto:RMohan_at_arbinet.com] Sent: Wednesday, July 21, 2004 12:21 AM
To: oracle-l_at_freelists.org
Subject: Slick tricks for extent size reduction in LMTS via LMTS=>DM=>LMTS ?

I am gathering, painfully, that once you create and partially populate a half terabyte tspace with local ext mgmt extent size 100M there's no way to reduce the extent size to, say, 5M.
Please someone tell me I am wrong.



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org put
'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
Received on Wed Jul 21 2004 - 10:52:01 CDT

Original text of this message

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