impdp and "ORA-00439: feature not enabled: Table compression"

Recently I have to deploy one development solution to client's Oracle 10gR2 database. Deployment solution was compound from few tablespaces, several schemes with data packages/functions/procedures-standard situation. Development was performed on Oracle 10gR2 Enterprise Edition database, what is default platform for any kind of our development. Developers were using advanced Oracle features (compressed tablesapces with some compressed tables), what I didn't found bad at all-contrary I was encouraging them as much as possible!

At the end, deployment was packed through expdp utility.

The problem

When I came to client, who had already installed 10gR2 database (not too bad to mention that mine previously mentioned expdp was done with correct COMPATIBLE parameter), firstly I wanted to create tablespaces on locations where it should be accordind client server partitions.

For that I wanted to use previously generated/modificated tablespace scripts. On mine third script I get error:

ORA-00439: feature not enabled: Table compression

which told me that COMPRESS option is not installed. Quick grep in v$version confirmed mine thoughts ... and I was in question why? Solution on that question came very quickly.

SYS> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Standard Edition Release 10.2.0.4.0 - 64bit
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 64-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SYS> 

showed me that client is using Standard Edition, which doesn't support compression at all. In this case I said-OK let me create classic tablespaces without compression. No problems. For that remove two lines in create tablespace script

COMPRESS
ONLINE

Happy to solve this issue, I was surprised again (!!) when mine impdp had a lot of errors like:

ORA-39083: Object type TABLE failed to create with error:
ORA-00439: feature not enabled: Table compression
Failing sql is:
CREATE TABLE  ... 

followed by a dozen of

ORA-39112: Dependent object type  ... skipped, base object type TABLE: ... creation failed

which are easily recognized that all tables with COMPRESS option will not be imported because they cannot be created. The same was with table's indexes as well and all dependent objects based on such a tables.

Now, I was in real trouble, expdp file is pure binary file which cannot be easily edited as good old exp dump file, client was far away from mine company location (where I could recreate all the objects again in "no compress" mode) and beside that delivery date was passed, so no excuse was not allowed-it should be done now!

The solution

Now it is time to mention, mine good habit, which now shined in brightest colors. I always have, as a precaution, generated through amazing Toad interface, all necessary scripts (create tablespace, create users, create all schemes objects etc.). Those scripts are plain text and in many cases the only source to finding problems-and that was the case now.

Beside that impdp has many advanced features that will be used now. Because create tablespaces and first (unsuccesfull) import was already done, the plan was:

1. Create users/tables/indexes from plain scripts (remove any compress definition)
2. Truncate data in all imported (existing) tables. This step is vital to prevent data duplication.
3. Import data (impdp) with TABLE_EXISTS_ACTION-APPEND, which would only add data to existing tables.
4. Rerun create user scripts which will add maybe missing parts in grants etc. (do not run drop user part!!)
5. Recompile database as sysdba with ?/rdbms/admin/utlrp.sql script
6. Recreate database statistics for all imported scheme objects and dictionary as well

Looks pretty simple when you have a plan and needed resources. And I was lucky to have it.

The end

From this situation I learned that a list of topics should always be checked. One of them are database editions and it's allowed options!

Cheers!