RE: Slick tricks for extent size reduction in LMTS via LMTS=>DM=> LMTS ?
- From: "Powell, Mark D" <mark.powell@xxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 21 Jul 2004 12:42:22 -0400
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.
-- Mark D Powell --
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Feighery Raymond
Sent: Wednesday, July 21, 2004 12:09 PM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxxxxx> create tablespace lmt_test datafile
'/tmp/lmt_test_01.dmp' size 100m
2 extent management local
3 uniform size 20M;
Tablespace created.
sys@xxxxxxxxxxxxxx> 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
------------------------------ -------------- ----------- ----------
---------- ---------
LMT_TEST 20971520 20971520 20971520 LOCAL
UNIFORM
sys@xxxxxxxxxxxxxx> create table lmt_test_tab (col1 varchar2(10))
2 tablespace lmt_test;
Table created.
sys@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx> exec
dbms_space_admin.tablespace_migrate_from_local('LMT_TEST');
PL/SQL procedure successfully completed.
sys@xxxxxxxxxxxxxx> alter table lmt_test_tab
2 storage (next 1M);
Table altered.
sys@xxxxxxxxxxxxxx> alter tablespace lmt_test default storage (initial 1M
next 1M);
Tablespace altered.
sys@xxxxxxxxxxxxxx> alter tablespace lmt_test minimum extent 1M;
Tablespace altered.
sys@xxxxxxxxxxxxxx> exec
dbms_space_admin.tablespace_migrate_to_local('LMT_TEST',1);
PL/SQL procedure successfully completed.
sys@xxxxxxxxxxxxxx> alter table lmt_test_tab allocate extent;
Table altered.
sys@xxxxxxxxxxxxxx> create table lmt_test_tab2 (col1 varchar2(10))
tablespace lmt_test;
Table created.
sys@xxxxxxxxxxxxxx> 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
------------------------------ -------------- ----------- ----------
---------- ---------
LMT_TEST 1048576 1048576 1048576 LOCAL
USER
sys@xxxxxxxxxxxxxx> 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@xxxxxxxxxxx]
Sent: Wednesday, July 21, 2004 4:42 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx]
Sent: Wednesday, July 21, 2004 10:02 AM
To: 'oracle-l@xxxxxxxxxxxxx'
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@xxxxxxxxxxx]
Sent: Wednesday, July 21, 2004 12:21 AM
To: oracle-l@xxxxxxxxxxxxx
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@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
-----------------------------------------------------------------
----------------------------------------------------------------
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
-----------------------------------------------------------------
Other related posts:
