Locally managed tablespace & NEXT extent sizing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Locally managed tablespace & NEXT extent sizing

  1. #1
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925

    Question Locally managed tablespace & NEXT extent sizing

    I am kind of getting lost here.

    I know that when you create a table with the locally managed tablespace, the extent allocation in either UNIFORM or of AUTO. But when I was playing with this, I noticed that when to convert a tablespace from LOCALLY managed to the dictionay managed one and then back to the LOCALLY managed tablespace, you can alter your extent sizing on your table.

    Whats up with this? Any one can explain ?

    Thanx,

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You're seeing this on an 8i (or lower) database?

    Oracle9i Release 2 - Managing Space in Tablespaces
    Tablespaces allocate space in extents. Tablespaces can use two different methods to keep track of their free and used space:

    Locally managed tablespaces: Extent management by the tablespace
    Dictionary managed tablespaces: Extent management by the data dictionary
    When you create a tablespace, you choose one of these methods of space management. You cannot alter the method at a later time.
    Share on Google+

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    What you mentioned were all true. But the issue I mentioned is something that I noticed on 9i (9.2.0.3)

    (i.e)
    Code:
    1. Created the tablespace with the tablespace management set to locally managed.
    
    2. Created the user with this as the default tablespace.
    
    3. created the table with the initial and next set to 1M and noticed that next set to 0 after table was created.
    
    4. Tried altering the table's next extent size and go the ORA-25150
    
    5. Upto this point it all worked the way they are supposed to be working.
    
    6. Moved the tablespace to dictionay managed using the 
    
      execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('tablespace_name');
    
    7. Changed the next extent and it was changed
    
    8. Now changed the tablespace to locally managed one using
    
      execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL(tablespace_name =>'tablespace_name', rfno => #);
     
    9. Now tried change the next extent size and I was succesful
    
    10. So created a new table under the same tablespace and tried to change the next extent size, I was successful
    This was all the cause of the puzzle. Aren't I supposed to get the ORA- 25150 when I try changing the next extent size on a locally managed tablespace? instead of allowing me to change it.


    -Sam
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

  4. #4
    Join Date
    May 2002
    Posts
    2,645
    Code:
    SQL> select tablespace_name, extent_management
      2  from dba_tablespaces;
    
    TABLESPACE_NAME                EXTENT_MAN
    ------------------------------ ----------
    SYSTEM                         LOCAL
    UNDOTBS1                       LOCAL
    TEMP                           LOCAL
    CWMLITE                        LOCAL
    DRSYS                          LOCAL
    EXAMPLE                        LOCAL
    INDX                           LOCAL
    ODM                            LOCAL
    TOOLS                          LOCAL
    USERS                          LOCAL
    XDB                            LOCAL
    
    11 rows selected.
    
    SQL> conn / as sysdba
    Connected.
    SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');
    BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;
    
    *
    ERROR at line 1:
    ORA-10616: Operation not allowed on this tablespace
    ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
    ORA-06512: at line 1
    Note 235805.1 on MetaLink says this is a bug in 9.2, fixed in 10.0.
    Share on Google+

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Your issue is that you have converted from local to dictionary back to local. When you convert from dictionary to local you get into a third type of extent management. You also need to look at allocation_type from dba_tablespaces. Your extent_management would be LOCAL, but your allocation_type will be USER (I think).
    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."
    Share on Google+

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    Originally posted by stecal
    Code:
    SQL> select tablespace_name, extent_management
      2  from dba_tablespaces;
    
    TABLESPACE_NAME                EXTENT_MAN
    ------------------------------ ----------
    SYSTEM                         LOCAL
    UNDOTBS1                       LOCAL
    TEMP                           LOCAL
    CWMLITE                        LOCAL
    DRSYS                          LOCAL
    EXAMPLE                        LOCAL
    INDX                           LOCAL
    ODM                            LOCAL
    TOOLS                          LOCAL
    USERS                          LOCAL
    XDB                            LOCAL
    
    11 rows selected.
    
    SQL> conn / as sysdba
    Connected.
    SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS');
    BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_FROM_LOCAL('USERS'); END;
    
    *
    ERROR at line 1:
    ORA-10616: Operation not allowed on this tablespace
    ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
    ORA-06512: at line 1
    Note 235805.1 on MetaLink says this is a bug in 9.2, fixed in 10.0.
    You have a different problem here. You are trying to create a dictionary managed tablespace in a database that can only handle LMTs (because the system tablespace is LMT).
    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."
    Share on Google+

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Sambavam :

    you would like to see this quote.

    Scenario 5: Migrating from a Dictionary-Managed to a Locally Managed Tablespace
    You migrate a dictionary-managed tablespace to a locally managed tablespace. You use the TABLESPACE_MIGRATE_TO_LOCAL procedure.

    Let us assume that the database block size is 2K, and the existing extent sizes in tablespace tbs_1 are 10, 50, and 10,000 blocks (used, used, and free). The MINIMUM EXTENT value is 20K (10 blocks). In this scenario, you allow the bitmap allocation unit to be chosen by the system. The value of 10 blocks is chosen, because it is the highest common denominator and does not exceed MINIMUM EXTENT.

    The statement to convert tbs_1 to a locally managed tablespace is as follows:

    EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('tbs_1');


    If you choose to specify a allocation unit size, it must be a factor of the unit size calculated by the system, otherwise an error message is issued.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
    Share on Google+

  8. #8
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Thanx Jeff. That explains why. Also my thanx to all those who contributed their thoughts to the problem.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!

    Share on Google+

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