如何令暂时不想使用的procedure存储过程由valid变成invalid

最新推荐文章于 2024-03-15 13:44:48 发布

clg10051

最新推荐文章于 2024-03-15 13:44:48 发布

阅读量647

点赞数

文章标签：数据库后端

背景

某群友问题，有一个存储过程，如果暂时不用，能让他暂时无效吗？

结论

1,如果想暂时禁用存储过程的对象状态为无用，其实底层是要更新基表obj$的status为非0和非1
2,user_objects所引用的基表"_CURRENT_EDITION_OBJ",它其实是一个视图
3,"_CURRENT_EDITION_OBJ"视图是基于基表user$及obj$的包装
4，操作基表非常危险，一定要慎用

测试

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> create or replace procedure proc_valid
2 as
3 v_num int;
4 begin
5 select 1 into v_num from dual;
6 end;
7 /

Procedure created.

SQL> select object_name,object_type,status from user_objects where object_name='PROC_VALID';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------------------ --------------
PROC_VALID PROCEDURE VALID

SQL> set long 99999999
SQL> set pagesize 300
SQL> select view_name,text from dba_views where view_name='USER_OBJECTS'
2 ;

VIEW_NAME TEXT
------------------------------------------------------------ --------------------------------------------------------------------------------
USER_OBJECTS select o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
and bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER', 101, 'DESTINATION',
'UNDEFINED'),
o.ctime, o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
o.namespace,
o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o
where o.owner# = userenv('SCHEMAID')
and o.linkname is null
and (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
or
(o.type# = 1 and 1 = (select 1
from sys.ind$ i
where i.obj# = o.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags, 128) = 0
union all
select l.name, NULL, to_number(null), to_number(null),
'DATABASE LINK',
l.ctime, to_date(null), NULL, 'VALID', 'N', 'N', 'N', NULL, NULL
from sys.link$ l
where l.owner# = userenv('SCHEMAID')

SQL>

可见是从如下表获知user_objects相关信息的，且user_objects.status对应"_CURRENT_EDITION_OBJ"表的status列
SQL> desc "_CURRENT_EDITION_OBJ";
Name Null? Type
----------------------------------------- -------- ----------------------------
OBJ# NOT NULL NUMBER
DATAOBJ# NUMBER
DEFINING_OWNER# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
NAMESPACE NOT NULL NUMBER
SUBNAME VARCHAR2(30)
TYPE# NOT NULL NUMBER
CTIME NOT NULL DATE
MTIME NOT NULL DATE
STIME NOT NULL DATE
STATUS NOT NULL NUMBER
REMOTEOWNER VARCHAR2(30)
LINKNAME VARCHAR2(128)
FLAGS NUMBER
OID$ RAW(16)
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE
OWNER# NUMBER
DEFINING_EDITION VARCHAR2(30)

根据DDL代码
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),

可见当前"_CURRENT_EDITION_OBJ"表的status列=1,我们只要更新其列值为非0和非1，即可
SQL> select object_name,object_id,object_type,status from user_objects where object_name='PROC_VALID';

OBJECT_NAME OBJECT_ID OBJECT_TYPE STATUS
------------------------------ ---------- ------------------------------ --------------
PROC_VALID 74876 PROCEDURE VALID

SQL> select obj#,name,status from "_CURRENT_EDITION_OBJ" where name='PROC_VALID';

OBJ# NAME STATUS
---------- ------------------------------------------------------------ ----------
74876 PROC_VALID 1

可见不能更新列值=2
SQL> update "_CURRENT_EDITION_OBJ" set status=2 where obj#=74876;
update "_CURRENT_EDITION_OBJ" set status=2 where obj#=74876
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table

可见"_CURRENT_EDITION_OBJ" 是个视图，所以获取共DDL基表定义
SQL> host oerr ora 01779
01779, 00000, "cannot modify a column which maps to a non key-preserved table"
// *Cause: An attempt was made to insert or update columns of a join view which
// map to a non-key-preserved table.
// *Action: Modify the underlying base tables directly.

或见最终还是要操作obj$基表

VIEW_NAME TEXT
------------------------------------------------------------ --------------------------------------------------------------------------------
_CURRENT_EDITION_OBJ select o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."TY
PE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."FL
AGS",o."OID$",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6",

o.spare3,
case when (o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87) or
bitand(u.spare1, 16) = 0) then
null
when (u.type# = 2) then
(select eo.name from obj$ eo where eo.obj# = u.spare2)
else
'ORA$BASE'
end
from obj$ o, user$ u
where o.owner# = u.user#
and ( /* non-versionable object */
( o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87,88)
or bitand(u.spare1, 16) = 0)
/* versionable object visible in current edition */
or ( o.type# in (4,5,7,8,9,10,11,12,13,14,22,87)
and ( (u.type# <> 2 and
sys_context('userenv', 'current_edition_name') = 'ORA$BASE')

or (u.type# = 2 and
u.spare2 = sys_context('userenv', 'current_edition_id'))
or exists (select 1 from obj$ o2, user$ u2
where o2.type# = 88
and o2.dataobj# = o.obj#
and o2.owner# = u2.user#
and u2.type# = 2
and u2.spare2 =
sys_context('userenv', 'current_edition_id'))
)
)
)

SQL> select obj#,name,status from obj$ where obj#=74876;

OBJ# NAME STATUS
---------- ------------------------------------------------------------ ----------
74876 PROC_VALID 1

SQL> update obj$ set status=2 where obj#=74876;

1 row updated.

SQL> commit;

Commit complete.

SQL> select object_name,object_type,status from user_objects where object_name='PROC_VALID';

OBJECT_NAME OBJECT_TYPE STATUS
------------------------------ ------------------------------ --------------
PROC_VALID PROCEDURE INVALID