oracle 几个脚本记录

--*****************查看表空间大小*****************


SELECT UPPER(F.TABLESPACE_NAME) "表空间名",D.TOT_GROOTTE_MB "表空间大小(M)",
 D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
 TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
 F.TOTAL_BYTES "空闲空间(M)",
 F.MAX_BYTES "最大块(M)" FROM (SELECT TABLESPACE_NAME,ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
 ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
 (SELECT DD.TABLESPACE_NAME,ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 
 FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D 
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 1 ;




--如果你不愿意使用awr或ash视图其实有一个最方便的方法，给你一个sql  ，这个sql也是我在工作中最常使用的sql，基本
--可以秒杀90%的awr与ash
select sql_id,event,count(*) from v$active_session_history
where sample_time > sysdate - 10/1440   --查询10分钟之内的最占用时间的sqlid 与等待事件
group by sql_id,event
order by count(*) desc






--*****************缓冲区命中率*****************
SELECT a.VALUE + b.VALUE logical_reads, 
 c.VALUE phys_reads, 
 round(100*(1-c.value/(a.value+b.value)),4) 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 sum(pinhits)/sum(pins)*100 from v$librarycache;








--****************查看SGA,PGA 共享池大小********************
select name,total,round(total-free,2) used, round(free,2) free,round((total-free)/total*100,2) pctused from 
 (select 'SGA' name,(select sum(value/1024/1024) from v$sga) total,
 (select sum(bytes/1024/1024) from v$sgastat where name='free memory')free from dual)
 union
 select name,total,round(used,2)used,round(total-used,2)free,round(used/total*100,2)pctused from (
 select 'PGA' name,(select value/1024/1024 total from v$pgastat where name='aggregate PGA target parameter')total,
 (select value/1024/1024 used from v$pgastat where name='total PGA allocated')used from dual)
  union
 select name,round(total,2) total,round((total-free),2) used,round(free,2) free,round((total-free)/total*100,2) pctused from (
 select 'Shared pool' name,(select sum(bytes/1024/1024) from v$sgastat where pool='shared pool')total,
 (select bytes/1024/1024 from v$sgastat where name='free memory' and pool='shared pool') free from dual)
 union
 select name,round(total,2)total,round(total-free,2) used,round(free,2) free,round((total-free)/total,2) pctused from (
 select 'Default pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
 (select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p 
 where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
 union
  select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
 select 'KEEP pool' name,(select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
 (select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p 
 where a.set_id=p.LO_SETID and p.name='KEEP' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)
union
 select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from (
 select 'RECYCLE pool' name,( select a.cnum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) total,
 (select a.anum_repl*(select value from v$parameter where name='db_block_size')/1024/1024 free from x$kcbwds a, v$buffer_pool p 
 where a.set_id=p.LO_SETID and p.name='RECYCLE' and p.block_size=(select value from v$parameter where name='db_block_size')) free from dual)


union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 16K buffer cache' name,(select a.cnum_repl*16/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) total,
  (select a.anum_repl*16/1024 free from x$kcbwds a, v$buffer_pool p 
 where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=16384) free from dual)
union
select name,nvl(round(total,2),0)total,nvl(round(total-free,2),0) used,nvl(round(free,2),0) free,nvl(round((total-free)/total,2),0) pctused from(
select 'DEFAULT 32K buffer cache' name,(select a.cnum_repl*32/1024 total from x$kcbwds a, v$buffer_pool p
  where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) total,
  (select a.anum_repl*32/1024 free from x$kcbwds a, v$buffer_pool p 
 where a.set_id=p.LO_SETID and p.name='DEFAULT' and p.block_size=32768) free from dual)
union
select name,total,total-free used,free, (total-free)/total*100 pctused from (
select 'Java Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='java pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='java pool' and name='free memory')free from dual)
union
select name,Round(total,2),round(total-free,2) used,round(free,2) free, round((total-free)/total*100,2) pctused from (
select 'Large Pool' name,(select sum(bytes/1024/1024) total from v$sgastat where pool='large pool' group by pool)total,
( select bytes/1024/1024 free from v$sgastat where pool='large pool' and name='free memory')free from dual)

order by pctused desc;

查询某张表最近更改的数据  

select versions_starttime, versions_endtime, versions_xid,versions_operation,emp.*
        from emp versions between timestamp minvalue and maxvalue 
        order by VERSIONS_STARTTIME; 

versions_starttime是数据被修改的起始时间  versions_endtime 是数据到下一次修改后的时间.为空的话表示之后再没有被修改过.

versions_operation 是修改状态 是DML操作的首字母 

注:只能查询到回滚段类的数据.