Export Compression 1

[THoey]

All,

I have 26GB worth of tablespaces that I will need to export, FTP, and then import into a new database. The two servers are in the California and Texas, so we are now having concerns how long this is going to take.

Anyone used the compression that comes with the EXP utility? How much compression can I expect to get? 50%? 75%? 25%?

Any other suggestions to get this as small and quick as possible?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[Turkbear]

Hi,
That does NOT compress the data into a smaller file size..
The Docs:

COMPRESS
Default: y

Specifies how Export and Import manage the initial extent for table data.

The default, COMPRESS=y, causes Export to flag table data for consolidation into one initial extent upon Import. If extent sizes are large (for example, because of the PCTINCREASE parameter), the allocated space will be larger than the space required to hold the data.

If you specify COMPRESS=n, Export uses the current storage parameters, including the values of initial extent size and next extent size. The values of the parameters may be the values specified in the CREATE TABLE or ALTER TABLE statements or the values modified by the database system. For example, the NEXT extent size value may be modified if the table grows and if the PCTINCREASE parameter is nonzero.


--------------------------------------------------------------------------------
Note: 
Although the actual consolidation is performed upon import, you can specify the COMPRESS parameter only when you export, not when you import. The Export utility, not the Import utility, generates the data definitions, including the storage parameter definitions. Therefore, if you specify COMPRESS=y when you export, you can import the data in consolidated form only.

--------------------------------------------------------------------------------

To compress, in the 'normal' sense of the word, use a Tar or Zip utility on the .dmp file created by the export.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[THoey]

Thanks. I knew that...

~ why can't I remember that... ~

<shakes head>

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[THoey]

As a side question is there a rule of thumb about the size of an export file for every 10GB of tablespace? I can run a test if no one has a "guestimate".

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[LKBrwnDBA]

Best compression ratio can be achieved with gzip.

If you have windoze, download cygwin


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[taupirho]

What O/S are you using.If UNIX (or a derivative) you can pipe the output of the export to a compression program. The process is reversed when importing. I can point you in the direction of more info if required.

 

[THoey]

It is a Unix server, but I am not sure the flavor. I have been advised that we have a TAR derivitive program. The command is compress -c <filename>

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[taupirho]

The article on my website about this is down at the moment but for the record its:

http://the-big-o.port5.com/article23.shtml

Check it out later when its back up. HHowever the gist of it is this. Use the following commands (or similar) to compress

1) Create a named pipe

$ mknod -p /tmp/tmpfile

2) Use the named pipe instead of the regular filename in your export command and run it in the background

$ exp myuser/mypass full=Y file=/tmp/tmpfile &

3) Read the pipe, compress its output and write to a file

$ compress < /tmp/filename > mydb.z

To read the file back in it's just:-

$ mknod -p /tmp/tmpfile
$ uncompress < mydb.z >/tmp/tmpfile &
$ imp myuser/mypass full=Y file=/tmp/tmpfile


Hope this helps

 

[THoey]

Tom,

I am a little confused on what would be the purpose of doing that when I can just export as normal and then compress the file. I am sure your document goes into more detail and I can check it out later.

But thanks!

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[taupirho]

Say you had limited disk space and couldn't fit the original export file and its compressed version in the space available. What would you do then?

Say you couldn't even fit just the export file on disk?

Doing it this way means you don't have an intermediate file taking up space un-necessarily and is also easier if you need to produce a zipped file onto tape for instance.

 

[THoey]

Thanks for the explanation. That is a handy tool. I will not need it this time, but I will keep it in my "toolbox".

As to another previous question:

As a side question is there a rule of thumb about the size of an export file for every 10GB of tablespace?

Terry
**************************
* General Disclaimer - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...