oracle plsql/sql

Scripts: format Library Cache Lock/pin wait event p3 value

2022-05-192016-09-08 by weejar zhang

SQL> exec lbc_p3(1571747577004035);
———————————————
……………………..Library cache P3 value: 1571747577004035
………………….Library cache P3 value HEX: 5957f00010003
…………………………………Object id: 365951
…………………………………Namespace: 1
……………………………….RequestMode: exclusive mode

PLS-00382: expression is of wrong type When using “execute immediate” dynamic SQL statement

2014-03-12 by weejar zhang

今天测式部门同事遇到的一个问题记录一下，开发库运行正常，同样的复制到测试库执行应用报错,
LINE/COL ERROR
——– —————————————————————–
59/5 PL/SQL: Statement ignored
59/23 PLS-00382: expression is of wrong type

Script:List all SQL in the library cache cursor referencing a given table .(列出指定表上所有相关SQL)

2014-03-04 by weejar zhang

PROMPT List all SQL in the library cache cursor referencing a given table — Author: weejar — — you need to run this script either as SYS or — need to have relevant X$ proxy views created — I tested in oracle 11g it worked . undefine owner undefine table_name prompt accept owner prompt ‘Please … Read more

Script: Display the sessions distribution and top 50 idle sessions

2013-11-22 by weejar zhang

This script displays the top 50 longest idle times and list distribution of sessions on the instance..

SQL Test Case Builder generate test case automatically(自动生成SQL所需测试数据)

2013-10-15 by weejar zhang

Why SQL Test Case Builder? For most SQL problems, the single most important factor for a speedy bug resolution is to obtain a reproducible test case. However, this is normally the longest and most painful step for customers. The goal of the SQL Test Case Builder (TCB) is to automatically gather as much information as … Read more

Dynamic SQL Supports Statements Length Characters Limit (execute immediate sql长度限制)

2013-08-08 by weejar zhang

有时需要在plsql 写一些动态sql,用execute immediate 或dbms_sql 调用，但是execute immediate 后动态sql 文本的长度限制是多少呢？

Troubleshooting wait event: ‘latch: row cache objects’

2022-06-192013-04-01 by weejar zhang

This latch comes into play when user processes are attempting to access or update the cached data dictionary values.

SQL hint mean force? More about parallel(二)(不并行的场景)

2014-06-042013-03-25 by weejar zhang

sql中如果使用了parallel hint 或是force parallel query 想并行查询的特性，如果那样做了就一定能保证 query optimizer选择并行的执行计划么？知道那样做实际上产生什么样的影响么？

Script: Who’s using a database link?(找出谁在使用dblink)

2018-11-012013-03-14 by weejar zhang

Every once in awhile it is useful to find out which sessions are using a database link in an Oracle database. It’s one of those things that you may not need very often, but when you do need it, it is usually rather important. Yong Huang includes this script on his website, and notes that … Read more

Script: TOP logical read within a specific time period(指定时间逻辑读最多的对象)

2014-03-042013-01-09 by weejar zhang

根据AWR的收集信息查询出指定时间段内的逻辑读最高的几个对象，script

Script: Oracle Table/Tablespace/DB Growth Prediction(预测表/表空间/库增长)

2025-02-102012-12-26 by weejar zhang

Some scripts for showing the growth of tablespaces over time periods. to show the starting tablespace size and the end tablespace size, computing the tablespace growth for the time period.

table function (函数返回结果集)

2012-12-12 by weejar zhang

经常见到select * from table(function(args))的查询方法,对pl/sql 强大功能赞不绝口,比如PIPELINED 管道函数
例子

Script: 通过AWR信息查询db 中segments 的增长信息

2014-03-042012-12-10 by weejar zhang

awr 中有很多wr开头的表收集的数据非常有用，通过dba_hist开头的视图可以很直观的查看，也可以手动查询一些详细或定制自己所要的信息

Script: oracle 删除用户(schema)下的所有对象

2014-03-042012-12-03 by weejar zhang

有时需要清除schema里的所有对象，但又没有dba权限,不可以drop user xxx cascade; re-create user;可以写个plsql，循环删除所有对象，注意有些对象存在依赖关系，如外键、index 和table..,这里建个procedure，方便在开发库一堆有schema中运行。