chanhe LMT uniform extent
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: chanhe LMT uniform extent

  1. #1
    Join Date
    Jan 2001
    Posts
    157

    Unhappy

    I have an LMT that has a uniform extent of 64000K.
    I would lkie to change it to 32000K. Can any body give me the steps
    involved in doing this?

    This is the command that I used to create the LMT:

    create tablespace LOCAL_TBS_DATA2
    datafile '/8i/ora816/oradata/V816/local2_01.dbf' size 1280000k
    extent management local
    uniform size 64000k

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Create a new tbs with the correct sizing and MOVE the tables to the new tbs.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Feb 2001
    Posts
    389
    That is one of the feature of LMT , to remove the usage of Next extent.
    If u need to change the next extent, change at the level of table.
    Oracle shall allocate in the multiple of uniform extent of tablespace.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    except, in this case, the NEXT extent is SMALLER than the UNIFORM extent size.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    Solution Description
    --------------------

    1. Migrate the locally-managed tablespace to a dictionary management.
    2. Then migrate it back to local management.

    Example:
    ==================================================
    MIGRATE FROM LOCALLY MANAGED TO DICTIONARY MANAGED
    ==================================================

    SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('LOCAL_TBS_DATA');
    PL/SQL procedure successfully completed.

    SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('LOCAL_TBS_DATA2');
    PL/SQL procedure successfully completed.

    ================================================
    Verify that tablespace is now dictionary-managed
    ================================================

    SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT
    2 FROM DBA_TABLESPACES
    3 where tablespace_name in ('LOCAL_TBS_DATA','LOCAL_TBS_DATA2');

    TABLESPACE_NAME EXTENT_MAN
    ------------------------------ ----------
    LOCAL_TBS_DATA2 DICTIONARY
    LOCAL_TBS_DATA DICTIONARY

    =======================================================
    MIGRATE FROM DICTIONARY MANAGED BACK TO LOCALLY MANAGED
    =======================================================
    Identify the file_id corresponding to the tablespace:

    SQL> select FILE_ID, TABLESPACE_NAME from dba_data_files
    2 where TABLESPACE_NAME in ('LOCAL_TBS_DATA','LOCAL_TBS_DATA2');

    FILE_ID TABLESPACE_NAME
    -------------- ----------------------------------------
    7 LOCAL_TBS_DATA
    8 LOCAL_TBS_DATA2

    SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(
    tablespace_name =>'LOCAL_TBS_DATA', rfno => 7);
    PL/SQL procedure successfully completed.

    SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(
    tablespace_name =>'LOCAL_TBS_DATA2', rfno => 8);
    PL/SQL procedure successfully completed.

    =============================================
    Verify that tablespace is now locally-managed
    =============================================

    SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT
    2 FROM DBA_TABLESPACES
    3 where tablespace_name in ('LOCAL_TBS_DATA','LOCAL_TBS_DATA2');

    TABLESPACE_NAME EXTENT_MAN
    ------------------------------ ----------
    LOCAL_TBS_DATA2 LOCAL
    LOCAL_TBS_DATA LOCAL


    =============================================
    Alter the TABLE with a new NEXT storage value
    =============================================

    SQL> ALTER TABLE t_local STORAGE (NEXT 200K);
    Table altered.

    SQL> ALTER TABLE t_local2 STORAGE (NEXT 200K);
    Table altered.

    SQL> select table_name, initial_extent, next_extent
    2 from dba_tables where table_name in ('T_LOCAL','T_LOCAL2');

    TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
    ------------------------------ -------------- -----------
    T_LOCAL2 73728 204800
    T_LOCAL 65536 204800


    Explanation
    -----------

    The 1st migration allows you to manage the extent sizing, and the
    2nd migration ignores extent size handling.

    Migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly
    created locally managed tablespaces.


    -----------------------
    Best wishes!
    Dmitri

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by kgb
    Migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly
    created locally managed tablespaces.
    Herein lies the key.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    OK, so you posted the link from which you stole the text. Smashing.

    The point I was trying to make was that although this would "work", it wouldn't give the result the poster was looking for. The poster wants to know how to change his 64M UNIFORM extents to 32M UNIFORM extents. By converting to a DMT back to a LMT would not give the poster UNIFORM extents.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    if you want *REAL* uniform extent size you must not use dbms_space package

  10. #10
    Join Date
    Mar 2001
    Location
    Ireland/Dublin
    Posts
    684
    I see you are very smart guy! Congratulations !!!!
    Let me know from which books did you steal your knowledge? I may tell you mine:
    Internet and Oracle Documentation.
    Or may be you ask someone first and then post an answer ? :-)

    Originally posted by marist89
    OK, so you posted the link from which you stole the text. Smashing.
    UNIFORM extents.
    Best wishes!
    Dmitri

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width