ansaurus

Question

Oracle: how to disable table compression on dmp file import

Answer 1

+1 A:

I think you'll have to precreate your tables by using dbms_metadata to extract the definitions from the source database.

You could also use the imp indexfile option to create an editable script in which COMPRESS could be globally replaced with NOCOMPRESS.

Datapump might have an option for doing this ... in 11g I'd be using datapump instead of imp/exp anyway.

David Aldridge 2009-11-18 17:40:46

thanks for reply. I have the script to recreate tables. My only problem is to load data from dmp file. Normally I use datapump. But this time one table was giving me so much problems (ORA-39014, ORA-04030) , that I've decided to try old import/export utilities.

mtim 2009-11-18 18:27:57

If you precreate the tables, then IMP with IGNORE=Y will just import the data from the export file

Gary 2009-11-18 21:34:38

@Gary. That is what I'm doing and I'm getting "ORA-00439: feature not enabled: Table compression".

mtim 2009-11-18 21:45:59

So your table is precreated, using NOCOMPRESS, and IGNORE=Y, but you still get that error? Is it possible to tell whether the error is occuring as part of the attempt to create the table? I wonder whether the attempt to create the table is raising this error but IGNORE=Y is only set to ignore "Table already exists" errors, and is not ignoring "Feature not enabled" errors. One option would be to open the exp file and replace `COMPRESS` with `NOCOMPRESS` (try to modify a string of a given length to one of the same length to avoid rewriting all the subsequent data though)

David Aldridge 2009-11-19 11:44:19

@David. You were right. Precreating tables and using **IGNORE=Y** should work. It does not work in my case because the table that I'm trying to import contains custom types. I missed the warning:"IMP-00063: Warning: Skipping table "USR12"."A_TABLE" because object type "USR2"."CUST_TYPE1" cannot be created or has different identifier"

mtim 2009-11-19 15:46:27

ansaurus

tags:

views:

answers:

Oracle: how to disable table compression on dmp file import

related questions