自己总结的ORACLE日常运维常用的SQL-java

fgdfg

　　-- 会话被阻塞
　　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;
　　select SID,SERIAL# from V$session  where SID in (select sid from v$enqueue_lock t where t.type='TO');
　　--释放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>　　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';