In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Thursday, July 30, 2009

sysdate in oracle database

TO view system date and time

SQL> select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
30-jul-2009 10:30:32

SQL> select sysdate from dual;

SYSDATE
---------
30-JUL-09

SQL> !date
Thu Jul 30 10:30:47 IST 2009

To change sysdate

SQL> alter system set fixed_date='2003-01-01-10:00:00';

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
01-JAN-03

If you want to remove the parameter and go back to the operating system date and time, you will need to bounce the database.
SQL> ALTER SYSTEM RESET fixed_date SCOPE=SPFILE SID='*';

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
30-JUL-09

otherwise,

SQL> alter system set fixed_date='2003-01-01-10:00:00';

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
01-JAN-03

SQL> alter system set fixed_date='none';

System altered.

SQL> select sysdate from dual;

SYSDATE
---------
30-JUL-09

Usually SYSDATE depands on OS date but if we need to set or fixed sysdate any other date from current sysdate then we can achieve this task by one parameter at system level.

FIXED_DATE parameter name.

Value = Date / NONE

to find host name
SQL> select host_name from v$instance;

HOST_NAME
----------------------------------------------------------------
Linux1

SQL> !uname -a
Linux Linux1 2.6.9-67.0.0.0.1.ELhugemem #1 SMP Sun Nov 18 00:31:12 EST 2007 i686 i686 i386 GNU/Linux


with the help of blogs:
http://it.toolbox.com/blogs/database-solutions/altering-oracles-value-returned-for-sysdate-14034
http://dbataj.blogspot.com/2009/07/how-to-set-sysdate-in-oracle-database.html
http://forums.dbasupport.com/forums/archive/index.php/t-33634.html

No comments:

 
Share/Bookmark