Tablespace has 3 datafiles - import uses which?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Tablespace has 3 datafiles - import uses which?

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    Tablespace has 3 datafiles - import uses which?

    I exported a db into a replacement database recently. The original database had one datafile per tablespace, but the new one has 3 per tablespace spread over 3 different disks.

    Example: One TBS had 3 x 100mB files. The Import filled one file 100%, the others: 25% and 27% (I had expected an even spread of 50% for each datafile).

    Another TBS - 3 x 400Mb. Had a spread over the disks of 73% - 24% and 26%

    A third TBS - 3 x 350Mb had a spread of 21% - 47% - 21%

    Can anyone xplain to me how Oracle decides which disks to use? Is Import different from the normal load balancing I might expect?

    Its 9.2.0.1 on Win NT4 and the TBSs are locally managed with uniform extent sizes of 1Mb
    Last edited by JMac; 04-16-2004 at 05:17 AM.

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Just wundering does it really matter ?How the data distribution is ?

    If you want to spread ur data evenly precreate ur tables and other objects..and import with ignore=y

    regards
    Hrishy

  3. #3
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Well, no, maybe not. I'm just interested in why one file filled 100% when the other TBSs have a more even spread of data.

    I might prefer to have an even spread. There's no partitioning and I might want the access to any row to be more balanced and not 'stress' any particular datafile or disk.

  4. #4
    Join Date
    Jan 2003
    Location
    india
    Posts
    175
    what was the value for the export parameter
    compress, when exporting.

    compress=Y will consolidate extents.

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Oracle tries to spread the data equally among the files. Round-robin fashion.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Oracle tries to spread the data equally among the files. Round-robin fashion.
    Which is what I thought.

    One issue might (or might not!) be the DB block size.

    The exporting DB had a block size of 6K, the importing one had a block size of 8K. To get round this I pre-created the tablespaces in the target DB, and set IGNORE=Y so as to ignore the errors when the FULL import tried to create Tablespaces of 6k blocks.

    However, the fact that the spread is not consistent and one TBS has a 100% full datafile leaves me to believe this is not the case.

    Export Params:
    FULL=Y
    GRANTS=Y
    CONSISTENT=Y
    ROWS=Y
    COMPRESS=N

    Import Params:

    IGNORE=Y
    FULL=Y
    ROWS=Y
    INDEXES=Y
    GRANTS=Y

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Originally posted by julian
    Oracle tries to spread the data equally among the files. Round-robin fashion.
    From what I observed with a MOVE, it would seem that this is "simplistic" in its implementation. It looked as if, for each table, Oracle always started with the same file - so that if all your tables had only one extent, they would all be in the first file (not sure how "first" is defined).
    Last edited by DaPi; 04-16-2004 at 06:23 AM.

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Yes, true. Oracle only "tries" to spread the data evenly through the files.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by DaPi
    (not sure how "first" is defined).
    Min of File ID..?
    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"

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