oracle 几个脚本记录

el/2024/7/24 0:56:26
--*****************查看表空间大小*****************


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操作的首字母 

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



http://www.ngui.cc/el/5557328.html

相关文章

oracle 索引类型

索引的分类 1二叉树索引或者叫B数索引(B-tree indexes),B树索引是使用最多的一种索引.在默认情况下,我们创建的索引都是B树索引.B树索引基于二叉树原理 2.二叉树聚簇索引(B-tree Cluster indexes) 主要用于聚簇 3.哈希聚簇索引(Hash Cluster indexes) 主要用于哈希(Hash)聚簇…

oracle scope

alter system set parameter value scopespfile; 这里面的scopespfile是什么意思,下面详细解释一下: SCOPE参数值有三个选项。 SCOPEMEMORY:只改变当前实例运行,亦即初始化参数改变了只对当前实例有效,当实例重启之后,初始化参数…

Oracle闪回(FlashBack)归档 数据库

Flashback Database功能非常类似与RMAN的不完全恢复,它可以把整个数据库回退到过去的某个时点的状态,这个功能依赖于Flashback log日志。比RMAN更快速和高效,因此Flashback Database 可以看作是不完全恢复的替代技术。闪回数据库的前提是要开…

oracle 表空间自动扩展大小

select a.FILE_NAME,a.AUTOEXTENSIBLE,a.MAXBYTES,a.INCREMENT_BY from dba_data_files a; --AUTOEXTENSIBLE 是否自动扩展 --MAXBYTES 最大 --INCREMENT_BY 自动扩展块数 SQL> show parameter db_block NAME TYPE VALUE ----…

使用微软提供的Office Online实现Office文档的在线查看,编辑等功能

使用微软提供的Office Online平台只需要一个网址即可在线查看Xls,doc,PPT等文档 http://view.officeapps.live.com/op/view.aspx?src要查看的文档地址 详情:http://www.cnblogs.com/huangtailang/p/76492af9d30087d8659d8d5400d20fc7.html pdf预览(pdf.…

上传windows上的项目到github

1.注册 github 账户 2.创建repository(储藏室) 3.然后到repository的管理界面 4.下载git base 地址: https://git-for-windows.github.io/,全部下一步下一步安装 5.用cd命令进入项目所在路径 6.按照官网的提示步骤上传提交文件…

jquery实现简单的图片轮播

第一步css布局 外层层一个固定显示一张图片大小的div,内层一个div放图片,大小根据图片大小决定,如果是左右轮播,设置图片float:left jquery实现 var src$("innerDiv img:first").attr("src"); $…

解决:在点击用户退出登录时,再按浏览器的后退按钮,仍然能够看到之前的页面

网上找了一些禁用缓存的方法都不太靠谱&#xff0c;以下两个方法亲试可用 方法1&#xff1a;在框架frameset制作的管理系统中&#xff0c;在index.jsp中加入下面js代码&#xff1a; <script languagejavascript> history.go(1); </script> 方法2&#xff1a;用a…

记录一个跌宕起伏的解决bug过程

问题症状&#xff1a;测试发现在服务在运行一段时间之后就会挂掉&#xff0c;尤其是在进行安全漏洞扫描和性能测试时更加明显&#xff0c;后台没有报错日志&#xff0c;单纯的应用不再接收新的请求&#xff1b; 问题重现、分析和解决&#xff1a;刚开始很难在开发环境上复现这…

swoole学习过程中踩到的坑

坑 swManager_loop(:272): wait() failed. [2017-09-02 13:41:19 #8475.2] NOTICE Server is shutdown now. [2017-09-02 13:41:19 $8476.0] ERROR swManager_loop(:272): wait() failed. Error: Interrupted system call[4]. 当前版本是1.8.11&#xff0c;在论坛中&a…