Oracle DB 讓 A Owner 可使用 B Owner 的 All Object

在 Oracle Database 中,

有讓 A Owner 使用 All Owner 的所有 Object 的語法: 點選此處,

但是, 若只是要讓 A Owner 使用 B Owner 的所有 Object,

而假設 B Owner 有 1000 個 Object,

若透過 Grant 一行一行授權, 則 1000 個 Object 就要執行授權 1000 次,

會令人受不了的,


所以, 解決方式,

Oracle DB 讓 A Owner 可使用 All Owner 的 All Object

在 Oracle Database 中,

要讓 A Owner 可以對所有 Owner 的 Object 進行查詢、維護、或執行,

可以透過下面語法 :

Oracle DB 讓 B Owner 可授權 A Owner 的 Object 給 C User

範例 1: A Owner Table 授權給 B Owner, 而 B Owner 要將此 Table 授權給 C Owner.

範例 2: B Owner 建立一個 View, 而此 View 含有 A Owner Table, 要將此 View 授權給 C Owner 使用, 如下圖 :


以上兩個範例, 都是相同的意思,

Oracle DB 的 A Owner 建立與維護 B Owner 的 Object

在 Oracle Database 中,

要讓 A Owner 可以建立與維護 B Owner 的 Object (如：Table, Procedure, ...),

有以下兩種方式 :

Oracle DB 的 A Owner 可以使用 B Owner 的 Object

在 Oracle Database 中,

要實現讓 A Owner 可以使用 B Owner 的 Object (如: Table, View, Stored Procedure, ...)

可以參考如下 :

Oracle Database Grant 與 Synonym 的差異

Grant 與 Synonym 的差異, 參考如下 :

 差異之處

Grant: Object 授權。
 
Synonym: 讓開發者可直接輸入 Object_Name, 而不用輸入 Owner.Object_Name。

單一 Synonym 語法 :

Oracle DB 用 SYSDBA 權限登入

在某些情況下, 如果忘記某個 Oracle DB Owner 的密碼,

你可以用 SYSDBA 登入 Database, 再進行該 Owner 的密碼修改,

而用 SYSDBA 登入的方式, 參考如下 :

Oracle DB 透過 DB Link 連線其他資料庫

在 Oracle Database 中,

若要連接其他資料庫, 進行查詢, 維護, 執行等處理,

可以透過 DB Link,

相關的語法與注意事項, 參考如下 :

 建立方式 1: DB Server 需先設定好 Host_Name

create [public] database link <Gateway_name> connect to <User_name> identified by <Password> Using '<Host_name>';
 
-- 其中 Host Name 需設定在 Database Server 端的 tnsnames.ora 檔案中.

 建立方式 2: DB Server 無需設定好 Host_Name

create [public] database link <Gateway_name> connect to <User_name> identified by <Password> Using '(DESCRIPTION =
  (ADDRESS_LIST =
      (ADDRESS =
        (COMMUNITY = tcp.world)
        (PROTOCOL = TCP)
        (Host = <Remote_DB_IP_Address>)
        (Port = <Remote_DB_Port>)
      )
  )
  (CONNECT_DATA = (SID = <Remote_DB_SID>)
  )
)';

 執行 SQL

Select * from <Table_Name>@<Gateway_Name>;

 執行 Package

<Package_Name>.<Procedure_Name>@<Gateway_Name>( parameter1, parameter2, ... );

 注意事項

雖然用 DB Link, 開發人員不知道登入遠端 DB 的帳號密碼, 但是開發人員仍可處理給予權限的事情.
 
所以, 不要讓 DB Link 連上遠端 DB 重要的帳號.

昭佑.天翔

Oracle DB 透過 dbms_job.submit 排定 Job Schedule 定期工作

在 Oracle Database 中,

有提供 dbms_job.submit 套件,

讓你可以排定 Job Schedule, 來定期執行某些處理,

相關的語法與注意事項, 參考如下 :

 建立 Job 語法

delcare
   v_job_num number;
begin
   dbms_job.submit( v_job_num      -- 此為輸出變數，會由系統自動給值
                  , '內容;'        -- 預定執行的內容 (記得要加入分號)
                  , SYSDATE + n    -- 第一次執行時間, 一分鐘為 1 / 1440
                  , 'SYSDATE + m'  -- 下一次執行時間, 'NULL' 表示只執行一次
                  , FALSE
                  );
end;

 建立 Job 注意事項

1) 內容可以是 Procedure、Package、Begin ... End 等程式碼.
 
2) 請設定 INIT<SID>.ORA 檔案的 JOB_QUEUE_PROCESSES 參數值 > 0 才能讓 Job 執行.

 刪除 Job 語法

begin
   dbms_job.remove( v_job_num );
end;

昭佑.天翔

Oracle Database Create or Modify User (Owner, Schema)

在 Oracle Database 中建立一個新的 DB Schma,

可以參考下面的基本語法 :

 語法

-- 建立 User 
Create User <UserName> IDENTIFIED BY <Passsword>;

-- 修改 User 
Alter User <UserName> IDENTIFIED BY <Passsword>;

-- 讓 User 具有 "連接 DB" 的權限
Grant CONNECT to <UserName>;

-- 讓 User 具有 "對本身 Object 的 DML、DDL" 權限
Grant RESOURCE to <UserName>;

-- 讓 User 具有 "存取 Tablespace" 的權限
Grant UNLIMITED TABLESPACE to <UserName>;

注意: 在 Oracle 12c 資料庫中 create user 若有問題, 請參考這裡: 點選此處

昭佑.天翔

Oracle Recyclebin 物件垃圾桶 (for 10g)

在 Oracle Database 10g 有一個功能: Flashback Table,

也就是當你 Table 不小心 drop 時,

還是有機會到 Recyclebin 把它救回來,

詳細做法, 請參閱 Oracle 這篇文章 : http://www.oracle.com/technology/pub/articles/10gdba/week5_10gdba.html.

昭佑.天翔

Oracle Database 使用 Advisor 了解 SQL Tuning 方向

以前,

公司曾經購買過 SQL Expert 來做 SQL Tuning,

但是它是根據 Database 現有環境進行 Tuning,

若你缺少 Index, 它不會告訴你要建 Index,

若你需做 Analyze, 它也不會告訴你,

總之, 不好用也不符合實際需求,

現在,

在 Oracle Database 10g 以上的版本,

Oracle DB 已內建非常好用的 SQL Tuning Advisor,

透過簡單的 dbms_sqltune 套件,

Oracle 會建議你 SQL Tuning 方向 (改善 Database 環境),

如: 建立 Index or Analyze ... 等

範例步驟, 如下 :

 建立與執行 Oracle SQL Tuning 任務

declare
  vTaskID  varchar2(255);
  vSQL     varchar2(1000);
begin
  -- 要進行 SQL Tuning 的語法
  vSQL := 'select ujc.ip_address'
       || '     , ujl.action_object'
       || '     , ujl.action_status'
       || '  from uf_javaftp_control ujc'
       || '     , uf_javaftp_log     ujl'
       || ' where ujc.ip_address = ujl.ip_address';
  
  -- 建立 Oracle SQL Tuning 任務
  vTaskID := dbms_sqltune.create_tuning_task(
             sql_text => vSQL
           , user_name => 'APPS'       -- 用哪個 DB Schema 進行 SQL Tuning
           , scope => 'COMPREHENSIVE'  -- Tuning 範圍
           , task_name => 'tomTuning'  -- Tuning 任務名稱, 隨便給一個
           );
  
  -- 執行 Oracle SQL Tuning 任務
  dbms_sqltune.execute_tuning_task( 'tomTuning' );
end;

 查看某個 Tuning Task 狀態

select status
  from dba_advisor_log
 where owner = 'APPS'
   and task_name = 'tomTuning';

 查看 Oracle SQL Tuning 結果

select dbms_sqltune.report_tuning_task( 'tomTuning' )
  from dual;
 
-- 查看結果:
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                  : tomTuning
Tuning Task Owner                 : APPS
Scope                             : COMPREHENSIVE
Time Limit(seconds)               : 1800
Completion Status                 : COMPLETED
Started at                        : 02/08/2010 10:37:44
Completed at                      : 02/08/2010 10:37:44
Number of Index Findings          : 1
 
-------------------------------------------------------------------------------
Schema Name: APPS
SQL ID     : 2cc2vmca5jjsy
SQL Text   : select ujc.ip_address     , ujl.action_object     ,
             ujl.action_status  from uf_javaftp_control ujc     ,
             uf_javaftp_log ujl where ujc.ip_address = ujl.ip_address
 
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
 
1- Index Finding (see explain plans section below)
--------------------------------------------------
 此敘述句的執行計劃可以藉由
 
  Recommendation (estimated benefit: 100%)
  ----------------------------------------
  - 考慮執行「存取建議程式」來改進實
    create index APPS.IDX$$_01280001 on APPS.UF_JAVAFTP_CONTROL('IP_ADDRESS');
 
  Rationale
  ---------
建立建議的索引可大幅改進此敘述句的執行計劃. 然而, 最好是使用代表性的 SQL 
這將可獲得廣泛的索引建議 (將索引維護成 ...

 刪除 Tuning 任務

begin
  dbms_sqltune.drop_tuning_task( 'tomTuning' );
end;

最後, 程式邏輯引起的效能問題, 這部分還是得靠自己.

以上, 希望對各位有所幫助.

昭佑.天翔

Oracle Database Hint

當 Oracle DB 本身在 parse Select 語法時, 有時會判斷錯誤,

如: 該用 Index 時卻沒有使用

如: 該用 Index1, 卻用到 Index2

這時候, 利用 Hint, 也就是 /*+ ... */, 來強制指定要使用哪種方式處理 Select 語法,

 強制指定 Index

select /*+ INDX (tomTable tomIndex1) */
       col1
     , col2
     , col3
  from tomTable
 where ...

 強制指定查詢方式

select /*+ RULE */
       col1
     , col2
     , col3
  from tomTable
 where ...

透過這種方式, 有時也可改善 Select 查詢效能喔.

Oracle 刪除 Table 資料, Tablespace 空間是否會減少

在 Oracle Database 中,

Delete Table 資料, 雖然資料沒有了, 但其實 Tablespace 空間並不會減少,

若要真正減少空間, 必須做 Reorg,

也就是先 Export Table, 再把原先 Table Drop 掉, 再 Import Table 進來,

這樣 Tablespace 才會縮減.

Oracle Character Set Migration 資料庫字元集轉換

幾篇關於 Oracle Character Set Migration 不錯的文章,

在此分享一下 :

Oracle Character Set Migration - PDF 檔 :
http://www.oracle.com/technology/tech/globalization/pdf/TWP_Character_Set_Migration_Best_Practices_10gR2.pdf

Oracle Character Set Migration - 網頁 :
http://download.oracle.com/docs/cd/B10501_01/server.920/a96529/ch10.htm

Oracle Character Set Migration using CSSCAN and CSALTER 網頁 :
http://www.oracle-base.com/articles/10g/CharacterSetMigration.php

希望對各位有所幫助.