ORA-39083 on TTS Import (Doc ID 1307183.1)

Last updated on JULY 13, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 and later
Information in this document applies to any platform.

Symptoms

Receiving the following errors on TTS import:

ORA-39083: Object type TABLE failed to create with error:
ORA-14519: Conflicting tablespace blocksizes for table : Tablespace REPO_DATA_03 block size 16384 [partition-level default] conflicts with previously specified/implied tablespace USERS block size 8192
[object-level default]

Here is an example:

connect kim/kim

CREATE TABLE "REPO_FCT_VIS_ROLLBACK"
( "TRADE_NUMBER" VARCHAR2(20),
"BUSINESS_DATE" DATE NOT NULL ENABLE,
"TICKET" VARCHAR2(8) NOT NULL ENABLE,
"TRADE_MATCH_TYPE" NUMBER ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
PARTITION BY RANGE ("BUSINESS_DATE")
SUBPARTITION BY LIST ("BUSINESS_DATE")
(PARTITION SADE_DATA VALUES LESS THAN (TO_DATE(' 2006-04-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT)
TABLESPACE SADE_DATA
( SUBPARTITION "SP_F_REPO_VIS_20060301" VALUES (TO_DATE(' 2006-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE SADE_DATA));

select dbms_metadata.get_ddl('TABLE','REPO_FCT_VIS_ROLLBACK','KIM') from dual;


DBMS_METADATA.GET_DDL('TABLE','REPO_FCT_VIS_ROLLBACK','KIM')
--------------------------------------------------------------------------------

CREATE TABLE "KIM"."REPO_FCT_VIS_ROLLBACK"
( "TRADE_NUMBER" VARCHAR2(20),
"BUSINESS_DATE" DATE NOT NULL ENABLE,
"TICKET" VARCHAR2(8) NOT NULL ENABLE,
"TRADE_MATCH_TYPE" NUMBER
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
PARTITION BY RANGE ("BUSINESS_DATE")
SUBPARTITION BY LIST ("BUSINESS_DATE")
(PARTITION "SADE_DATA" VALUES LESS THAN (TO_DATE(' 2006-04-01 00:00:00', 'SYYY
Y-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
STORAGE(
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SADE_DATA"
( SUBPARTITION "SP_F_REPO_VIS_20060301" VALUES (TO_DATE(' 2006-03-01 00:00:00'
, 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
TABLESPACE "SADE_DATA" NOCOMPRESS ) )

exp kim/kim file=tts.dmp tables=REPO_FCT_VIS_ROLLBACK log=tts.log

drop table REPO_FCT_VIS_ROLLBACK;
Table dropped.

imp kim/kim file=tts.dmp full=y log=tts_imp.log

Import: Release 11.2.0.2.0 - Production on Wed Mar 9 07:39:31 2011
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, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. importing KIM's objects into KIM
. importing KIM's objects into KIM
IMP-00017: following statement failed with ORACLE error 14519:
"CREATE TABLE "REPO_FCT_VIS_ROLLBACK" ("TRADE_NUMBER" VARCHAR2(20), "BUSINES"
"S_DATE" DATE NOT NULL ENABLE, "TICKET" VARCHAR2(8) NOT NULL ENABLE, "TRADE_"
"MATCH_TYPE" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPA"
"CE "USERS" PARTITION BY RANGE ("BUSINESS_DATE" ) SUBPARTITION BY LIST ("B"
"USINESS_DATE" ) (PARTITION "SADE_DATA" VALUES LESS THAN (TO_DATE(' 2006-04"
"-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFRE"
"E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE "SADE_DATA" (SUBPARTITI"
"ON "SP_F_REPO_VIS_20060301" VALUES (TO_DATE(' 2006-03-01 00:00:00', 'SYYYY-"
"MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) TABLESPACE "SADE_DATA" ) )"
IMP-00003: ORACLE error 14519 encountered
ORA-14519: Conflicting tablespace blocksizes for table : Tablespace SADE_DATA block size 16384 [partition-level default] conflicts with previously specified/implied tablespace USERS block size 8192 [object-level default]
Import terminated successfully with warnings.



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms