Home » RDBMS Server » Server Utilities » ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified (11g, 11.2.0.1, windows server 2008)
ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605055] Tue, 07 January 2014 04:07 Go to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear All,

Below is the script to get database backup in expdp.


Export: Release 11.2.0.3.0 - Production on Sun Jan 5 13:13:44 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_06":  SYSTEM/******** dumpfile=expdp_uatoltp_05012014_iUat.dmp logfile=expdp_uatoltp_05012014_iUat.log directory=dp_dir full=y


Backup completed successfully without any error...

There are 6 partition tables which are creating problem during the import.

import script is as below -

;;; 
Import: Release 11.2.0.1.0 - Production on Mon Jan 6 23:31:39 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_05" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_05":  system/******** dumpfile=expdp_uatoltp_05012014_iUat.dmp logfile=impdp_expdp_uatoltp_05012014_iUat.log directory=DP_DIR SCHEMAS=INS,REINS,CNFG,RILOG,RNL,CONFSYS,BATJOB,DMS,DW,GEN,ACCEXT,RATEENG,MULTCURR,MS,TAGICRM exclude=statistics 
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE:"INS"."UW_VEHICLE_DTLS" failed to create with error:
ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified
Failing sql is:
CREATE TABLE "INS"."UW_VEHICLE_DTLS" ("NUM_REFER_NO" NUMBER(15,0), "DAT_REFERENCE_DATE" DATE, "TXT_REGISTRATION_NO" VARCHAR2(20 BYTE), "TXT_ENGINE_NO" VARCHAR2(30 BYTE), "TXT_CHASIS_NO" VARCHAR2(30 BYTE), "TXT_COVER_TYPE" VARCHAR2(50 BYTE), "TXT_VEHICLECLASS" VARCHAR2(100 BYTE), "TXT_VEHICLESUBCLASS" VARCHAR2(100 BYTE), "NUM_POLICY_NUMBER" NUM


When I append parameter transform=segment_attributes:n in import script, data are importing without any error.



;;; 
Import: Release 11.2.0.1.0 - Production on Tue Jan 7 14:26:26 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_05" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_05":  system/******** dumpfile=expdp_uatoltp_05012014_iUat.dmp logfile=impdp_expdp_uatoltp_05012014_iUat.log directory=DP_DIR SCHEMAS=INS,REINS,CNFG,RILOG,RNL,CONFSYS,BATJOB,DMS,DW,GEN,ACCEXT,RATEENG,MULTCURR,MS,TAGICRM exclude=statistics transform=segment_attributes:n 
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT


When i check the details of those partition tables, its allocated tablespaces are missing.

SQL> select owner, table_name, tablespace_name from dba_tables where table_name='GENMST_LO
CATION';

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ----------------------------
--
INS                            GENMST_LOCATION



Kindly assist me how can i make this process smooth?

Regards,
Ishika

Report message to a moderator

Re: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605068 is a reply to message #605055] Tue, 07 January 2014 04:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Export: Release 11.2.0.3.0
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0

Quote:
Import: Release 11.2.0.1.0
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Something is possible in 11.2.0.3 and not in 11.2.0.1.
Export with the VERSION parameter matching your target database.

Report message to a moderator

Re: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605076 is a reply to message #605068] Tue, 07 January 2014 04:52 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

Even version 11.2.0 is same at both the database. Please tell me how can i take backup which can be imported successfully to 11.2.0.1?
Do i need to upgrade it to 11.2.0.3? Or version can be specified as 11.2.0.1 while taking backup?

Waiting for your kind reply...

[Updated on: Tue, 07 January 2014 04:58]

Report message to a moderator

Re: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605082 is a reply to message #605076] Tue, 07 January 2014 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
Do i need to upgrade it to 11.2.0.3?


Yes.
But before try what I suggested.

Report message to a moderator

Re: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605142 is a reply to message #605082] Tue, 07 January 2014 10:03 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Dear Michel,

I tried as you suggested but getting the same error. Please have a look and guide me.

Below is the export script - 

;;; 
Export: Release 11.2.0.3.0 - Production on Tue Jan 7 16:48:46 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYSTEM"."SYS_EXPORT_FULL_07":  system/******** dumpfile=expdp_uatoltp_07-01-2014.dmp logfile=expdp_uatoltp_07-01-2014.log directory=dp_dir version=11.2.0.1.0 full=y 
Estimate in progress using BLOCKS method...
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 20.38 GB


Below is the import script and error -

;;; 
Import: Release 11.2.0.1.0 - Production on Tue Jan 7 21:11:44 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_05" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_05":  system/******** dumpfile=expdp_uatoltp_07-01-2014.dmp logfile=impdp_expdp_uatoltp_07-01-2014.log directory=DP_DIR version=11.2.0.1 SCHEMAS=INS,REINS,CNFG,RILOG,RNL,CONFSYS,BATJOB,DMS,DW,GEN,ACCEXT,RATEENG,MULTCURR,MS,TAGICRM exclude=statistics 
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/SYNONYM
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/INC_TYPE
Processing object type DATABASE_EXPORT/SCHEMA/TYPE/TYPE_SPEC
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE:"INS"."UW_VEHICLE_DTLS" failed to create with error:
ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified
Failing sql is:
CREATE TABLE "INS"."UW_VEHICLE_DTLS" ("NUM_REFER_NO" NUMBER(15,0), "DAT_REFERENCE_DATE" DATE, "TXT_REGISTRATION_NO" VARCHAR2(20 BYTE), "TXT_ENGINE_NO" VARCHAR2(30 BYTE), "TXT_CHASIS_NO" VARCHAR2(30 BYTE), "TXT_COVER_TYPE" VARCHAR2(50 BYTE), "TXT_VEHICLECLASS" VARCHAR2(100 BYTE), "TXT_VEHICLESUBCLASS" VARCHAR2(100 BYTE), "NUM_POLICY_NUMBER" NUM
ORA-39083: Object type TABLE:"INS"."RISK_HEADERS" failed to create with error:
ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified
Failing sql is:
CREATE TABLE "INS"."RISK_HEADERS" ("REFERENCE_NUM" NUMBER(15,0) CONSTRAINT "SYS_C00191464" NOT NULL ENABLE NOVALIDATE, "REFERENCE_DATE" DATE CONSTRAINT "SYS_C00191465" NOT NULL ENABLE NOVALIDATE, "PRODUCT_INDEX" VARCHAR2(10 BYTE), "MODULE_INDEX" VARCHAR2(10 BYTE), "LOCATION_CODE" VARCHAR2(50 BYTE), "POLICY_RISK_SERIAL" NUMBER(8,0) CONSTRAINT "SYS
ORA-39083: Object type TABLE:"INS"."RISK_DETAILS" failed to create wit

Report message to a moderator

Re: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605145 is a reply to message #605142] Tue, 07 January 2014 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

So you have to upgrade (best solution) or precreate the table that raises the error.

Report message to a moderator

Re: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605511 is a reply to message #605145] Fri, 10 January 2014 02:35 Go to previous messageGo to next message
ishika_20
Messages: 339
Registered: December 2006
Location: delhi
Senior Member
Yes Mr. Michel,

After upgrading. Import work completely successfully.

Thanks

Regards,
Ishika

Report message to a moderator

Re: ORA-14460: only one COMPRESS or NOCOMPRESS clause may be specified [message #605514 is a reply to message #605511] Fri, 10 January 2014 03:11 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Thanks for the feedback.

Report message to a moderator

Previous Topic: Bug in sqlldr using trim in ctl (control file)
Next Topic: Table reorganization
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Feb 06 22:03:57 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.