Printable View
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!
you cant estimate without doing it
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,
you asked the question - you got an answer and then you ask the same question again - whats up with that?
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.
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
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
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
...and unfortunatelly you are wrong :)Quote:
Originally Posted by JChiappa
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? :cool:
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
Well... this is pretty interesting because you have managed to move this forum into the quantum world, you are correct and at the same time you are wrong.
You are correct because what you say is true.
You are wrong because you are not answering the poster's question, you have derived a new question on your own and then you have answered it.
Yes, maybe I can bring back some clarity : the poster´s Q was :
" How to estimate the size of export without running export in Oracle 9i environment?"
The answer must be :
==> "** WITHOUT RUNNING **, you are out of luck, this can´t be done, BUT you can do it running export with just a (relatively) small fraction of data, consuming very few resources and time, in a a manner similar to expdp but not identical, using the procedure I wrote in my original post - is this useful ?"
The other answers will be :
"Is there any process to make export exp & import imp faster??"
=> Yes, use the DIRECT, BUFFER and RECORDLENGTH option in export, do NOT export indexes and constraints (make a script only and later run the script with parallel/nologging/enable novalidate), do some simultaneous exports (each one exporting a distinct fraction of data), things like that... Read the Utilities manual to find refs and syntax.
"... and how do i calculated how much time import (original imp utility) takes without actually doing import?( imp)"
==> Again, this can´t be done, BUT you can import a fraction and make a extrapolation, that´s it.... be warned, this is an ESTIMATE, you can be wrong for a margin...
Regards,
Chiappa
Thanks!
Quote:
Originally Posted by JChiappa
you mean dont import index and constraints
Thank you for supporting my point, here is the question poster never asked and you made up...
...it's even signed by you.Quote:
Originally Posted by JChiappa
By the way... you say "other answer" and you write a "question"? :D