[Oracle]ORA-01034: ORACLE not available

     話說把Oracle移轉到新的server之後，為了讓client可以不要調整，所以得把新的server的hostname與ip改成跟之前的一樣，就在同事調完hostname與ip重開Server後，我打算啟動Oracle，結果剛進sqlplus就遇到下圖的錯誤

ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0

[Oracle優化]CPU使用率突然飆高了！

        某天管理的Oracle，它的CPU使用率突然飆高了一倍，平常使用率很低的，所以飆高了一倍還好。對一個穩定的系統來講，CCU沒有多一倍，CPU卻飆高一倍是很奇怪的，而且接下公司有辦活動，要是CCU暴增就不敢保證不會有問題了，所以要想辦法找出什麼原因造成CPU飆高

        首先看飆高當天的AWR，下圖是Top 5 Timed Foreground Events，第一名的DB CPU約佔73%，第二名的library cache: mutex X只佔10%左右，這還算正常

[SQL Server]Linked Server到Oracle時，出現'To_Date' is not a recognized built-in function name的解法

       用連結伺服器連到Oracle要取資料，因為有日期時間的過濾條件，在Oracle需要用To_Date函數，但此函數無法通過SQL Server語法剖析的檢查，會出如下的錯誤

Select * From LK_ORA..HR.EMPLOYEES Where  Login_Date >= To_Date('2015-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss') And Login_Date < TO_DATE('2015-05-02 00:00:00','yyyy-mm-dd hh24:mi:ss');

       解法一：在Oracle建View，在SQL端直接讀View，但多了道建View的手續

       解法二：在SQL端改用Exec，比較簡單，範例如下

EXEC ('Select * From HR.EMPLOYEES Where  Login_Date >= To_Date(''2015-05-01 00:00:00'',''yyyy-mm-dd hh24:mi:ss'') And Login_Date < TO_DATE(''2015-05-02 00:00:00'',''yyyy-mm-dd hh24:mi:ss'')') at LK_ORA;

[Oracle]分批刪除資料

        一次取得所有待刪除資料的ROWID，然後分批小量刪除資料

Set Serveroutput On;
DECLARE
  V_DATE DATE;
  Type V_Rowid Is Table Of Varchar2(100) Index By Binary_Integer;
  Var_Rowid V_Rowid;        
  Cursor V_Cur Is Select /*+parallel(a,2)*/Rowid From Hr.Employee Where LoginDate <= V_Date;

Begin
  V_Date := To_Date(To_Char(Trunc(Sysdate-60),'YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD HH24:MI:SS');

  Open V_Cur ;

  Loop
  Fetch V_Cur Bulk Collect
          Into Var_Rowid Limit 10000;
  Forall I In 1 .. Var_Rowid.Count
  Delete From Hr.Employee  Where  Rowid =Var_Rowid(I);
  Commit;
Exit When V_Cur%Notfound Or V_Cur%Notfound Is Null;
End Loop;

Close V_Cur;

End;

[Oracle]如何在SQLPlus格式化輸出成CSV

        SQLPlus預設環境下有很多System Variable，如果沒特別去設定，用SPOOL輸出到文字檔時，可能會看到一些不乾淨資料，像是表頭、回傳筆數及統計時間等等的

        如下圖，配合一些System Variable設定就可以不顯示了

[SQL Server][Oracle][MySQL][PostgreSQL] IN條件式寫法的差異

        一般找某個資料表的資料是否存在另一個資料表中，就語法上來說，可以用INNER JOIN，若條件式僅JOIN單一欄位，可以改成用IN或EXISTS；若是JOIN多個欄位，就無法用IN囉，這是在SQL Server的情況

        無意間看到在Oracle中，IN條件式的寫法裡竟然可以接受多個欄位呢，另外試了MySQL與PostgreSQL也都可以喔

[Oracle]With Clase與SQL Server的CTE用法不太一樣喔，要注意！

        SQL Server的With子句叫CTE，可以說是暫存的結果集，讓語法看器來更簡潔，可以Reuse結果；Oracle也有With子句，叫Subquery Factoring，我以為用法跟SQL Server一樣用，實際上有滿大的差異，今天花點時間研究一下，原來Oracle可以把with子句當inline view(在FORM裡的子查詢)或temporary table來處理喔，很不一樣吧

        據說Oracle會自行判斷with子句當inline view或temporary table處理，讓它自行判斷往往會發生意想不到的結果，可以使用兩個undocumented materialize hint與inline hint來指定用哪種囉

[Oracle]Rman備份時利用Rate參數限制IO流量

        話說用Rman備份資料庫時，開兩個Channel備份，雖然備份時間縮短了，但那段時間的IO好高，雖沒人反映那段時間系統運作很慢，我還是想想有沒有限流的方法，結果Channel那邊本身就有參數支援囉。

        Rate參數是用來限制IO Bandwith/sec的，下面例子限制為10M/s

run { allocate channel t1 type disk rate 10M; CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED ARCHIVELOG ALL ; backup full format '/backup/rman_backup/db_%T_%u_%s_%p' database include current controlfile; sql 'alter system archive log current'; backup format '/backup/rman_backup/archive_%T_%u_%s_%p' archivelog all delete input; delete noprompt obsolete; crosscheck backup; Release Channel t1; }

[Oracle]Partition Table的維護

        如果分區表先天設計不良，後天要改又很麻煩，造成清除逾期資料無法用truncate partition來處理，只能用delete處理逾期資料，那會因為HWM高水位的問題造成資料表占用空間不斷成長，可以參考以下命令來針對partition table與partition index做搬移與重建，就可以釋放空間囉

--alter table TABLE_NAME  truncate partition PARTITION_NAME;

--查看資料表佔用空間

select SUM(BYTES)/1024/1024 SIZE_MB from DBA_SEGMENTS where SEGMENT_NAME='TABLE_NAME';

--查看索引佔用空間

select sum(bytes)/1024/1024 SIZE_MB from DBA_segments where segment_name IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME='TABLE_NAME');

--產生搬移分區表命令

--alter table TABLE_NAME move  partition PARTITION_NAME

SELECT 'alter table ' || table_name || ' move  partition ' || partition_name　from DBA_TAB_PARTITIONS WHERE TABLE_NAME= 'TABLE_NAME';

--因為搬移完分區表，分區索引會失效，所以要重建喔
--產生重建分區索引命令 

--alter index INDEX_NAME rebuild partition PARTITION_NAME TABLESPACE TABLESPACE_NAME

SELECT 'alter index '|| index_name ||'  rebuild partition  ' || partition_name　|| ' TABLESPACE ' || tablespace_name  from DBA_Ind_Partitions WHERE  index_name in (select index_name from dba_part_indexes where table_name = 'TABLE_NAME');

--檢查是否有無效的索引分區

SELECT *　from DBA_Ind_Partitions WHERE  index_name in (select index_name from dba_part_indexes where table_name = 'TABLE_NAME') and status = 'UNUSABLE';

[Oracle]ORA-00923: FROM keyword not found where expected

執行下列語句

SELECT *,  ROW_NUMBER() OVER (ORDER BY LOG_DATE ) AS RN FROM TEMP3;

返回如下錯誤

ORA-00923: FROM keyword not found where expected

猜猜看哪裡錯？

[轉貼]免費資料庫學習影片

        有一些免費資料庫教學影片喔，請造訪51CTO學院吧，不只SQL Server還有Oracle與MySQL呢！

在SQL Server上建立連結伺服器到Oracle

        方法超簡單，首先要SQL Server上安裝ODAC，裝完後記得重開機

        然後展開Linked Servers的Providers，應該要有OraOLEDB.Oracle喔，如上圖

[SSRS][Oracle]Memory Usage Report

        接著分享Memory Usage Report

[MS SQL][Oracle][MySQL][PostgreSQL]字串相連

        雖然字串相連是很簡單的東西，但不同DB卻是有差異的喔，整理目前會用到的，供參考

[SSRS][Oracle]Tablespace Usage Report

        話說小弟我打字不快，但我又想有效率的管理DB，那怎麼做呢？就是把日常對DB作檢查的Script客製成自己想要的報表就好啦，只要用滑鼠點點點就可以看我想看的資訊，這樣是不是輕鬆很多？

        客製自己想要的報表，好處之一是可以滿足自己的需求，好處之二就是你得要求自己去研究那些資訊要如何取得，從中也順便對DB有進一步的了解，也是自學的好方法之一！

        因為小弟對MS SQL較熟悉，所以就想到用Reporting Services 2008 R2作為我報表呈現的工具，來去取得Oracle的資料，Reporting Services可以呈現精美的報表，也有訂閱的功能可以把報表送出，很方便喔

        以下就先分享Tablespace Usage的報表

[Rman]將備份檔還原到異機

        假設要將QA1的Rman備份還原到QA2，首先在QA1用Rman完整備份，記得archivelog與controlfile都要備份，命令如下

RMAN>run { allocate channel t1 type disk; allocate channel t2 type disk; CROSSCHECK ARCHIVELOG ALL; DELETE NOPROMPT EXPIRED ARCHIVELOG ALL ; backup full format '/home/oracle/rman_backup/db_%T_%u_%s_%p' database include current controlfile; sql 'alter system archive log current'; backup format '/home/oracle/rman_backup/archive_%T_%u_%s_%p' archivelog all delete input; delete noprompt obsolete; crosscheck backup; Release Channel t1; Release Channel t2; }

[PLSQL]當Tablespace的剩餘空間不足時，自動增加Datafile

        因為工作需要用Oracle，小弟只好自學了，因為剛開始不熟所以原廠說什就配合什，那時原廠DBA說要時常監控tablespace的剩餘空間，最好兩個小時就check一次，如果小於2GB時，就要主動增加datafile，避免單一datafile自動增長超過4G，那時想說Oracle怎麼那麼麻煩，不就一開始估計會長多大，就先分配好足夠的空間就好嗎？

        正因為不熟所以只好傻傻地照做，想說寫個Procedure來幫我做這件事好了，於是就產生的這個Procedure囉，但後來還是沒用到，但還是放上來給有需要的人參考，不過話說PLSQL跟T-SQL差好多啊

用Reporting Services 2008 R2連接Oracle

        最近打算用Reporting Services 2008 R2連接Oracle 11gR2，光測試到底要安裝哪個ODAC就快瘋掉，有夠複雜，因為我是先在VM上測試，OS是裝windows server 2008 r2 x64，打算開發與RS都在同一台上，但發現這樣行不通，似乎得拆開，因為開發用Visual Studio 2008是x32的，而RS是x64的，這會影響ODAC的版本，若全都裝在同一台，容易出錯的

安裝Oracle Instant Client與SQLPlus在Ubuntu上

        話說現在開始要學另外三種資料庫，真是很難消化啊

        最近在裝Oracle Instant Client就遇到很多問題，在Win上可以裝x32與x64的版本，在試Toad for Oracle時，又看到只支援x32版client的訊息，新版的好像可以支援x64，裝來裝去兩個都裝就出問題了，Linux反而比較單純

       裝完Client後，最基本連Oracle的工具就是SQL*Plus，我嘗試在Ubuntu上安裝也是花好多時間，原因就是網路上很多文章都誤導了我，連Oracle Database Online Documentation的說明也是不清不楚，像是SQL*Plus在Linux有分x32與x64版本，實際用起來有什差別嗎？你去網路上查看看差在哪裡，幾乎99%命令列都是打sqlplus，問題x64的實際上要打sqlplus64耶，這差很多的，害我明明裝好x64了，卻一直試著執行sqlplus，卻一直出錯說找不到命令，百思不解為何？以為是environment variable出錯，還是權限不足，怎麼try都不行，其實打sqlplus64就OK了，真是浪費我一堆時間（其實真正原因是我不熟啦...還怪人）

       在Ubuntu上的裝法如下，以下是我剛裝完Ubuntu後，只花十分鐘就裝好SQLPlus用的命令了，供各位參考