Oracle常用維護語句總結

--不斷更新中

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';