如何令暫時不想使用的procedure儲存過程由valid變成invalid
背景
某群友問題,有一個儲存過程,如果暫時不用,能讓他暫時無效嗎?結論
1,如果想暫時禁用儲存過程的物件狀態為無用,其實底層是要更新基表obj$的status為非0和非12,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
個人簡介:
8年oracle從業經驗,具備豐富的oracle技能,目前在國內北京某專業oracle服務公司從事高階技術顧問。
服務過的客戶:
中國電信
中國移動
中國聯通
中國電通
國家電網
四川達州商業銀行
湖南老百姓大藥房
山西省公安廳
中國郵政
北京302醫院
河北廊坊新奧集團公司
專案經驗:
中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通CRM資料庫效能最佳化
中國移動10086電商平臺資料庫部署及最佳化
湖南老百姓大藥房ERR資料庫sql最佳化專案
四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
北京高鐵訊號監控系統RAC資料庫部署及最佳化
河南宇通客車資料庫效能最佳化
中國電信電商平臺核心採購模組表模型設計及最佳化
中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
北京302醫院資料庫遷移實施
河北廊坊新奧data guard部署及最佳化
山西公安廳身份證審計資料庫系統故障評估
聯絡方式:
手機:18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博:wisdomone9
qq群:275813900
itpub部落格名稱:wisdomone1 http://blog.itpub.net/9240380/
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-1819145/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SQL 建立儲存過程PROCEDURESQL儲存過程
- PL/SQL 05 儲存過程 procedureSQL儲存過程
- [Procedure]Oracle之分頁儲存過程Oracle儲存過程
- Oracle 基礎 ----procedure(儲存過程)Oracle儲存過程
- mysql 儲存過程 procedure 批次建表MySql儲存過程
- oracle父儲存過程呼叫子儲存過程procedure與輸出引數Oracle儲存過程
- mysql儲存過程procedure、函式function的用法MySql儲存過程函式Function
- mysql檢視儲存過程show procedure status;MySql儲存過程
- MySQL入門--儲存過程(PROCEDURE)和儲存函式(FUNCTION)MySql儲存過程儲存函式Function
- procedure儲存過程呼叫dba字首的字典dba_objects儲存過程Object
- 通過v$access檢視正在執行的儲存過程procedure儲存過程
- 儲存過程中巢狀儲存過程的變數執行方式儲存過程巢狀變數
- 使用儲存過程儲存過程
- MySQL儲存過程中如何使用ROLLBACKMySql儲存過程
- Mysql 儲存過程的使用MySql儲存過程
- oracle procedure儲存過程(pl/sql)_使用declare cursor_begin end巢狀Oracle儲存過程SQL巢狀
- 如何查詢儲存過程procedure所依賴物件的有效性--dba_dependencies儲存過程物件
- sql server 儲存過程中使用變數表,臨時表的分析(續)SQLServer儲存過程變數
- oracle procedure 儲存過程輸入及輸出in out示例Oracle儲存過程
- 如何讓procedure儲存過程中的異常程式碼不中斷繼續執行儲存過程
- oracle儲存過程(procedure)中執行動態SQL小記Oracle儲存過程SQL
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- mysql如何呼叫儲存過程MySql儲存過程
- 儲存過程編譯時卡死儲存過程編譯
- 儲存過程中使用cursor儲存過程
- 從sybase的儲存過程轉向oracle的儲存過程儲存過程Oracle
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- SqlServer儲存過程的建立與使用SQLServer儲存過程
- MySQL儲存過程的建立和使用MySql儲存過程
- ORA-20003 , procedure儲存過程失效問題儲存過程
- 由Delphi程式改寫Sql(2000)的儲存過程SQL儲存過程
- 儲存過程儲存過程
- vertica 如何實現儲存過程?儲存過程
- 【實驗】【PROCEDURE】一個最簡單的oracle儲存過程"proc_helloworld"Oracle儲存過程
- 在儲存過程A中呼叫儲存過程B的結果儲存過程
- mysql-定時呼叫儲存過程MySql儲存過程
- 儲存過程編譯時會卡死儲存過程編譯
- Laravel 中使用 MySQL 儲存過程LaravelMySql儲存過程