Oracle Tips and Tricks — David Fitzjarrell

December 1, 2015

Export-Import Business

Filed under: General — dfitzjarrell @ 07:22

"Don't you know anything at all about numbers?"

"Well, I don't think they're very important," snapped Milo, too embarrassed to admit the truth.

"NOT IMPORTANT!" roared the Dodecahedron, turning red with fury. "Could you have tea for two without
the two -- or three blind mice without the three? Would there be four corners of the earth if there
weren't a four? And how would you sail the seven seas without a seven?"

"All I meant was--" began Milo, but the Dodecahedron, overcome with emotion and shouting furiously,
carried right on.

"If you had high hopes, how would you know how high they were? And did you know that narrow escapes
come in all different widths? Would you travel the whole wide world without ever knowing how wide it
was? And how could you do anything at long last," he concluded, waving his arms over his head,
"without knowing how long the last was? Why, numbers are the most beautiful and valuable things in
the world. Just follow me and I'll show you." He turned on his heel and stalked off into the cave. 

Norton Juster, The Phantom Tollbooth 

Numbers mean a lot of things, and in Oracle 12.1.0.2 they can spell trouble for interval-partitioned tables. Oracle Support received notification of this back in March of 2015 and as of the most recent update to the SR the issue hasn’t been solved. Let’s look at what can happen with an interval-partitioned table and Data Pump.

Data Pump export and import usually function without error but an interval-partitioned table in release 12.1.0.2 causes some serious trouble. The example below shows the problem, and was modified from the example provided in the bug report by changing table and column names:


SQL> 
SQL> CREATE TABLE "EXP_IMP_TEST"
  2  (	     "KEY_NR" NUMBER(6,0) NOT NULL ENABLE,
  3  	     "VERS" NUMBER(5,0) NOT NULL ENABLE,
  4  	     "MY_DAT" DATE NOT NULL ENABLE,
  5  	     "YONP" NUMBER(3,0) NOT NULL ENABLE,
  6  	     "LAST_MODIFY_TIME" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE,
  7  	      CONSTRAINT "IX_PK_WAL" PRIMARY KEY ("KEY_NR","VERS", "MY_DAT","YONP")
  8    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  9    STORAGE(
 10    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 11    LOCAL (PARTITION "P_EXP_IMP_201301"  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
 12    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 13    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 14    TABLESPACE USERS )  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 15    STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 16    TABLESPACE USERS
 17    PARTITION BY RANGE ("KEY_NR") INTERVAL (100)
 18    (PARTITION "P_EXP_IMP_201301"  VALUES LESS THAN (201401) SEGMENT CREATION IMMEDIATE
 19    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
 20    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 21    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 22    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 23    TABLESPACE USERS )  ENABLE ROW MOVEMENT;

Table created.

SQL> 
SQL>  insert into EXP_IMP_TEST("KEY_NR","VERS", "MY_DAT", "YONP") values(999901,1,sysdate,1);

1 row created.

SQL>   commit;

Commit complete.

SQL> 
SQL> $ expdp bing/*********@yinski directory=data_pump_dir2 tables=EXP_IMP_TEST dumpfile=exp_imp_20b.dmp logfile=te_tst_log10b.log
...
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "BING"."SYS_EXPORT_TABLE_03":  bing/********@yinski directory=data_pump_dir2 tables=EXP_IMP_TEST dumpfile=exp_imp_30b.dmp logfile=te_tst_log10b.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 8.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
. . exported "BING"."EXP_IMP_TEST":"SYS_P9681"           6.929 KB       1 rows
. . exported "BING"."EXP_IMP_TEST":"P_EXP_IMP_201301"        0 KB       0 rows
Master table "BING"."SYS_EXPORT_TABLE_03" successfully loaded/unloaded
******************************************************************************
Dump file set for BING.SYS_EXPORT_TABLE_03 is:
  C:\USERS\APP\ORACLE\ADMIN\SMED1234\DPDUMP\EXP_IMP_30B.DMP
Job "BING"."SYS_EXPORT_TABLE_03" successfully completed at Mon Nov 30 14:36:54 2015 elapsed 0 00:02:40

So far, so good, the export completes successfully. Let’s try to import that table into another schema in the same database:


SQL> 
SQL> $ impdp bing/*********@yinski directory=data_pump_dir2 dumpfile=exp_imp_20b.dmp tables=EXP_IMP_TEST remap_schema=BING:BONG logfile=ti_tst_log10b.log
...
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "BING"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "BING"."SYS_IMPORT_TABLE_02":  bing/********@yinski directory=data_pump_dir2 dumpfile=exp_imp_20b.dmp tables=EXP_IMP_TEST remap_schema=BING:BONG logfile=ti_tst_log10b.log
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"BONG"."EXP_IMP_TEST" failed to create with error:
ORA-01438: value larger than specified precision allowed for this column
Failing sql is:
CREATE TABLE "BONG"."EXP_IMP_TEST" ("KEY_NR" NUMBER(6,0) NOT NULL ENABLE, "VERS" NUMBER(5,0) NOT NULL ENABLE,
"MY_DAT" DATE NOT NULL ENABLE, "YONP" NUMBER(3,0) NOT NULL ENABLE, "LAST_MODIFY_TIME" TIMESTAMP (6)
DEFAULT SYSTIMESTAMP NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39112: Dependent object type INDEX:"BONG"."IX_PK_WAL" skipped, base object type TABLE:"BONG"."EXP_IMP_TEST" creation failed
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"BONG"."IX_PK_WAL" skipped, base object type TABLE:"BONG"."EXP_IMP_TEST" creation failed
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "BING"."SYS_IMPORT_TABLE_02" completed with 3 error(s) at Mon Nov 30 14:24:15 2015 elapsed 0 00:02:06

Oracle throws a precision error due to the number column declarations. Let’s see if increasing the length of the NUMBER columns improves the situation:


SQL> 
SQL> CREATE TABLE "EXP_IMP_TEST2"
  2  (	     "KEY_NR" NUMBER(10) NOT NULL ENABLE,
  3  	     "VERS" NUMBER(10) NOT NULL ENABLE,
  4  	     "MY_DAT" DATE NOT NULL ENABLE,
  5  	     "YONP" NUMBER(10) NOT NULL ENABLE,
  6  	     "LAST_MODIFY_TIME" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE,
  7  	      CONSTRAINT "IX_PK_WAL2" PRIMARY KEY ("KEY_NR","VERS", "MY_DAT","YONP")
  8    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  9    STORAGE(
 10    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 11    LOCAL (PARTITION "P_EXP_IMP2_201301"  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
 12    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 13    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 14    TABLESPACE USERS )  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 15    STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 16    TABLESPACE USERS
 17    PARTITION BY RANGE ("KEY_NR") INTERVAL (100)
 18    (PARTITION "P_EXP_IMP2_201301"  VALUES LESS THAN ('201401') SEGMENT CREATION IMMEDIATE
 19    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
 20    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 21    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 22    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 23    TABLESPACE USERS )  ENABLE ROW MOVEMENT;

Table created.

SQL> 
SQL>  insert into EXP_IMP_TEST2("KEY_NR","VERS", "MY_DAT", "YONP") values(999901,1,sysdate,1);

1 row created.

SQL>   commit;

Commit complete.

SQL> 
SQL> $ expdp bing/*******@yinski directory=data_pump_dir2 tables=EXP_IMP_TEST2 dumpfile=exp_imp_21b.dmp logfile=te_tst_log11b.log

The export output won’t be repeated as it’s the same as the previous example. Looking at the output from the import we see a difference:


SQL> 
SQL> $ impdp bing/*********@yinski directory=data_pump_dir2 dumpfile=exp_imp_21b.dmp tables=EXP_IMP_TEST2 remap_schema=BING:BONG logfile=ti_tst_log11b.log
...
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "BING"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "BING"."SYS_IMPORT_TABLE_02":  bing/********@yinski directory=data_pump_dir2 dumpfile=exp_imp_21b.dmp tables=EXP_IMP_TEST2 remap_schema=BING:BONG logfile=ti_tst_log11b.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BONG"."EXP_IMP_TEST2":"SYS_P9701"          6.937 KB       1 rows
. . imported "BONG"."EXP_IMP_TEST2":"P_EXP_IMP2_201301"      0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "BING"."SYS_IMPORT_TABLE_02" successfully completed at Mon Nov 30 14:44:47 2015 elapsed 0 00:02:03

Declaring the NUMBER columns larger than the expected data length seems to ‘fix’ the issue. Obviously if we eliminate any size declaration for those same columns the import succeeds, but let’s prove that with the next example:


SQL> 
SQL> CREATE TABLE "EXP_IMP_TEST3"
  2  (	     "KEY_NR" NUMBER NOT NULL ENABLE,
  3  	     "VERS" NUMBER NOT NULL ENABLE,
  4  	     "MY_DAT" DATE NOT NULL ENABLE,
  5  	     "YONP" NUMBER NOT NULL ENABLE,
  6  	     "LAST_MODIFY_TIME" TIMESTAMP (6) DEFAULT SYSTIMESTAMP NOT NULL ENABLE,
  7  	      CONSTRAINT "IX_PK_WAL3" PRIMARY KEY ("KEY_NR","VERS", "MY_DAT","YONP")
  8    USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
  9    STORAGE(
 10    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 11    LOCAL (PARTITION "P_EXP_IMP3_201301"  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
 12    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 13    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 14    TABLESPACE USERS )  ENABLE   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 15    STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 16    TABLESPACE USERS
 17    PARTITION BY RANGE ("KEY_NR") INTERVAL (100)
 18    (PARTITION "P_EXP_IMP3_201301"  VALUES LESS THAN (201401) SEGMENT CREATION IMMEDIATE
 19    PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
 20    STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 21    PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 22    BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT )
 23    TABLESPACE USERS )  ENABLE ROW MOVEMENT;

Table created.

SQL> 
SQL>  insert into EXP_IMP_TEST3("KEY_NR","VERS", "MY_DAT", "YONP") values(999901,1,sysdate,1);

1 row created.

SQL>   commit;

Commit complete.

SQL> 
SQL> $ expdp bing/********@yinski directory=data_pump_dir2 tables=EXP_IMP_TEST3 dumpfile=exp_imp_22b.dmp logfile=te_tst_log12b.log
(output not shown)

SQL> 
SQL> $ impdp bing/**********@yinski directory=data_pump_dir2 dumpfile=exp_imp_22b.dmp tables=EXP_IMP_TEST3 remap_schema=BING:BONG logfile=ti_tst_log12b.log
...
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "BING"."SYS_IMPORT_TABLE_02" successfully loaded/unloaded
Starting "BING"."SYS_IMPORT_TABLE_02":  bing/********@yinski directory=data_pump_dir2 dumpfile=exp_imp_22b.dmp tables=EXP_IMP_TEST3 remap_schema=BING:BONG logfile=ti_tst_log12b.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "BONG"."EXP_IMP_TEST3":"SYS_P9721"          6.789 KB       1 rows
. . imported "BONG"."EXP_IMP_TEST3":"P_EXP_IMP3_201301"      0 KB       0 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "BING"."SYS_IMPORT_TABLE_02" successfully completed at Mon Nov 30 14:50:07 2015 elapsed 0 00:02:13

Having number data reach the data length limit of NUMBER columns is a ‘no-no’ in Oracle 12.1.0.2 when it comes to interval-partitioned tables, it appears. Some industrious souls may decide that altering the column lengths of an existing table will correct the problem, but not for interval-partitioned tables since the partition key cannot be modified:


SQL> 
SQL> alter table exp_imp_test modify (key_nr number(10), vers number(9), yonp number(7));
alter table exp_imp_test modify (key_nr number(10), vers number(9), yonp number(7))
                                 *
ERROR at line 1:
ORA-14060: data type or length of a table partitioning column may not be 
changed 


SQL> 

Until this issue is resolved care must be taken when creating interval-partitioned tables in Oracle 12.1.0.2 as imports will fail if the lenth of the data values reaches the declared maximum the NUMBER column can contain. This not only affects ‘normal’ import operations but also those when using transportable tablespaces; the same error is generated in those cases.

Numbers, they’re not just painful at tax time anymore.

Blog at WordPress.com.