ITPub博客

3. 查询schema下所有表的记录数-- 创建计算表记录数的函数[@more@]3. 查询schema下所有表的记录数-- 创建计算表记录数的函数CREATE OR REPLACE FUNCTION sf_tab_count(iv_table_name IN VARCHAR2) RETURN NUMBER IS v_cursor_handle INTEGER; -- 句柄 v_count INTEGER; -- 计数器 v_text VARCHAR2(4000); -- 运算PL/SQL串 r_num NUMBER;BEGIN v_text := v_text || 'DECLARE v_num NUMBER; BEGIN SELECT COUNT(*) INTO :v_num FROM '||iv_t

2. 查询锁等待和处理死锁脚本[@more@]2. 查询锁等待和处理死锁脚本SELECT /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(b1) NO_MERGE(c) NO_MERGE(d) */ 'Lock' "Status", a.username "用户名", a.sid "SID", a.serial# "SERIAL#", b.type "锁类型", DECODE(b.lmode, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive', 4, 'Share', 5, 'Share Row Exclusive', 6, 'Exclusive', 'NONE') "占用的模式", DECODE(b.request, 1, 'No Lock', 2, 'Row Share', 3, 'Row Exclusive

1. 重建schema下的索引脚本[@more@]1. 重建schema下的索引脚本SET LINESIZE 200SET FEED OFFSPOOL rebuild_inx.sqlSELECT 'ALTER INDEX '|| owner ||'.'|| index_name || ' REBUILD ONLINE;' FROM DBA_INDEXESWHERE owner = 'HR';SPOOL OFFSET FEED ON