--1.查出锁定object的session的信息以及被锁定的object名
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username,
l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
FROM v$locked_object l, all_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY sid, s.serial# ;
--2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
--比上面那段多出sql_text和action
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;
--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;
--4.在linux及hp_ux下批量 kill进程
SELECT s.MACHINE as 计算机名称,p.SPID 操作系统进程号,'kill -9 ' || SPID || ';' AS KILL语句
FROM V$PROCESS p,
V$SESSION s
WHERE p.addr = s.PADDR
and s.machine like '%MICROSOF-F8DC73%' --计算机名称MICROSOF-F8DC73
--5.查看表空单大小使用情况
SELECT D.TABLESPACE_NAME as 表空间,
SPACE "总空间大小(M)",
BLOCKS as 总数据块,
SPACE - NVL(FREE_SPACE,
0) "已使用(M)",
ROUND((1 - NVL(FREE_SPACE,
0) / SPACE) * 100,
2) "使用率(%)",
FREE_SPACE "未使用(M)",
ROUND(NVL(FREE_SPACE,
0) / SPACE * 100,
2) "未使用率(%)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024),
2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024),
2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME as 表空间,
SPACE "总空间大小(M)",
BLOCKS as 总数据块,
USED_SPACE "已使用(M)",
ROUND(NVL(USED_SPACE,
0) / SPACE * 100,
2) "使用率(%)",
NVL(FREE_SPACE,
0) "未使用(M)",
ROUND(NVL(FREE_SPACE,
0) / SPACE * 100,
2) "未使用率(%)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024),
2) SPACE,
SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES_USED) / (1024 * 1024),
2) USED_SPACE,
ROUND(SUM(BYTES_FREE) / (1024 * 1024),
2) FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
--6.查看数据库连接数
show parameter processes #最大连接
alter system set processes = value scope = spfile;重启数据库 #修改连接
/*总连接数*/
select machine,count(machine) from v$session group by machine
/*激活连接*/
select machine,count(machine) from v$session where status = 'ACTIVE' group by machine
/*未激活连接*/
select machine,count(machine) from v$session where status = 'INACTIVE' group by machine
--7.查询字符编码集
select * from v$nls_parameters;
select userenv('language') from dual;
--8.查询使用过的sql语句
SELECT SQL_TEXT as 运行的语句,LAST_LOAD_TIME as 运行的时间 FROM V$SQL
--8 查询某表的字段
select col.*, com.Comments
from sys.all_tab_columns col, sys.all_col_comments com
where col.owner = 'GD_CCATSUPT' --用户名
and col.table_name = 'SVR_PUB_DA_MAINQUEUE_HIS' --表名
and com.Owner(+) = 'GD_CCATSUPT' --用户名
and com.Table_Name(+) = 'SVR_PUB_DA_MAINQUEUE_HIS' --表名
and com.Column_Name(+) = col.Column_Name
order by col.column_id
/*查询版本*/
select * from v$version
/*查询临时表空间*/
select * from dba_temp_files
select * from v$sort_segment
select * from v$sort_usage
select name from v$tempfile
select username,temporary_tablespace from dba_users
select tablespace_name, current_users, total_blocks, free_blocks from v$sort_segment
/*查询字符编码集*/
select * from v$nls_parameters;
select userenv('language') from dual;
/*临时表的使用情况*/
select * from user_tables a where a.temporary='Y' and a.table_name not like 'RUPD%'
/*表空间使用情况*/
SELECT A.TABLESPACE_NAME,
A.BYTES/(1024*1024*1024) TOTAL,
B.BYTES/(1024*1024*1024) USED,
C.BYTES/(1024*1024*1024) FREE,
(B.BYTES * 100) / A.BYTES "% USED",
(C.BYTES * 100) / A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
select sum(bytes)/(1024*1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
/*总连接数*/
select machine,count(machine) from v$session group by machine
/*激活连接*/
select machine,count(machine) from v$session where status = 'ACTIVE' group by machine
/*未激活连接*/
select machine,count(machine) from v$session where status = 'INACTIVE' group by machine
/*导出DDL*/
SELECT u.username,DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;
SELECT ts.tablespace_name,to_char(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)) FROM DBA_TABLESPACES TS
/*使用到的表空间*/
select * from sys.dba_tablespaces s where s.tablespace_name not in ('SYSTEM','USERS') and s.tablespace_name in (select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT')
select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT'
select b.table_name,b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT' and b.tablespace_name in('SYSTEM','USERS')
select * from sys.dba_tables b where b.tablespace_name like '%SVR%'
select * from sys.dba_tables b where b.tablespace_name='SYSTEM'
SELECT A.TABLESPACE_NAME,
A.BYTES / (1024 * 1024) as TOTAL ,
B.BYTES USED,
C.BYTES FREE,
(B.BYTES * 100) / A.BYTES "% USED",
(C.BYTES * 100) / A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
and A.TABLESPACE_NAME not in ('SYSTEM','USERS')
and A.TABLESPACE_NAME in (select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT')
/*查询表空间（无table的）*/
select a.tablespace_name
from dba_tablespaces a
minus (select distinct b.tablespace_name
from dba_tables b
union
select distinct c.tablespace_name from dba_indexes c)
SELECT m.compile_state FROM User_Mviews m
select * from user_jobs
--drop table interface_97to112_his
select * from interface_97to112_his t
select * from user_tables u where u.table_name like '%INTERFACE%'
create table interface_97to112_his
tablespace SVR_BASE_DAT
as
select * from where 1=2
/*添加字段*/
alter table tmp_telephone add newservice varchar(10);
DBMS_OUTPUT.PUT_LINE(sqlcode);
/*删除重复数据*/
delete from tmp_imp_user_tel t1 where rowid > (select min(rowid) from tmp_imp_user_tel t2 where t1.no2 = t2.no2)
/*更新2张表*/
UPDATE tmp_imp_user_97 a
SET a.no5 = (SELECT b.no2
FROM tmp_imp_user_tel b
WHERE a.no1 = b.no1
and rownum = 1)
WHERE EXISTS (SELECT 1 FROM tmp_imp_user_tel b WHERE a.no1 = b
1 找到锁过程的session id;
可以通过v$access 找到被死锁的过程的session id.
方法如下：
select sid from v$access t where t.OBJECT='过程名'
例如INTERFACE97_CUSTINFO_JH被死锁。
执行select sid from v$access t where t.OBJECT='INTERFACE97_CUSTINFO_JH'
可以找到锁过程的session id；
2 根据session id找到对应的数据库进程id
执行 SELECT SPID FROM V$PROCESS p, V$SESSION s WHERE p.addr=s.PADDR and s.SID=session id；
3 用root用户登陆操作系统 kill 数据库进程id
/*查询版本*/
select * from v$version
/*查询临时表空间*/
select * from dba_temp_files
select * from v$sort_segment
select * from v$sort_usage
select name from v$tempfile
select username,temporary_tablespace from dba_users
select tablespace_name, current_users, total_blocks, free_blocks from v$sort_segment
/*查询字符编码集*/
select * from v$nls_parameters;
select userenv('language') from dual;
/*临时表的使用情况*/
select * from user_tables a where a.temporary='Y' and a.table_name not like 'RUPD%'
/*表空间使用情况*/
SELECT A.TABLESPACE_NAME,
A.BYTES/(1024*1024*1024) TOTAL,
B.BYTES/(1024*1024*1024) USED,
C.BYTES/(1024*1024*1024) FREE,
(B.BYTES * 100) / A.BYTES "% USED",
(C.BYTES * 100) / A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;
select sum(bytes)/(1024*1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;
/*总连接数*/
select machine,count(machine) from v$session group by machine
/*激活连接*/
select machine,count(machine) from v$session where status = 'ACTIVE' group by machine
/*未激活连接*/
select machine,count(machine) from v$session where status = 'INACTIVE' group by machine
/*导出DDL*/
SELECT u.username,DBMS_METADATA.GET_DDL('USER',U.username) FROM DBA_USERS U;
SELECT ts.tablespace_name,to_char(DBMS_METADATA.GET_DDL('TABLESPACE', TS.tablespace_name)) FROM DBA_TABLESPACES TS
/*使用到的表空间*/
select * from sys.dba_tablespaces s where s.tablespace_name not in ('SYSTEM','USERS') and s.tablespace_name in (select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT')
select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT'
select b.table_name,b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT' and b.tablespace_name in('SYSTEM','USERS')
select * from sys.dba_tables b where b.tablespace_name like '%SVR%'
select * from sys.dba_tables b where b.tablespace_name='SYSTEM'
SELECT A.TABLESPACE_NAME,
A.BYTES / (1024 * 1024) as TOTAL ,
B.BYTES USED,
C.BYTES FREE,
(B.BYTES * 100) / A.BYTES "% USED",
(C.BYTES * 100) / A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
AND A.TABLESPACE_NAME = C.TABLESPACE_NAME
and A.TABLESPACE_NAME not in ('SYSTEM','USERS')
and A.TABLESPACE_NAME in (select distinct b.tablespace_name from sys.dba_tables b where b.owner='CCATSUPT')
/*查询表空间（无table的）*/
select a.tablespace_name
from dba_tablespaces a
minus (select distinct b.tablespace_name
from dba_tables b
union
select distinct c.tablespace_name from dba_indexes c)
SELECT m.compile_state FROM User_Mviews m
select * from user_jobs
--drop table interface_97to112_his
select * from interface_97to112_his t
select * from user_tables u where u.table_name like '%INTERFACE%'
create table interface_97to112_his
tablespace SVR_BASE_DAT
as
select * from where 1=2
/*添加字段*/
alter table tmp_telephone add newservice varchar(10);
DBMS_OUTPUT.PUT_LINE(sqlcode);
/*删除重复数据*/
delete from tmp_imp_user_tel t1 where rowid > (select min(rowid) from tmp_imp_user_tel t2 where t1.no2 = t2.no2)
/*更新2张表*/
UPDATE tmp_imp_user_97 a
SET a.no5 = (SELECT b.no2
FROM tmp_imp_user_tel b
WHERE a.no1 = b.no1
and rownum = 1)
WHERE EXISTS (SELECT 1 FROM tmp_imp_user_tel b WHERE a.no1 = b
1 找到锁过程的session id;
可以通过v$access 找到被死锁的过程的session id.
方法如下：
select sid from v$access t where t.OBJECT='过程名'
例如INTERFACE97_CUSTINFO_JH被死锁。
执行select sid from v$access t where t.OBJECT='INTERFACE97_CUSTINFO_JH'
可以找到锁过程的session id；
2 根据session id找到对应的数据库进程id
执行 SELECT SPID FROM V$PROCESS p, V$SESSION s WHERE p.addr=s.PADDR and s.SID=session id；
3 用root用户登陆操作系统 kill 数据库进程id
--1 获取等待时间
select sid,username,
event,blocking_session,
seconds_in_wait, wait_time
from v$session where state in ('WAITING')
and wait_class != 'Idle';
--2 根据上面获得SID和blocking_session (即正在占用锁的SID)两个会话执行的sql
select sid, sql_text
from v$session s, v$sql q
where sid in (85,87)
and (q.sql_id = s.sql_id or q.sql_id = s.prev_sql_id);
--说明上面的85是sid，87是block_session
--enq: TX - row lock contention tx事务锁方式block
--3 从v$session_wait_class中获得等待的相关信息
select wait_class_id,wait_class,total_waits,time_waited
from v$session_wait_class where sid=87;
结果如下：
WAIT_CLASS_ID WAIT_CLASS TOTAL_WAITS TIME_WAITED
1893977003 Other 1 0
4217450380 Application 176 47471
3386400367 Commit 4 6
2723168908 Idle 52 108030
2000153315 Network 53 0
1740759767 User I/O 7 5
4108307767 System I/O 5 0
在这个结果中，显示了每个类中会话的等待事件的次数，还有等待的时间，我们看到application这一级别中，
的等待次数是176,而time_waited时间是474.71秒(原来的6986631单位是厘秒单位，百分之一秒).
那么这个时候我们还可以从application等待类中寻找引起等待的原因。
--4 v$system_event视图中我们可以获得每种等待的出现次数。
我们这里的application的id为4217450380
select event,total_waits,time_waited
from v$system_event e,v$event_name n
where n.event_id=e.event_id
and n.wait_class_id=4217450380;
结果如下：
EVENT TOTAL_WAITS TIME_WAITED
enq: RO - fast object reuse 319 133
enq: TM - contention 2 0
enq: TX - row lock contention 162 47470
SQL*Net break/reset to client 14498 2284
假如我们并不知道是什么原因，导致了这个锁的争用，那我们该怎么去定位这个TX-row lock contention的问题呢？
这些数据仅仅告诉了我们ok,用户经历了162次的锁的竞争，共花费了47470厘秒，
有可能大多数的等待只有1到2厘秒,那么如何在进行进一步的诊断呢？
--5 Oracle 10g还为我们提供了另外一个视图叫v$event_histogram
它标市了等待时间的周期以及会话等待某一特定时间周期的频度
select wait_time_milli,wait_count
from v$event_histogram
where event='enq: TX - row lock contention';
结果如下:
WAIT_TIME_MILLI WAIT_COUNT
1 0
2 0
4 0
8 0
16 0
32 0
64 0
128 0
256 0
512 0
1024 0
2048 0
4096 162
v$event_histogram视图显示等待时间段以及在这期间会话等待某一特定事件--在这个例子中就是事务锁争用--的次数。
例如，会话等待少于4096毫秒（ms）的事件共162次。WAIT_COUNT列值之和为162.v$event_histogram视图显示，
大多数等待发生在4096毫秒的事件上，这就充分证明了该应用程序正在经历锁的争用问题，
如果视图显示等待发生在1毫秒的范围内，我们就不应该认为这是锁争用的问题，因为这样短时间的等待似乎是正常的
查看某表空间所有的表占空间大小
SELECT owner,
DECODE (partition_name,
NULL, segment_name,
segment_name || ':' || partition_name
) NAME,
segment_type, tablespace_name, BYTES/1024/1024 , initial_extent, next_extent,
pct_increase, extents, max_extents
FROM dba_segments
WHERE 1 = 1 AND extents > 1
ORDER BY 9 DESC, 3
查看表空间是否自动扩涨
select tablespace_name,file_name,bytes,autoextensible from dba_data_files;
