如何令暫時不想使用的procedure儲存過程由valid變成invalid

wisdomone1發表於2015-10-30

背景

  某群友問題,有一個儲存過程,如果暫時不用,能讓他暫時無效嗎?


結論

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

個人簡介:


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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章