Tuesday, November 3, 2009
ORACLE INDEX REBUILD SCRIPT
REM:********************************************************************************************** REM: SCRIPT FOR Database Informations : Index Rebuild REM: Author: Kumar Menon REM: Date Submitted: 10.07.2009 REM:FileName: indexreb.sql REM: REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE. REM: Author will not be responsible for any damage that may be cause by this script. **************************************************************************************************** SET HEADING OFF SET FEEDBACK OFF SET LINESIZE 200 SET PAGESIZE 0 SPOOL rebtemp.sql SELECT 'SELECT to_char(SYSDATE,''YYYY/MM/DD HH24:MI:SS'') FROM dual;' FROM dual; SELECT 'ALTER INDEX '||owner||'.'||index_name||' REBUILD TABLESPACE '||tablespace_name||' ONLINE;' FROM dba_indexes WHERE owner IN ('&ownername','&ownername') ORDER BY owner, index_name; SELECT 'SELECT to_char(SYSDATE,''YYYY/MM/DD HH24:MI:SS'') FROM dual;' FROM dual; SPOOL OFF SET ECHO ON SET FEEDBACK ON SPOOL Al_Indexes.log @rebtemp.sql spool off
Saturday, October 31, 2009
TIME AND DATE SYNCHRONIZATION AMONG THE NODES IN ORACLE RAC
We may face date and time difference among various nodes in our Oracle RAC setup .
Some application are really time critical , such issues may lead to big business loss if ignored .
Shutdown your database on each node . Shutdown crs servervices as well to ensure all services are down .
We will be using ntpdate command to reset time in our server .
The ntpdate command sets the local date and time by polling the NTP servers specified to determine the correct time. It obtains a number of samples from each server specified and applies the standard NTP clock filter and selection algorithms to select the best of the samples.
RUN BELOW COMMAND FROM ROOT USER ON EACH NODE
#############################################
ora*****:root:/u01/app/oracle/product/10.2/crs/bin>ntpdate 10.111.35.21
29 Sep 18:32:36 ntpdate[462942]: step time server 10.111.35.21 offset 31.980096
Thursday, October 29, 2009
RESIZING SGA IN ORACLE RAC 10g USING SOLARIS PROJECT FILE
RESIZING SGA IN ORACLE RAC 10g USING SOLARIS PROJECT FILE
Its very crucial and important to have a well needed size of SGA . Here I will go thru some steps in order to resize SGA on oracle RAC 10g setup on Sun Solaris 10, when you are using /etc/project file to configure your oracle required memory instead of /etc/system file .
The recommended method for modifying the /etc/project file is to use the "proj*" commands,
such as projadd(1) for creating a project and projmod(1) for modifying a project.
Examples of projadd and projmod
a.) # projadd -c "Oracle" 'user.oracle'
b.) # projmod -s -K "project.max-shm-memory=(privileged,6GB,deny)" 'user.oracle'
** Resource Control assignments made in this way (in the /etc/project file) are permanent, and will survive a system re-boot.
** There is also an "on-the-fly" way to temporarily set Resource Control assignments using the prctl(1) command. However, unlike the /etc/project file, resource assignments made in this way will NOT survive a system re-boot.
My Exercise
In My system i found below entries for poracle project ( Oracle owner on system)
poracle:100:OracleProject:poracle:pdba:project.max-sem-ids=(priv,4096,deny);project.max-sem-nsems=(priv,4906,deny);project.max-sem-ops=(priv,4906,deny);project.max-shm-ids=(priv,256,deny)
I was unable to see parameter "project.max-shm-memory" , which actually define oracle required memory on system.
Here on the other hand when i try to increase my sga_target parameter from 4 GB to higher , My database was unable to come up with error defined Memory not available to extend .
While digging up more on proejct file inside metalink , I found if "max-shm-memory" is not defined in project file system has been designed in such a way where it allows oracle to use 1/4th of total available system memory .
That was really interesting as My system is having 16 GB of memory and out of 16 GB , 4 GB was usable for Oracle
due to default nature of project file in SOLARIS 10 .
Now it was straight for me to add "project.max-shm-memory" in project file in order to increase oracle required memory . Below command modified the existing poracle project and added shm max memory for Oralce user .
projmod -s -K "project.max-shm-memory=(privileged,8GB,deny)" 'user.poracle'
Login as root
Run below command
# projmod -s -K "project.max-shm-memory=(privileged,8GB,deny)" 'user.poracle'
#cat /etc/project
poracle:100:OracleProject:poracle:pdba:project.max-sem-ids=(priv,4096,deny);project.max-sem-nsems=(priv,4906,deny);project.max-sem-ops=(priv,4906,deny);project.max-shm-ids=(priv,256,deny);project.max-shm-memory=(priv,8589934592,deny)
Do it on all RAC nodes .
Newly assigned memory will available on each new session and will persist after server reboot .
Now happily you can increase your SGA (sga_target) size without prompting any error .
** You need to define only (sga_target= ..) in init.ora file When using automatic SGA . **
Tuesday, September 29, 2009
Good To Know Scripts 1
How to find current session ID
Way1:
------
select sid from v$session where audsid = sys_context('userenv','sessionid');
Way2:
------
select distinct sid from v$mystat;
Way3:
--------
If DBMS_SUPPORT is installed,
select dbms_support.mysid from dual;
------
select sid from v$session where audsid = sys_context('userenv','sessionid');
Way2:
------
select distinct sid from v$mystat;
Way3:
--------
If DBMS_SUPPORT is installed,
select dbms_support.mysid from dual;
-- Thanks to Arju
Sunday, September 20, 2009
How To Add/Increase The Size Of Redo Log Files In Rac Env
How To Add/Increase The Size Of Redo Log Files In Rac Env
n RAC, each instance has a redo thread; when you start the instance, it mounts one and only one thread. Therefore if you want to add logs or increase the size, you must do it for each thread. The goal is normally to have the same number and size of logs for each thread.
First, determine what log members each thread has.
Here is a sample of a script to show what log members you currently have and their sizes:
-- Script begins here --
--
-- Please note, this is a sample script
-- provided for educational purposes only
-- and is not certified by Oracle Support for any purpose.
spool log.lst
set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
spool off
-- End of script --
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
1 1 /acme/oraredo/acme/redo01.log NO CURRENT 50
1 1 /acme/oraredo/acme/redo01a.log NO CURRENT 50
2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03a.log YES ACTIVE 50
3 2 /acme/oraredo/acme/redo03.log YES ACTIVE 50
4 2 /acme/oraredo/acme/redo04a.log NO CURRENT 50
4 2 /acme/oraredo/acme/redo04.log NO CURRENT 50
Importent Points
1) Thread is same as Instance number in RAC env .
2) Our Goal is here to Add 6 group , 3 for each Thread(instance) size of 200MB .
3) At the end drop 50 MB group
ACTION PLAN
##############
1) Add 6 new redo log groups to Thread 1 AND 2, with 2 member each, the member 200 mb in size.
2) Once you have added them, rotate the logs ("alter system switch logfile") on instance 2 so that
the active log is 200mb and both of the 50mb logs are inactive
3) Once both of the 50 mb logs are inactive, you can drop the redo log groups with the 50 mb members.
Thread # 1
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '/acme/oraredo/acme/redo5a.log','/acme/oraredo/acme/redo5b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '/acme/oraredo/acme/redo6a.log','/acme/oraredo/acme/redo6b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 7 ( '/acme/oraredo/acme/redo7a.log','/acme/oraredo/acme/redo7b.log') SIZE 200M;
Thread#2
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '/acme/oraredo/acme/redo8a.log','/acme/oraredo/acme/redo8b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ( '/acme/oraredo/acme/redo9a.log','/acme/oraredo/acme/redo9b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ( '/acme/oraredo/acme/redo10a.log','/acme/oraredo/acme/redo10b.log') SIZE 200M;
-> Result of Above query
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
1 1 /acme/oraredo/acme/redo01a.log YES ACTIVE 50
1 1 /acme/oraredo/acme/redo01.log YES ACTIVE 50
2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03.log NO CURRENT 50
3 2 /acme/oraredo/acme/redo03a.log NO CURRENT 50
4 2 /acme/oraredo/acme/redo04a.log YES ACTIVE 50
4 2 /acme/oraredo/acme/redo04.log YES ACTIVE 50
5 1 /acme/oraredo/acme/redo5a.log NO CURRENT 200
5 1 /acme/oraredo/acme/redo5b.log NO CURRENT 200
6 1 /acme/oraredo/acme/redo6a.log YES UNUSED 200
6 1 /acme/oraredo/acme/redo6b.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200
Action :
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
To move Current logs from old redo log file to newly added logfile .
4. Issue the below statement to make the Inactive status of active status and avoid the 'ORA-01624'.
SQL> alter system checkpoint global;
Check ####
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
1 1 /acme/oraredo/acme/redo01.log YES INACTIVE 50
1 1 /acme/oraredo/acme/redo01a.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03a.log YES INACTIVE 50
4 2 /acme/oraredo/acme/redo04.log YES INACTIVE 50
4 2 /acme/oraredo/acme/redo04a.log YES INACTIVE 50
5 1 /acme/oraredo/acme/redo5a.log YES INACTIVE 200
5 1 /acme/oraredo/acme/redo5b.log YES INACTIVE 200
6 1 /acme/oraredo/acme/redo6a.log NO CURRENT 200
6 1 /acme/oraredo/acme/redo6b.log NO CURRENT 200
7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200
8 2 /acme/oraredo/acme/redo8a.log NO CURRENT 200
8 2 /acme/oraredo/acme/redo8b.log NO CURRENT 200
9 2 /acme/oraredo/acme/redo9a.log YES UNUSED 200
9 2 /acme/oraredo/acme/redo9b.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10a.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10b.log YES UNUSED 200
Action :
## Time to Drop old REDO LOG file groups ( 50 MB redo Log groups )
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;
Check ####
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
5 1 /acme/oraredo/acme/redo5a.log YES INACTIVE 200
5 1 /acme/oraredo/acme/redo5b.log YES INACTIVE 200
6 1 /acme/oraredo/acme/redo6a.log NO CURRENT 200
6 1 /acme/oraredo/acme/redo6b.log NO CURRENT 200
7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200
8 2 /acme/oraredo/acme/redo8a.log NO CURRENT 200
8 2 /acme/oraredo/acme/redo8b.log NO CURRENT 200
9 2 /acme/oraredo/acme/redo9a.log YES UNUSED 200
9 2 /acme/oraredo/acme/redo9b.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10a.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10b.log YES UNUSED 200
Action : Check your log file for some time .
Result : You are Done now .
How To Add/Increase The Size Of Redo Log Files In Rac Env
How To Add/Increase The Size Of Redo Log Files In Rac Env
n RAC, each instance has a redo thread; when you start the instance, it mounts one and only one thread. Therefore if you want to add logs or increase the size, you must do it for each thread. The goal is normally to have the same number and size of logs for each thread.
First, determine what log members each thread has.
Here is a sample of a script to show what log members you currently have and their sizes:
-- Script begins here --
--
-- Please note, this is a sample script
-- provided for educational purposes only
-- and is not certified by Oracle Support for any purpose.
spool log.lst
set echo off
set feedback off
set linesize 120
set pagesize 35
set trim on
set trims on
set lines 120
col group# format 999
col thread# format 999
col member format a70 wrap
col status format a10
col archived format a10
col fsize format 999 heading "Size (MB)"
select l.group#, l.thread#,
f.member,
l.archived,
l.status,
(bytes/1024/1024) fsize
from
v$log l, v$logfile f
where f.group# = l.group#
order by 1,2
/
spool off
-- End of script --
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
1 1 /acme/oraredo/acme/redo01.log NO CURRENT 50
1 1 /acme/oraredo/acme/redo01a.log NO CURRENT 50
2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03a.log YES ACTIVE 50
3 2 /acme/oraredo/acme/redo03.log YES ACTIVE 50
4 2 /acme/oraredo/acme/redo04a.log NO CURRENT 50
4 2 /acme/oraredo/acme/redo04.log NO CURRENT 50
Importent Points
1) Thread is same as Instance number in RAC env .
2) Our Goal is here to Add 6 group , 3 for each Thread(instance) size of 200MB .
3) At the end drop 50 MB group
ACTION PLAN
##############
1) Add 6 new redo log groups to Thread 1 AND 2, with 2 member each, the member 200 mb in size.
2) Once you have added them, rotate the logs ("alter system switch logfile") on instance 2 so that
the active log is 200mb and both of the 50mb logs are inactive
3) Once both of the 50 mb logs are inactive, you can drop the redo log groups with the 50 mb members.
Thread # 1
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 5 ( '/acme/oraredo/acme/redo5a.log','/acme/oraredo/acme/redo5b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 6 ( '/acme/oraredo/acme/redo6a.log','/acme/oraredo/acme/redo6b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 7 ( '/acme/oraredo/acme/redo7a.log','/acme/oraredo/acme/redo7b.log') SIZE 200M;
Thread#2
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 8 ( '/acme/oraredo/acme/redo8a.log','/acme/oraredo/acme/redo8b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 9 ( '/acme/oraredo/acme/redo9a.log','/acme/oraredo/acme/redo9b.log') SIZE 200M;
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 10 ( '/acme/oraredo/acme/redo10a.log','/acme/oraredo/acme/redo10b.log') SIZE 200M;
-> Result of Above query
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
1 1 /acme/oraredo/acme/redo01a.log YES ACTIVE 50
1 1 /acme/oraredo/acme/redo01.log YES ACTIVE 50
2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03.log NO CURRENT 50
3 2 /acme/oraredo/acme/redo03a.log NO CURRENT 50
4 2 /acme/oraredo/acme/redo04a.log YES ACTIVE 50
4 2 /acme/oraredo/acme/redo04.log YES ACTIVE 50
5 1 /acme/oraredo/acme/redo5a.log NO CURRENT 200
5 1 /acme/oraredo/acme/redo5b.log NO CURRENT 200
6 1 /acme/oraredo/acme/redo6a.log YES UNUSED 200
6 1 /acme/oraredo/acme/redo6b.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200
Action :
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
To move Current logs from old redo log file to newly added logfile .
4. Issue the below statement to make the Inactive status of active status and avoid the 'ORA-01624'.
SQL> alter system checkpoint global;
Check ####
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
1 1 /acme/oraredo/acme/redo01.log YES INACTIVE 50
1 1 /acme/oraredo/acme/redo01a.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02.log YES INACTIVE 50
2 1 /acme/oraredo/acme/redo02a.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03.log YES INACTIVE 50
3 2 /acme/oraredo/acme/redo03a.log YES INACTIVE 50
4 2 /acme/oraredo/acme/redo04.log YES INACTIVE 50
4 2 /acme/oraredo/acme/redo04a.log YES INACTIVE 50
5 1 /acme/oraredo/acme/redo5a.log YES INACTIVE 200
5 1 /acme/oraredo/acme/redo5b.log YES INACTIVE 200
6 1 /acme/oraredo/acme/redo6a.log NO CURRENT 200
6 1 /acme/oraredo/acme/redo6b.log NO CURRENT 200
7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200
8 2 /acme/oraredo/acme/redo8a.log NO CURRENT 200
8 2 /acme/oraredo/acme/redo8b.log NO CURRENT 200
9 2 /acme/oraredo/acme/redo9a.log YES UNUSED 200
9 2 /acme/oraredo/acme/redo9b.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10a.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10b.log YES UNUSED 200
Action :
## Time to Drop old REDO LOG file groups ( 50 MB redo Log groups )
SQL> alter database drop logfile group 1;
SQL> alter database drop logfile group 2;
SQL> alter database drop logfile group 3;
SQL> alter database drop logfile group 4;
Check ####
GROUP# THREAD# MEMBER ARCHIVED STATUS Size (MB)
------ ------- ---------------------------------------------------------------------- ---------- --
5 1 /acme/oraredo/acme/redo5a.log YES INACTIVE 200
5 1 /acme/oraredo/acme/redo5b.log YES INACTIVE 200
6 1 /acme/oraredo/acme/redo6a.log NO CURRENT 200
6 1 /acme/oraredo/acme/redo6b.log NO CURRENT 200
7 1 /acme/oraredo/acme/redo7a.log YES UNUSED 200
7 1 /acme/oraredo/acme/redo7b.log YES UNUSED 200
8 2 /acme/oraredo/acme/redo8a.log NO CURRENT 200
8 2 /acme/oraredo/acme/redo8b.log NO CURRENT 200
9 2 /acme/oraredo/acme/redo9a.log YES UNUSED 200
9 2 /acme/oraredo/acme/redo9b.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10a.log YES UNUSED 200
10 2 /acme/oraredo/acme/redo10b.log YES UNUSED 200
Action : Check your log file for some time .
Result : You are Done now .
Saturday, September 19, 2009
What is enq: TX - row lock contention
What is enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
------------------------------------Thanks to Arju
What is enq: TX - row lock contention
Enqueues are locks that coordinate access to database resources. enq: wait event indicates that the session is waiting for a lock that is held by another session. The name of the enqueue is as part of the form enq: enqueue_type - related_details.
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
The V$EVENT_NAME view provides a complete list of all the enq: wait events.
TX enqueue are acquired exclusive when a transaction initiates its first change and held until the transaction does a COMMIT or ROLLBACK.
Several Situation of TX enqueue:
--------------------------------------
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
2) Waits for TX in mode 4 can occur if a session is waiting due to potential duplicates in UNIQUE index. If two sessions try to insert the same key value the second session has to wait to see if an ORA-0001 should be raised or not. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
The solution is to have the first session already holding the lock perform a COMMIT or ROLLBACK.
3)Waits for TX in mode 4 is also possible if the session is waiting due to shared bitmap index fragment. Bitmap indexes index key values and a range of ROWIDs. Each ‘entry’ in a bitmap index can cover many rows in the actual table. If two sessions want to update rows covered by the same bitmap index fragment, then the second session waits for the first transaction to either COMMIT or ROLLBACK by waiting for the TX lock in mode 4. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
Troubleshooting:
for which SQL currently is waiting to,
select sid, sql_text
from v$session s, v$sql q
where sid in (select sid
from v$session where state in ('WAITING')
and wait_class != 'Idle' and event='enq: TX - row lock contention'
and (
q.sql_id = s.sql_id or
q.sql_id = s.prev_sql_id));
The blocking session is,
SQL> select blocking_session, sid, serial#, wait_class, seconds_in_wait from v$session
where blocking_session is not NULL order by blocking_session;
------------------------------------
Thanks to Arju
RECENTLY MODIFIED OBJECTS IN DATABASE
Subject : When Timestamp and Last_Ddl_time columns of dba_objects will differ?
Doc ID: 309235.1 Type: BULLETIN
Modified Date: 18-DEC-2007 Status: PUBLISHED
PURPOSE
-------
The purpose of this document is to give a clear understanding on:
1.The meaning of Last_ddl_time and Timestamp column of dba_objects
2.Under what circumstances these 2 columns differ
SCOPE & APPLICATION
-------------------
For all analysts and customers to get a clear understanding of these
2 important columns of dba_objects
LAST_DDL_TIME and TIMESTAMP
-----------------------------
First we should understand the meaning of last_ddl_time and timestamp columns of
dba_objects with a small example
LAST_DDL_TIME -->Timestamp for the last modification of the object resulting
from a DDL command (including grants and revokes)
TIMESTAMP --->Timestamp for the specification of the object
+Created a table called YY
SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- -------------------
30-MAR-2005 21:08:34 2005-03-30:21:08:34
As it can be seen when the table is created both has the same value
SQL> GRANT SELECT ON YY TO PUBLIC; <-----------DDL performed
Grant succeeded.
SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- -------------------
30-MAR-2005 21:13:44 2005-03-30:21:08:34
It is seen that last_ddl_time is different,TIMESTAMP remains same as object creation
Now we will see under what circumstances last_ddl_time and timestamp will differ.
1.When DDL is performed on the dependent objects of the table.
For example when an index is created or altered.
2.When a table is truncated
3.When roles/privileges are granted/revoked.
A small example to better understand point 1 :
Test case:
-----------
SQL> CREATE TABLE YY(A NUMBER);
Table created.
SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- -------------------
31-MAR-2005 15:16:54 2005-03-31:15:16:54
SQL> CREATE INDEX YYI ON YY(A);
Index created.
SQL> select TO_CHAR(last_ddl_time,'DD-MON-YYYY HH24:MI:SS') DDL,timestamp from
dba_objects where object_name='YY';
DDL TIMESTAMP
-------------------- --------------
31-MAR-2005 15:21:53 2005-03-31:15:16:54
It can be clearly seen that though we did not perform any DDL operation on the table
but the creation of an index on the table changed its last_ddl_time.
Timestamp does not change
_____________________________________________________________
Another Good Example
From the dba_objects/user_objects/all_objects view you can know about the information about when an object(table,index,function etc) was created or when the last DDL operation was done against the table or when last compilation was done.
As of other oracle views.
DBA_OBJECTS contains all database objects.
USER_OBJECTS contains all objects that is owned by the current user.
ALL_OBJECTS contains all objects on which current user has access.
Note that obj is the synonym of USER_OBJECTS view.
The CREATED column of the view contains date about when an object was created.
The LAST_DDL_TIME column of the view contains date about when the object was last modified by a DDL statement. Note that this column value also contain the timing of revoke and grant that was issued against the object. Similarly on procedure, function, trigger if you compile the object then only LAST_DDL_TIME is only modified.
The TIMESTAMP column of the view contains timestamp of the last ddl time excluding any grants, revoke or any compile time.
Before proceed let's set nls_date_format on sql*plus so that we can see the timings of date data type.
Now I am adding a column to the table. After adding column see the LAST_DDL_TIME
and TIMESTAMP column value is changed.
Now I grant select on test table to user arju. After grant note that LAST_DDL_TIME is changed but TIMESTAMP value is not changed.
As of other oracle views.
DBA_OBJECTS contains all database objects.
USER_OBJECTS contains all objects that is owned by the current user.
ALL_OBJECTS contains all objects on which current user has access.
Note that obj is the synonym of USER_OBJECTS view.
The CREATED column of the view contains date about when an object was created.
The LAST_DDL_TIME column of the view contains date about when the object was last modified by a DDL statement. Note that this column value also contain the timing of revoke and grant that was issued against the object. Similarly on procedure, function, trigger if you compile the object then only LAST_DDL_TIME is only modified.
The TIMESTAMP column of the view contains timestamp of the last ddl time excluding any grants, revoke or any compile time.
Before proceed let's set nls_date_format on sql*plus so that we can see the timings of date data type.
SQL> alter session set nls_date_format='DD-MON-YY HH24:MI:SS';
Session altered.
SQL> select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';
CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 21-SEP-08 00:49:16 2008-09-21:00:43:11 VALID
Now I am adding a column to the table. After adding column see the LAST_DDL_TIME
and TIMESTAMP column value is changed.
SQL> alter table test add col2 number;
Table altered.
SQL> select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';
CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:02:29 2009-02-20:11:02:29 VALID
SQL> select sysdate from dual;
SYSDATE
------------------
20-FEB-09 11:02:47
Now I grant select on test table to user arju. After grant note that LAST_DDL_TIME is changed but TIMESTAMP value is not changed.
SQL> grant select on test to arju;
Grant succeeded.
SQL> select created, last_ddl_time, timestamp, status from obj where object_name='TEST'
and object_type='TABLE';
CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------------ ------------------ ------------------- -------
21-SEP-08 00:43:11 20-FEB-09 11:12:33 2009-02-20:11:02:29 VALID
SQL> select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST'
and object_type='PROCEDURE';
CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 10:42:11 2009-02-20:10:42:11 VALID
SQL> alter procedure p_test compile;
Procedure altered.
SQL> select created, last_ddl_time, timestamp, status from obj where object_name='P_TEST'
and object_type='PROCEDURE';
CREATED LAST_DDL_TIME TIMESTAMP STATUS
------------------ ------------------ ------------------- -------
20-FEB-09 10:42:11 20-FEB-09 11:18:41 2009-02-20:10:42:11 VALID
Subscribe to:
Posts (Atom)