Scripts
lemontree1123
这个作者很懒,什么都没留下…
展开
-
Script:创建一个job,通过存储过程定期删除数据的脚本
------脚本出自《让oracle跑的更快2》,谭怀远create or replace procedure drop_partition as v_part_name varchar2(100);----要添加分区表的名称前缀 v_over_time number; ----过期时间间隔 v_err_num number;转载 2013-10-28 17:21:42 · 859 阅读 · 0 评论 -
Scripts:perf_performance_snapshot.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:08:33 · 464 阅读 · 0 评论 -
Scripts:查询sga中各组件使用率的脚本perf_sga_usage.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:08:47 · 701 阅读 · 0 评论 -
Scripts:找出磁盘读最多的SQL的脚本 perf_top_sql_by_disk_reads.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:08 · 756 阅读 · 0 评论 -
Scripts:找出使用最多buffer get的SQL脚本perf_top_sql_by_buffer_gets.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:21 · 1279 阅读 · 0 评论 -
Scripts:列出用户信息的脚本sec_users.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:11:22 · 576 阅读 · 0 评论 -
Scripts:segment_size.sql
col owner for a20col segment_name for a40col tablespace_name for 20select owner,segment_name,tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_segments where segment_name=upper('&1') group b转载 2014-06-02 17:12:59 · 511 阅读 · 0 评论 -
Scripts:sqlhc.sql
SPO sqlhc.logSET DEF ^ TERM OFF ECHO ON VER OFF SERVEROUT ON SIZE 1000000;REMREM $Header: 1366133.1 sqlhc.sql 11.4.4.6 2012/06/02 carlos.sierra $REMREM Copyright (c) 2000-2012, Oracle Corpor转载 2014-06-02 17:13:15 · 1515 阅读 · 0 评论 -
Scripts:列出角色信息的脚本sec_roles.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:38:06 · 554 阅读 · 0 评论 -
Scripts:查出用户有未提交事务的脚本sess_uncommited_transactions.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:39:25 · 553 阅读 · 0 评论 -
Scripts:sess_users_active_sql.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:40:09 · 610 阅读 · 0 评论 -
Scripts:找出使用最高的10个表的脚本perf_top_10_tables.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:34 · 731 阅读 · 0 评论 -
Scripts:找出10个最高使用的过程perf_top_10_procedures.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:49 · 615 阅读 · 0 评论 -
Scripts:top.sql
column username format a10column event format a25column machine format a15column spid format a10column wt format 9column p123 format a15column sess format a25set line 200SELECT转载 2014-06-02 17:13:47 · 750 阅读 · 0 评论 -
Scripts:session_wait.sql
select a.sid ,a.event,substr(b.program,1,30) program,b.machine,b.sql_id,b.SQL_CHILD_NUMBER sql_cnum,b.sql_hash_value hash_value from v$session_wait a ,v$session b where a.event not like 'SQL%' and a转载 2014-06-02 17:14:23 · 530 阅读 · 0 评论 -
Scripts:给出系统pid找出sql的脚本pid4sql.sql
select /*+ ORDERED USE_NL(st) */ sql_text from v$sqltext awhere a.hash_value = (select sql_hash_value from v$session bwhere b.paddr = (select addr from v$process cwhere c.spid = '&spid')转载 2014-06-02 17:09:00 · 565 阅读 · 0 评论 -
Scripts:sess_users_by_memory.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:40:47 · 522 阅读 · 0 评论 -
Scripts:检查ASMM的各个动态组件信息asmm_components.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-23 12:14:24 · 683 阅读 · 0 评论 -
Scripts:诊断sga diag信息auto_sga_diag.sql
spool auto_sga_diag.log set line 190 pagesize 1400 SELECT a.SGA_MEM + b.PGA_MEM "TOTAL_MEMORY" FROM (SELECT SUM(current_size) / 1024 / 1024 "SGA_MEM" FROM v$sga_dynamic_comp转载 2014-05-23 12:16:17 · 642 阅读 · 0 评论 -
Scripts:打印执行计划中AWR的执行计划信息awr_plan_stats.sql
-- Note that I have modified this script slightly to include snaps with 0 executions.-- This is to account for situations with very long running statements (that generally-- cross snapshot boundar转载 2014-05-26 15:22:47 · 645 阅读 · 0 评论 -
Scripts:创建SQL PROFILE create_sql_profile.sql
-------------------------------------------------------------------------------------------- File name: create_sql_profile.sql---- Purpose: Create SQL Profile based on Outline hints in V转载 2014-05-26 15:41:19 · 639 阅读 · 0 评论 -
Scripts:查看未提交的事务的脚本CurrentActivity.sql
set lines 150set pages 999clear colset termout offset trimout onset trimspool onREMREM Current transactionsREMREM Will show only last transaction by a userREMREM M转载 2014-05-26 15:43:08 · 475 阅读 · 0 评论 -
Scripts:显示数据库中的控制文件dba_controlfiles.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:48:18 · 663 阅读 · 0 评论 -
Scripts:查询数据库中参数文件的信息(在重新建库或者克隆数据库时经常使用)dba_cr_init.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:50:44 · 784 阅读 · 0 评论 -
Scripts:比较数据库对象的信息(在开发环境中可能需要经常遇到,需要小心使用哦)dba_compare_schemas.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:54:02 · 630 阅读 · 0 评论 -
Scripts:查询控制文件的信息dba_controlfile_records.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:56:24 · 538 阅读 · 0 评论 -
Scripts:查看数据库中的报错dba_errors.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:59:27 · 621 阅读 · 0 评论 -
Scripts:查看数据文件使用率的脚本(包括临时表空间的文件哦)dba_file_space_usage.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:01:32 · 592 阅读 · 0 评论 -
Scripts:报告物理数据库增长情况(注意脚本是看你数据库添加数据文件的时间哦)dba_db_growth.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:04:46 · 625 阅读 · 0 评论 -
Scripts:查看数据库里面建的directories dba_directories.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:06:20 · 1517 阅读 · 0 评论 -
Scripts:查询数据库中的无效对象dba_invalid_objects.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:11:08 · 1219 阅读 · 0 评论 -
Scrips:查看AWR的redo记录awr_redo_size_history.sql
REM --------------------------------------------------------------------------------------------------REM Author: Riyaj Shamsudeen @OraInternals, LLCREM www.orainternals.comREMREM Func转载 2014-05-26 15:12:44 · 1216 阅读 · 0 评论 -
Scripts:查看AWR中热点物理读awrtoprdrpt.sql
RemRem Copyright (c) 2008, Oracle Corporation. All rights reserved.RemRem NAMERem awrtoprdrpt.sqlRemRem DESCRIPTIONRem This report displays AWR ststistics for segments转载 2014-05-26 15:16:35 · 1121 阅读 · 0 评论 -
Scrips:打印AWR中执行计划的改变awr_plan_change.sql
set lines 155col execs for 999,999,999col avg_etime for 999,999.999col avg_lio for 999,999,999.9col begin_interval_time for a30col node for 99999break on plan_hash_value on startup_time sk转载 2014-05-26 15:18:49 · 903 阅读 · 0 评论 -
Scripts:计算每天的redo大小awr_redo_nologging_size.sql
--------------------------------------------------------------------------------------------------- Script : awr_redo_nologging_size.sql-----------------------------------------------------------转载 2014-05-26 15:20:47 · 1276 阅读 · 0 评论 -
Scripts:查看表的列上的统计信息col_stats.sql
set verify offset pagesize 999set lines 165col table_name format a25 trunccol column_name format a25col avg_len format 9999999col NDV format 999,999,999col buckets format 999999col low转载 2014-05-26 15:32:03 · 605 阅读 · 0 评论 -
Scripts:按日期查询归档信息的SQL check_archive.sql
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; set pagesize 999; set linesize 200; col name for a80; select thread#,sequence#,name,first_time, (next_time-first_time)*24*60转载 2014-05-26 15:34:53 · 604 阅读 · 0 评论 -
Scripts:自动检查数据库里面的陈旧的统计信息check_stale_stats.sql
-- - - - - - - - - - - - - - - - - - - - - - - - - Script begins here - - - - - - - - - - - - - - - - - - - - - - - - - --- NAME: CHECK_STALE_STATS.SQL-- Execute as SYS as sysdba-- ---------转载 2014-05-26 15:37:10 · 1380 阅读 · 0 评论 -
Scripts:创建手工的SQL PROFILE的脚本,老外写的很好用coe_xfr_sql_profile.sql
SPO coe_xfr_sql_profile.log;SET DEF ON TERM OFF ECHO ON FEED OFF VER OFF HEA ON LIN 2000 PAGES 100 LONG 8000000 LONGC 800000 TRIMS ON TI OFF TIMI OFF SERVEROUT ON SIZE 1000000 NUMF "" SQLP SQL>;RE转载 2014-05-26 15:29:29 · 1694 阅读 · 0 评论 -
Scripts:报告已安装的数据库选项(在升级数据库时需要用到)dba_options.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-27 09:53:55 · 561 阅读 · 0 评论