DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 24

Thread: Extent Management

  1. #11
    Join Date
    May 2006
    Posts
    8
    Davey23uk

    No I am not but I do not have the exact date when the tablespace was converted from dictionary to locally managed and wanted to do this on many databases (more than 50) so wanted to find a solution.

    Thanks for your inputs and appreciate your help.

  2. #12
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    go and check the extent sizes then

  3. #13
    Join Date
    Apr 2006
    Posts
    377
    Guess I'm kinda confused. What method did you use for the migration? If you ran my select from above and it returned zero rows, then that would lead me to believe that the SYSTEM tablespace is LOCALLY managed?

    Does the following query show DICTIONARY ?

    Code:
    select extent_management
    from dba_tablespaces
    where tablespace_name = 'SYSTEM';
    .

  4. #14
    Join Date
    May 2006
    Posts
    8
    After migrating a dictionary managed tablespace to
    locally managed tablespace
    you notice that the tables are still extending with the next extent specified
    even though it's now a locally managed tablespace

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

    I would like to create new tablespaces and then
    alter table T move tablespace NEW_TBS;
    alter index I rebuild tablespace NEW_TBS;

    Issue here is
    *************
    I wanted a list of tables which should be moved to get the full benefit of
    locally managed tablespace.

  5. #15
    Join Date
    May 2006
    Posts
    8
    Mr/Ms DAVEY23UK

    If you DO NOT understand what I am asking for here is; then do not waste your time posting junk in here. Simply read what others are saying.

  6. #16
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    ill post it in simple language

    go.check.your.extent.sizes

    plus you do realise that any tablespace which has been converted doesnt have to enforce extent sizes even on newly created objects

    i guess you dont know that
    Last edited by davey23uk; 05-09-2006 at 02:44 PM.

  7. #17
    Join Date
    May 2006
    Posts
    8
    Read my post carefully and multiple times. I said migrated tablespaces are not subject to the UNIFORM/SYSTEM policy of newly created locally managed tablespaces. thus the need to find the objects which should be moved to new tablespace to get full benefit of LMT.

    for you davey23uk - There's nothing wrong in accepting your mistake and not repeat it.

  8. #18
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    for gods sake

    CHECK YOUR EXTENT SIZES.

    think about it ok, just for a moment

  9. #19
    Join Date
    Apr 2006
    Posts
    377
    davey23uk, I don't know how he can conclude much from extent sizes in and of themselves?

    rkhanna, the following query should show which objects where converting to LOCAL from DICTIONARY:

    Code:
    select a.owner, a.segment_name, a.segment_type
    	from dba_segments a, dba_tablespaces b
    	where a.tablespace_name = b.tablespace_name
    	and b.extent_management = 'LOCAL'
    	and b.allocation_type = 'USER';
    To reap the full benefits of LOCALLY MANAGED tablespaces, your best bet is just to "alter table move/alter index rebuild" all the tables & indexes to a new LOCALLY MANAGED tablespace because utimately, you are going to want to do away with your migrated tablespaces.

    .

  10. #20
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    chck the extent sizes, looks for one where initial != next where initial has some way large value or some way low value.

    Those are objects which havent been created with any sort of extent sizing thought

    so in summary, check your extent sizes.

    i'm guessing, but i reckon any object with have an allocation_type of 'USER' even if they were created after a ts was converted to a LMT

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