oracle 数据库管理操作

最新推荐文章于 2024-03-27 21:44:08 发布

马尔斯的蓝色

最新推荐文章于 2024-03-27 21:44:08 发布

阅读量131

点赞数

分类专栏： sql 文章标签： sql

本文链接：https://blog.csdn.net/qq_37312838/article/details/106958977

版权

sql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1、查询激活的链接

select program,username,count(*) from v$session where status='INACTIVE'  group by program,username;

2、处理过多的链接

begin
 	for rs in (select sid,serial# from v$session s
				where s.username is not null and s.program in ('JDBC Thin Client')
				   and s.last_call_et >= 5 and s.status='INACTIVE')
	loop
		begin
		execute immediate 'alter system disconnect session ''' || rs.sid || ', ' ||rs.serial# || '''immediate';
		commit;
		 exception when others then
		 	null;
		end;
	end loop;
end;
/

3、查询执行进度

SELECT SE.SID,SE.serial#,
　　OPNAME,
　　TRUNC(SOFAR / TOTALWORK * 100, 2) || '%' AS PCT_WORK,
　　ELAPSED_SECONDS ELAPSED,
　　ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR) REMAIN_TIME,
　　SQL_TEXT
　　FROM V$SESSION_LONGOPS SL, V$SQLAREA SA, V$SESSION SE
　　WHERE SL.SQL_HASH_VALUE = SA.HASH_VALUE
　　AND SL.SID = SE.SID
　　AND SOFAR != TOTALWORK
　　ORDER BY START_TIME;

4、检查死锁的情况

SELECT SE.sid,
       SE.serial#,
       PR.spid,
       SE.status,
       SUBSTR(SE.program, 1, 10) PROG,
       SUBSTR(SE.machine, 1, 10) MACH,
       SQ.sql_text
  FROM v$session SE, v$sqlarea SQ, v$process PR
 WHERE SE.paddr = PR.ADDR(+) and se.sid=2874
   AND SE.sql_address = SQ.address(+)
   AND schemaname <> 'SYS'
 ORDER BY SE.sid

5、查看那个用户那个进程造成死锁

select b.username,b.sid,b.serial#,logon_time 
from v$locked_object a,v$session b where a.session_id = b.sid 
order by b.logon_time;

6、查询正在运行的job

SELECT SID,JOB FROM DBA_JOBS_RUNNING;

7、耗时长的sql

select * from (select SQL_TEXT,round(ELAPSED_TIME/1000000,2),round(ELAPSED_TIME/1000000/decode(EXECUTIONS,0,1,null,1,EXECUTIONS),2) perELAPSED_time,EXECUTIONS
	 from (select * from v$sql order by ELAPSED_TIME desc) where rownum<26) order by perELAPSED_time desc

8、数据库模糊检索

select name,text from dba_source where lower(text) like '%xxxx%'

9、查看表空间的使用情况

SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes 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;