partitiong on timestamp with local timezone data type

From: Dba DBA <oracledbaquestions_at_gmail.com>
Date: Thu, 23 May 2013 13:45:13 -0400
Message-ID: <CAE-dsOJaFYc8V=+VwO3rTymheS1n0Q-cVVyzBKUM5r0M_xfarA_at_mail.gmail.com>

Oracle 11.2.0.3
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 hour.
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.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 23 2013 - 19:45:13 CEST