Useful scripts for DBA
来源:互联网 发布:知乎客户端pc版 编辑:程序博客网 时间:2024/04/20 07:27
These work good on 9i .
free.sql-amount of freespace in a tablespace
select tablespace_name, round(sum(bytes)/(1024*1024),0) MB from dba_free_space group by tablespace_name
/
---------------------------------------------------------------------
compile.sql-compile uncompiled objects
set pages 10000
set lines 1500
set feedback off
set heading off
spool com.lst
select ' alter '|| decode(object_type, 'PACKAGE BODY', 'package ',object_type) || ' '||
substr(object_name,1,30) || ' COMPILE; '
from user_objects
where status != 'VALID'
and object_name <> 'DBMS_APPLICATION_INFO'
/
spool off
set feedback on
set heading on
set term on
@com.lst
----------------------------------------------------------------
sga_stats.sql-Database SGA statistics
set serveroutput on
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_cache_size';
select value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line('> SGA CACHE STATISTICS');
dbms_output.put_line('> ********************');
dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('> Redo Log space requests = '||redlog);
dbms_output.put_line('> ');
dbms_output.put_line('> INIT.ORA SETTING');
dbms_output.put_line('> ****************');
dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Bytes');
dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('> ');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: DB Cache too low! Increase the DB Cache value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
free.sql-amount of freespace in a tablespace
select tablespace_name, round(sum(bytes)/(1024*1024),0) MB from dba_free_space group by tablespace_name
/
---------------------------------------------------------------------
compile.sql-compile uncompiled objects
set pages 10000
set lines 1500
set feedback off
set heading off
spool com.lst
select ' alter '|| decode(object_type, 'PACKAGE BODY', 'package ',object_type) || ' '||
substr(object_name,1,30) || ' COMPILE; '
from user_objects
where status != 'VALID'
and object_name <> 'DBMS_APPLICATION_INFO'
/
spool off
set feedback on
set heading on
set term on
@com.lst
----------------------------------------------------------------
sga_stats.sql-Database SGA statistics
set serveroutput on
DECLARE
libcac number(10,2);
rowcac number(10,2);
bufcac number(10,2);
redlog number(10,2);
spsize number;
blkbuf number;
logbuf number;
BEGIN
select value into redlog from v$sysstat
where name = 'redo log space requests';
select 100*(sum(pins)-sum(reloads))/sum(pins) into libcac from v$librarycache;
select 100*(sum(gets)-sum(getmisses))/sum(gets) into rowcac from v$rowcache;
select 100*(cur.value + con.value - phys.value)/(cur.value + con.value) into bufcac
from v$sysstat cur,v$sysstat con,v$sysstat phys,v$statname ncu,v$statname nco,v$statname nph
where cur.statistic# = ncu.statistic#
and ncu.name = 'db block gets'
and con.statistic# = nco.statistic#
and nco.name = 'consistent gets'
and phys.statistic# = nph.statistic#
and nph.name = 'physical reads';
select value into spsize from v$parameter where name = 'shared_pool_size';
select value into blkbuf from v$parameter where name = 'db_cache_size';
select value into logbuf from v$parameter where name = 'log_buffer';
dbms_output.put_line('> SGA CACHE STATISTICS');
dbms_output.put_line('> ********************');
dbms_output.put_line('> SQL Cache Hit rate = '||libcac);
dbms_output.put_line('> Dict Cache Hit rate = '||rowcac);
dbms_output.put_line('> Buffer Cache Hit rate = '||bufcac);
dbms_output.put_line('> Redo Log space requests = '||redlog);
dbms_output.put_line('> ');
dbms_output.put_line('> INIT.ORA SETTING');
dbms_output.put_line('> ****************');
dbms_output.put_line('> Shared Pool Size = '||spsize||' Bytes');
dbms_output.put_line('> DB Block Buffer = '||blkbuf||' Bytes');
dbms_output.put_line('> Log Buffer = '||logbuf||' Bytes');
dbms_output.put_line('> ');
if
libcac < 99 then dbms_output.put_line('*** HINT: Library Cache too low! Increase the Shared Pool Size.');
END IF;
if
rowcac < 85 then dbms_output.put_line('*** HINT: Row Cache too low! Increase the Shared Pool Size.');
END IF;
if
bufcac < 90 then dbms_output.put_line('*** HINT: DB Cache too low! Increase the DB Cache value.');
END IF;
if
redlog > 100 then dbms_output.put_line('*** HINT: Log Buffer value is rather low!');
END IF;
END;
/
- Useful scripts for DBA
- useful scripts for memo
- Scripts SQL for Apps DBA
- Oracle Security Useful Scripts for Auditing
- DBA Scripts
- Useful Scripts for E-Business Suite Applications Analysts
- USEFUL ONE-LINE SCRIPTS FOR SED (Unix stream editor)
- Order Management Useful Scripts
- Purchasing Useful Scripts
- WIP Useful Scripts
- Purchasing Useful Scripts .
- WIP Useful Scripts .
- Order Management Useful Scripts
- Order Management Useful Scripts
- Top DBA Shell Scripts for Monitoring the Database
- ORACLE DBA SCRIPTS
- General Ledger Useful SQL Scripts
- Here I put nontrivial MOLPRO scripts, which I found useful for me and other:
- C#中 DateTime
- 设计模式4:Singleton Pattern (单例模式)
- Oracle Security Useful Scripts for Auditing
- Java Web中的中文处理问题:
- EJB 3事务之BEAN管理事务
- Useful scripts for DBA
- 设计模式5:Builder Pattern(建造者模式)
- 获得当前记录的上一条记录和下一条记录
- 设计模式6:Prototype Pattern (原型模式)
- asp.net页中动态加入样式表文件
- axis2使用WSDL2Java把WSDL生成JAVA类的具体代码
- ASP.NET代码后置和代码内嵌
- 面试题
- 初到北京