oracle常用语句

查询表空间信息

SELECT * FROM Dba_Data_Files

 

解锁用户

SELECT * FROM dba_users;
SELECT * FROM dba_users where username='账户'

ALTER USER 账户 ACCOUNT UNLOCK;

 

解锁表

SYS用户才能操作

1..查询被锁的表的 'sid,seial#';

select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;

 

SELECT
	sess. SID,
	sess.serial#,
	lo.oracle_username,
	lo.os_user_name,
	ao.object_name,
	lo.locked_mode
FROM
	v$locked_object lo,
	dba_objects ao,
	v$session sess
WHERE
	ao.object_id = lo.object_id
AND lo.session_id = sess. SID

2.解锁表

alter system kill session 'sid,seial#';

 

约束查询

ORA-02292: 违反完整约束条件 (用户名.约束名) - 已找到子记录

SELECT * FROM user_constraints WHERE constraint_name = '约束名';

 

修改主键

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = 'TL9_QU_USER_STATUS' AND CONSTRAINT_TYPE = 'P';

ALTER TABLE TL9_QU_USER_STATUS DROP CONSTRAINT SYS_C0082274;

ALTER TABLE TL9_QU_USER_STATUS ADD CONSTRAINT SYS_C0082274 PRIMARY KEY (USER_NO, BRANCH_NO);

 

数据库连接池溢出

v$session 大量会话的 status = inactive 

SELECT
	SCHEMANAME,
	COUNT (1)
FROM
	v$session
WHERE
	status = 'INACTIVE'
GROUP BY
	SCHEMANAME

 

处于SQL*Net message from client 事件

sys用户, 查看当前运行的sql语句

SELECT
	osuser,
	A .username,
	sql_fulltext,
	machine
FROM
	v$session A,
	v$sqlarea b
WHERE
	A .sql_address = b.address
ORDER BY
	cpu_time / executions DESC;

 

 

 

 

 

select * from v$session_wait