partitiong on timestamp with local timezone data type

  • From: Dba DBA <oracledbaquestions@xxxxxxxxx>
  • To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 23 May 2013 13:45:13 -0400

Production OS: Solaris (don't know the version)
We insert date data that comes in a variety of timezones. We can tell what
timezone each record is in. We need to normalize this date field to the
timezone of our DB. We partition on this field.
Timestamp with Local Timezone appears to meet this requirement and
normalize it for me as long as I state the local timezone when I insert and
set the dbtimezone parameter.

Concern (I am going to test this, but I want to make sure my test case is
set up correctly).
-- what happen sif the Timezone of the OS is out of sync with what we have
set in dbtimezone?
-- my understanding is that Oracle figures out what partition to put date
data into base don the system clock which comes from an OS call.
-- The production team is from another company and I have no contact with
them. So mistakes can happen.
-- We partition by hour and we absolutely have to get data out of our
system based on a specific number of hours. If data is here 1 hour late, it
is a big problem.

My concern may be totally off base because I have not worked with this data
type yet.

I am planning on running the following tests.
1. create table, partition by range on timestamp with local timezone by
insert records in a variety of different timezones. Verify this is correct.
2. change the OS clock without changing the dbtimezone parameter.
3. see what the data looks like when I query it.

Test 2:
with the OS time out of sync with the DB timezone
1. insert records to the table
2. see what they look like.
3. figure out what partition they are in.


Other related posts: