Oracle Scripts
文章平均质量分 79
翁特锋
Oracle 博大精深
展开
-
比较相应schema 索引和表的大小
平时我们在做DB运维的时候会碰到业务上存在个别表的dml操作非常频繁,结果导致该表的索引大小比表本身还要大,需要dba rebild index,一般情况下通过如下sql可以观察哪些表需要重建索引,此处讲index 大小与表大小的比较阀值设置为0.5:select idx.owner owner, idx.table_name tablename, idx.index原创 2013-02-20 11:01:18 · 702 阅读 · 0 评论 -
查询数据库对象block使用情况
定义获取db_block_info Function原创 2015-03-06 13:38:34 · 1172 阅读 · 0 评论 -
实用脚本收录:Identifying Blocking Transactions
select s1.username blkg_user, s1.machine blkg_ws, s1.sid blkg_sid, s2.username wait_user, s2.machine wait_ws, s2.sid wait_sid, lo.object_id blkd_obj_id,原创 2014-11-28 11:01:57 · 952 阅读 · 0 评论 -
Data Guard Switchover Unix shell script
The following Unix shell scripts can be used to automate the Data Guard Switchover process of a physical standby database.转载 2014-06-21 15:41:21 · 1033 阅读 · 0 评论 -
dbupgdiag.sql—Script to Collect DB Upgrade/Migrate Diagnostic Information
该脚本用来收集升级前及升级后数据库无效和对象等数据库信息,执行后需要指定dbupgdiag.sql 输出日志的路径 Oracle 解释如下: 适用范围: Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.1 - Release: 9.2 to 11.2 Information in this docu原创 2014-03-20 12:33:38 · 2556 阅读 · 0 评论 -
scripts for query proportion of the on-idle wait time is taken up by these wait classes
You can use the following command to determine what proportion of the entire non-idle wait time is taken up by these wait classes: SELECT SUBSTR(WAIT_CLASS, 1, 30) WAIT_CLASS, ROUND(TIME_WAIT原创 2014-02-08 14:45:16 · 934 阅读 · 0 评论 -
SQLSERVER 查询被阻塞进程的实际查询文本
SQLSERVER 查询被阻塞进程的实际查询文本: SELECT WT.session_id ASwaiting_session_id, (SELECT /*convert(varchar, c.connect_time,120)+' IP='+*/c.client_net_address+'/'+s.host_name+', '+s.login_name转载 2013-12-26 10:28:31 · 1028 阅读 · 0 评论 -
oracle database size monitor(daily)
Check daily database size and sent mail to dba #crontab -l 0 7 * * * su - oracle -c "/housekeep/scripts/dbsize cuoln01" > /dev/null 2>&1 ##/housekeep/scripts/dbsize #edit by wonderful 2012-12-原创 2013-12-09 09:54:24 · 1271 阅读 · 0 评论 -
限制用户访问数据库另外一个用户的对象,reference how to clone user privileges [ID 473317.1]
故事背景: 作为非db 开发技术人员,当有如题这种需求时候,作为运维人员需要告诉开发哪些用户目前有哪些权限即可: 1.当需要知道某个用户角色和角色的具体权限时候,如下脚本即可满足: select lpad(' ', 2*level) || granted_role "User, his roles and privileges" from ( /* THE USERS */ sele原创 2013-06-06 10:26:22 · 689 阅读 · 0 评论 -
Get DDL metadata sql
来自metalkink 的dbms_metadata包中的get_ddl函数,用法如下: --GET_DDL: Return the metadata for a single object as DDL. -- This inte***ce is meant for casual browsing (e.g., from SQLPlus) -- vs. the programmatic翻译 2013-05-27 13:50:38 · 1033 阅读 · 0 评论 -
监控数据库表空间使用率
Tablespace used rate monitor: col used_space(M) for a15 col sum_space(M) for a20 col used_rate(%) for a20 col tablespace_name for a20 set linesize 150 SELECT D.TABLESPACE_NAME, SPACE || 'M'原创 2013-04-25 09:12:18 · 963 阅读 · 0 评论 -
awr及ash和addm 来源视图之一dba_hist_active_sess_history
Ash的来源视图之一:dba_hist_active_sess_history 以下是查询rac 中出现 lc和 pin s wait on x 2个经典等待事件的例子: select * from dba_hist_active_sess_history where sample_time >= to_timestamp('2013-04-08 02:00:00', 'yyyy-mm原创 2013-04-08 10:29:30 · 1937 阅读 · 0 评论 -
Scripts for monitoring oracle DB temporary tablespace by sql statement
1.查询临时表空间使用率 SELECT d.tablespace_name "Name", TO_CHAR(NVL(a.bytes / 1024 / 1024, 0),'99,999,990.900') "Size (M)", TO_CHAR(NVL(t.hwm, 0)/1024/1024,'99999999.999') "HWM (M)", TO_CHAR(NVL(t.hwm /原创 2013-02-19 15:57:12 · 754 阅读 · 0 评论 -
监控并记录Oracle数据库空间增长的简单方法
1.跟踪数据库空间增长 SELECT SUM(MB_ALLOC)/1024 GB_DB_SIZE FROM (SELECT SUM(ROUND(bytes_used/(1024*1024),2) + ROUND(bytes_free/(1024*1024),2)) MB_ALLOC FROM V$temp_space_header, dba_temp_files WHERE V原创 2016-12-05 16:32:37 · 4066 阅读 · 0 评论