Saturday, October 2, 2010

ORA-14037 & nls_date_format

SQL> conn donghua/donghua
Connected.
SQL> create table t (rtime varchar2(31))
2 partition by range (rtime)
3 (partition p201001 values less than (to_date('20100201','YYYYMMDD')),
4 partition p201002 values less than (to_date('20100301','YYYYMMDD')),
5 partition p201003 values less than (to_date('20100401','YYYYMMDD')))
6
SQL> /
partition p201002 values less than (to_date('20100301','YYYYMMDD')),
*
ERROR at line 4:
ORA-14037: partition bound of partition "P201002" is too high


SQL> alter session set nls_date_format='YYYYMMDD';

Session altered.

SQL> create table t (rtime varchar2(31))
2 partition by range (rtime)
3 (partition p201001 values less than (to_date('20100201','YYYYMMDD')),
4 partition p201002 values less than (to_date('20100301','YYYYMMDD')),
5 partition p201003 values less than (to_date('20100401','YYYYMMDD')));

Table created.

SQL>


[oracle@rh5 ~]$ expdp donghua/donghua directory=dir1 dumpfile=t.dmp tables=T

Export: Release 11.2.0.2.0 - Production on Sat Oct 2 10:26:56 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Starting "DONGHUA"."SYS_EXPORT_TABLE_01": donghua/******** directory=dir1 dumpfile=t.dmp tables=T
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "DONGHUA"."T":"P201001" 0 KB 0 rows
. . exported "DONGHUA"."T":"P201002" 0 KB 0 rows
. . exported "DONGHUA"."T":"P201003" 0 KB 0 rows
Master table "DONGHUA"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for DONGHUA.SYS_EXPORT_TABLE_01 is:
/u11gr2/dir1/t.dmp
Job "DONGHUA"."SYS_EXPORT_TABLE_01" successfully completed at 10:27:12






[oracle@rh5 ~]$ impdp donghua/donghua directory=dir1 dumpfile=t.dmp tables=T table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Sat Oct 2 10:29:42 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "DONGHUA"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "DONGHUA"."SYS_IMPORT_TABLE_01": donghua/******** directory=dir1 dumpfile=t.dmp tables=T table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39083: Object type TABLE:"DONGHUA"."T" failed to create with error:
ORA-14037: partition bound of partition "P201002" is too high
Failing sql is:
CREATE TABLE "DONGHUA"."T" ("RTIME" VARCHAR2(31 BYTE)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARTITION BY RANGE ("RTIME") (PARTITION "P201001" VALUES LESS THAN (to_date('20100201','YYYYMMDD')) SEGMENT CREATION DEFERRED PCTFREE 10 PCTUSED 40 INITRANS 1 MA
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DONGHUA"."T":"P201001" 0 KB 0 rows
. . imported "DONGHUA"."T":"P201002" 0 KB 0 rows
. . imported "DONGHUA"."T":"P201003" 0 KB 0 rows
Job "DONGHUA"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 10:29:45


[oracle@rh5 ~]$ sqlplus donghua/donghua

SQL*Plus: Release 11.2.0.2.0 Production on Sat Oct 2 10:32:24 2010

Copyright (c) 1982, 2010, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> desc t
ERROR:
ORA-04043: object t does not exist