自己總結的ORACLE日常運維常用的SQL
-- 會話被阻塞
select t.BLOCKING_SESSION ,t.sid,t.serial# ,t.* from v$session t where t.BLOCKING_SESSION is not null;
歷史session:
select * from V$ACTIVE_SESSION_HISTORY;
-- 當前正在執行的SQL
select sql.INST_ID,sql.SQL_TEXT,sql.SQL_ID,se.* from gv$sqlarea sql ,gv$session se where sql.ADDRESS=se.SQL_ADDRESS and sql.INST_ID=se.INST_ID;
--- 透過程式定位session
select p.SPID,s.* from gv$process p , gv$session s where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID and p.SPID='2394';
select p.SPID from gv$process p , gv$session s where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID and s.sid in (select sid from v$mystat);
-- 併發session
select * from v$px_session
--- 檢視程式正在執行的SQL
select S.EVENT,s.BLOCKING_SESSION,s.BLOCKING_INSTANCE, p.SPID,sql.SQL_TEXT,s.* from gv$process p , gv$session s,gv$sqlarea sql
where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID and sql.INST_ID=s.INST_ID
and sql.ADDRESS=s.SQL_ADDRESS
and s.BLOCKING_SESSION is not null
-- and p.SPID='2394';
;
# 檢視阻塞其它session的session
select 'kill -9 ' || p.SPID,p.SPID,s.* from gv$process p ,
gv$session s where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID
and (s.sid,s.inst_id) in (select t.BLOCKING_SESSION,t.BLOCKING_INSTANCE
from gv$session t where t.BLOCKING_SESSION is not null)
--查詢誰鎖定
SELECT /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,o.owner, o.object_name,
o.object_type, s.sid, s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC;
--釋放session Sql 語法:
alter system kill session 'sid, serial#'
--例子:
alter system kill session '379, 21132';
-- 遊標
select * from gv$open_cursor;
select inst_id,sid,count(1) from gv$open_cursor group by inst_id,sid order by count(1) desc;
select inst_id, substr(sql_text,1,80),count(1) from gv$open_cursor group by inst_id, substr(sql_text,1,80) order by count(1) desc;
-- 閃回查詢
select * from t as of timestamp to_timestamp('2010-06-29 22:57:47', 'yyyy-mm-dd hh24:mi:ss');
select * from t as of scn 1060174;
select * from t as of timestamp sysdate-5/24/60;
撤銷表空間使用情況:
SELECT sess.inst_id,sess.SID,sess.serial#, sess.osuser, sess.username,
rseg.segment_name SEGMENT, rseg.tablespace_name, trans.used_ublk,
trans.used_ublk*8 "UNDO SIZE(KB)", rseg.STATUS, sa.sql_text
FROM gv$session sess, gv$transaction trans, dba_rollback_segs rseg, gv$sqlarea sa
WHERE sess.inst_id=trans.inst_id and trans.inst_id=sa.inst_id
and sess.taddr=trans.addr AND trans.xidusn=rseg.segment_id(+)
AND sa.ADDRESS=sess.SQL_ADDRESS
ORDER BY sql_text;
SELECT * FROM dba_rollback_segs rseg;
SELECT * FROM gv$transaction ;
SELECT * FROM DBA_UNDO_EXTENTS t where t.status='ACTIVE';
SELECT t.STATUS,sum(t.BYTES)/1024/1024/1024 FROM DBA_UNDO_EXTENTS t group by t.STATUS
select * from dba_segments t where t.segment_name='_SYSSMU10_1197734989$';
--檢視當前哪些使用者使用撤銷段以及段的大小,啟動時間,活動狀態等
SELECT t.xidusn,t.start_time,t.used_ublk,t.status, s.username,r.segment_name
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
JOIN dba_rollback_segs r ON r.segment_id = t.xidusn ;
-- SQL
SELECT seg.BYTES/1024/1024/1024, sql.SQL_TEXT, t.xidusn,t.start_time,t.used_ublk,t.status, s.username,r.segment_name
FROM gv$transaction t
JOIN gv$session s ON t.ses_addr = s.saddr and t.INST_ID = s.INST_ID
JOIN dba_rollback_segs r ON r.segment_id = t.xidusn
JOIN gV$sqlarea sql on sql.ADDRESS=s.SQL_ADDRESS and sql.INST_ID = s.INST_ID
JOIN dba_segments seg on r.segment_name=seg.segment_name
select t.TABLESPACE_NAME,t.STATUS,sum(t.BYTES)/1024/1024 from DBA_UNDO_EXTENTS t
group by t.TABLESPACE_NAME,t.STATUS
order by t.TABLESPACE_NAME,t.STATUS
-----------------------------------------------------------------------------
-- 檢視錶空間使用率
select a.tablespace_name,
round(a.maxbytes /1024/1024/1024) "Sum GB",
round((a.bytes - b.free_bytes) /1024/1024/1024) "used GB",
(a.bytes - b.free_bytes) "used ",
round((a.maxbytes-(a.bytes - b.free_bytes)) /1024/1024/1024) "free_all GB",
round(((a.bytes - b.free_bytes) / a.maxbytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(decode(maxbytes,0,decode(autoextensible,'YES',34359721984,bytes),maxbytes)) maxbytes,sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) free_bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
and a.tablespace_name in ('USERS')
order by ((a.bytes - b.free_bytes) / a.maxbytes) desc;
-- 檢視哪個表的記錄數最多
select t.num_rows,t.* from dba_tables t where owner='AMRDCS' order by t.num_rows desc;
-- 查詢空塊多的TABLE
select ex.owner,ex.segment_name,ex.segment_type,round(sum(ex.bytes)/1024/1024) tab_free from dba_free_space sp, dba_extents ex
where sp.block_id=ex.block_id
group by ex.owner,ex.segment_name,ex.segment_type
order by tab_free desc;
-- 檢視資料庫整體各個時間段的負載,每小時一天記錄
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char (BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, '
hh24:mi') date_time, stat_name, round((e_value - nvl(b_value, 0)) / (extract(day
from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour
from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute
from(end_interval_time - begin_interval_time)) * 60 + extract(second
from(end_interval_time - begin_interval_time))), 0) per_sec
from sysstat
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0
-----------------------------------------------------------------------------
查詢歸檔日誌大小
select t.thread#,sum(t.BLOCKS*t.BLOCK_SIZE/1024/1024/1024) from v$archived_log t where t.FIRST_TIME > sysdate-1 group by t.thread#;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('0jytavpsa7yaj',0));
select t.thread#,to_char(t.FIRST_TIME,'yyyymmdd hh24'),round(sum(t.BLOCKS*t.BLOCK_SIZE/1024/1024/1024)) from v$archived_log t
where t.FIRST_TIME > sysdate-1 group by t.thread#,to_char(t.FIRST_TIME,'yyyymmdd hh24')
order by 1,2 ;
----------------------------------------------------------------------------
sql最佳化相關包
DBMS_ADVISOR, DBMS_SQLTUNE, DBMS_AUTO_SQLTUNE, and DBMS_WORKLOAD_REPOSITORY packages
select * from dba_advisor_definitions;
select * from DBA_ADVISOR_FINDINGS;
select dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE') from dual;
根據SQL_ID檢視執行計劃
select * from table(dbms_xplan.display_awr('sql_id'));
select * from table(dbms_xplan.display_cursor('sql_id'))
select * from table(dbms_xplan.display_cursor('sql_id',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
select * from table(dbms_xplan.display_awr('sql_id'));
----------------------------------------------------------
關聯關係查詢:
select t.name,t.referenced_owner,t.referenced_name,s.type,s.text
from user_dependencies t,user_source s
where s.name=t.name and upper(s.text) like '%'||t.referenced_name ||'%'
--and t.name=upper('sa_customer_analysis_pkg')
and t.referenced_name=upper('hp_customer_temp_rec');
------------------------------------------------------------
檢視備份進度(查詢v$session_longops)
select sid,serial#,context,sofar,totalwork round(sofar/totalwork*100,2) "% Complete",
from v$session_longops
where opname like 'RMAN:%'
and opname NOT LIKE 'RMAN: aggregate%';
------------------------------------------------------------
12c 審計
select * from UNIFIED_AUDIT_TRAIL order by event_timestamp desc
------------------------------------------------------------
查詢繫結變數值
SELECT SQL_ID,NAME, POSITION, value_string, ANYDATA.accesstimestamp(value_anydata)
From gV$sql_Bind_Capture A
Where sql_id='aqfcs7drzzcfw';
select t.BLOCKING_SESSION ,t.sid,t.serial# ,t.* from v$session t where t.BLOCKING_SESSION is not null;
歷史session:
select * from V$ACTIVE_SESSION_HISTORY;
-- 當前正在執行的SQL
select sql.INST_ID,sql.SQL_TEXT,sql.SQL_ID,se.* from gv$sqlarea sql ,gv$session se where sql.ADDRESS=se.SQL_ADDRESS and sql.INST_ID=se.INST_ID;
--- 透過程式定位session
select p.SPID,s.* from gv$process p , gv$session s where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID and p.SPID='2394';
select p.SPID from gv$process p , gv$session s where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID and s.sid in (select sid from v$mystat);
-- 併發session
select * from v$px_session
--- 檢視程式正在執行的SQL
select S.EVENT,s.BLOCKING_SESSION,s.BLOCKING_INSTANCE, p.SPID,sql.SQL_TEXT,s.* from gv$process p , gv$session s,gv$sqlarea sql
where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID and sql.INST_ID=s.INST_ID
and sql.ADDRESS=s.SQL_ADDRESS
and s.BLOCKING_SESSION is not null
-- and p.SPID='2394';
;
# 檢視阻塞其它session的session
select 'kill -9 ' || p.SPID,p.SPID,s.* from gv$process p ,
gv$session s where p.ADDR=s.PADDR and p.INST_ID=s.INST_ID
and (s.sid,s.inst_id) in (select t.BLOCKING_SESSION,t.BLOCKING_INSTANCE
from gv$session t where t.BLOCKING_SESSION is not null)
--查詢誰鎖定
SELECT /*+ rule */
lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,o.owner, o.object_name,
o.object_type, s.sid, s.serial#
FROM v$locked_object l, dba_objects o, v$session s
WHERE l.object_id = o.object_id
AND l.session_id = s.sid
ORDER BY o.object_id, xidusn DESC;
--釋放session Sql 語法:
alter system kill session 'sid, serial#'
--例子:
alter system kill session '379, 21132';
-- 遊標
select * from gv$open_cursor;
select inst_id,sid,count(1) from gv$open_cursor group by inst_id,sid order by count(1) desc;
select inst_id, substr(sql_text,1,80),count(1) from gv$open_cursor group by inst_id, substr(sql_text,1,80) order by count(1) desc;
-- 閃回查詢
select * from t as of timestamp to_timestamp('2010-06-29 22:57:47', 'yyyy-mm-dd hh24:mi:ss');
select * from t as of scn 1060174;
select * from t as of timestamp sysdate-5/24/60;
撤銷表空間使用情況:
SELECT sess.inst_id,sess.SID,sess.serial#, sess.osuser, sess.username,
rseg.segment_name SEGMENT, rseg.tablespace_name, trans.used_ublk,
trans.used_ublk*8 "UNDO SIZE(KB)", rseg.STATUS, sa.sql_text
FROM gv$session sess, gv$transaction trans, dba_rollback_segs rseg, gv$sqlarea sa
WHERE sess.inst_id=trans.inst_id and trans.inst_id=sa.inst_id
and sess.taddr=trans.addr AND trans.xidusn=rseg.segment_id(+)
AND sa.ADDRESS=sess.SQL_ADDRESS
ORDER BY sql_text;
SELECT * FROM dba_rollback_segs rseg;
SELECT * FROM gv$transaction ;
SELECT * FROM DBA_UNDO_EXTENTS t where t.status='ACTIVE';
SELECT t.STATUS,sum(t.BYTES)/1024/1024/1024 FROM DBA_UNDO_EXTENTS t group by t.STATUS
select * from dba_segments t where t.segment_name='_SYSSMU10_1197734989$';
--檢視當前哪些使用者使用撤銷段以及段的大小,啟動時間,活動狀態等
SELECT t.xidusn,t.start_time,t.used_ublk,t.status, s.username,r.segment_name
FROM v$transaction t
JOIN v$session s ON t.ses_addr = s.saddr
JOIN dba_rollback_segs r ON r.segment_id = t.xidusn ;
-- SQL
SELECT seg.BYTES/1024/1024/1024, sql.SQL_TEXT, t.xidusn,t.start_time,t.used_ublk,t.status, s.username,r.segment_name
FROM gv$transaction t
JOIN gv$session s ON t.ses_addr = s.saddr and t.INST_ID = s.INST_ID
JOIN dba_rollback_segs r ON r.segment_id = t.xidusn
JOIN gV$sqlarea sql on sql.ADDRESS=s.SQL_ADDRESS and sql.INST_ID = s.INST_ID
JOIN dba_segments seg on r.segment_name=seg.segment_name
select t.TABLESPACE_NAME,t.STATUS,sum(t.BYTES)/1024/1024 from DBA_UNDO_EXTENTS t
group by t.TABLESPACE_NAME,t.STATUS
order by t.TABLESPACE_NAME,t.STATUS
-----------------------------------------------------------------------------
-- 檢視錶空間使用率
select a.tablespace_name,
round(a.maxbytes /1024/1024/1024) "Sum GB",
round((a.bytes - b.free_bytes) /1024/1024/1024) "used GB",
(a.bytes - b.free_bytes) "used ",
round((a.maxbytes-(a.bytes - b.free_bytes)) /1024/1024/1024) "free_all GB",
round(((a.bytes - b.free_bytes) / a.maxbytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(decode(maxbytes,0,decode(autoextensible,'YES',34359721984,bytes),maxbytes)) maxbytes,sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) free_bytes
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
and a.tablespace_name in ('USERS')
order by ((a.bytes - b.free_bytes) / a.maxbytes) desc;
-- 檢視哪個表的記錄數最多
select t.num_rows,t.* from dba_tables t where owner='AMRDCS' order by t.num_rows desc;
-- 查詢空塊多的TABLE
select ex.owner,ex.segment_name,ex.segment_type,round(sum(ex.bytes)/1024/1024) tab_free from dba_free_space sp, dba_extents ex
where sp.block_id=ex.block_id
group by ex.owner,ex.segment_name,ex.segment_type
order by tab_free desc;
-- 檢視資料庫整體各個時間段的負載,每小時一天記錄
WITH sysstat AS
(select sn.begin_interval_time begin_interval_time,
sn.end_interval_time end_interval_time,
ss.stat_name stat_name,
ss.value e_value,
lag(ss.value, 1) over(order by ss.snap_id) b_value
from dba_hist_sysstat ss, dba_hist_snapshot sn
where trunc(sn.begin_interval_time) >= sysdate - 7
and ss.snap_id = sn.snap_id
and ss.dbid = sn.dbid
and ss.instance_number = sn.instance_number
and ss.dbid = (select dbid from v$database)
and ss.instance_number = (select instance_number from v$instance)
and ss.stat_name = 'DB time')
select to_char (BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, '
hh24:mi') date_time, stat_name, round((e_value - nvl(b_value, 0)) / (extract(day
from(end_interval_time - begin_interval_time)) * 24 * 60 * 60 + extract(hour
from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute
from(end_interval_time - begin_interval_time)) * 60 + extract(second
from(end_interval_time - begin_interval_time))), 0) per_sec
from sysstat
where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0
-----------------------------------------------------------------------------
查詢歸檔日誌大小
select t.thread#,sum(t.BLOCKS*t.BLOCK_SIZE/1024/1024/1024) from v$archived_log t where t.FIRST_TIME > sysdate-1 group by t.thread#;
SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('0jytavpsa7yaj',0));
select t.thread#,to_char(t.FIRST_TIME,'yyyymmdd hh24'),round(sum(t.BLOCKS*t.BLOCK_SIZE/1024/1024/1024)) from v$archived_log t
where t.FIRST_TIME > sysdate-1 group by t.thread#,to_char(t.FIRST_TIME,'yyyymmdd hh24')
order by 1,2 ;
----------------------------------------------------------------------------
sql最佳化相關包
DBMS_ADVISOR, DBMS_SQLTUNE, DBMS_AUTO_SQLTUNE, and DBMS_WORKLOAD_REPOSITORY packages
select * from dba_advisor_definitions;
select * from DBA_ADVISOR_FINDINGS;
select dbms_space.asa_recommendations('TRUE', 'TRUE', 'FALSE') from dual;
根據SQL_ID檢視執行計劃
select * from table(dbms_xplan.display_awr('sql_id'));
select * from table(dbms_xplan.display_cursor('sql_id'))
select * from table(dbms_xplan.display_cursor('sql_id',null,'ADVANCED ALLSTATS LAST PEEKED_BINDS'));
select * from table(dbms_xplan.display_awr('sql_id'));
----------------------------------------------------------
關聯關係查詢:
select t.name,t.referenced_owner,t.referenced_name,s.type,s.text
from user_dependencies t,user_source s
where s.name=t.name and upper(s.text) like '%'||t.referenced_name ||'%'
--and t.name=upper('sa_customer_analysis_pkg')
and t.referenced_name=upper('hp_customer_temp_rec');
------------------------------------------------------------
檢視備份進度(查詢v$session_longops)
select sid,serial#,context,sofar,totalwork round(sofar/totalwork*100,2) "% Complete",
from v$session_longops
where opname like 'RMAN:%'
and opname NOT LIKE 'RMAN: aggregate%';
------------------------------------------------------------
12c 審計
select * from UNIFIED_AUDIT_TRAIL order by event_timestamp desc
------------------------------------------------------------
查詢繫結變數值
SELECT SQL_ID,NAME, POSITION, value_string, ANYDATA.accesstimestamp(value_anydata)
From gV$sql_Bind_Capture A
Where sql_id='aqfcs7drzzcfw';
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/10995764/viewspace-2125303/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 總結幾個ORACLE資料庫日常運維常用的命令(持續更新)Oracle資料庫運維
- ORACLE DG 日常維護常用SQLOracleSQL
- Redis日常運維-常用命令彙總Redis運維
- Oracle DG運維常用SQLOracle運維SQL
- Oracle日常運維操作總結-資料庫的啟動和關閉Oracle運維資料庫
- DBA日常維護SQL指令碼_自己編寫的SQL指令碼
- Lync日常運維常用命令運維
- Linux運維筆記-日常操作命令總結(2)Linux運維筆記
- Linux運維筆記-日常操作命令總結(3)Linux運維筆記
- Linux運維筆記-日常操作命令總結(1)Linux運維筆記
- ORACLE資料庫日常維護知識總結Oracle資料庫
- Oracle常用維護語句總結Oracle
- 運維經理的運維經驗總結運維
- MongoDB日常運維操作命令小結MongoDB運維
- SQLServer常用運維SQL整理SQLServer運維
- MS SQL 日常維護管理常用指令碼(下)SQL指令碼
- MS SQL 日常維護管理常用指令碼(上)SQL指令碼
- 你必須知道的python運維常用指令碼!(日常更新)Python運維指令碼
- RAC日常運維運維
- oracle 維護常用SQLOracleSQL
- Oracle RAC日常運維-DATA磁碟組故障Oracle運維
- Oracle RAC日常運維-ASM磁碟擴容Oracle運維ASM
- ORACLE OGG運維及日常監控Oracle運維
- 運維排查問題常用sql運維SQL
- Oracle 常用運維命令整理Oracle運維
- 運維日常工作運維
- EBS DBA日常維護使用的sqlSQL
- 運維人員常用的Linux命令彙總運維Linux
- Linux運維常用的20個命令彙總!Linux運維
- Linux 系統日常運維 9 大技能,搞定 90% 日常運維Linux運維
- 日常工作最常用的一些DOS命令總結
- ansible環境部署及常用模組總結 - 運維手冊運維
- Python 運維總結Python運維
- 【Tips】【UE】總結自己常用的UltraEdit使用技巧
- 日常常用SQL命令SQL
- MongoDB日常運維-06副本集搭建錯誤彙總MongoDB運維
- Oracle常用的轉儲方法總結Oracle
- ogg日常運維命令運維