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

Saturday, July 25, 2009

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.




Friday, July 24, 2009

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

Thursday, July 23, 2009

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.
 
Share/Bookmark