OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » Export Utility (10g)

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

Export Utility [message #510692]

Tue, 07 June 2011 04:57

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Dear All,

I am exporting a table that is 3 GB in size and also Partitioned with option NOCOMPRESS specified.

Now when i export it with COMPRESS=N option of exp utility then it should take 3 Gb in target server but will exporting it with COMPRESS=Y will save some storage during import or once NOCOMPRESS option specified on partition has no impact on exp utility COMPRESS=Y option and it will take 3 GB space in both cases

Is this true that whether u specify COMPRESS=N|Y during export it does not matter the size will be 3 GB always after import??

Cheers,
Rajat Ratewal

Report message to a moderator

Re: Export Utility [message #510708 is a reply to message #510692]

Tue, 07 June 2011 06:03

Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

COMPRESS has nothing to do with dump compression, it just means that during the table creation at import time ALL data will go to one extent.
In 10g, use Data Pump instead.

Regards
Michel

Report message to a moderator

Re: Export Utility [message #510771 is a reply to message #510708]

Tue, 07 June 2011 11:28

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Dear Michel,

Thanks for the same.Sorry may be what i said was misinterpreted.

I know that COMPRESS do not really compress when specified in export. This is an input to IMP basically.

My question is when i am exporting a table with ROWS=N it still taking the same space after importing. As per my understanding it should not take that much space. Because it's just an empty table
So does this have to do something with NOCOMPRESS option specified on table partitions or it does not matter. One of the table is taking 3 GB space after import that was exported with ROWS=N parameter.

This i am unable to understand.

Regards,
Rajat

Report message to a moderator

Re: Export Utility [message #510772 is a reply to message #510771]

Tue, 07 June 2011 11:32

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

if ROWS=NO then just pre-create table with desired size

Report message to a moderator

Re: Export Utility [message #510774 is a reply to message #510772]

Tue, 07 June 2011 11:42

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Thanks BlackSwan for the help and advise. But i am curious to know that. We can solve that in some different ways that's OK but why this is happening i want to know the reason. Why so much space??
Is this due to NO COMPRESS option on partitioned table.

Regards,
Rajat

Report message to a moderator

Re: Export Utility [message #510777 is a reply to message #510774]

Tue, 07 June 2011 11:48

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>why this is happening i want to know the reason.
new table is created with the size of the source table; which is only size Oracle knows.

Report message to a moderator

Re: Export Utility [message #510778 is a reply to message #510777]

Tue, 07 June 2011 11:54

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

So it does not matter if a table is having 30 GB in size and when we export using ROWS=N still after the import the size of table will be 30 GB.

To avoid this only method is to create that by using DBMS_METADATA or can we specify some kind of parameter in exp or expdp

Cheers,
Rajat

Report message to a moderator

Re: Export Utility [message #510782 is a reply to message #510778]

Tue, 07 June 2011 12:45

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Export with rows=N then use an editor to change the initial extent for your objects.

Be warned that the file will not be 100% ascii.

Report message to a moderator

Re: Export Utility [message #510812 is a reply to message #510778]

Tue, 07 June 2011 22:51

Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

So it does not matter if a table is having 30 GB in size and when we export using ROWS=N still after the import the size of table will be 30 GB.

Use COMPRESS=N then tha table is created with only the extents given by the MINEXTENTS clause (1 by default).

Quote:

To avoid this only method is to create that by using DBMS_METADATA or can we specify some kind of parameter in exp or expdp

You can get the metadata from both imp or impdp using INDEXFILE or SQLFILE options.

Regards
Michel

Report message to a moderator

Re: Export Utility [message #510901 is a reply to message #510812]

Wed, 08 June 2011 05:09

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

Thanks Michel Smile

I also found one link with example that what i was looking for.
Hope this will help others.

Example

Cheers,
Rajat

Report message to a moderator

Re: Export Utility [message #510904 is a reply to message #510901]

Wed, 08 June 2011 05:21

Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Thanks for your feedback and link.

Regards
Michel

Report message to a moderator

Re: Export Utility [message #510989 is a reply to message #510904]

Wed, 08 June 2011 21:43

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

I was going through one more link and found that in 11g thier is one option to handle this. Deferred Segment Creation - Means tables get created but no segments allocated even if u don't have any quota on tablespace.

Cool New Feature

Cheers,
Rajat

Report message to a moderator

Previous Topic:	load jpg picture into oracle database
Next Topic:	Porting data from text file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 16 09:04:35 CDT 2024