DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 24

Thread: Extent Management

  1. #1
    Join Date
    May 2006
    Posts
    8

    Extent Management

    I migrated a database from 7 to 8 to 9i (9.2.0.6) and in 9i changed from Dictionary to Locally Managed tablespace. There are many objects which were created before we migrated to locally managed tablespace. Is there a way to find out which objects are still managed by dictionary.

    thanks in advance
    RK

  2. #2
    Join Date
    Apr 2006
    Posts
    377
    This should work:

    Code:
    select a.owner, a.segment_name, a.segment_type, b.extent_management
    	from dba_segments a, dba_tablespaces b
    	where a.tablespace_name = b.tablespace_name
    	and b.extent_management = 'DICTIONARY';

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    all objects will be locally managed.

    Whether you have the benefits of LMTs is debateable.

    You could probably look at the extent sizes to see which were created before

  4. #4
    Join Date
    May 2006
    Posts
    8
    It did not work; the above query did not show anything. It gives no rows selected.

  5. #5
    Join Date
    Apr 2006
    Posts
    377
    That's correct, 'IF' you created you database with a LOCAL MANAGEMENT system tablespace, then you can NOT have DICTIONARY managed tablespaces.

  6. #6
    Join Date
    Apr 2006
    Posts
    377
    Quote Originally Posted by rkhanna
    It did not work; the above query did not show anything. It gives no rows selected.
    Then everything is LOCALLY managed.

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    but that doesnt answwr his question of working outr which objects where created before the tablespace was converted.

    Another way would be to compare the object creation time with the time you converted

  8. #8
    Join Date
    Apr 2006
    Posts
    377
    Quote Originally Posted by rkhanna
    Is there a way to find out which objects are still managed by dictionary.
    This would lead me to believe that he wants to see if anything is still DICTIONARY managed???

    Guess the OP may be able to confirm.

  9. #9
    Join Date
    May 2006
    Posts
    8
    My SYSTEM tablespace is still dictionary managed but ALL other are locally managed and I am looking for a way to find the objects which were created before the migration from dictionary to local so that I can rebuild them (alter table move...) in a newly created tablespace to have all the benefits of locally managed.

    Any help to find out those objects would be greatly appreciated.

  10. #10
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    are you just ignoring my posts or what?

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