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: Powell, Mark D <mark.powell_at_eds.com>
Date: Wed, 21 Jul 2004 12:42:22 -0400
Message-ID: <564DE4477544D411AD2C00508BDF0B6A2133DE14@usahm018.exmi01.exch.eds.com>


But is the resulting tablespace defined as locally managed with uniform extents? The allocation_type column of dba_tablespaces appears to be either USER which means dictionary managed or null where normally it should show UNIFORM for a locally managed tablespace with uniform extents.

I do not have a test db available to use for testing right now but any process that results in my dictionary views not returning normal information makes me concerned that the process is not doing something correctly or that I misunderstood what I was looking at.

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

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Feighery Raymond Sent: Wednesday, July 21, 2004 12:09 PM
To: 'oracle-l_at_freelists.org'
Subject: RE: Slick tricks for extent size reduction in LMTS via LMTS=>DM=> LMTS ? Version: 9.2.0.4
Platform: Solaris 8

Test Case:
Start out with uniform extent size of 20M and reduce to 1M


sys_at_ORCL9I.UK.X.CH> create tablespace lmt_test datafile '/tmp/lmt_test_01.dmp' size 100m
  2 extent management local
  3 uniform size 20M;

Tablespace created.

sys_at_ORCL9I.UK.X.CH> select      tablespace_name,
  2                  initial_extent,
  3                  next_extent,
  4                  min_extlen,
  5                  extent_management,
  6                  allocation_type

  7 from dba_tablespaces where tablespace_name = 'LMT_TEST';
TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTLEN
EXTENT_MAN ALLOCATIO
------------------------------ -------------- ----------- ----------
sys_at_ORCL9I.UK.X.CH> create table lmt_test_tab (col1 varchar2(10))   2 tablespace lmt_test;

Table created.

sys_at_ORCL9I.UK.X.CH> select      segment_name,
  2                  bytes,
  3                  blocks,
  4                  extents,
  5                  initial_extent,
  6                  next_extent

  7 from dba_segments
  8 where segment_name = 'LMT_TEST_TAB';

SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
--------------- ---------- ---------- ---------- -------------- -----------
LMT_TEST_TAB 20971520 2560 1 20971520 20971520 sys_at_ORCL9I.UK.X.CH> exec
dbms_space_admin.tablespace_migrate_from_local('LMT_TEST');

PL/SQL procedure successfully completed.

sys_at_ORCL9I.UK.X.CH> alter table lmt_test_tab   2 storage (next 1M);

Table altered.

sys_at_ORCL9I.UK.X.CH> alter tablespace lmt_test default storage (initial 1M next 1M);

Tablespace altered.

sys_at_ORCL9I.UK.X.CH> alter tablespace lmt_test minimum extent 1M;

Tablespace altered.

sys_at_ORCL9I.UK.X.CH> exec
dbms_space_admin.tablespace_migrate_to_local('LMT_TEST',1);

PL/SQL procedure successfully completed.

sys_at_ORCL9I.UK.X.CH> alter table lmt_test_tab allocate extent;

Table altered.

sys_at_ORCL9I.UK.X.CH> create table lmt_test_tab2 (col1 varchar2(10)) tablespace lmt_test;

Table created.

sys_at_ORCL9I.UK.X.CH> select      tablespace_name,
  2                  initial_extent,
  3                  next_extent,
  4                  min_extlen,
  5                  extent_management,
  6                  allocation_type

  7 from dba_tablespaces where tablespace_name = 'LMT_TEST';
TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTLEN
EXTENT_MAN ALLOCATIO
------------------------------ -------------- ----------- ----------
sys_at_ORCL9I.UK.X.CH> select      segment_name,
  2                  bytes,
  3                  blocks,
  4                  extents,
  5                  initial_extent,
  6                  next_extent

  7 from dba_segments
  8 where tablespace_name = 'LMT_TEST';

SEGMENT_NAME BYTES BLOCKS EXTENTS INITIAL_EXTENT NEXT_EXTENT
--------------- ---------- ---------- ---------- -------------- -----------

LMT_TEST_TAB      22020096       2688          2       20971520     1048576
LMT_TEST_TAB2      1048576        128          1        1048576     1048576


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

From: Mohan, Ross [mailto:RMohan_at_arbinet.com] Sent: Wednesday, July 21, 2004 4:42 PM
To: oracle-l_at_freelists.org
Subject: RE: Slick tricks for extent size reduction in LMTS via LMTS=>DM=> LMTS ? 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
-----------------------------------------------------------------
----------------------------------------------------------------
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 - 11:39:50 CDT

Original text of this message

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