查询当前等待事件对应的对象
创新互联建站主要为客户提供服务项目涵盖了网页视觉设计、VI标志设计、成都全网营销推广、网站程序开发、HTML5响应式重庆网站建设公司、手机网站开发、微商城、网站托管及成都网站维护、WEB系统开发、域名注册、国内外服务器租用、视频、平面设计、SEO优化排名。设计、前端、后端三个建站步骤的完善服务体系。一人跟踪测试的建站服务标准。已经为被动防护网行业客户提供了网站制作服务。
select distinct wait_class#,wait_class from v$session_wait_class order by 1
以上sql发现wait_class#=6的是空闲等待
select * from
(select sid,event,p1text,p1,p2text,p2,p3text,p3,WAIT_TIME,SECONDS_IN_WAIT,wait_class# from v$session_wait where wait_class# <> 6 order by wait_time desc)
where rownum <=10;
能查出等待的对象是否来自数据文件（如果以上查到p1text是file#或file number）
select * from
(select owner,segment_name,segment_type,block_id,bytes
from
dba_extents where file_id=p1 and
block_id where rownum<2 把上面第二个sql结果的p1、p2值代入上述sql的file_id、block_id 查询当前正在消耗临时空间的sql语句 Select distinct
se.username, se.sid, su.blocks
* to_number(rtrim(p.value))/1024/1024 as space_G, su.tablespace,
sql_text from
V$TEMPSEG_USAGE su, v$parameter p,
v$session se, v$sql s where
p.name
=
'db_block_size' and
su.session_addr=se.saddr and
su.sqlhash=s.hash_value and
su.sqladdr=s.address and
se.STATUS='ACTIVE' 或 官方文档对display_cursor这个函数的说明里面没有advanced这个参数值，只有BASIC、TYPICAL、ALL这几个，不过实践中发现advanced这个参数值显示的内容比这几个参数值显示的都多
查询因PGA不足而使用临时表空间的最频繁的10条SQL语句
select * from
(
select OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE,
sum(OPTIMAL_EXECUTIONS) optimal_cnt,sum(ONEPASS_EXECUTIONS) as onepass_cnt,
sum(MULTIPASSES_EXECUTIONS) as mpass_cnt,s.sql_text
from V$SQL_WORKAREA swa, v$sql s
where swa.sql_id=s.sql_id
group by OPERATION_TYPE,ESTIMATED_OPTIMAL_SIZE,ESTIMATED_ONEPASS_SIZE,sql_text
having sum(ONEPASS_EXECUTIONS+MULTIPASSES_EXECUTIONS)>0
order by sum(ONEPASS_EXECUTIONS) desc
)
where rownum<10
查询正在消耗PGA的SQL
select s.sql_text, sw.EXPECTED_SIZE, sw.ACTUAL_MEM_USED,sw.NUMBER_PASSES, sw.TEMPSEG_SIZE
from v$sql_workarea_active sw, v$sql s where sw.sql_id=s.sql_id;
查询需要使用绑定变量的sql,10G以后推荐第二种
（任何一条执行过的语句不管执行了几次在V$SQL中都只有一条记录，V$SQL中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select * from t1.test在sye、system下执行则V$SQL只有一条记录(谁先执行则PARSING_SCHEMA_NAME显示谁)。如在sys和system都执行select * from test则V$SQL中有两条记录，两条记录的CHILD_NUMBER和PARSING_SCHEMA_NAME不一样。同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个V$SQL记录，说明V$SQL对应的sql语句必须一模一样，如果alter system flush shared_pool（主站慎用）后再执行一样的语句，发现语句在V$SQL中的SQL_ID和HASH_VALUE与之前的一样，说明SQL_ID和HASH_VALUE应该是oracle自己的一套算法来的，只是根据sql语句内容来进行转换，sql语句不变则SQL_ID和HASH_VALUE也不变。）
第一种
select * from (
select count(*),sql_id, substr(sql_text,1,40)
from v$sql
group by sql_id, substr(sql_text,1,40) having count(*) > 10 order by count(*) desc) where rownum<10
第二种
count(1)>10表示类语句运行了10次以上
select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
(select /*+ unnest */
FORCE_MATCHING_SIGNATURE
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 10)
查看数据文件可用百分比(dba_free_space并不会包含所有file_id，如果该数据文件满了，则dba_free_space.file_id没有该数据文件，所以以下sql中a.file_id=b.file_id的条件过滤后是不会有所有file_id的)
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",
ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件总容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件已用容量",
ROUND(sum(nvl(a.bytes,0))/1024/1024/1024,2)||'G' "文件可用容量",
ROUND(sum(nvl(a.bytes,0))/(b.bytes),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by b.tablespace_name;
--如下为标准版
select b.file_id,b.tablespace_name,b.file_name,b.AUTOEXTENSIBLE,
ROUND(b.MAXBYTES/1024/1024/1024,2) ||'G' "文件最大可用总容量",
ROUND(b.bytes/1024/1024/1024,2) ||'G' "文件当前总容量",
ROUND((b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024,2)||'G' "文件当前已用容量",
ROUND((decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes)/1024/1024/1024,2)||'G' "文件可用容量",
ROUND((decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes)/(decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)),2)*100||'%' "文件可用百分比"
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_name,b.file_id,b.bytes,b.AUTOEXTENSIBLE,b.MAXBYTES
order by decode(AUTOEXTENSIBLE,'NO',b.BYTES,b.MAXBYTES)+sum(nvl(a.bytes,0))-b.bytes;
查看数据库文件的实际总量，单位G
select a.datafile_size+b.tempfile_size-c.free_size from
(select sum(bytes/1024/1024/1024) datafile_size from dba_data_files ) a,
(select sum(bytes/1024/1024/1024) tempfile_size from dba_temp_files ) b,
(select sum(bytes/1024/1024/1024) free_size from dba_free_space ) c
查看表空间可用百分比（dba_free_space不会包含所有tablespace，如果一个表空间的数据文件都满了，则这个表空间不会出现在dba_free_space中）
select b.tablespace_name,a.maxsize max_M,a.total total_M,b.free free_M,round((b.free/a.total)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total ,sum(MAXBYTES/(1024*1024)) maxsize from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name order by "% Free";
--如下为标准版
select b.tablespace_name,a.maxsize max_M,a.total total_M,b.free free_M,round(((a.maxsize+b.free-a.total)/a.maxsize)*100) "% Free" from
(select tablespace_name, sum(bytes/(1024*1024)) total ,sum((decode(AUTOEXTENSIBLE,'NO',BYTES,MAXBYTES))/(1024*1024)) maxsize from dba_data_files group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space group by tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name order by "% Free";
查看临时表空间使用率
SELECT temp_used.tablespace_name,round(total),used,
round(total - used) as "Free",
round(nvl(total-used, 0) * 100/total,1) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used
FROM GV$TEMP_SPACE_HEADER
GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(decode(autoextensible,'YES',MAXBYTES,bytes))/1024/1024 total
FROM dba_temp_files
GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name
或
SELECT a.tablespace_name, round(a.BYTES/1024/1024) total_M, round(a.bytes/1024/1024 - nvl(b.bytes/1024/1024, 0)) free_M,
round(b.bytes/1024/1024) used,round(b.using/1024/1024) using
FROM (SELECT tablespace_name, SUM (decode(autoextensible,'YES',MAXBYTES,bytes)) bytes FROM dba_temp_files GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (bytes_cached) bytes,sum(bytes_used) using FROM v$temp_extent_pool GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)
查询undo表空间使用情况
select tablespace_name,status,sum(bytes)/1024/1024 M from dba_undo_extents group by tablespace_name,status
查询使用undo比较多的SQL
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value>0
ORDER BY a.value DESC
估计undo需要多大
SELECT (UR * (UPS * DBS)) AS "Bytes"
FROM (select max(tuned_undoretention) AS UR from v$undostat),
(SELECT undoblks/((end_time-begin_time)*86400) AS UPS
FROM v$undostat
WHERE undoblks = (SELECT MAX(undoblks) FROM v$undostat)),
(SELECT block_size AS DBS
FROM dba_tablespaces
WHERE tablespace_name = (SELECT UPPER(value) FROM v$parameter WHERE name = 'undo_tablespace'));
产生undo的当前活动会话是哪些
即dba_undo_extents.status=active对应的v$session.sid
SELECT a.inst_id, a.sid, c.username, c.osuser, c.program, b.name,
a.value, d.used_urec, d.used_ublk
FROM gv$sesstat a, v$statname b, gv$session c, gv$transaction d
WHERE a.statistic# = b.statistic#
AND a.inst_id = c.inst_id
AND a.sid = c.sid
AND c.inst_id = d.inst_id
AND c.saddr = d.ses_addr
AND a.statistic# = 284
AND a.value>0
ORDER BY a.value DESC
查看ASM磁盘组使用率
select name,round(total_mb/1024) "总容量",round(free_mb/1024) "空闲空间",round((free_mb/total_mb)*100) "可用空间比例" from gv$asm_diskgroup
统计每个用户使用表空间率
SELECT c.owner "用户",
a.tablespace_name "表空间名",
total/1024/1024 "表空间大小M",
free/1024/1024 "表空间剩余大小M",
( total - free )/1024/1024 "表空间使用大小M",
Round(( total - free ) / total, 4) * 100 "表空间总计使用率 %",
c.schemas_use/1024/1024 "用户使用表空间大小M",
round((schemas_use)/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,
(Select owner ,Tablespace_Name,
Sum(bytes) schemas_use
From Dba_Segments
Group By owner,Tablespace_Name) c
WHERE a.tablespace_name = b.tablespace_name
and a.tablespace_name =c.Tablespace_Name
order by "用户","表空间名"
查看闪回区\快速恢复区空间使用率
select sum(percent_space_used)||'%' "已使用空间比例" from V$RECOVERY_AREA_USAGE
或
select round(100*(a.space_used/space_limit),2)||'%' "已使用空间比例",a.* from v$recovery_file_dest a;
查看僵死进程，分两种（一种是会话不在的，另一种是会话标记为killed的但是会话还在的）
alter system kill session一执行则session即标记为KILLED，但是如果会话产生的数据量大则这个kill可能会比较久，在这个过程中session标记为KILLED但是这个会话还在V$session中，则V$session.paddr还在，所以可以匹配到V$process.addr，所以process进程还在；当kill过程执行完毕，则这个会话即不在V$session中
会话不在的
select * from v$process where addr not in (select paddr from v$session) and pid not in (1,17,18)
会话还在的，但是会话标记为killed
select * from v$process where addr in (select paddr from v$session where status='KILLED')
再根据上述结果中的SPID通过如下命令可以查看到process的启动时间
ps auxw|head -1;ps auxw|grep SPID
查看行迁移或行链接的表
select * From dba_tables where nvl(chain_cnt,0)<>0
chain_cnt ：Number of rows in the table that are chained from one data block to another or that have migrated to a new block, requiring a link to preserve the old rowid. This column is updated only after you analyze the table.
数据缓冲区命中率（百分比小于90就要加大db_cache_size）
SELECT a.VALUE+b.VALUE logical_reads, c.VALUE phys_reads,
round(100*(1-c.value/(a.value+b.value)),2)||'%' hit_ratio
FROM v$sysstat a,v$sysstat b,v$sysstat c
WHERE a.NAME='db block gets'
AND b.NAME='consistent gets'
AND c.NAME='physical reads';
或
SELECT DB_BLOCK_GETS+CONSISTENT_GETS Logical_reads,PHYSICAL_READS phys_reads,
round(100*(1-(PHYSICAL_READS/(DB_BLOCK_GETS+CONSISTENT_GETS))),2)||'%' "Hit Ratio"
FROM V$BUFFER_POOL_STATISTICS WHERE NAME='DEFAULT';
共享池命中率（百分比小于90就要加大shared_pool_size）
以下两者应该都可以，看个人怎么理解
select sum(pinhits)/sum(pins)*100 from v$librarycache;
select sum(pinhits-reloads)/sum(pins)*100 from v$librarycache;
查询归档日志切换频率
select sequence#,to_char(first_time,'yyyymmdd_hh34:mi:ss')
firsttime,round((first_time-lag(first_time) over(order by first_time))*24*60,2) minutes from
v$log_history where first_time > sysdate - 3 order by first_time,minutes;
或
select sequence#,to_char(first_time,'yyyy-mm-dd hh34:mi:ss') First_time,First_change#,switch_change# from
v$loghist where first_time>sysdate-3 order by 1;
或
SELECT TO_CHAR(first_time, 'MM/DD') DAY,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '00', 1, 0)) H00,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '01', 1, 0)) H01,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '02', 1, 0)) H02,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '03', 1, 0)) H03,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '04', 1, 0)) H04,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '05', 1, 0)) H05,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '06', 1, 0)) H06,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '07', 1, 0)) H07,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '08', 1, 0)) H08,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '09', 1, 0)) H09,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '10', 1, 0)) H10,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '11', 1, 0)) H11,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '12', 1, 0)) H12,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '13', 1, 0)) H13,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '14', 1, 0)) H14,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '15', 1, 0)) H15,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '16', 1, 0)) H16,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '17', 1, 0)) H17,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '18', 1, 0)) H18,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '19', 1, 0)) H19,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '20', 1, 0)) H20,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '21', 1, 0)) H21,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '22', 1, 0)) H22,
SUM(DECODE(TO_CHAR(first_time, 'HH24'), '23', 1, 0)) H23,
COUNT(*) TOTAL
FROM (SELECT ROWNUM RN, FIRST_TIME FROM V$LOG_HISTORY WHERE first_time>sysdate-18
and FIRST_TIME>ADD_MONTHS(SYSDATE,-1) ORDER BY FIRST_TIME)
GROUP BY TO_CHAR(first_time, 'MM/DD')
ORDER BY MIN(RN);
查询lgwr进程写日志时每执行一次lgwr需要多少秒，在state是waiting的情况下，某个等待编号seq#下，seconds_in_wait达多少秒，就是lgwr进程写一次IO需要多少秒
select event,state,seq#,seconds_in_wait,program from v$session where program like '%LGWR%' and state='WAITING'
查询没有索引的表
Select table_name from user_tables where table_name not in (select table_name from user_indexes)
Select table_name from user_tables where table_name not in (select table_name from user_ind_columns)
查询一个AWR周期内的平均session数、OS平均负载、平均db time、平均每秒多少事务
select to_char(max(BEGIN_TIME),'yyyy-mm-dd hh34:mi')||to_char(max(end_time),'--hh34:mi') time,
snap_id,
trunc(sum(case metric_name when 'Session Count' then average end),2) sessions,
trunc(sum(case metric_name when 'Current OS Load' then average end),2) OS_LOAD,
(trunc(sum(case metric_name when 'Database Time Per Sec' then average end),2)/100)*(ceil((max(end_time)-max(BEGIN_TIME))*24*60*60)) Database_Time_second,
trunc(sum(case metric_name when 'User Transaction Per Sec' then average end),2) User_Transaction_Per_Sec
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;
--Database Time Per Sec对应值的单位是百分一秒/每秒
--(/100)*(ceil((max(end_time)-max(BEGIN_TIME))*24*60*60))是代表每个snap周期内的总秒数，oracle 两个时间相减默认的是天数,*24*60*60 为相差的秒数
--这个SQL查到的DB TIME比较准确，和awr上面的db time比较一致
查询产生热块较多的对象
x$bh .tch(Touch)表示访问次数越高，热点快竞争问题就存在
SELECT e.owner, e.segment_name, e.segment_type
FROM dba_extents e,
(SELECT *
FROM (SELECT addr,ts#,file#,dbarfil,dbablk,tch
FROM x$bh
ORDER BY tch DESC)
WHERE ROWNUM < 11) b
WHERE e.relative_fno = b.dbarfil
AND e.block_id <= b.dbablk
AND e.block_id + e.blocks > b.dbablk;
手工创建快照的语句
exec dbms_workload_repository.create_snapshot;
AWR设置每隔30分钟收集一次报告，保留14天的报告
exec DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>14*24*60, interval=>30);
select * from dba_hist_wr_control;
AWR基线查看和创建
select * from dba_hist_baseline;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id=>7550,end_snap_id=>7660,baseline_name=>'am_baseline');
导出AWR报告的SQL语句
select * from dba_hist_snapshot
select * from table(dbms_workload_repository.awr_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid))
select * from TABLE(DBMS_WORKLOAD_REPOSITORY.awr_diff_report_html(DBID, INSTANCE_NUMBER, startsnapid,endsnapid, DBID, INSTANCE_NUMBER, startsnapid,endsnapid));
导出最新ADDM的报告（需要sys用户）
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks
where task_id =(
select max(t.task_id) from dba_advisor_tasks t, dba_advisor_log l where
t.task_id=l.task_id and t.advisor_name='ADDM' and l.status='COMPLETED' );
select task_id,task_name,description from dba_advisor_tasks order by 1 desc
select dbms_advisor.get_task_report(task_name) from dba_advisor_tasks where task_id =XX
查询某个SQL的执行计划
select * from table(dbms_xplan.display_cursor('sql_id',0,'advanced'));
上面的0表示v$sql.child_number为0，如果一个sql_id在v$sql中有多行说明有多个child_number，要看哪儿child_number的执行计划，就写哪个的值，比如要看child_number为2的执行计划，就把上面sql的0改为2
含顺序的
select * from table(xplan.display_cursor('v$sql.sql_id',0,'advanced'));
不过要先创建xplan包，再执行
SQL> CREATE PUBLIC SYNONYM XPLAN FOR SYS.XPLAN;
SQL> grant execute on sys.xplan to public;
查询Rman的配置信息
SELECT NAME,VALUE FROM V$RMAN_CONFIGURATION;
查询Rman备份集详细信息（未过期的，过期并已删除的查不到）
SELECT B.RECID BackupSet_ID,
A.SET_STAMP,
DECODE (B.INCREMENTAL_LEVEL,
'', DECODE (BACKUP_TYPE, 'L', 'Archivelog', 'Full'),
1, 'Incr-1级',
0, 'Incr-0级',
B.INCREMENTAL_LEVEL)
"Type LV",
B.CONTROLFILE_INCLUDED "包含CTL",
DECODE (A.STATUS,
'A', 'AVAILABLE',
'D', 'DELETED',
'X', 'EXPIRED',
'ERROR')
"STATUS",
A.DEVICE_TYPE "Device Type",
A.START_TIME "Start Time",
A.COMPLETION_TIME "Completion Time",
A.ELAPSED_SECONDS "Elapsed Seconds",
A.BYTES/1024/1024/1024 "Size(G)",
A.COMPRESSED,
A.TAG "Tag",
A.HANDLE "Path"
FROM GV$BACKUP_PIECE A, GV$BACKUP_SET B
WHERE A.SET_STAMP = B.SET_STAMP AND A.DELETED = 'NO'
ORDER BY A.COMPLETION_TIME DESC;
查询Rman备份进度
SELECT SID, SERIAL#, opname,ROUND(SOFAR/TOTALWORK*100)||'%' "%_COMPLETE",
TRUNC(elapsed_seconds/60) || ':' || MOD(elapsed_seconds,60) elapsed,
TRUNC(time_remaining/60) || ':' || MOD(time_remaining,60) remaining,
CONTEXT,target,SOFAR, TOTALWORK
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK;
查询执行过全表扫描的sql语句的SQL_ID和sql_fulltext
select s.sid,s.serial#,s.inst_id,s.sql_id,s.username,s.target,s.ELAPSED_SECONDS,s.START_TIME,s.LAST_UPDATE_TIME,v.sql_fulltext
from gv$session_longops s,gv$sql v
where s.OPNAME = 'Table Scan'
and s.SQL_PLAN_OPERATION = 'TABLE ACCESS'
and s.SQL_PLAN_OPTIONS = 'FULL'
and s.sql_id=v.sql_id
order by s.LAST_UPDATE_TIME desc
查询死事务需要多长的回滚时间
X$KTUXE：[K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table)
X$KTUXE表的一个重要功能是，可以获得无法通过v$transaction来观察的死事务信息，当一个数据库发生异常中断，或者进行延迟事务恢复时，数据库启动后，无法通过V$TRANSACTION来观察事务信息，但是X$KTUXE可以帮助我们获得这些信息。该表中的KTUXECFL代表了事务的Flag标记，通过这个标记可以找到那些Dead事务：
SQL> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;
KTUXECFL COUNT(*)
------------------------ ----------
DEAD 1
NONE 2393
KTUXESIZ用来记录事务使用的回滚段块数，可以通过观察这个字段来评估恢复进度,例如如下事务回滚经过测算需要大约3小时：：
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL ='DEAD';
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
FFFFFFFF7D07B91C 10 39 2567412 1086075
SQL> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ from x$ktuxe where KTUXECFL ='DEAD';
ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------
FFFFFFFF7D07B91C 10 39 2567412 1086067
SQL> declare
l_start number;
l_end number;
begin
select ktuxesiz into l_start from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39;
dbms_lock.sleep(60);
select ktuxesiz into l_end from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39;
dbms_output.put_line('time_H:'|| round(l_end/(l_start -l_end)/60,2));
end;
/
time_H:3
把XXX用户下面的某些YYY表赋权给user,XXX\YYY要大写
set serveroutput on
--XXX要大写
declare tablename varchar2(200);
begin
for x IN (SELECT * FROM dba_tables where owner='XXX' and table_name like '%YYY%') loop
tablename:=x.table_name;
dbms_output.put_line('GRANT SELECT ON XXX.'||tablename||' to user');
EXECUTE IMMEDIATE 'GRANT SELECT ON XXX.'||tablename||' TO user';
end loop;
end;
Oracle查出一个用户具有的所有系统权限和对象权限
系统权限（和用户自己查询select * from session_privs的结果一致）
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = '用户名'
UNION ALL
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN
(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '用户名');
对象权限（和用户自己查询select * FROM TABLE_PRIVILEGES where GRANTEE='当前用户'的结果一致）
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = '用户名'
UNION ALL
SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE IN
(SELECT GRANTED_ROLE FROM DBA_ROLE_PRIVS WHERE GRANTEE = '用户名');
查询某个用户拥有的角色
select * from dba_role_privs where GRANTEE='用户名';
查询拥有DBA角色权限的用户
select * from dba_role_privs where GRANTED_ROLE='DBA';
查询某个角色拥有的系统权限
select * from ROLE_SYS_PRIVS where role='角色名'
清除某个SQL的执行计划
Exec DBMS_SHARED_POOL.PURGE('v$sqlarea.ADDRESS,v$sqlarea.HASH_VALUE','c')
查询密码是否有过期限制，默认是180天，一般修改为unlimited
select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME like 'PASSWORD%';
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED
查询和修改隐含参数（必须在sysdba权限下操作）
select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
from x$ksppi a, x$ksppcv b
where a.indx = b.indx and a.ksppinm like '%_small_table_threshold%'
alter system set "_small_table_threshold"=value scope=both sid='*';
不加sid则说明在默认在RAC的所有实例中修改
需要注意的是一定要加上双引号, 另外引号内不能有空格, 只能包含参数的名字
评估PGA该设置多少
select PGA_TARGET_FOR_ESTIMATE from (select * from V$PGA_TARGET_ADVICE where ESTD_OVERALLOC_COUNT=0 order by 1) where rownum=1;
评估SGA该设置多少
select SGA_SIZE from (select * from V$SGA_TARGET_ADVICE where ESTD_DB_TIME_FACTOR=1 order by 1) where rownum=1;
查看shared pool还剩多少
select * from v$sgastat where name='free memory' and pool='shared pool';
统计所有表的容量大小(含分区字段、LOB字段)
一般先执行select distinct SEGMENT_TYPE from dba_segments where owner<>'SYS' and tablespace_name<>'SYSAUX'查看到所有的segment_type
一般如下SQL就足够了
SELECT
owner,table_name, TRUNC(sum(bytes)/1024/1024) Meg
FROM
(SELECT segment_name table_name, owner, bytes
FROM dba_segments
WHERE segment_type = 'TABLE'
UNION ALL
SELECT s.segment_name table_name, pt.owner, s.bytes
FROM dba_segments s, dba_part_tables pt
WHERE s.segment_name = pt.table_name
AND s.owner = pt.owner
AND s.segment_type = 'TABLE PARTITION'
UNION ALL
SELECT i.table_name, i.owner, s.bytes
FROM dba_indexes i, dba_segments s
WHERE s.segment_name = i.index_name
AND s.owner = i.owner
AND s.segment_type = 'INDEX'
UNION ALL
SELECT pi.table_name, pi.owner, s.bytes
FROM dba_part_indexes pi, dba_segments s
WHERE s.segment_name = pi.index_name
AND s.owner = pi.owner
AND s.segment_type = 'INDEX PARTITION'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOBSEGMENT'
UNION ALL
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.index_name
AND s.owner = l.owner
AND s.segment_type = 'LOBINDEX'
union all
SELECT l.table_name, l.owner, s.bytes
FROM dba_lobs l, dba_segments s
WHERE s.segment_name = l.segment_name
AND s.owner = l.owner
AND s.segment_type = 'LOB PARTITION'
)
GROUP BY owner,table_name
HAVING SUM(bytes)/1024/1024 > 10
ORDER BY SUM(bytes) desc
查看当前会话的SID
select * from V$MYSTAT where rownum<2
查询某个SID的某个统计信息，比如consistent gets一致性读
select A.SID,A.STATISTIC#,A.VALUE SID_VALUE,B.NAME,B.VALUE ALL_SID_VALUE from V$SESSTAT A ,V$SYSSTAT B where A.STATISTIC#=B.STATISTIC#
and A.SID=1187 and B.NAME='consistent gets'
V$SYSSTAT统计整个DB的统计信息，V$SYSSTAT已经取代了V$STATNAME，并且多了VALUE这一列
V$SESSTAT统计每个用户的统计信息
查询某个SID的某个等待事件的信息，比如log file sync
select A.SID,A.EVENT,C.NAME,C.PARAMETER1,C.PARAMETER2,C.PARAMETER3,
A.TIME_WAITED SID_TIMEWAITED,B.TIME_WAITED ALL_SID_TIMEWAITED,A.TOTAL_WAITS SID_TOTALWAITS,B.TOTAL_WAITS ALL_SID_TOTALWAITS
from V$SESSION_EVENT A ,V$SYSTEM_EVENT B,V$EVENT_NAME C where A.EVENT=B.EVENT and A.EVENT=C.NAME and A.SID=1 and C.NAME='log file sync'
V$SESSION_EVENT描述每个用户的等待事件信息
V$SYSTEM_EVENT描述整个DB等待事件信息
V$EVENT_NAME描述等待事件信本身的信息(比如V$ACTIVE_SESSION_HISTORY的P1TEXT、P2TEXT、P2TEXT匹配V$EVENT_NAME的PARAMETER1、PARAMETER2、PARAMETER3)
RAC跨节点杀会话
alter system kill session 'SID,serial#,@1' --杀掉1节点的进程
alter system kill session 'SID,serial#,@2' --杀掉2节点的进程
Truncate分区的SQL
ALTER TABLE table_name TRUNCATE PARTITION p1 DROP STORAGE UPDATE GLOBAL INDEXES;
Drop分区的SQL
ALTER TABLE table_name DROP PARTITION p1 UPDATE GLOBAL INDEXES;
DATAGUARD主备延迟多少时间的查询方法
备库sqlplus>select value from v$dataguard_stats where name='apply lag'
或
备库sqlplus>select ceil((sysdate-next_time)*24*60) "M" from v$archived_log where applied='YES' AND SEQUENCE#=(SELECT MAX(SEQUENCE#) FROM V$ARCHIVED_LOG WHERE applied='YES');
查看某个包或存储过程是否正在被调用,如果如下有结果，则此时不能编译，否则会锁住
select * from V$DB_OBJECT_CACHE where pin>0 and name=upper('XX')
文章标题：DBA日常维护SQL脚本_自己编写的
网站URL：http://kswsj.cn/article/jggiej.html
