Oracle常用维护语句总结

本文提供了一系列Oracle数据库管理的实用SQL查询语句,包括检查表空间使用情况、查看锁的情况、监控资源消耗大的进程等,帮助数据库管理员更好地维护和优化Oracle数据库。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

--不断更新中

1. 查看表空间使用情况:

SELECT a.tablespace_name "表空间名",total 表空间大小,free 表空间剩余大小,
(total-free) 表空间使用大小,
ROUND((total-free)/total,4)*100 "使用率 %"
FROM (SELECT tablespace_name,SUM(bytes) free FROM DBA_FREE_SPACE
GROUP BY tablespace_name ) a,
(SELECT tablespace_name,SUM(bytes) total FROM DBA_DATA_FILES
GROUP BY tablespace_name) b
WHERE a.tablespace_name=b.tablespace_name
/

--根据自己需要,可以对使用率进行order by下

[@more@]

2. 查看锁的情况

select /*+ RULE */ ls.osuser os_user_name,  ls.username user_name, 
decode(ls.type, 'RW', 'Row wait enqueue lock', 'TM', 'DML enqueue lock', 'TX', 
'Transaction enqueue lock', 'UL', 'User supplied lock') lock_type, 
o.object_name object,  decode(ls.lmode, 1, null, 2, 'Row Share', 3, 
'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', null) 
lock_mode,  o.owner,  ls.sid,  ls.serial# serial_num,  ls.id1,  ls.id2  
from sys.dba_objects o, (  select s.osuser,  s.username,  l.type,  
l.lmode,  s.sid,  s.serial#,  l.id1,  l.id2  from v$session s,  
v$lock l  where s.sid = l.sid ) ls where o.object_id = ls.id1 and  o.owner 
<> 'SYS'  order by o.owner, o.object_name
/

3. 耗资源的进程(top session)

select s.schemaname schema_name,  decode(sign(48 - command), 1, 
to_char(command), 'Action Code #' || to_char(command) ) action,  status 
session_status,  s.osuser os_user_name,  s.sid,     p.spid ,     s.serial# serial_num, 
nvl(s.username, '[Oracle process]') user_name,  s.terminal terminal,  
s.program program,  st.value criteria_value from v$sesstat st,  v$session s , v$process p  
where st.sid = s.sid and  st.statistic# = to_number('38') and  ('ALL' = 'ALL' 
or s.status = 'ALL') and p.addr = s.paddr order by st.value desc, p.spid asc, s.username asc, s.osuser asc
/

4. 捕捉运行很久的SQL

column username format a12 
column opname format a16 
column progress format a8 
select username,sid,opname, 
    round(sofar*100 / totalwork,0) || '%' as progress, 
    time_remaining,sql_text 
from v$session_longops , v$sql 
where time_remaining <> 0 
and sql_address = address 
and sql_hash_value = hash_value;


5. 查看数据库版本

Select version FROM Product_component_version 
Where SUBSTR(PRODUCT,1,6)='Oracle';

6. 查看日志文件

select member from v$logfile;

7. 查看回滚段名称和大小

select segment_name, tablespace_name, r.status, 
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, 
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;

8. 查看表空间物理文件的名称及大小

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

9. 查找产生锁的SQL语句

set pagesize 60
set linesize 132
select s.username username,
a.sid sid,
a.owner||'.'||a.object object,
s.lockwait,
t.sql_text SQL
from v$sqltext t,
v$session s,
v$access a
where t.address = s.sql_address
and t.hash_value = s.sql_hash_value
and s.sid = a.sid
and a.owner != 'SYS'
and upper(substr(a.object,1,2)) != 'V$'
/

10. 检查用户是否将SYSTEM表空间作为缺省表空间

select username,default_tablespace from dba_users where default_tablespace = 'SYSTEM';

11. 检查用户是否将SYSTEM表空间作为临时表空间

select username,temporary_tablespace from dba_users where temporary_tablespace = 'SYSTEM';

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9896745/viewspace-926839/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/9896745/viewspace-926839/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值