8i - 9i conversion tablespaces much larger after import
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: 8i - 9i conversion tablespaces much larger after import

  1. #1
    Join Date
    Aug 2005
    Posts
    69

    8i - 9i conversion tablespaces much larger after import

    hi,
    we are converting our databases from 8i to 9i.
    We are using locally managed ts with uniform 1m.
    The tablespaces on the 9i are much larger than the 8i after we import the data - and some are showing 100% used
    e.g. one tablespace is used 70meg sized at 400meg so 17% used in 8i . And after import into 9i with size 400 meg and specifying UNIFORM extent growth of 1meg, it is 100% used and is taking up 452 meg ??? - is this anything to do with uniform, or is it something to do with 9i tablespaces? - we would have to allocate a lot more disk space if this is going to happen, and we do not have a lot of spare space presently. I just do not understand why the tablespace has so much more used space in 9i version - should I have chosen the compress option in the exp/imp etc and would this have made any difference to the physical data anyway?

    here is the create tablespace command
    CREATE TABLESPACE ENTPER
    DATAFILE SIZE 400M
    AUTOEXTEND ON
    EXTENT MANAGEMENT LOCAL
    UNIFORM SIZE 1M
    ONLINE
    PERMANENT;

    also, should we make the SYSTEM tablespace locally managed or leave it Dictionary managed?

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    did u use compress=n in ur export?

  3. #3
    Join Date
    May 2005
    Location
    Toronto Canada
    Posts
    57
    If you still have the old 8i database around, run the following query:

    Code:
    SELECT segment_name, segment_type, blocks, extents
    FROM dba_segments
    WHERE tablespace_name = 'YOUR_TBS'
    ORDER BY blocks
    My guess is that you will find a number (probably a large number) of segments that take only a few blocks in the old database. With your UNIFORM LMT, every segment will take at least 1 MB of space whether it needs it or not.

    You have two options. Either create another UNIFORM LMT with a much smaller extent size (perhaps 64K) and move all of the small segments into that tablespace, or if you want to have only a single tablespace, then make it auto allocate. The first few extents of every segment will be small (64K), and the extent sizes will grow as the number of extents increases.

    HTH
    John

  4. #4
    Join Date
    Aug 2005
    Posts
    69
    thanks for the replies.
    Think I will change it to autoallocate.
    Presumably I can do this with a command - can you tell me what it is?

    I am a newbie dba.

    No I left the default in my export (which is Y)

    Future scripts for creation will be changed too.

    p.s. what is your opinion on SYSTEM tablespace - should it be locally or dictionary managed?

  5. #5
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    If you use compress=y then you will get into this type of problems

    Always use compress=n

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