Oracle eBS經驗分享

NVL進階版函式

select Coalesce('C','B','A') from dual  ＝＝> C

select Coalesce(null,'B','A') from dual  ＝＝> B

select Coalesce(null,null,'A') from dual  ＝＝> A

監控資料庫效能

SELECT   s.username
       , s.SID
       , s.serial#
       , s.machine
       , TO_CHAR (sl.start_time, 'mm/dd/yyyy hh24:mi:ss') start_time
       , s.osuser
       , s.module
       , s.program
       , s.client_info
       , ROUND (sl.elapsed_seconds / 60) || ':'
         || MOD (sl.elapsed_seconds, 60) elapsed
       , ROUND (sl.time_remaining / 60) || ':' || MOD (sl.time_remaining, 60)
                                                                    remaining
       , ROUND (sl.sofar / sl.totalwork * 100, 2) progress_pct
       , sl.MESSAGE
    FROM v$session s
       , v$session_longops sl
   WHERE s.SID = sl.SID
     AND s.serial# = sl.serial#
     AND sl.time_remaining > 0
ORDER BY progress_pct DESC;

 SELECT *
  FROM wf_item_activity_statuses wias, wf_process_activities wpa
  WHERE wias.process_activity = wpa.instance_id
-- AND   wpa.activity_name = 'PURCHASE RELEASE ELIGIBLE'
  AND   wias.item_type = 'OEOL'
  AND   wias.item_key  = to_char(2369510) -- order line id
-- AND   wias.activity_status = 'NOTIFIED';

--目前資料庫的語系
SELECT *
  FROM nls_database_parameters
 WHERE parameter = 'NLS_CHARACTERSET';

--查詢目前資料庫名稱
SELECT NAME
  FROM v$database;