Compress paramter in Oracle exp utility

[Chris Fung]

Hi all,

I have compared the size of the export file created with "compress=Y" and "compress=N" options. The compress=Y option saves a lot of disk space and the performance was not degraded.

From my understanding, with the compressed option enabled, all the data will fit to a single initial extent during the import process. However, in some cases, espcially for production database running for so many years, there many not have "a single extent" large enough to hold a large single table. If that is the case, does it mean the import will fail or more "next extents" will be acquired to accomodate the data ?

=> FYI, I am now thinking to modify our existing production database export script to not using "compress=N"....in order to save some disk space.

Thanks for your clarification in advance.

Cheers,

Chris,

[Yogeeraj_1]

hi,

Import will cause you to allocate lots of extents up front in order to satisfy the compress=y. Eg: if the existing table has 50m allocated to it and you exp with compress=y -- the initial will be 50m. Compress will sum up the existing extents and ask for that much space.

The tablespace will allocate the amount of extents requested by the implicit "create table.." during the import. If there is not enough space available on tablespace, two things may happen:
a. The datafile associated with the tablespace will autoextend (assuming autoextend is on)
b. An error will be generated and the import of that particular table will fail.
(nb. the import will continue/fail based on the value of "Ignore")


I would prefer to use locally managed tablespace (LMT) and pre-create the tables before importing the data. LMTs allows me better manage my tables and tablespaces. I classify my tables into 3 categories small, medium and large and create them in my tablespaces (LMT_small, LMT_medium, LMT_large - autoextend enabled) accordingly. Extents of fixed sizes are allocated as and when required. This prevents me from unneccasarily reserving excess space for a particular table. I let them grow by themself. With LMTs, there is "no" fragmentation.

If you import into an LMT with 1meg extents -- it'll allocate 50 of them initially. When you import, the LMT will look at your initial request, divide by its extent size and give you that many extents.

I do not like compress=y. Totally wish it did not exist.

I hope you are not using exp as a backup tool.

Backups are the only backups.

If my dmp file got to be 20-30gig -- I would stop doing it. what is the purpose/goal of doing this?

If you are concerned about the size of the export files, compress them using disk utilities gzip, compress, etc.

if you need any further clarifications, pleas let us know.

Best regards
Yogeeraj

[Rod White]

Chris

You may be a little mistaken about the statement "The compress=Y option saves a lot of disk space" since as Yogeeraj states the compress parameter simply means that the export file will contain either the INITIAL_EXTENT value for a table or the value of the total extents currently used by the table. (This also applies to indexes.) The export utility does this incase you originally created an index/table with small extents and it grew large over time resulting in many incrementally bigger extents. Compress=Y allows the import to create one large extent when the import occurs which means that if the table does not grow in the future it will be in one large extent (a good thing). However, I would be more concerned with the value of the NEXT_EXTENT and PCT_INCREASE values in the import incase the table once imported grows in the future becuase it may not be the imported extent that kills you but the next one. Except of course if you autoextend and then one day you may find your filesystems full.

All this means is that the resulting export (dmp) file will differ only in several bytes between a compress=Y and compress=N, it has no effect on the data (which is the bulk of the export file), it comes out as it is (not compressed).

A more subtle issue in the use of the export/import as a long term backup/recovery method is that the export file contains the TABLESPACE_NAME to which the objects belong. Bad luck if in the future you decide to change your tablespace_names.

A few things to keep in mind if you're are stuck with an export file that doesn't like to be imported.

1) You can always edit the export file and change tablespace_names, table_names, index_name etc and storage values. You need to use a HEX EDITOR that can edit the file and leave the binary bits in place, one that doesn't mind very very long single line files. I use a very old copy of Wordperfect (ED.exe /B) whenever I find an old export file lying around and want to get the data out of it without messing up my exising application. This is really handy when the export was done by a DBA user and you're not one.

2) You can always precreate the object and import with ingore errors = Y. If you don't have the table definition handy then run the import and choose the list option, redirect the output to a file and edit the file.

I agree with Yogeeraj that import/export is not a valid backup/restore utility, still it is the best way to get data from one schema/one operating system to another and if you inadvertently trash a table then finding an import file with your table is a hell of a lot easier than restoring the database.

Tapes are great but you can't restore a unix backup to a NT server and have the database come up. But you can export the data from a Unix database and import it into a NT database, either via ftp or across sqlnet.

Also, find the details of your metalink login. It's probably a more appropriate forum for Oracle questions.

Rod.

[Tuomas Nurmela]

Besides pointers provided by Yogeeraj and Rod, I would just like to add that export can be _part_ of your database backup routine. It complements hot and cold backups, since its the only way to get a _logical_ backup of the database in case e.g. a developers or users mistakenly drops a table or deletes rows or...

In this case, should you need to recover part of a table data quickly from an export, you will probably be using the export as a way to to means, creating a table temporarily under another user to copy only the specified data and check that everything is ok, before fix (hence compress issue is rather irrelevant). However, using compress=n at least makes sure you can create a table exactly as it was, should that be part of the solution.

[Indira Aramandla · Accepted solution]

Hi Chris,
Compress=Y option does not save a lot of disk space". Oracle Export and Import utilities are used to transfer Oracle data to and from Oracle databases. The Oracle Export utility writes data from an Oracle database into a proprietary export dump file. An export/import operation is commonly used to reduce fragmentation of extents in a database. Because an import operation creates each table and then imports all rows before beginning the next table, the entire table will be written contiguously.

Additionally, by default, the Export utility will `compress' the extents of each table when creating the dump file. The Export utility modifies the value of INITIAL from what the table was originally created using. For example, consider a table that was created with a storage of (INITIAL 10K NEXT 10K) and the table currently has 100 extents. If an export-compress operation is performed, the SQL create syntax stored in the dump file would be (INITIAL 1000K NEXT 10K). The INITIAL value is made as large as the sum of the existing extents. The NEXT value is unchanged.

When using the COMPRESS option, there are a couple of points to keep in mind. The INITIAL value chosen by Export is the sum of extents that a table currently has, and has nothing to do with the amount of data in the table. For example, I perform a DELETE FROM INVENTORY, where the `inventory' table has four 100 megabyte (MB) extents. I then export with COMPRESS; the new table will have an initial extent of400 MB, even though the table is completely empty.

The size of the export dump file has very little to do with the actual amount of data that exists in the database and/or the amount of space required in the importing database. For example, I perform a table-level export of a particular table that is empty, but has extents totalling 400 MB. The export dump file will be tiny, as it will contain the table DDL and no data. However, on import, that dump file will require 400 MB of free space in the importing database.

The second point is the size of the INITIAL extent created relative to datafile sizes for the tablespace it is being placed in. As an example, I have the USERS tablespace that has four 50 MB datafiles. In it I have the EMPLOYEES table which consists of fifteen 10 MB extents. If I export this table with COMPRESS, the resulting dump file will try to import the table back with a single 150 MB initial extent. Neither the table nor the tablespace has changed size, but the new table will not fit in the USERS tablespace because a single extent can not be split among datafiles. Note that this is only a factor if the tablespace consists of multiple datafiles. The only workarounds are to export without compression or to pre-create this table prior to import and give it workable storage options.

[Chris Fung]

Dear All,

Thanks for the very detail explanation and examples. I would say "Indira" give a very relavent example to my question !! Thanks.

Besides, Yogeeraj also broaden my view on new Oracle features "LMT" !! Thanks.

Cheers,

Chris,