OraFAQ Forum: Server Utilities » Schema export with less space on windows

Home » RDBMS Server » Server Utilities » Schema export with less space on windows (Oracle 10.2.0.4.0 , Windows server 2003)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Schema export with less space on windows [message #615120]

Fri, 30 May 2014 07:25

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Hi ,

i want to take schema export with 150gb in size but have only 70 gb space on windows. I dont think export will run suceessful
with less space.Is there any alternate way to accomplish this task ?

following is the command i am planning to use

expdp schemas=DROWN directory=EXP_MAY30  compression=metadata_only  parallel=4 dumpfile=DROWN_%U.dmp logfile=exp_30.log

Method used is Datapump export.

Thanks,
Jack

Report message to a moderator

Re: Schema export with less space on windows [message #615124 is a reply to message #615120]

Fri, 30 May 2014 08:08

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

Run expdp with the ESTIMATE parameter, and you will find how much space will be needed.
--update and ESTIMATE_ONLY=YES

[Updated on: Fri, 30 May 2014 08:10]

Report message to a moderator

Re: Schema export with less space on windows [message #615125 is a reply to message #615120]

Fri, 30 May 2014 08:09

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>compression=metadata_only
only DDL statements (no data) is contained in dump file so it should fit in 70 GB

Report message to a moderator

Re: Schema export with less space on windows [message #615127 is a reply to message #615125]

Fri, 30 May 2014 08:11

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

I think the issue is that unless Jack has bought the Advanced Compression Option, the metadata is all that he can compress.

Report message to a moderator

Re: Schema export with less space on windows [message #615130 is a reply to message #615125]

Fri, 30 May 2014 08:27

cookiemonster
Messages: 13966
Registered: September 2008
Location: Rainy Manchester

Senior Member

BlackSwan wrote on Fri, 30 May 2014 14:09

>compression=metadata_only
only DDL statements (no data) is contained in dump file so it should fit in 70 GB

You're confusing the compression parameter with the content one.

Report message to a moderator

Re: Schema export with less space on windows [message #615131 is a reply to message #615130]

Fri, 30 May 2014 08:33

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>You're confusing the compression parameter with the content one.
You are correct.
I just woke up & need another cup of tea.

Report message to a moderator

Re: Schema export with less space on windows [message #615139 is a reply to message #615131]

Fri, 30 May 2014 10:24

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Can i have answer to my question ?

Report message to a moderator

Re: Schema export with less space on windows [message #615140 is a reply to message #615139]

Fri, 30 May 2014 10:28

John Watson
Messages: 8966
Registered: January 2010
Location: Global Village

Senior Member

What was the result of the estimate that I suggested you run?

Report message to a moderator

Re: Schema export with less space on windows [message #615184 is a reply to message #615140]

Sat, 31 May 2014 00:41

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

John,
sorry to tell you that i have put this task on hold due to lack of space on server and sent a note to wintel team to provide more space on server ,so no need to hurry up.Moreover we have a long weekend leave coming up. So i can't give you the estimation size of dump file now. I will update you once i continue to work on this.

Thanks for your interest shown though.

Thanks,
Jack

Report message to a moderator

Re: Schema export with less space on windows [message #615186 is a reply to message #615184]

Sat, 31 May 2014 00:48

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Remember that the next time you will ask for a question.
We may also have some time off and so not immediately answer you and so be patient, carefully read the posts we have already given to you and answer our questions.

Report message to a moderator

Re: Schema export with less space on windows [message #615393 is a reply to message #615184]

Tue, 03 June 2014 05:59

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Schema size is 138 GB

SQL> select sum(bytes)/1024/1024/1024 from dba_segments where owner='DROWN';

SUM(BYTES)/1024/1024/1024
-------------------------
               138.154663

Using estimate_only=yes

Total estimation using BLOCKS method: 84.75 GB
Job "DROWN"."SYS_EXPORT_SCHEMA_01" successfully completed at 11:56:35

I am planning to use Network_Link parameter to import source schema dump into target database.
Is it advisable to use this parameter to import schema to target database with 85 GB size.Also
would like to know how long it would take to complete this schema refresh using netwrok_link parameter ?

Thanks,
Jack

Report message to a moderator

Re: Schema export with less space on windows [message #615394 is a reply to message #615393]

Tue, 03 June 2014 06:15

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This is the estimated dump size not the estimated size in the target database.
If you want to use network_link, this estimation is useless for you.

Report message to a moderator

Re: Schema export with less space on windows [message #615395 is a reply to message #615394]

Tue, 03 June 2014 06:22

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Michel,
Quote:

This is the estimated dump size not the estimated size in the target database.

yes i am aware of it .

based on estimated dump file size can you tell me estimated time ?

Thanks,
jack

Report message to a moderator

Re: Schema export with less space on windows [message #615402 is a reply to message #615395]

Tue, 03 June 2014 07:52

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No, no one can tell you.

Report message to a moderator

Re: Schema export with less space on windows [message #615406 is a reply to message #615402]

Tue, 03 June 2014 08:56

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Michel,
Thanks for your responds.

I took a export back of schema with estimated size of 84.5 GB but it occupied 102 GB space on windows drive.

here is the export command i used to take schema backup.

expdp system directory=EXP_MAY30 dumpfile=exp_schema.dmp schemas=DROWN logfile=exp_schema.log

Could you please clarify my doubt ?

Thanks,
jack

Report message to a moderator

Re: Schema export with less space on windows [message #615407 is a reply to message #615406]

Tue, 03 June 2014 09:03

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ It is an estimation and only an estimation
2/ Space occupied on Windows disk on disk format (if you look at file properties you will see 2 size, file size and size on disk, check them)

Report message to a moderator

Re: Schema export with less space on windows [message #615408 is a reply to message #615407]

Tue, 03 June 2014 09:21

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Michel,

Does this kind of extra space occupy ( apart from what estimated using ESTIMATE_ONLY=YES) happens only on windows server or
on Unix server as well ?

Thanks,
jack

Report message to a moderator

Re: Schema export with less space on windows [message #615412 is a reply to message #615408]

Tue, 03 June 2014 10:04

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I can't say for all file systems but it is a characteristic of Windows ones (and so it provides both values).

Report message to a moderator

Re: Schema export with less space on windows [message #615413 is a reply to message #615412]

Tue, 03 June 2014 10:16

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Thank you Michel for your clarification

Report message to a moderator

Re: Schema export with less space on windows [message #615478 is a reply to message #615413]

Wed, 04 June 2014 08:58

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Have anybody worked on Network_link parameter ? i am planning to use this parameter to import schema with 100 GB into target database.
Could you tell me what is the suitable time to run this import with network_link parameter ?

Thanks,
Jack

Report message to a moderator

Re: Schema export with less space on windows [message #615479 is a reply to message #615478]

Wed, 04 June 2014 09:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Could you tell me what is the suitable time to run this import with network_link parameter ?
It depends upon speed of the network link & how busy both the source & target systems are.
You can test & produce guessitmate by transferring a 5GB file between the 2 & then multiply the time by at least 20.

Report message to a moderator