Tag Archives: Oracle

V$Session_LONGOPS

这个视图的来源大致是这样的:

SELECT inst_id, ksulosno, ksulosrn, ksulopna, ksulotna, ksulotde, ksulosfr,
ksulotot, ksulouni,
TO_DATE (ksulostm, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
TO_DATE (ksulolut, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
DECODE (SIGN (ksulotot - ksulosfr),
-1, TO_NUMBER (NULL),
DECODE (ksulosfr,
0, TO_NUMBER (NULL),
ROUND (ksuloetm * ((ksulotot - ksulosfr) / ksulosfr))
)
),
ksuloetm, ksuloctx, ksulomsg, ksulounm, ksulosql, ksulosqh, ksuloqid
FROM x$ksulop;

补充信息: 一篇非常好的解释文档。有些内容我之前也不知道。

继续阅读 →

Tom 的 Expert one on one 的一个Bug

Tom 的 Expert one on one：　Oracle 的一个Bug. 第三章讲到DML所的时候 Tom 用了一个脚本：

tkyte@TKYTE816> select username,
2         v$lock.sid,
3         trunc(id1/power(2,16)) rbs,
4         bitand(id1,to_number('ffff','xxxx'))+0 slot,
5         id2 seq,
6         lmode,
7         request
8  from v$lock, v$session
9  where v$lock.type = 'TX'
10    and v$lock.sid = v$session.sid
11    and v$session.username = USER
12  /
USERNAME        SID        RBS       SLOT        SEQ      LMODE    REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE             8          2         46        160          6          0

产品库上最好别直接用这个脚本，很慢很慢的.

继续阅读 →

ORA-02248

遇到一个ora-02248: invalid option for ALTER SESSION的错误．环境是
java/10g instant client连接到
Oracle 9206 数据库发生的

继续阅读 →

重编译 invalid 对象

DBA在一些场合下,要对无效的数据库对象进行重新编译.如果只是对all_objects等视图中选出来的无效对象进行重新编译,可能需要很多次:因为各个对象之间有依赖性的.这样并不是一个很好的办法.Raymond 在Recompiling invalid objects提到了如何有效地重新编译无效对象.提到了三种比较有效地方法(

  • 利用$ORACLE_HOME/rdbms/admin下的utlrp.sql脚本编译.一般都是在迁移或者升级之后运行该脚本.Raymond说该方法的不足之处在于这个脚本是对整个数据库中的对象进行重新编译的,所以不可取.有网友指出utlrp.sql 实际上是调用utlrcmp.sql的这样就可以用utl_recomp包来做喽(这样就是比较好的方法).
  • 用DBMS_UTILITY包来进行编译.但是也有一定的局限性.
  • Raymond提到了自己的解决办法: 不过也立刻有人指出来,这样对 View 的重新编译是无能为力的(ALTER_COMPILE只能处理:PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TRIGGER).参见下面的脚本
CREATE OR REPLACE PROCEDURE RECOMPILE_SCHEMA
IS
v_Type USER_OBJECTS.OBJECT_TYPE%TYPE;
v_Name USER_OBJECTS.OBJECT_NAME%TYPE;
v_Stat USER_OBJECTS.STATUS%TYPE;
CURSOR c_Obj
IS
SELECT  BASE
FROM  (SELECT A.OBJECT_ID BASE
,      B.OBJECT_ID REL
FROM   USER_OBJECTS A
,      USER_OBJECTS B
,      SYS.DEPENDENCY$  C
WHERE  A.OBJECT_ID = C.D_OBJ#
AND    B.OBJECT_ID = C.P_OBJ#
AND    A.OBJECT_TYPE IN ('PACKAGE',
'PROCEDURE',
'FUNCTION',
'PACKAGE BODY',
--  'VIEW',
'TRIGGER')
AND    B.OBJECT_TYPE IN ('PACKAGE',
'PROCEDURE',
'FUNCTION',
'PACKAGE BODY',
--  'VIEW',
'TRIGGER')
AND    NOT A.OBJECT_NAME = B.OBJECT_NAME) OBJECTS
CONNECT BY BASE = PRIOR REL
GROUP   BY BASE
ORDER   BY MAX(LEVEL) DESC;
BEGIN
-- loop through all objects in order of dependancy.
FOR c_Row IN c_Obj
LOOP
-- select the objects attributes (type, name & status).
SELECT OBJECT_TYPE
,      OBJECT_NAME
,      STATUS
INTO   v_Type
,      v_Name
,      v_Stat
FROM   USER_OBJECTS
WHERE  OBJECT_ID = c_Row.BASE;
-- if the OBJECT is INVALID, recompile it.
IF v_Stat = 'INVALID' THEN
DBMS_DDL.ALTER_COMPILE(v_Type, USER, v_Name);
END IF;
END LOOP;
-- Recompile all remaining INVALID OBJECTS (all those without dependencies).
FOR c_Row IN ( SELECT OBJECT_TYPE
,      OBJECT_NAME
FROM   USER_OBJECTS
WHERE  STATUS = 'INVALID'
AND    OBJECT_TYPE IN ('PACKAGE',
'PROCEDURE',
'FUNCTION',
'TRIGGER',
'PACKAGE BODY',
--   'VIEW',
'TRIGGER') )
LOOP
DBMS_DDL.ALTER_COMPILE(c_Row.OBJECT_TYPE, USER, c_Row.OBJECT_NAME);
END LOOP;
END RECOMPILE_SCHEMA;
Rem   EXAMPLES
Rem      1. Recompile all objects sequentially:
Rem             execute utl_recomp.recomp_serial();
Rem
Rem      2. Recompile objects in schema SCOTT sequentially:
Rem             execute utl_recomp.recomp_serial('SCOTT');
Rem
Rem      3. Recompile all objects using 4 parallel threads:
Rem             execute utl_recomp.recomp_parallel(4);
Rem
Rem      4. Recompile objects in schema JOE using the number of threads
Rem         specified in the paramter JOB_QUEUE_PROCESSES:
Rem             execute utl_recomp.recomp_parallel(NULL, 'JOE');
Rem
Rem      5. Recompile all objects using 2 parallel threads, but allow
Rem         other applications to use the job queue concurrently:
Rem             execute utl_recomp.recomp_parallel(2, NULL,
Rem                                                utl_recomp.share_job_queue);
Rem
Rem      6. Restore the job queue after a failure in recomp_parallel:
Rem             execute utl_recomp.restore_job_queue();

调用 utl_recomp 包是比较好的做法.需要 sys 权限.
BTW:刚才浏览eygle 的站点,发现他有一 Blog 说的正是这个事情:
http://www.eygle.com/archives/2005/01/ecioaeaoeeeaoea.html ,晕倒