Hi ,
I have an oracle 8.1.6 running on solaris platform. This database have a
standby DB of same version and OS platform for query or report generation.
At day time the standby DB is in read-only mode and the archived logs are
applied at night.
Last week, this production DB have a maintenance and added 4 datafiles to
different tablespaces and this physical changes have been re-structed at the
standby DB also. The standby DB have been successfully recovered through the
archived logs and I can turn back it to read only mode the next for users to
query.
However, this week some users complained that their query report return "no
rows selected" for selecting the column A ( which is a unix timestamp) on a
table named SERVICE . Then I checked it with simple query like below:
select columnB from SERVICE where columnA = 10283848;
Table : SERVICE
columnA NUMBER(38)
columnB NUMBER(38)
columnC VARCHAR2(255)
columnD NUMBER(38)
columnE NUMBER(38)
columnF NUMBER(38)
columnG NUMBER(38)
It returned " no rows selected" ONLY if the timestamp number ( columnA ) I
choosed was after the day of prod DB maintenance. And I found that not only
columnA , but also all the columns with datatype NUMBER(38) got this problem
If I tried the SQL like this :
select columnB from SERVICE where columnA like '10283848'; or apply
'tochar' on columnA
It will returned the rows I needed !!
The total number of rows of this table is the same as the one in production
DB
Can anyone help to give some hint to troubleshoot what's happening in this
table ?
Thanks in advance
James
I have an oracle 8.1.6 running on solaris platform. This database have a
standby DB of same version and OS platform for query or report generation.
At day time the standby DB is in read-only mode and the archived logs are
applied at night.
Last week, this production DB have a maintenance and added 4 datafiles to
different tablespaces and this physical changes have been re-structed at the
standby DB also. The standby DB have been successfully recovered through the
archived logs and I can turn back it to read only mode the next for users to
query.
However, this week some users complained that their query report return "no
rows selected" for selecting the column A ( which is a unix timestamp) on a
table named SERVICE . Then I checked it with simple query like below:
select columnB from SERVICE where columnA = 10283848;
Table : SERVICE
columnA NUMBER(38)
columnB NUMBER(38)
columnC VARCHAR2(255)
columnD NUMBER(38)
columnE NUMBER(38)
columnF NUMBER(38)
columnG NUMBER(38)
It returned " no rows selected" ONLY if the timestamp number ( columnA ) I
choosed was after the day of prod DB maintenance. And I found that not only
columnA , but also all the columns with datatype NUMBER(38) got this problem
If I tried the SQL like this :
select columnB from SERVICE where columnA like '10283848'; or apply
'tochar' on columnA
It will returned the rows I needed !!
The total number of rows of this table is the same as the one in production
DB
Can anyone help to give some hint to troubleshoot what's happening in this
table ?
Thanks in advance
James