【SYNONYM】ORA-00980錯誤處理暨刪除無效同名的SQL生成指令碼

secooler發表於2009-11-04
SQL指令碼
我在此透過一個具體的實驗來給大家展示一種常見的導致“ORA-00980”錯誤發生的情況，然後給出一些其他可能導致這個錯誤發生的原因，最後給出一個可以自動化獲得刪除所有無效同名的SQL指令碼。

一、先來模擬一下這個“ORA-00980: synonym translation is no longer valid”錯誤。
1.在secooler使用者中建立一張表t
secooler@ora10g> create table t (x number);

Table created.

secooler@ora10g> insert into t values (1);

1 row created.

secooler@ora10g> commit;

Commit complete.

2.切換到sec使用者，建立同名syn_t引用secooler使用者下的t表
secooler@ora10g> conn sec/sec
Connected.
sec@ora10g> create synonym syn_t for secooler.t;

Synonym created.
sec@ora10g> select * from syn_t;

         X
----------
         1

3.回到secooler使用者中將表t刪除掉
sec@ora10g> conn secooler/secooler
Connected.
secooler@ora10g> drop table t purge;

Table dropped.

4.此時，我們再次使用同名syn_t檢視錶t時就會見到這個“ORA-00980”錯誤了
secooler@ora10g> conn sec/sec
Connected.
sec@ora10g> select * From syn_t;
select * From syn_t
              *
ERROR at line 1:
ORA-00980: synonym translation is no longer valid

二、導致“ORA-00980”錯誤的原因小結
1.刪除了資料庫物件，但是忘記刪除同名；
2.刪除一個使用者，但是忘記刪除與此使用者中相關的同名；
3.建立同名時，同名對應的資料庫物件已經被刪除掉了（這種情況很有意思）。

針對第三點，進一步演示一下，前面我們已經刪除了secooler使用者下t表的情況。此時我們嘗試使用secooler使用者下被刪除的t表再建立兩個同名，有意思的是雖然表已經不存在了，但同名還是可以建立成功的。需要清醒的認識到此時被建立出來的同名是無效的，從後面的指令碼執行結果中可以更清晰的得出這個結論。
sec@ora10g> create synonym syn_public_t for secooler.t;

Synonym created.

sec@ora10g> create synonym syn_tt for secooler.t;

Synonym created.

三、隆重推出自動化生成刪除無效同名的指令碼
1.指令碼如下
select 'drop '
       || decode (s.owner,
                  'PUBLIC', 'public synonym ',
                  'synonym ' || s.owner || '.')
       || s.synonym_name
       || ';' as "Dropping invalid synonyms:"
  from dba_synonyms s
 where table_owner not in ('SYSTEM', 'SYS') and db_link is null
   and not exists
          (select null
             from dba_objects o
            where s.table_owner = o.owner
              and s.table_name = o.object_name)
/

2.針對上面的場景，真實演示一下
sys@ora10g> select 'drop '
  2         || decode (s.owner,
  3                    'PUBLIC', 'public synonym ',
  4                    'synonym ' || s.owner || '.')
  5         || s.synonym_name
  6         || ';' as "Dropping invalid synonyms:"
  7    from dba_synonyms s
  8   where table_owner not in ('SYSTEM', 'SYS') and db_link is null
  9     and not exists
 10            (select null
 11               from dba_objects o
 12              where s.table_owner = o.owner
 13                and s.table_name = o.object_name)
 14  /

Dropping invalid synonyms:
------------------------------------------------------------------------
drop synonym SEC.SYN_TT;
drop synonym SEC.SYN_PUBLIC_T;
drop synonym SEC.SYN_T;

3.使用指令碼生成的SQL指令碼即可輕鬆完成無效同名的清理任務。
sys@ora10g> drop synonym SEC.SYN_TT;

Synonym dropped.

sys@ora10g> drop synonym SEC.SYN_PUBLIC_T;

Synonym dropped.

sys@ora10g> drop synonym SEC.SYN_T;

Synonym dropped.

四、小結
在DBA的日常維護中需要不斷的思考一個問題：如何才能更加高效的應對故障的發生？
帶著這個問題，它就會不斷的指引我們向自動化的道路邁進。
編寫一些順手易用的指令碼是一名優秀DBA必須具有的基本技能之一。
希望這個小文兒能給大家帶來一點點思考。

Good luck.

-- The End --

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/519536/viewspace-618147/，如需轉載，請註明出處，否則將追究法律責任。

相關文章