Script: Oracle Table/Tablespace Growth Prediction(预测表/表空间增长)

Some scripts for showing the growth of tablespaces over time periods. to show the starting tablespace size and the end tablespace size, computing the tablespace growth for the time period.

,

hugepage on linux

内存管理是一个非常复杂的结构,在操作系统中用Virtual memory System进行内存管理,pagetable是中记录了内存的虚拟地址和物理地址的映射关系,当一个进程真正的访问数据是首先访问的是page table然后转向真实的地址,cpu中有固定的大小来缓存部份的pagetables,这块区域就是translation lookaside buffer(TLB).

Check goldengate replicate Availability (检查OGG REPLICATE)

通过检查replicate进程确认OGG REPLICATE进程是否存活shell

about get bind_data column in v$sql(绑定变量值)

今天查看AWR时,发现有个sql占用的cpu很高而且也并不复杂,top 5 event出现了cpu,read by other sessions,而且sql中再次看到的hint index,随后从生产环境中拿到绑定变量参数值进行本地调试,发现hint index 选择了错误的索引一致读为93万,去掉hint立即降低到了36,随后服务器压力将了下来

How To send email From a shell on linux

usually,we do not want to setup an email serve,but just want to send an email from a Linux Shell script easily when got any alert or exptions.then let’s know what happend with the event on the Host,then to solve this problem.

,

log file switch (checkpoint incomplete) event

The log file switch – checkpoint incomplete event tells you that Oracle wants to reuse a redo log file, but the current checkpoint position is still in that log file. This results in redo generation being stopped until the log switch is done.

About Network stats tools on linux

Bing determines bandwidth on a point-to-point link by sending ICMP ECHO_REQUEST packets and measuring their roundtrip times for different packet sizes on each end of the link

table function (函数返回结果集)

经常见到select * from table(function(args))的查询方法,对pl/sql 强大功能赞不绝口,比如PIPELINED 管道函数
例子

Session-Level init Parameters or Environment(会话级参数环境)

sometimes we use ALTER SESSION statement to set or modify any of the conditions or parameters that affect your connection to the database. The statement stays in effect until you disconnect from the database. we can query current system Initialization parameter from v$parameter view,but where are able to confirm that the session-level parameter modification happened?

ASM Rebalance and ASM_POWER_LIMIT

ASM is the ability to add and remove disks/luns from a diskgroup with no down time and an automatic rebalancing of the data on the luns. This creates all kinds of possibilities.

Invisible Indexes in oracle11g

11gr1起提供了Invisible Indexes,作用就是通过alter index xx invisible,对CBO隐身,我突然想到之前的一个情况,就是开发的在应用中滥用hint index,当时为了让hint index 无效,当时是选择index rename,现在看来如果是11g可以用invisible

External Table On GoldenGate Discard Files

goldengate 做为数据同步的利器,越来越普遍,往往会因为某些原因replicat进程在应用trail文件时会失败,进程要么Abending也可以也忽略记录到 Discard Files中,默认在OGG_HOME/dirrpt/ *.dsc,可以用文本工具查看内容,当然也可以更方便的用pl/sql function 读取文件,做为一张外部表(external table)查询

Script: 通过AWR信息查询db 中segments 的增长信息

awr 中有很多wr开头的表收集的数据非常有用,通过dba_hist开头的视图可以很直观的查看,也可以手动查询一些详细或定制自己所要的信息

Estimate of the duration of a rollback operation(估算rollback事务回滚剩余时间)

when a long, running transaction has been rolled back, How Much Longer completion time of the rollback process? The questions are justified, because the transaction holds the locks and normal processing often suffers as the rollback progresses.

Why Disk space has been made available archive process do not Restart.ORA-19815 ORA-16014

We use to import a dump file into a database today. imp […]

Script: oracle 删除用户(schema)下的所有对象

有时需要清除schema里的所有对象,但又没有dba权限,不可以drop user xxx cascade; re-create user;可以写个plsql,循环删除所有对象,注意有些对象存在依赖关系,如外键、index 和table..,这里建个procedure,方便在开发库一堆有schema中运行。

read/write mode open no longer be physical standby db

如果primary db 丢失或不再需要的physical standby ,想把这些库(standby)利用起来,logical standby 本来就可以write open,11g ADG也可以APPLY LOG时open,但是read only,下面演示如果把off-line standby 转为primary,open read/write mode.

Script: AWR snap sessions,load profile, SCN or Transactions per second

自定义生成一些报表,曲线数据从AWR 数据表。

About like 5% selectivity引起的不完美执行计划

CBO优化器的内部有两个重要的标准:selectivity and cardinality,cardinality 就CBO在处理完后返回的行数,selectivity是返回数据范围百分比,cardinality = (number of input rows)* selectivity,所以selectivity 的直接影响了CBO表访问路径

rlwrap 独立的历史操作记录

我是习惯在sqlplus中做一些操作,在linux中安装rlwrap 支持方向键,但如果多个用户在同一台机器上登录,每个人常用的命令不同,方向键查找起来很麻烦,so.那就把我们经常用的操作分类或以用户分类,设立独立的历史操作记录log