Estimation of dump files- oracle 9i,10g2
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Estimation of dump files- oracle 9i,10g2

  1. #1
    Join Date
    Dec 2007
    Posts
    82

    Estimation of dump files- oracle 9i,10g2

    My environment is Oracle 9i. I want to estimate the size of export file. The option estimate_only=y can be set in expdp command. In Oracle 9i, we use exp command to export database not expdp. How to estimate the size of export without running export in Oracle 9i environment?

    Is there any process to make export exp & import imp faster??. and how do i calculated how much time import (original imp utility) takes without actually doing import?( imp)

    Thanks!

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you cant estimate without doing it

  3. #3
    Join Date
    Dec 2007
    Posts
    82
    any way to estimate disk space occupaied by oracle dumps (exp utility)?. my DB size is 150 GB, how do I calculate disk space required for export dumps with exp utitlity in oracle 9i,10g.
    thanks,

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you asked the question - you got an answer and then you ask the same question again - whats up with that?

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Support Davey, you don't trust him?... just run exp help=y and check the answer by yourself.

    If you are looking for estimates you are looking in the wrong place, try expdp in Ora10g.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    pmfji, but why the poster can´t generate a parfile with the small tables, like :

    set term off feedback off verify off pages 0 lines 500 trimspool on head off
    spool pathandnameofparfile.par
    select 'TABLES=' from dual
    /
    select decode(rownum, 1, '', ',') || linha
    from (
    select 'owner.' || table_name linha
    from dba_tables
    where owner in (listofowners)
    and table_name not in
    (list of big tables)
    )
    order by table_name
    ) a
    /
    spool off
    exit

    , export it, AND later generate another export script with only the (few, we think) big tables
    using the syntax exp file=test tables=(listofbigtables) query='where rownum "<" 100000' ? After this the estimate is EASY, 100000 rows of big tables = x bytes in .dmp, SO total number of lines (knowed, one cant think) = y bytes....

    Regards,

    Chiappa

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    that would be good if all the rows were the same size

    and you still have to do an export to get any information, so the answer of you must do it to find out still holds

  8. #8
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    Yes, a reasonable set of hypotheses/assumptions is made , more or less homogeneous line sizes is one of them - we call "estimate" due to reasons like that... And more, I doubt that expdp estimates will made something much more different, so the answer can be a valid method in 9i, obviously with a margin for errors, due to assumptions not being valids, but this is part of an estimation process, I think... And yes, one must export to do it, but an export of just a bunch of rows will not be an issue, I suppose...

    Regards,

    Chiappa

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by JChiappa
    ... I doubt that expdp estimates will made something much more different
    ...and unfortunatelly you are wrong

    Not only expdp estimate does not performs any actual expdp, you can also direct expdp estimate to work either based on actual data blocks or object stats. Isn't that cool?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  10. #10
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    When I said "something more different" I was refering to the estimating method based on assumptions, a "rule" if you will, and the (always) possible estimate errors when the rule is false, like in my 9i case based on tables having (more or less) the same size : block-based expdp estimate will not (of course) read assume that all the blocks, so it must supose more or less the same data distribution on all the blocks regarding the read blocks. And what if the rest of the blocks have a RADICALLY different data distribution ? The estimate will fail, EXACTLY LIKE the 9i case if the rest of the non-readed lines have different line sizes, SO no differences to me in usability.... ==> This is the point I wanted to made, estimates can and willl "fail" if the roles in that they are based are false in a given situation, yes ? OF COURSE, for sure block-based estimate will be MUCH MUCH more accurate, but will be an ESTIMATE yet, it can be wrong for a LARGE margin if the given assumptions are false... So, with this in mind, my answer fits the case, I think...
    And yes, expdp can do the estimate without ant file output, 9i canīt do, ok, but as I said I see no great problem in this, this donīt precludes the 9i method...

    Regards,

    Chiappa

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