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

Triggers

A trigger is a stored subprogram associated with a table, view, or event. The trigger can be invoked once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be invoked before or after the event.

The trigger in example is invoked whenever salaries in the employees table are updated. For each update, the trigger writes a record to the emp_audit table.

Example:

Creating a Trigger

SQL> CREATE TABLE emp_audit (
2    emp_audit_id  NUMBER(6),
3    up_date       DATE,
4    new_sal       NUMBER(8,2),
5    old_sal       NUMBER(8,2)
6  );

Table created.

SQL> CREATE OR REPLACE TRIGGER audit_sal
2    AFTER UPDATE OF salary
3      ON employees
4        FOR EACH ROW
5  BEGIN
6    INSERT INTO emp_audit
7      VALUES(:old.employee_id,SYSDATE,:new.salary,
            :old.salary);
8  END;
9  /

Trigger created.

SQL> update employees set salary=5000
2  where salary < 3000;

24 rows updated.


SQL> select * from emp_audit;
EMP_AUDIT_ID UP_DATE      NEW_SAL    OLD_SAL
------------ --------- ---------- ----------
   116 25-JUL-09       5000       2900
   117 25-JUL-09       5000       2800
   118 25-JUL-09       5000       2600
   119 25-JUL-09       5000       2500
   126 25-JUL-09       5000       2700
   127 25-JUL-09       5000       2400
   128 25-JUL-09       5000       2200
   130 25-JUL-09       5000       2800
   131 25-JUL-09       5000       2500
   132 25-JUL-09       5000       2100
   134 25-JUL-09       5000       2900

EMP_AUDIT_ID UP_DATE      NEW_SAL    OLD_SAL
------------ --------- ---------- ----------
   135 25-JUL-09       5000       2400
   136 25-JUL-09       5000       2200
   139 25-JUL-09       5000       2700
   140 25-JUL-09       5000       2500
   143 25-JUL-09       5000       2600
   144 25-JUL-09       5000       2500
   182 25-JUL-09       5000       2500
   183 25-JUL-09       5000       2800
   190 25-JUL-09       5000       2900
   191 25-JUL-09       5000       2500
   195 25-JUL-09       5000       2800

EMP_AUDIT_ID UP_DATE      NEW_SAL    OLD_SAL
------------ --------- ---------- ----------
   198 25-JUL-09       5000       2600
   199 25-JUL-09       5000       2600

24 rows selected.

ORA-27121

ORACLE:/home/oracle>su - txnmgr
Password:
DEV:/d02/txnmgr>sqlplus

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 24 17:15:40 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: ftuser
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27121: unable to determine size of shared memory segment
Linux Error: 13: Permission denied

solution:
ORACLE:/home/oracle>cd $ORACLE_HOME/bin
ORACLE:/d01/app/oracle/product/10.2.0/db_1/bin>chmod 6751 oracle
ORACLE:/d01/app/oracle/product/10.2.0/db_1/bin>

Enter user-name: ftuser
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from dual;

D
-
X

ora-00054 error

SQL> alter table FUND_TRANSFER_TBL enable row movement;
alter table FUND_TRANSFER_TBL enable row movement
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


SQL> col username format a20
col sess_id format a10
col object format a25
col mode_held format a10
select oracle_username || ' (' || s.osuser || ')' username
, s.sid || ',' || s.serial# sess_id
, owner || '.' || object_name object
, object_type
, decode( l.block
, 0, 'Not Blocking'
, 1, 'Blocking'
, 2, 'Global') status
, decode(v.locked_mode
, 0, 'None'
, 1, 'Null'
, 2, 'Row-S (SS)'
, 3, 'Row-X (SX)'
, 4, 'Share'
, 5, 'S/Row-X (SSX)'
, 6, 'Exclusive', TO_CHAR(lmode)) mode_held
from v$locked_object v
, dba_objects d
, v$lock l
, v$session s
where v.object_id = d.object_id
and v.object_id = l.id1
and v.session_id = s.sid
order by oracle_username
, session_id
/


USERNAME SESS_ID OBJECT OBJECT_TYPE
-------------------- ---------- ------------------------- -------------------
STATUS MODE_HELD
------------ ----------
FTUSER (txnmgr) 159,20 FTUSER.FUND_TRANSFER_TBL TABLE
Not Blocking Row-X (SX)

SQL> select object_name, o.object_id from user_objects o, v$locked_object l
2 where o.object_id = l.object_id
3 /

OBJECT_NAME
-------------------------------------------------------------------------------- OBJECT_ID
----------
FUND_TRANSFER_TBL
54953



SQL> alter system kill session '159,20';

System altered.

SQL> alter table fund_transfer_tbl enable row movement;

Table altered.