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 · 861 阅读 · 0 评论 -
Scripts:perf_performance_snapshot.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:08:33 · 467 阅读 · 0 评论 -
Scripts:查询sga中各组件使用率的脚本perf_sga_usage.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:08:47 · 712 阅读 · 0 评论 -
Scripts:找出磁盘读最多的SQL的脚本 perf_top_sql_by_disk_reads.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:08 · 759 阅读 · 0 评论 -
Scripts:找出使用最多buffer get的SQL脚本perf_top_sql_by_buffer_gets.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:21 · 1284 阅读 · 0 评论 -
Scripts:列出用户信息的脚本sec_users.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:11:22 · 580 阅读 · 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 · 519 阅读 · 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 · 1523 阅读 · 0 评论 -
Scripts:列出角色信息的脚本sec_roles.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:38:06 · 557 阅读 · 0 评论 -
Scripts:查出用户有未提交事务的脚本sess_uncommited_transactions.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:39:25 · 555 阅读 · 0 评论 -
Scripts:sess_users_active_sql.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:40:09 · 617 阅读 · 0 评论 -
Scripts:找出使用最高的10个表的脚本perf_top_10_tables.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:34 · 735 阅读 · 0 评论 -
Scripts:找出10个最高使用的过程perf_top_10_procedures.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-02 17:09:49 · 620 阅读 · 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 · 754 阅读 · 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 · 533 阅读 · 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 · 569 阅读 · 0 评论 -
Scripts:sess_users_by_memory.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-06-03 09:40:47 · 525 阅读 · 0 评论 -
Scripts:检查ASMM的各个动态组件信息asmm_components.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-23 12:14:24 · 689 阅读 · 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 · 647 阅读 · 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 · 651 阅读 · 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 · 648 阅读 · 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 · 481 阅读 · 0 评论 -
Scripts:显示数据库中的控制文件dba_controlfiles.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:48:18 · 670 阅读 · 0 评论 -
Scripts:查询数据库中参数文件的信息(在重新建库或者克隆数据库时经常使用)dba_cr_init.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:50:44 · 788 阅读 · 0 评论 -
Scripts:比较数据库对象的信息(在开发环境中可能需要经常遇到,需要小心使用哦)dba_compare_schemas.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:54:02 · 636 阅读 · 0 评论 -
Scripts:查询控制文件的信息dba_controlfile_records.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:56:24 · 541 阅读 · 0 评论 -
Scripts:查看数据库中的报错dba_errors.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 15:59:27 · 625 阅读 · 0 评论 -
Scripts:查看数据文件使用率的脚本(包括临时表空间的文件哦)dba_file_space_usage.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:01:32 · 599 阅读 · 0 评论 -
Scripts:报告物理数据库增长情况(注意脚本是看你数据库添加数据文件的时间哦)dba_db_growth.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:04:46 · 633 阅读 · 0 评论 -
Scripts:查看数据库里面建的directories dba_directories.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:06:20 · 1527 阅读 · 0 评论 -
Scripts:查询数据库中的无效对象dba_invalid_objects.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-26 16:11:08 · 1235 阅读 · 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 · 1231 阅读 · 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 · 1128 阅读 · 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 · 911 阅读 · 0 评论 -
Scripts:计算每天的redo大小awr_redo_nologging_size.sql
--------------------------------------------------------------------------------------------------- Script : awr_redo_nologging_size.sql-----------------------------------------------------------转载 2014-05-26 15:20:47 · 1285 阅读 · 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 · 616 阅读 · 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 · 609 阅读 · 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 · 1385 阅读 · 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 · 1701 阅读 · 0 评论 -
Scripts:报告已安装的数据库选项(在升级数据库时需要用到)dba_options.sql
-- +----------------------------------------------------------------------------+-- | Jeffrey M. Hunter |-- | jhunter@转载 2014-05-27 09:53:55 · 568 阅读 · 0 评论