Create partition is using timestamp with local time zone [Oracle 11g]

Question

I try to find one solution for my problem when create a partition in oracle database 11g. I think partition can't be created on datatype "TIMESTAMP WITH LOCAL TIME ZONE", Is it true?

I find the tread below that talk about this subject: https://community.oracle.com/message/9515330

Anyone know the solution for this problem?

Code Example:

CREATE TABLE "XPTO" 
(
  "XPTO_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE
)
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TABLES"
PARTITION BY RANGE ("XPTO_DATE") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
(
  PARTITION "P1" VALUES LESS THAN (TIMESTAMP' 2013-01-01 00:00:00')
  SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  TABLESPACE "TABLES"
 )

SQL Error: ORA-14020 - "this physical attribute may not be specified for a table partition"...

You're getting ORA-14020, not ORA-14751: Invalid data type for partitioning column of an interval partitioned table? I think you're right though... — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2014-11-19T16:58:00

You can't interval partition on a column of that type. You could potentially add a virtual column that has the UTC equivalent of you local time zone timestamp, and partition on that instead:

CREATE TABLE "XPTO" 
(
  "XPTO_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE,
  "XPTO_GMT" TIMESTAMP (6) GENERATED ALWAYS AS (SYS_EXTRACT_UTC("XPTO_DATE"))
)
STORAGE(BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TABLES"
PARTITION BY RANGE ("XPTO_GMT") INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
(
  PARTITION "P1" VALUES LESS THAN (TIMESTAMP '2013-01-01 00:00:00 UTC')
  SEGMENT CREATION DEFERRED 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  NOCOMPRESS LOGGING
  TABLESPACE "TABLES"
 );

table "XPTO" created.

The partitions will be roughly the same size but the boundaries will be a bit different, but that may or not matter - I guess it would be a problem for partition swaps but not sure if it would have any other noticeable effect for you.

Create partition is using timestamp with local time zone [Oracle 11g]

1 Answers