oracle 锁问题的解决
可以用Spotlight软件对数据库的运行状态进行监控。
当出现session锁时,我们要及时进行处理.
1. 查看哪些session锁:
SQL语句:select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
SQL> select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select sid from v$lock where block = 1);
'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||''';'
--------------------------------------------------------------------------------
alter system kill session '132,731';
alter system kill session '275,15205';
alter system kill session '308,206';
alter system kill session '407,3510';
2. 查看session锁.
sql语句:select s.sid, q.sql_text from v$sqltext q, v$session s
where q.address = s.sql_address
and s.sid = &sid
order by piece;
SQL> select s.sid,q.sql_text from v$sqltext q, v$session s where q.address = s.sql_address and s.sid in (select sid from v$lock where block = 1) order by piece;
SID SQL_TEXT
---------- ----------------------------------------------------------------
77 UPDATE PROFILE_USER SET ID=1,COMPANY_ID=2,CUSTOMER_ID=3,NAMED
77 _INSURED_ID=4,LOGIN=5,ROLE_ID=6,PASSWORD=7,EMAIL=8,TIME_ZON
77 E=9 WHERE PROFILE_USER.ID=:34
3 rows selected.
3. kill锁的进程.
SQL语句:alter system kill session '77,22198';
SQL> alter system kill session '391,48398';
System altered.
4. 查看谁锁了谁。
select s1.username || [email='@']'@'[/email] || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || [email='@']'@'[/email] || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
&nb
相关文档:
1、对象类型规范
创建对象类型规范的语法如下
CREATE [OR REPLACE] TYPE [schema.] type_name
[AUTHID {CURRENT_USER|DEFINER}] AS OBJECT (
Attribute1 datatype,
[attribute2 datatype,…]
[method 1]
[method 2]);
/
其中AUTHID指示将来执行该方法时, ......
4、对象依赖性
CREATE OR REPLACE TYPE Obj1 AS OBJECT (
f1 NUMBER,
f2 VARCHAR2(10),
f3 DATE
);
/
CREATE OR REPLACE TYPE Obj2 AS OBJECT (
f1 DATE,
f2 CHAR(1)
);
/
CREATE OR REPLACE TYPE Obj3 AS OBJECT (
a Obj1,
b Obj2
);
/
OBJ3依赖于OBJ ......
ROLLUP和CUBE语句。
Oracle的GROUP
BY语句除了最基本的语法外,还支持ROLLUP和CUBE语句。如果是ROLLUP(A, B, C)的话,首先会对(A、B、C)进行GROUP
BY,然后对(A、B)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。如果是GROUP BY
CUBE(A, B, C),则首先会对(A、B、C)进行GROUP
BY,然后依次是( ......
安装oracle 11g以后 无法启动dbconsole 。通过两天的努力终于解决了
1 发现日志报 端口5520已经被占用,无法使用,打开 /%ORACLE_HOME%/oc4j/je22/OC4J_DBConsole_localhost_orcl/config/rmi.xml
发现 port="5520" ssl-port="5520" 端口号使用相同,于是修改 port="5521" ssl-port=" ......
1. create table people (age int, id int);
2. 创建数据文件PeopleDate.txt.
数据为:
20,1
30,2
3. 创建 ......