effect on changing system clock

[a128]

I am trying to build an Oracle application with time-sensitive data. Since this is a demo system and the data set only works correctly if the system time is set to certain date, I am trying to figure out what the effects on Oracle are if I keep rolling back system clock to a certain date without stopping Oracle/reboot the system. For one thing, I know if the tables were created after the date I roll back to, export will complain because the tables should not exist yet.

As an example why I need to roll back the system clock: when user clicks on 'quarter to date' button in my page, it should show the data since the beginning of this current quarter. If I don't put in new data, as time goes by, the 'quarter to date' will not return anything after the next quarter starts. Thus I need to roll back the system clock in order to show some results.

Does anybody know any other effects when system clock got rolled back?

Any inputs are appreciated!

[JChiappa]

Dear friend : the Oracle database per se will ** IGNORE ** the system clock : see, the database controls itself using an internal number, a "sequence" called SCN, not the system clock. So, if you change the system clock, the data WILL be rigorously THE SAME, and the database WILL be self-consistent.
So, what will be your problems/difficulties if you change the clock ? Basically things/actions based in date/time - JOBs, RECOVERs UNTIL time, things like that.

Now : your allegation about IMP being time/date dependent is , in the best of my knowledge, PURE and TOTAL b.s. : see the example (ignore the text messages in another language, it´s just the routine sql*plus and MS-DOS messages) :


system:SQL>select TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
04/03/2004 21:39:06

1 linha selecionada.


system:SQL>create table TAB_TEST_EXP (c1 number, c2 varchar2(40));

Tabela criada.

system:SQL>insert into TAB_TEST_EXP values(1, 'Line 1');

1 linha criada.

system:SQL>insert into TAB_TEST_EXP values(2, 'Line 2');

1 linha criada.

system:SQL>commit;

Validação completa.

system:SQL>show user
USER é "SYSTEM"

==> table created in 2004, march, ok ?

Microsoft Windows 2000 [Versão 5.00.2195]
(C) Copyright 1985-2000 Microsoft Corp.

C:\>date /t
qui 04/03/2004

==> ok, let´s export ....

C:\>exp system/manager@o8ir3 file=system_exp.dmp direct=y compress=n statistics=none tables=TAB_TEST_EXP

Export: Release 8.1.7.0.0 - Production on Qui Mar 4 21:44:10 2004

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Conectado a: Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production
ExportaþÒo executada no conjunto de caracteres de WE8ISO8859P1 e no conjunto de caracteres de WE8ISO8859P1 NCHAR

Sobre exportar tabelas especificadas ... via Caminho Direto ...
. . exporting table TAB_TEST_EXP 2 linhas exportadas
ExportaþÒo encerrada com Ûxito, sem advertÛncias.

===>>> now I will change the system clock, see the proofs :

system:SQL>select TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
04/03/2003 21:47:47

1 linha selecionada.

system:SQL>drop table TAB_TEST_EXP;

Tabela eliminada.


C:\>date /t
ter 04/03/2003

===> the key point : I will try to import a .dmp with a table created AFTER the system clock date ...


C:\>imp system/manager@o8ir3 file=system_exp.dmp analyze=n full=y

Import: Release 8.1.7.0.0 - Production on Ter Mar 4 21:49:28 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.


Conectado a: Personal Oracle8i Release 8.1.7.0.0 - Production
With the Partitioning option
JServer Release 8.1.7.0.0 - Production

Arquivo de exportaþÒo criado por EXPORT:V08.01.07 via caminho direto
importaþÒo realizada nos conjuntos de caracteres WE8ISO8859P1 e NCHAR WE8ISO8859P1
. importando objetos de SYSTEM para SYSTEM
. . importando table "TAB_TEST_EXP" 2 linhas importadas
ImportaþÒo encerrada com Ûxito, sem advertÛncias.


system:SQL>select TO_CHAR(SYSDATE, 'dd/mm/yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD
-------------------
04/03/2003 21:50:25

1 linha selecionada.

system:SQL>select * from TAB_TEST_EXP;

C1 C2
---------- ----------------------------------------
1 Line 1
2 Line 2

2 linhas selecionadas.

system:SQL>

===>>>WOW!! TOTAL and ABSOLUTE success : case dismissed......

SUGGESTION : I suppose that the reason for you wanting to change the system clock is to do something like that :

select datecolumn - SYSDATE from sometable

and you DO NOT want the correct/real day/time in SYSDATE, so think about writing a MY_SYSDATE function returning the desired date (reading it from a parameter/auxiliar table, maybe)

Regards,

Chiappa

[KenEwald]

Sounds like you're creating a monster. I'm not sure I understand why you're "rolling back" the system clock. Why not just execute your query with the criteria "sysdate - value"?

But then again .. I may be wrong.
- Dennis Miller

[a128]

JChiappa,

Thank you for the elaborated example showing me your point. However, if I am not mistaken, you rolled back your clock AFTER the export. My original post said: "For one thing, I know if the tables were created after the date I roll back to, export will complain because the tables should not exist yet.". Maybe I did not make it clear the export was done AFTER the clock is rolled back to prior table creation date.

Here is the sequence of events for my scenerio:

1. 1/2/2004: tables are created
2. 1/3/2004: data inserted into the tables
3. I roll back the clock to 1/1/2004
4. do an export on 1/1/2004, during the export, exp will complain. (pardon me for not posting the exact messages. I will do it once the demo is system is not needed for demo)

As for why I need to do this: the key point here is it's a DEMO system (not a PRODUCTION system) with a fixed set of data, which are entered only once. As an example,

1. Data entered on 1/2/2004
2. In January 2004, when I query 'month-to-date', I get the data above returned.
3. This is a demo system with a fixed set of data entered in Step 1 above. And I don't enter any more data ever.
2. Now it's 3/4/2004. and I try to do a query 'month-to-date', I get nothing back because it's already March 2004.

Thus when I need to do a demo, I need to roll back to January 2004 in order to show the data.

Does this make sense or do I miss something here?

[Rickenbacker]

As JChiappa points out, point in time recovery is an issue, so beware.

For one thing, I know if the tables were created after the date I roll back to,
export will complain because the tables should not exist yet.

You may well get an error from export, but how do you know it is "because the tables
should not exist yet."?

I bet you can select from them, even though they shouldn't exist yet.