oracle 10.2.0.5创建EM报错

给客户实施数据库的安装,win2k3_x64+oracle 10.2.0.5,手欠勾选了”创建EM”的选项,结果在创建实例的时候,长时间的停在85%,配置EM的过程点上,最后得到了如下的报错:

————————————————————————————————————-

oracle.sysman.emcp.exception.EMConfigException: 启动 Database Control 时出错
at oracle.sysman.emcp.EMDBPostConfig.performConfiguration(EMDBPostConfig.java:649)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:227)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:196)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:184)
at oracle.sysman.assistants.util.em.EMConfiguration.run(EMConfiguration.java:449)
at java.lang.Thread.run(Thread.java:595)

—————————————————————————————————————

网上找了一圈,发现可能和如下这篇文章的描述类似。本想去metalink上下载相应的patch试试,结果metalink今天例行维护,需要5小时以后才能访问,囧。改天试试吧。

 

My Oracle Support is currently unavailable due to scheduled maintenance.

Maintenance Start Date: Friday, March 30

Maintenance Start Time:

San Francisco Friday 9:00 PM Pacific
Orlando Saturday 12:00 AM Eastern
GMT/UTC Saturday 4:00 AM
London Saturday 5:00 AM
Bangalore Saturday 9:30 AM
Melbourne Saturday 2:00 PM

Duration:  5 hours

Impact: All My Oracle Support users

After the outage, you may need to delete your browsers’ cookies cache and all temporary internet files then close all internet browser windows.

If you have an urgent issue please call your local support center. Click here for a list of Oracle Support phone numbers. Click here for a list of CRM On Demand Support phone numbers.

Thank you in advance for your patience, and we apologize for any inconvenience.

The My Oracle Support Team

———————————————转载———————————————————————–

x64安装oracle 10.2.0.4无法启动em dbconsole问题解决

在一台windows 2003 r2 x64上安装oracle 10g 10.2.0.4,采用默认安装方式,安装进度到85%时出现错误提示窗口

“由于以下错误,Enterprise Manager配置失败 – 启动Database Control时出错 有关详细资料,请参阅E:oracleproduct10.2.0db_1cfgtoollogsdbcaorcl emConfig.log中的日志文件。您可以以后通过手动运行E:oracleproduct10.2.0db_1bindmca脚本, 重新使用Enterprise Manager配置此数据库。”

日志%ORACLE_HOME%cfgtoollogsdbcaorclemConfig.log输出:

配置: Waiting for service ‘OracleDBConsoleorcl’ to fully start
2011-2-22 10:37:15 oracle.sysman.emcp.util.PlatformInterface serviceCommand
配置: Initialization failure for service during start
2011-2-22 10:37:15 oracle.sysman.emcp.EMConfig perform
严重: 启动 Database Control 时出错
有关详细资料, 请参阅 E:oracleproduct10.2.0db_1cfgtoollogsdbcaorclemConfig.log 中的日志文件。
2011-2-22 10:37:15 oracle.sysman.emcp.EMConfig perform
配置: Stack Trace:
oracle.sysman.emcp.exception.EMConfigException: 启动 Database Control 时出错
at oracle.sysman.emcp.EMDBPostConfig.performConfiguration(EMDBPostConfig.java:646)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:224)
at oracle.sysman.emcp.EMDBPostConfig.invoke(EMDBPostConfig.java:193)
at oracle.sysman.emcp.EMConfig.perform(EMConfig.java:184)
at oracle.sysman.assistants.util.em.EMConfiguration.run(EMConfiguration.java:436)
at java.lang.Thread.run(Thread.java:595)

trace文件%ORACLE_HOME%<HOSTNAME>_<SID>sysmanlogemagent.trc输出:

2011-02-22 10:29:51 Thread-3068 ERROR util.files: ERROR: nmeufis_new: failed in lfiopn on file: E:oracleproduct10.2.0db_1dbserver1_orclsysmanemdagntstmp.txt. error = 0 (No error)
2011-02-22 10:29:51 Thread-3068 ERROR ssl: Open wallet failed, ret = 28750
2011-02-22 10:29:51 Thread-3068 ERROR ssl: nmehlenv_openWallet failed
2011-02-22 10:29:51 Thread-3068 ERROR http: 660: Unable to initialize ssl connection with server, aborting connection attempt
2011-02-22 10:29:51 Thread-3068 ERROR pingManager: nmepm_pingReposURL: Cannot connect to https://dbserver1:1158/em/upload/: retStatus=-1

经查询,此问题是由于enterprise manager database control组件的跟CA证书授权过期造成的,其证书到期日为2010年12月31日,2011年安装此版本数据库都会出现这个问题,官方的解决方案是打Patch 8350262

单实例数据库应用此patch的方法如下:

1、安装或者升级数据库到10.2.0.4过程中忽略此错误继续安装,数据库的创建不受影响。
2、使用opatch把此补丁应用到oracle安装
设置ORACLE_HOME和ORACLE_SID系统环境变量,将%ORACLE_HOME%opatch加入PATH环境变量,将patch 8350262解压缩,打开cmd窗口,进入解压缩后目录,执行
cmd>opatch apply
完成后检查%ORACLE_HOME%cfgtoollogsopatch目录下生成的日志文件确认安装patch是否成功。
3、应用patch成功后,重新配置em dbconsole
cmd>emctl secure dbconsole -reset
根据提示输入管理员密码,然后会有两次确认请求,两次都是输入大写的Y
4、重新启动dbconsole
cmd>emctl start dbconsole

当然,如果不使用enterprise manager database control这个组件的话,那么可以不用理会这个错误,不打这个patch。

13

Basically,on that stuff,I have no safety at all,until the property was settle down.They cannot be trusted.

how to archive the listener log for oracle database

sometimes,after long time running of database,the listener log became large and inconvenient  to locate the error msg in large text file, so it’s necessary to archive the listener log. follow the step.

on win platform:

lsnrctl set log_status off // to stop log msg to listener log file

rename the listener.log

lsnrctl set log_status on// to start the log msg to listener.log

 

on unix or linux platform:

echo “123” > listener.log

–EOF–

 

catch sql

客户希望抓取OS上占用cpu资源较多的sql语句,以供分析。如下语句可以抓取整个sqltext,而不是取自v$sql、抓的是前1000个字符。

select s.sid,sq.sql_text from v$session s,v$process p,v$sqltext sq where s.paddr=p.addr and s.sql_id=sq.sql_id and p.spid=’323954′

 

 V$SQL中关于SQL_TEXT的解释说明

SQL_TEXT VARCHAR2(1000) First thousand characters of the SQL text for the current cursor

 

V$SQLTEXT中关于sql_text的解释

SQL_TEXT VARCHAR2(64) A column containing one piece of the SQL text

“with check option” test

There is a table EMP under scott,the detail information go as below

 

 

create a view for “with check option” test

SQL> create or replace view testview as select empno,ename from emp where ename like ‘M%’ with check option;

View created
SQL> select * from testview;

EMPNO       ENAME
———-      ———-
7654           MARTIN
7934            MILLER

update the view which ename is Mile(satisfy the like ‘M%’ condition)

SQL> update testview set ename = ‘Mike’ where empno=7654;

1 row updated.

do another update to the view

SQL> update testview set ename = ‘Robin’ where empno=7654
update testview set ename = ‘Robin’ where empno=7654
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

error occur….because update the ename= Robin violate the “with check option”

 

go on our test… try to insert a row into view “testview”,yes,U can insert,update a view

SQL> insert into testview values (8888,’LANDRY’);
insert into testview values (8888,’LANDRY’)
*
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

//error trigger again while you are trying to insert a row with “LANDRY”,but if you insert a “M***” into the ename column will meet the “with check option”, go as below…
SQL> edit
Wrote file afiedt.buf

1* insert into testview values (8888,’MANDRY’)
SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from testview;

EMPNO     ENAME
———-    ———-
7654         Mike
7934         MILLER
8888         MANDRY

 

Because we had specify the “with check option” on ename column ,so each row in the testview must meet the condition specified when creating the object “testview”

-EOF-

constraint parameter test

1.prepare the data materials

SQL> create table cust (cust_id number(2),cust_name varchar2(15));

SQL> desc cust;
Name                                                                                    Null?                         Type
—————————————————– ——– ——————————————–
CUST_ID                                                                                                           NUMBER(2)
CUST_NAME                                                                                                 VARCHAR2(15)

add a constraint

SQL> alter table cust add constraint cust_id_pk primary key (cust_id)  deferrable   initially   deferred;

deferrable :我理解为在事务中可以改变上一条参数的设置

deferred:我理解为”延迟生效”。

如果不指定参数,默认设置是 initially immediate not deferrable。
注意:如果约束是not deferrable,那么它只能是initially immediate,而不能是initially deferred。

继续测试,插入数据

SQL> insert into cust values (1,’RAJ’);

1 row created.

SQL> insert into cust values (1,’SAM’);

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.CUST_ID_PK) violated

在尝试commit的时候,由于primary key的constraint,报错了。这个应该是deferred的效果,也就是在整个事务结束的时候才验证constraint,是否合规。

继续测试,还是在这个session中,更改constraint的参数设置,”immediate”这个关键字,也能猜出个一二三了,应该是在语句结束时就验证,而且其生效区间应该结束于commit。

SQL> set constraint cust_id_pk immediate;

Constraint set.

查看当前cust表中有哪些内容,有两条记录。

SQL> select * from cust;

CUST_ID CUST_NAME
———- —————
1                 LATA
2                 king

尝试insert一条记录

SQL> insert into cust values (1,’new haha’);
insert into cust values (1,’new haha’)
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.CUST_ID_PK) violated

立刻参数立刻生效报错了,然后结束这个事务,commit.

SQL> commit;

Commit complete.

SQL> insert into cust values (1,’new haha’);

1 row created.

//你又可以塞进去东西了,但是在你commit的时候,估计还是会报错,因为constraint的参数是deferrable   initially   deferred:延迟生效,在每个commit结束后生效。

SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-00001: unique constraint (SCOTT.CUST_ID_PK) violated

//果然

–EOF–

 

 

 

“pivoting insert” test

what’s pivoting insert? follow the example and get a better understanding

1.prepare the data material

create table sales_input_data
(
product_id varchar2(10),custmer_id varchar2(10),week_st varchar2(15),
sales_sun number(4),sales_mon number(4),sales_tue number(4),
sales_wed number(4),sales_thu number(4),sales_fri number(4),sales_sat number(4)
);

SQL> desc sales_input_data;
Name Null?                 Type
———————  ——————————————–
PRODUCT_ID           VARCHAR2(10)
CUSTMER_ID           VARCHAR2(10)
WEEK_ST                   VARCHAR2(15)
SALES_SUN              NUMBER(4)
SALES_MON            NUMBER(4)
SALES_TUE              NUMBER(4)
SALES_WED             NUMBER(4)
SALES_THU              NUMBER(4)
SALES_FRI               NUMBER(4)
SALES_SAT               NUMBER(4)

insert into sales_input_data values(‘111′,’222′,’01-OCT-00’,100,200,300,400,500,600,700);
insert into sales_input_data values(‘222′,’333′,’08-OCT-00’,200,300,400,500,600,700,800);
insert into sales_input_data values(‘333′,’444′,’15-OCT-00’,300,400,500,600,700,800,900);

SQL> select * from sales_input_data;

PRODUCT_ID CUSTMER_ID WEEK_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
———- ———- ————— ———- ———- ———- ———- ———- ———- ———-
111      222       01-OCT-00      100   200   300   400   500   600    700
222     333      08-OCT-00      200    300   400   500   600   700    800
333      444        15-OCT-00    300     400   500   600   700   800    900
create table sales (prod_id varchar2(10),cust_id varchar2(10),time_id varchar2(15),amount_sold number(4));

2.piece of information from the oracle online document

Pivoting Scenarios
A data warehouse can receive data from many different sources. Some of these source systems may not be relational databases and may store data in very different formats from the data warehouse. For example, suppose that you receive a set of sales records from a nonrelational database having the form:

product_id, custmer_id, weekly_start_date, sales_sun, sales_mon, sales_tue,sales_wed, sales_thu, sales_fri, sales_sat

The input table looks like the following:

SQL> select * from sales_input_data;

PRODUCT_ID CUSTMER_ID WEEK_ST SALES_SUN SALES_MON SALES_TUE SALES_WED SALES_THU SALES_FRI SALES_SAT
———- ———- ————— ———- ———- ———- ———- ———- ———- ———-
111      222    01-OCT-00   100   200   300   400   500   600    700
222     333    08-OCT-00   200   300   400   500   600   700   800
333     444     15-OCT-00   300   400   500   600   700   800   900

In your data warehouse, you would want to store the records in a more typical relational form in a fact table sales of the sh sample schema:

prod_id, cust_id, time_id, amount_sold
Note:

A number of constraints on the sales table have been disabled for purposes of this example, because the example ignores a number of table columns for the sake of brevity.
Thus, you need to build a transformation such that each record in the input stream must be converted into seven records for the data warehouse’s sales table. This operation is commonly referred to as pivoting, and Oracle Database offers several ways to do this.

3.do pivoting insert

insert all
into sales values (product_id,custmer_id,week_st,sales_sun)
into sales values (product_id,custmer_id,week_st,sales_mon)
into sales values (product_id,custmer_id,week_st,sales_tue)
into sales values (product_id,custmer_id,week_st,sales_wed)
into sales values (product_id,custmer_id,week_st,sales_thu)
into sales values (product_id,custmer_id,week_st,sales_fri)
into sales values (product_id,custmer_id,week_st,sales_sat)
select product_id,
custmer_id,
week_st,
sales_sun,
sales_mon,
sales_tue,
sales_wed,
sales_thu,
sales_fri,
sales_sat
from sales_input_data;
The result of the previous example will resemble the following:

SQL> select * from sales;

PROD_ID CUST_ID TIME_ID AMOUNT_SOLD
———- ———- ————— ———–
111   222 01-OCT-00 100
222   333 08-OCT-00 200
333   444 15-OCT-00 300
111   222 01-OCT-00 200
222   333 08-OCT-00 300
333   444 15-OCT-00 400
111    222 01-OCT-00 300
222   333 08-OCT-00 400
333   444 15-OCT-00 500
111    222 01-OCT-00 400
222   333 08-OCT-00 500
333   444 15-OCT-00 600
111    222 01-OCT-00 500
222    333 08-OCT-00 600
333    444 15-OCT-00 700
111    222 01-OCT-00 600
222   333 08-OCT-00 700
333   444 15-OCT-00 800
111    222 01-OCT-00 700
222   333 08-OCT-00 800
333   444 15-OCT-00 900
111    222 01-OCT-00 100
222   333 08-OCT-00 200
333   444 15-OCT-00 300
111    222 01-OCT-00 200
222   333 08-OCT-00 300
333   444 15-OCT-00 400
111    222 01-OCT-00 300
222    333 08-OCT-00 400
333    444 15-OCT-00 500
111    222 01-OCT-00 400
222   333 08-OCT-00 500
333   444 15-OCT-00 600
111    222 01-OCT-00 500
222   333 08-OCT-00 600
333   444 15-OCT-00 700
111    222 01-OCT-00 600
222   333 08-OCT-00 700
333   444 15-OCT-00 800
111   222 01-OCT-00 700
222  333 08-OCT-00 800
333  444 15-OCT-00 900

 

 

Marking columns unused and Removing the unused column

I got this explaination from oracle online doc of  proceeding the unused columns,mark as reminder

 

Marking column unused


If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE…SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE hr.admin_emp SET UNUSED (hiredate, mgr);

You can later remove columns that are marked as unused by issuing an ALTER TABLE…DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER                  TABLE_NAME                       COUNT
————————— ————————— —–
HR                              ADMIN_EMP                               2

 

Removing Unused Columns


The ALTER TABLE…DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE hr.admin_emp DROP UNUSED COLUMNS CHECKPOINT 250;

In all, the purpose of UNUSED key words could be summarized as below:

If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE…SET UNUSED statement

 

 

some “JOIN” test

1.prepare the data material
SQL> desc part
Name                                      Null?                     Type
—————————————– ——– —————–
PART_ID                                   NOT NULL    VARCHAR2(4)
SUPPLIER_ID                                                   VARCHAR2(4)

SQL> select * from part;
PART SUPP
—- —-
P1   S1
P2   S2
P3
P4

SQL> desc supplier
Name                                      Null?    Type
—————————————– ——– —————–
SUPPLIER_ID                               NOT NULL VARCHAR2(4)
SUPPLIER_NAME                             NOT NULL VARCHAR2(20)

SQL> select * from supplier;
SUPP SUPPLIER_NAME
—- ——————–
S1   Supplier#1
S2   Supplier#2
S3   Supplier#3

P3,P4对应没有数据,执行如下的sql

SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id=s.supplier_id

PART            SUPPLIER_NAME
—-                  ——————–
p1                    Supplier#1
p2                   Supplier#2

//it’s easy to understand the inner join,get the rows which values exsit in both of the table

//The join shown above is an inner join, which results in just the rows that have corresponding rows in both tables

//of course,the sql could be written as below(inner join)
SQL> select p.part_id,s.supplier_name from part p join supplier s on p.supplier_id=s.supplier_id;

PART SUPPLIER_NAME
—- ——————–
p1   Supplier#1
p2   Supplier#2

//there will be the same result no matter which object was based on as join object
SQL> select p.part_id,s.supplier_name from supplier s join part p on p.supplier_id=s.supplier_id;

PART          SUPPLIER_NAME
—-            ——————–
p1                Supplier#1
p2               Supplier#2

2.outer join
//If we want all parts to be listed in the result set, irrespective of whether they are supplied by any supplier or not,
then we need to perform an outer join

//left outer join, based on table in left side,if the column of right side don’t have value,take the left side as bench mark

and appeared as null
SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id=s.supplier_id(+)

PART          SUPPLIER_NAME
—-                ——————–
p1                Supplier#1
p2               Supplier#2
p4
p3

//The outer join above lists all of the parts. For the parts that don’t have a corresponding supplier,
null values are displayed for the SUPPLIER_NAME column. However, not all the suppliers are displayed.
Since supplier S3 doesn’t supply any parts, it gets excluded from the result set of the above outer join

//If we want all the suppliers listed in the result set,
irrespective of whether they supply any part or not, we need to perform an outer join like the following

SQL>select p.part_id,s.supplier_name from part p,supplier s where p.supplier_id(+)=s.supplier_id

PART    SUPPLIER_NAME
—-         ——————–
p1            Supplier#1
p2            Supplier#2
                 Supplier#3

 

-EOF-

“enq: TX – row lock contention”-case 2

due to “Waits due to Unique or Primary Key Constraint enforcement

–Session#1: ALTER TABLE tx_eg ADD CONSTRAINT tx_eg_pk PRIMARY KEY( num );

–Session#1: insert into tx_eg values (10,’New’,’MALE’);
–Session#2: insert into tx_eg values (10,’OtherNew’,null);

–DBA:

SQL> select sid,type,id1,id2,lmode,request,block from v$lock where type='TX';

SID       TY     ID1         ID2      LMODE     REQUEST    BLOCK
—–    —– — ———- ———- ———- ———- ———-
150     TX     327690   298        0                 4                     0
150     TX     524335   262          6                0                     0
159     TX      327690   298        6                  0                     1

This shows SID 150 is waiting for the TX lock held by SID 159 and it wants the lock in share mode (as REQUEST=4).

check out the v$session_wait for wait events

SQL> select sw.event,sw.sid,sw.wait_class from v$session_wait sw where sw.event like ‘%enq%’;

EVENT                                                       SID                      WAIT_CLASS
—————————–                   ———-          ——————
enq: TX – row lock contention            150                Application

–Ses#1:commit;
–Ses#2:ORA-00001: unique constraint (SCOTT.TX_EG_PK) violated
–Ses#2:rollback;

-EOF-

how to resolve a ‘enq: TX – row lock contention’ event

Senario:two update sessions were operated on the same table,you will see a “TX -row lock” event
in the third console that one session hold the TX lock resource which other is trying to request.
Normally,the requester can do nothing except waiting,the only solution is to revise the app logic on the resource-holder,let’s check out the example.

prepare the materials
——————————————————————————————-
DROP TABLE tx_eg;
CREATE TABLE tx_eg ( num number, txt varchar2(10), sex varchar2(10) ) INITRANS 1 MAXTRANS 1;
INSERT into tx_eg VALUES ( 1, 'First','FEMALE' );
INSERT into tx_eg VALUES ( 2, 'Second','MALE' );
INSERT into tx_eg VALUES ( 3, 'Third','MALE' );
INSERT into tx_eg VALUES ( 4, 'Fourth','MALE' );
INSERT into tx_eg VALUES ( 5, 'Fifth','MALE' );
COMMIT

——————————————————————————————-
–Session#1:
update tx_eg set txt=’Garbage’ where num=1;

–Session#2:
update tx_eg set txt=’Garbage’ where num=1;

–DBA#3
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type=’TX’;
/*display the TX lock info*/

SQL> select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type='TX';

SID TY ID1 ID2 LMODE REQUEST
———- — ———- ———- ———- ———-
147 TX 524297 261 0 6
158 TX 524297 261 6 0

you can see 147 is requesting the resource and 158 is the TX lock resource-holder

select * from v$lock where type=’TX’ and request>0;
/*display who is waiting for a TX lock*/
SQL> select * from v$lock where type=’TX’ and request>0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- — ———- ———- ———- ———- ———- ———-
3043444C 30434460 147 TX 524297 261 0 6 4261 0

select * from v$lock where type=’TX’ and lmode>0;
/*display who is holding a TX lock and ‘BLOCK=1’ means it’s blocking a session who wants the TX lock*/

SQL> select * from v$lock where type='TX' and lmode>0;

ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
——– ——– ———- — ———- ———- ———- ———- ———- ———-
2FA16060 2FA16084 158 TX 524297 261 6 0 3988 1

sid=147 is the unlucky session who is blocking and waiting for TX lock.

select sid,p1raw, p2, p3,event from v$session_wait where wait_time=0 and event like ‘%enq%’;
/*display the sid info for a enq: TX – row lock contention who is waiting for the TX lock resource*/

SQL> select sid,p1raw, p2, p3,event from v$session_wait where wait_time=0 and event like ‘%enq%’;

SID P1RAW P2 P3 EVENT
———- ——– ———- ———- —————————————————————-
147 54580006 524297 261 enq: TX – row lock contention

this is a “enq: TX – row lock contention”, the only way to resolve this problem is to notify the unproper behavior of sid=158,commit or rollback the session.

you can use this to show some detail
//
select osuser,machine,program,Module,sid,serial#,event,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;

//
select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=147;
/*the waiter is waiting for TX lock in order to lock ‘ROW_WAIT_ROW’
in file ‘ROW_WAIT_FILE’ block ‘ROW_WAIT_BLOCK’ of object ‘ROW_WAIT_OBJ’
this could be used to locate the object in contention
*/

SQL> select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=147
2 /

ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW#
————- ————– ————— ————-
51931 4 144 0

the sid=147 session is visit the 144 block# of 51931# object.

ok let’s figure out the object which is in contention

SQL> select object_id,object_name from dba_objects where owner=’SCOTT’ and object_id=51931

OBJECT_ID OBJECT_NAME
———- ——————–
51931 TX_EG

table TX_EG of scott was in contention

after locating the object in contention,you can find out the session who need TX lock resource and manually kill it using alter system kill session ‘sid,serial#’

SQL>select sid,serial# from v$session where sid=147;

SQL> select sid,serial# from v$session where sid=147;

SID SERIAL#
———- ———-
147 91
alter system kill session ‘sid,serial#’;
//kill session should be agreed on by developer or well considerated

but the key to take over the issue is to find out the improper logic of the “TX lock holder”

catch the sid=158 SQLTEX

//
SELECT SQL_TEXT
FROM v$sqltext a
WHERE (a.HASH_VALUE, a.ADDRESS) IN
(SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode(sql_hash_value, 0, prev_sql_addr, sql_address)
FROM v$session b
where b.sid = &sid
and b.serial# = &serial)

get the serial# of sid=158 for locating the sqltext

——————————————————————————————–
SQL> SELECT SQL_TEXT
FROM v$sqltext a
WHERE (a.HASH_VALUE, a.ADDRESS) IN
2 3 4 (SELECT decode(sql_hash_value, 0, prev_hash_value, sql_hash_value),
decode(sql_hash_value, 0, prev_sql_addr, sql_address)
5 6 FROM v$session b
where b.sid = &sid
7 8 and b.serial# = &serial)
9 /
Enter value for sid: 158
old 7: where b.sid = &sid
new 7: where b.sid = 158
Enter value for serial: 89
old 8: and b.serial# = &serial)
new 8: and b.serial# = 89)

SQL_TEXT
—————————————————————-
update tx_eg set txt=’Garbage’ where num=1

—————————————————————-
add the commit…