Home » RDBMS Server » Server Utilities » Export Utility (10g)
Export Utility [message #510692] Tue, 07 June 2011 04:57 Go to next message
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
Re: Export Utility [message #510708 is a reply to message #510692] Tue, 07 June 2011 06:03 Go to previous messageGo to next message
Michel Cadot
Messages: 60017
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
Re: Export Utility [message #510771 is a reply to message #510708] Tue, 07 June 2011 11:28 Go to previous messageGo to next message
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
Re: Export Utility [message #510772 is a reply to message #510771] Tue, 07 June 2011 11:32 Go to previous messageGo to next message
BlackSwan
Messages: 23161
Registered: January 2009
Senior Member
if ROWS=NO then just pre-create table with desired size
Re: Export Utility [message #510774 is a reply to message #510772] Tue, 07 June 2011 11:42 Go to previous messageGo to next message
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
Re: Export Utility [message #510777 is a reply to message #510774] Tue, 07 June 2011 11:48 Go to previous messageGo to next message
BlackSwan
Messages: 23161
Registered: January 2009
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.
Re: Export Utility [message #510778 is a reply to message #510777] Tue, 07 June 2011 11:54 Go to previous messageGo to next message
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
Re: Export Utility [message #510782 is a reply to message #510778] Tue, 07 June 2011 12:45 Go to previous messageGo to next message
joy_division
Messages: 4559
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.
Re: Export Utility [message #510812 is a reply to message #510778] Tue, 07 June 2011 22:51 Go to previous messageGo to next message
Michel Cadot
Messages: 60017
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
icon12.gif  Re: Export Utility [message #510901 is a reply to message #510812] Wed, 08 June 2011 05:09 Go to previous messageGo to next message
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



Re: Export Utility [message #510904 is a reply to message #510901] Wed, 08 June 2011 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 60017
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for your feedback and link.

Regards
Michel
Re: Export Utility [message #510989 is a reply to message #510904] Wed, 08 June 2011 21:43 Go to previous message
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
Previous Topic: load jpg picture into oracle database
Next Topic: Porting data from text file
Goto Forum:
  


Current Time: Mon Dec 22 11:50:07 CST 2014

Total time taken to generate the page: 1.24455 seconds