Hi,

Even simple "INSERT INTO table1 ( column1 ) SELECT col1 FROM table2" generates this issue on TIMESTAMP column.

Regards

...

select to_char(eventtime,'DD/MM/YYYY HH24:MI:SS.FF6 TZR') dt, dump(eventtime) dmp 
from lbt_archive_170320_exp 
where eventtime > sysdate;

DT	DMP
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
00/00/2047 16:50:57.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,13,171,58,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225
03/01/2047 08:51:52.251857 ASIA/MUSCAT	Typ=231 Len=11: 120,147,0,0,77,172,53,15,3,7,225

This wrong dates are generated in normal insert into from select * from source table.

OS is windows 2003 server

Both databases running on windows 2003. I will send the NLS settings tomorrow, I could reproduce the same issue via insert into. I execute the insert into from the target oracle database and do the select * from sourcetable@dblink which inserts data with 10% corrupted time stamp.

...
How to use {code} tags and make your code easier to read
...

...
Before, Please read How to use [code] tags and make your code easier to read.

*BlackSwan added {code} tags. PLEASE do so yourself in the future. You have been TWICE how to do so!

...
Once again, read How to use [code] tags and make your code easier to read.

...
how can reproduce what you report?

...
How can we reproduce what you claim above (which is offered without ANY proof)?
...

...
As BlackSwan said and repeated, how can we reproduce what you say?

...
Either provide us reproducible test case or stop wasting everyone's time with your useless & baseless tales of woe.

I am sorry ...

Here i am explaining my issue with all details,

My issue is when i am trying to copy old data to backup database from live database, some very old dates are getting inserted to the backup db along with the correct data.

The OS version is windows 2003
DB version for both live and backup db is 10.2.0.3.0

the wrong date format will look like

EVENTTIME
0002-12-02 07.11.35.453183457 PM
0002-12-03 10.52.04.453183457 PM
0002-12-03 10.52.04.453183457 PM
0002-12-03 10.52.04.453183457 PM

but none of these are there in the live database.

The data is copied to the backup db is through db link using

insert into backup_db_table
select * from live_table@dblink

And when we copy within 100000 rows, the wrong data is not getting entered. When the row count goes up more than 300000 rows, this issue is appearing in the column.

below i am copying the nls parameters for the backup db at session level.

select * from nls_session_parameters;

PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT YYYY-MM-DD HH.MI.SS.FF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

db version is

select * from v$version;

BANNER
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production
PL/SQL Release 10.2.0.3.0 - Production
"CORE 10.2.0.3.0 Production"
TNS for 64-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

below showing the nls session parameters at live db

select * from nls_session_parameters;

PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE

I tried to resolve using cursor, but it was returning with ora error as below;
Error report:
ORA-01804: failure to initialize timezone information
ORA-06512: at line 13
01804. 00000 - "failure to initialize timezone information"
*Cause: The timezone information file was not properly read.
*Action: Please contact Oracle Customer Support.


The pl/sql i used was

create or replace procedure test_old_date
as
p_array_size PLS_INTEGER DEFAULT 1000;
TYPE ARRAY IS TABLE OF lbt_test1%ROWTYPE;
l_data ARRAY;

CURSOR c IS SELECT * FROM lbt_bagtracking@totestdate where eventtime>systimestamp-10/1440;

BEGIN
execute immediate 'alter session set nls_timestamp_format = ''YYYY-MM-DD HH.MI.SS.FF AM'''; 
execute immediate 'alter session set time_zone = ''Asia/Muscat''';
    OPEN c;
    LOOP
    FETCH c BULK COLLECT INTO l_data LIMIT p_array_size;

    FORALL i IN 1..l_data.COUNT
    INSERT INTO lbt_test1 VALUES l_data(i);

    EXIT WHEN c%NOTFOUND;
    END LOOP;
    CLOSE c;
END test_old_date;