--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%'
/*添加字段*/ 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%'
/*添加字段*/ 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; |