oracle dba 日常维护 sql 脚本大全（收藏版） -m6米乐安卓版下载

把上面第二个sql结果的p1、p2值代入上述sql的file_id、block_id

通过awr的top sql或v$sql.sql_text查看是否有该对象的语句，检查该语句的执行计划就可以查出问题所在。

查询当前正在消耗临时空间的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'

方法二：

select v$sql.sql_id,v$sql.sql_fulltext,swa.tempseg_size/1024/1024 tempseg_m, swa.* 
 from v$sql_workarea_active swa,v$sql where swa.sql_id=v$sql.sql_id and swa.number_passes>0 

查询因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以后推荐第二种）

注意：任何一条执行过的语句不管执行了几次在vsql中都只有一条记录，vsql中会记录执行了几次。两条一模一样的语句但是在不同的schema下执行的两种结果，如select * from t1.test在sye、system下执行则vsql只有一条记录(谁先执行则parsing_schema_name显示谁)。如在sys和system都执行select * from test则vsql中有两条记录，两条记录的child_number和parsing_schema_name不一样。

同一个用户下执行一样的语句如果大小写不一样或加了hint的话则会出现多个vsql记录，说明vsql对应的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 *from (
  select maxqueryid,
 round(sum(undoblks )*8/1024) consumed_size_mb
 from v$undostat    group by maxqueryid order by  consumed_size_mb desc
 ) where rownum<10; 

估计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的当前活动会话是哪些

方法一

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 b.name = 'undo change vector size' 
and a.value>0 
order by a.value desc 

方法二

select s.sid,s.serial#,s.sql_id,v.usn,r.status, v.rssize/1024/1024 mb
from dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s
where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr
order by 6 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但是这个会话还在vsession中，则vsession.paddr还在，所以可以匹配到vprocess.addr，所以process进程还在；当kill过程执行完毕，则这个会话即不在vsession中

会话不在的

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_hh24: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 hh24: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_ind_columns)

查询一个awr周期内的平均session数、os平均负载、平均db time、平均每秒多少事务

select to_char(max(begin_time),'yyyy-mm-dd hh24:mi')||to_char(max(end_time),'--hh24: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))246060))是代表每个snap周期内的总秒数，oracle 两个时间相减默认的是天数,2460*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表示vsql.child_number为0，如果一个sql_id在vsql中有多行说明有多个child_number，要看哪儿child_number的执行计划，就写哪个的值，比如要看child_number为2的执行计划，就把上面sql的0改为2 。

官方文档对display_cursor这个函数的说明里面没有advanced这个参数值，只有basic、typical、all这几个，不过实践中发现advanced这个参数值显示的内容比这几个参数值显示的都多。

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)

xktuxe表的一个重要功能是，可以获得无法通过vtransaction来观察的死事务信息，当一个数据库发生异常中断，或者进行延迟事务恢复时，数据库启动后，无法通过vtransaction来观察事务信息，但是xktuxe可以帮助我们获得这些信息。该表中的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' 

vsysstat统计整个db的统计信息，vsysstat已经取代了vstatname，并且多了value这一列 vsesstat统计每个用户的统计信息

查询某个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'  

vsession_event描述每个用户的等待事件信息 vsystem_event描述整个db等待事件信息
vevent_name描述等待事件信本身的信息(比如vactive_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')

查询数据库打补丁的记录

select * from dba_registry_history;

查询某表的索引字段的distinct行数和clustering_factor信息

select a.table_name,a.index_name,b.column_name,a.blevel,a.distinct_keys,a.clustering_factor,a.num_rows,trunc((a.distinct_keys/a.num_rows),2)*100||'%' "distinct%",trunc((a.clustering_factor/a.num_rows),2)*100||'%' "clustering_factor%"
 from dba_ind_statistics a,dba_ind_columns b 
where a.table_name='xx' and a.index_name=b.index_name order by 5 desc

查询某表的所有字段的distinct行数

select a.table_name,b.num_rows,a.column_name,a.data_type,a.data_length,a.num_distinct,trunc((a.num_distinct/b.num_rows),2)*100||'%' 
from dba_tab_cols a,dba_tables b where a.table_name='xx' and a.table_name=b.table_name order by 6 desc

查询5g以上空闲空间可以进行收缩的数据文件

select 'alter database datafile ''' || a.file_name || ''' resize ' ||
round(a.filesize - (a.filesize - c.hwmsize) * 0.8) || 'm;',
a.filesize || 'm' as "数据文件的总大小",
c.hwmsize || 'm' as "数据文件的实用大小"
from (select file_id, file_name, round(bytes / 1024 / 1024) as filesize
from dba_data_files) a,
(select file_id, round(max(block_id) * 8 / 1024) as hwmsize
from dba_extents group by file_id) c
where a.file_id = c.file_id
and a.filesize - c.hwmsize > 5000;

原文：http://blog.itpub.net/30126024/viewspace-2057474/ 感谢原作者，如有侵权，私信我立即删除。