During the synch-phase of an online redefintion, I received the error:
begin ORA-42009: error occurred while synchronizing the redefinition ORA-12008: error in materialized view or zonemap refresh path ORA-01843: not a valid month ORA-06512: at "SYS.DBMS_REDEFINITION", line 219 ORA-06512: at "SYS.DBMS_REDEFINITION", line 5392
Solution:
Alter your session setting for NLS_TERRITORY. Add the line
alter session set nls_territory='';
to your script.
This seems to be due to the fact that I am partitioning on a virtual column, using a column that is in turn of datatype TIMESTAMP:
CREATE TABLE USER1.TAB1_INTERIM
(
ENTRY_ID VARCHAR2(36 BYTE) NULL,
DNAME VARCHAR2(11 BYTE) NULL,
DTYPE VARCHAR2(64 BYTE) NULL,
CREATED_TIME TIMESTAMP(6) NULL,
DYEAR INTEGER NULL,
CREATED_DAY AS (
CAST(
TO_CHAR(CREATED_TIME,'DD.MM.YYYY')
AS DATE
)
) VIRTUAL
)
TABLESPACE MYTABLESPACE
PARTITION BY RANGE (CREATED_DAY)
INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION P_INIT VALUES LESS THAN (TO_DATE('01.01.2017', 'DD.MM.YYYY'))
TABLESPACE MYTABLESPACE
);
Since the creator of the database didn't set the NLS_TERRITORY to NORWAY, it is using the default value "AMERICA".
Without any altering of the default NLS setting, the CREATED_TIME column will therefore contain data formatted according to US standard:
select created_time from user1.tab1 fetch first 1 rows only; CREATED_TIME -------------------------- 26-SEP-17 09.30.17.040000 AM
I would like the data to be formatted according to Norwegian locale rules, so I use 'DD.MM.YYYY' in my virtual column. So I tested in sqlplus:
alter session set nls_territory='NORWAY'; select created_time from user1.tab1 fetch first 1 rows only; CREATED_TIME -------------------------- 26.09.2017 09.30.17,040000
So my script would look like this:
alter session set nls_territory='NORWAY'; begin DBMS_REDEFINITION.SYNC_INTERIM_TABLE(uname=>'USER1',orig_table=>'TAB1',int_table=>'TAB1_INTERIM'); end; /
and it completed successfully.
This is not really a solution to the problem, it is a workaround that doesnt really see what is wrong...
ReplyDeleteThe virtual column definition is the real problem here:
CREATED_DAY AS (
CAST(
TO_CHAR(CREATED_TIME,'DD.MM.YYYY') --to_char creates a string version of the date based on a specific nls format
AS DATE ---the cast as date doesnt specify any date format, so it defaults to the nls format of the current session
)
) VIRTUAL
)
The solution presented is to always use a session nls setting that is compatible with the hard coded date format in the to_char expression....not very global as solutions go.... The explanation that you have provided is based on the premise that virtual columns are actually stored and that this column is being stored in a specific nls format...
The virtual column is not stored, and even if it was, it would not stored be in any nls specific format unless it was stored as varchar2 data... dates are stored as dates, there is nothing format specific about the storage value of a date or timestamp column.
The intent of this virtual column is to get rid of the time portion of the timestamp, so why not just do that?
Try this approach and this problem cannot happen....instead of formatting the timestamp as a date with a hard coded format that will have nls dependencies, just truncate the date before casting it...
CREATED_DAY AS (
CAST( TRUNC(CREATED_TIME) AS DATE ) )
VIRTUAL )
This definition of the virtual column does exactly what was desired, it truncates the time portion of the timestamp and converts it to a date....