Oracle日常维护脚本

1.正常停库流程

<code> </code><code>ps -ef|grep LOCAL=NO|cut -c 9-15|xargs kill -9 </code>

<code> </code><code>shutdown immediate;</code>

2.备份数据库

<code> </code><code>backup database format </code><code>'/home/orarch_ccmpdb1/backup2/full_%d_%s_%p_%u.%T'</code><code>; </code>

<code> </code><code>backup current controlfile format </code><code>'/home/orarch_ccmpdb1/backup2/controlfile_%d_%s_%p_%u.%T'</code><code>;</code>

3.建立连接

<code> </code><code>ln -s /dev/ccmpvg01/rccmpv01l3111 /home/db/oracle/oradata/cmpdb/rtbs_data2_05_10g </code>

<code> </code><code>ln -s /dev/ccmpvg01/rccmpv01l3112 /home/db/oracle/oradata/cmpdb/rtbs_data2_06_10g</code>

4.增加表空间的空间

<code> </code><code>alter tablespace tbs_data2 add datafile </code>

<code> </code><code>'/home/db/oracle/oradata/cmpdb/rtbs_data2_05_10g'</code> <code>size 10200m, </code>

<code> </code><code>'/home/db/oracle/oradata/cmpdb/rtbs_data2_06_10g'</code> <code>size 10200m;</code>

5.查看主分区表和子分区表

<code> </code><code>col partition_keyname </code><code>for</code> <code>a20 </code>

<code> </code><code>col sub_partition_keyname </code><code>for</code> <code>a20 </code>

<code> </code><code>select a.*,b.COLUMN_NAME sub_partition_keyname from </code>

<code> </code><code>(select t1.OWNER, </code>

<code> </code><code>t1.TABLE_NAME, </code>

<code> </code><code>t1.PARTITIONING_TYPE, </code>

<code> </code><code>t1.SUBPARTITIONING_TYPE, </code>

<code> </code><code>t1.PARTITION_COUNT, </code>

<code> </code><code>t2.column_name partition_keyname </code>

<code> </code><code>from dba_part_tables t1,dba_part_key_columns t2 </code>

<code> </code><code>where t1.owner = t2.owner </code>

<code> </code><code>and t1.table_name = t2.name </code>

<code> </code><code>and t1.OWNER = </code><code>'BILL'</code>

<code> </code><code>and t1.TABLE_NAME = </code><code>'HA_CREDIT_CTRL'</code>

<code> </code><code>) a,dba_subpart_key_columns b </code>

<code> </code><code>where a.owner = b.owner(+) </code>

<code> </code><code>and a.table_name = b.name(+);</code>

6.查看子分区信息

<code> </code><code>select TABLE_OWNER, </code>

<code> </code><code>TABLE_NAME, </code>

<code> </code><code>PARTITION_NAME, </code>

<code> </code><code>SUBPARTITION_NAME, </code>

<code> </code><code>SUBPARTITION_POSITION </code>

<code> </code><code>from DBA_TAB_SUBPARTITIONS </code>

<code> </code><code>where TABLE_OWNER = </code><code>'CASHBILL_TEST'</code>

<code> </code><code>and TABLE_NAME = </code><code>'BALANCE_PAYOUT'</code>

<code> </code><code>order by PARTITION_NAME,SUBPARTITION_POSITION; </code>

<code> </code><code>select dbms_metadata.get_ddl(</code><code>'TABLE'</code><code>,</code><code>'ACCT_ITEM_1080'</code><code>,</code><code>'CASH_BILL'</code><code>) from dual;</code>

7.段级别统计信息

<code> </code><code>select owner,object_name,statistic_name,value </code>

<code> </code><code>from v$segment_statistics </code>

<code> </code><code>where owner=</code><code>'SYS'</code> <code>and object_name = </code><code>'I_FILE2'</code>

8.看最严重的前20个等待事件

<code> </code><code>set lines 200 </code>

<code> </code><code>set pages 200 </code>

<code> </code><code>col parameter1 </code><code>for</code> <code>a30 </code>

<code> </code><code>col parameter2 </code><code>for</code> <code>a30 </code>

<code> </code><code>col parameter3 </code><code>for</code> <code>a30 </code>

<code> </code><code>col value </code><code>for</code> <code>999999999999999999 </code>

<code> </code><code>select * from (select event,wait_time,SECONDS_IN_WAIT from v$session_wait </code>

<code> </code><code>where wait_class not in (</code><code>'Idle'</code><code>,</code><code>'Network'</code><code>) order by SECONDS_IN_WAIT desc) </code>

<code> </code><code>where rownum <= 20;</code>

9.看最严重的等待事件

<code> </code><code>select event,count(*) from v$session </code>

<code> </code><code>where wait_class not in (</code><code>'Idle'</code><code>,</code><code>'Network'</code><code>) </code>

<code> </code><code>group by event </code>

<code> </code><code>order by 2 desc;</code>

10.看当前连接会话信息

<code> </code><code>select username,program,status,count(*) from v$session </code>

<code> </code><code>where username is not null group by username,program,status order by 3; </code>

<code> </code><code>select prev_sql_id,count(*) from v$session </code>

<code> </code><code>where program is null group by prev_sql_id order by 2</code>

11.最严重的前10个latch

<code> </code><code>select * from (select addr,name,</code><code>gets</code><code>,misses,sleeps,SPIN_GETS,WAIT_TIME </code>

<code> </code><code>from v$latch order by misses desc) where rownum <= 10 </code>

<code> </code><code>--latch: row cache objects </code>

<code> </code><code>select distinct s.kqrstcln latch#, </code>

<code> </code><code>r.cache#, </code>

<code> </code><code>r.parameter name, </code>

<code> </code><code>r.subordinate#, </code>

<code> </code><code>from v$rowcache r,x$kqrst s </code>

<code> </code><code>where r.cache# = s.kqrstcid </code>

<code> </code><code>order by 1,4,5;</code>

<code> </code><code>select * from (select cache#,type,SUBORDINATE#,parameter,count, </code>

<code> </code><code>USAGE,GETS,GETMISSES from v$rowcache order by GETMISSES desc) </code>

<code> </code><code>where rownum <= 10; </code>

<code> </code><code>select addr,latch#,child#,level#,name,</code><code>gets</code> <code>from v$latch_children </code>

<code> </code><code>where name = </code><code>'row cache objects'</code> <code>and </code><code>gets</code> <code><>0 order by </code><code>gets</code><code>;</code>

--latch cbc

--个个查询

<code> </code><code>select * from (select addr from v$latch_children </code>

<code> </code><code>where name = </code><code>'cache buffers chains'</code> <code>order by misses) </code>

<code> </code><code>select </code><code>/*+ rule */</code> <code>owner,object_name from dba_objects </code>

<code> </code><code>where object_id in </code>

<code> </code><code>(select </code><code>/*+ rule */</code> <code>distinct obj from x$bh </code>

<code> </code><code>where hladdr = </code><code>'C000000BC6813AB8'</code><code>);</code>

12.查询前十个cbc latch最严重对应的对象

<code> </code><code>select </code><code>/*+ rule */</code> <code>owner,object_name from dba_objects where object_id in </code>

<code> </code><code>select </code><code>/*+ rule */</code> <code>distinct obj from x$bh where hladdr in </code>

<code> </code><code>(select </code><code>/*+ rule */</code> <code>* from (select </code><code>/*+ rule */</code> <code>addr </code>

<code> </code><code>from v$latch_children </code>

<code> </code><code>where name = </code><code>'cache buffers chains'</code>

<code> </code><code>order by </code><code>gets</code> <code>desc) where rownum <= 10) </code>

13.检查分区表的创建与否

<code> </code><code>order by PARTITION_NAME,SUBPARTITION_POSITION;</code>

14.表空间自动扩展性

<code> </code><code>SELECT T.TABLESPACE_NAME,D.FILE_NAME,D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS </code>

<code> </code><code>FROM DBA_TABLESPACES T,DBA_DATA_FILES D </code>

<code> </code><code>WHERE T. TABLESPACE_NAME =D. TABLESPACE_NAME </code>

<code> </code><code>ORDER BY TABLESPACE_NAME,FILE_NAME;</code>

15.全表扫描 ,注意修改 OBJECT_OWNER

<code> </code><code>select distinct t.sql_text from v$sqlarea t, v$sql_plan p </code>

<code> </code><code>where t.hash_value=p.hash_value </code>

<code> </code><code>and t.PLAN_HASH_VALUE=p.PLAN_HASH_VALUE </code>

<code> </code><code>and p.operation=</code><code>'TABLE ACCESS'</code>

<code> </code><code>and p.options=</code><code>'FULL'</code>

<code> </code><code>and p.OBJECT_OWNER =</code><code>'TBMS'</code><code>;</code>

16.全索引扫描, 注意修改 OBJECT_OWNER

<code> </code><code>and p.operation=</code><code>'INDEX'</code>

<code> </code><code>and p.options=</code><code>'FULL SCAN'</code>

17.查看归档错误:

<code> </code><code>select dest_id,error from v$archive_dest where dest_id=1;</code>

18.数据字典命中率

<code> </code><code>column parameter format a21 </code>

<code> </code><code>column pct_succ_gets format 999.9 </code>

<code> </code><code>column updates format 999,999,999 </code>

<code> </code><code>SELECT parameter </code>

<code> </code><code>, sum(getmisses) </code>

<code> </code><code>, 100*sum(</code><code>gets</code> <code>- getmisses) / sum(</code><code>gets</code><code>) pct_succ_gets </code>

<code> </code><code>, sum(modifications) updates </code>

<code> </code><code>FROM V$ROWCACHE WHERE </code><code>gets</code> <code>> 0 </code>

<code> </code><code>GROUP BY parameter;</code>

19.查看归档错误

<code> </code><code>select dest_id,error from v$archive_dest where dest_id=1;</code>

20.是否有行迁移

--收集对象的统计信息:

<code>analyze table t compute statistics;</code>

<code> </code><code>select TABLE_NAME,BLOCKS,EMPTY_BLOCKS,NUM_ROWS,AVG_ROW_LEN,CHAIN_CNT </code>

<code> </code><code>from user_tables where table_name=</code><code>'T'</code><code>;</code>

21.取出全表扫描的表

<code> </code><code>select sql_text from v$sqltext where sql_id in </code>

<code> </code><code>(select sql_id from v$sql_plan </code>

<code> </code><code>where operation like </code><code>'%TABLE ACCESS%'</code>

<code> </code><code>and options like </code><code>'%FULL%'</code><code>) </code>

<code> </code><code>where sql_text like </code><code>'%EMP%'</code>

22.全表扫描统计项

<code> </code><code>select name,value from v$sysstat where name like </code><code>'table scan%'</code><code>;</code>

23.哪个用户哪个表作了全表扫描

<code> </code><code>select OPERATION,object_owner,OPTIONS,OBJECT_NAME from </code>

<code> </code><code>where options=</code><code>'FULL'</code>

<code> </code><code>and OPERATION=</code><code>'TABLE ACCESS'</code>

<code> </code><code>and object_owner=</code><code>'SCOTT'</code><code>;</code>

24.哪个用户下有多少张表作了全表扫描

<code> </code><code>select ss.username||</code><code>'('</code><code>||se.sid||</code><code>') '</code> <code>"User Process"</code><code>, </code>

<code> </code><code>sum(decode(name,</code><code>'table scans (short tables)'</code><code>,value)) </code><code>"Short Scans"</code><code>, </code>

<code> </code><code>sum(decode(name,</code><code>'table scans (long tables)'</code><code>, value)) </code><code>"Long Scans"</code><code>, </code>

<code> </code><code>sum(decode(name,</code><code>'table scan rows gotten'</code><code>,value)) </code><code>"Rows Retreived"</code>

<code> </code><code>from v$session ss, </code>

<code> </code><code>v$sesstat se, </code>

<code> </code><code>v$statname sn </code>

<code> </code><code>where se.statistic# = sn.statistic# </code>

<code> </code><code>and (name like </code><code>'%table scans (short tables)%'</code>

<code> </code><code>or name like </code><code>'%table scans (long tables)%'</code>

<code> </code><code>or name like </code><code>'%table scan rows gotten%'</code><code>) </code>

<code> </code><code>and ss.username is not null </code>

<code> </code><code>group by ss.username||</code><code>'('</code><code>||se.sid||</code><code>') '</code>

25.增加全表扫描时I/O的吞吐量

<code> </code><code>db_file_multiblock_read_count=1~128</code>

26.使用并行处理提高全表扫描效率:

<code> </code><code>select </code><code>/*+ parallel (ob ,16)*/</code> <code>count(*) from OB;</code>

27.条带化堆表数据:

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users01.dbf'</code><code>); </code>

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users02.dbf'</code><code>); </code>

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users03.dbf'</code><code>); </code>

<code> </code><code>alter table ob allocate extent (size 5m datafile </code><code>'/u01/app/oracle/oradata/madrid/users04.dbf'</code><code>);</code>

28.发生cbc latch争用时，如可查是哪个sql语句造成的？

<code> </code><code>select v.SQL_HASH_VALUE, v.SQL_ADDRESS, v.INST_ID, v.EVENT </code>

<code> </code><code>from gv$session v </code>

<code> </code><code>where v.EVENT = </code><code>'cache buffer chains'</code><code>;</code>

<code> </code><code>select a.INST_ID, a.SQL_TEXT from gv$sqltext a </code>

<code> </code><code>where a.HASH_VALUE = </code><code>'&HASH_VALUE'</code>

<code> </code><code>and a.ADDRESS = </code><code>'&ADDRESS'</code>

<code> </code><code>ORDER BY address, hash_value, piece;</code>

29.数据字典命中率

30.估算 db_cache 放大或减小后对 I/o 的影响

<code> </code><code>COLUMN size_for_estimate FORMAT 999,999,999,999 heading </code><code>'Cache Size (MB)'</code>

<code> </code><code>COLUMN buffers_for_estimate FORMAT 999,999,999 heading </code><code>'Buffers'</code>

<code> </code><code>COLUMN estd_physical_read_factor FORMAT 999.90 heading </code><code>'Estd Phys|Read Factor'</code>

<code> </code><code>COLUMN estd_physical_reads FORMAT 999,999,999 heading </code><code>'Estd Phys| Reads'</code>

<code> </code><code>SELECT size_for_estimate, buffers_for_estimate, estd_physical_read_factor, </code>

<code> </code><code>estd_physical_reads </code>

<code> </code><code>FROM V$DB_CACHE_ADVICE </code>

<code> </code><code>WHERE name = </code><code>'DEFAULT'</code>

<code> </code><code>AND block_size = (SELECT value FROM V$PARAMETER WHERE name = </code><code>'db_block_size'</code><code>) </code>

<code> </code><code>AND advice_status = </code><code>'ON'</code><code>;</code>

本文转自张冲andy 博客园博客，原文链接： http://www.cnblogs.com/andy6/p/5840657.html ，如需转载请自行联系原作者

Oracle日常维护脚本

继续阅读

报错：'mysql' 不是内部或外部命令，也不是可运行的程序或批处理文件。

Linxu常用命令技巧汇总

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艰难安装LDAP,SSL认证

《Linux命令行与Shell脚本编程大全第2版.布卢姆》pdf

MySQL的4种隔离级别？出现问题

XX系统实施过程问题总结

无组件上传图片到数据库中，最完整解决方案

【MySQL数据库】数据库索引事务1.索引2.事务

neo4j之cypher使用文档

NOSQL安全攻击

mybatis_入门程序Mybatis入门

登录plsql 报错 the account is locked --用户被锁

sqlServer根据经纬查距离

SequoiaDB巨杉数据库C++驱动概述

Oracle 批量查询传入List 返回List