2016年12月19日星期一

人生非常奇妙

2012年時開始學習MS SQL，
後來工作上使用到的卻是Oracle、Sybase、Mysql。

正當我想要在Oracle上努力前進，並且考到證照時，
找上門的工作卻是用SQL SERVER為主的工作。

雖然在去找Oracle的工作也不是太難的選項，不過SQL SERVER熟悉些也不錯。
給自己未來多一些選項並不壞。
更何況SQL SERVER2016來勢洶洶，而這家公司用的也是最新的SQL SERVER2016。
命運或許是要讓我看看SQL SERVER的魅力。

好多前輩都說我現在還年輕...
那就試著趁年輕的時候多接觸些吧。

繼續練功~~

2015年2月2日星期一

[Windows]刪除舊檔案+建立今日資料夾+WinSCP抓取Linux檔案到Windows

需要準備三個檔案：
A.WinSCP安裝擋
B.Windows Batch檔
C.WinSCP指令檔

步驟如下：
一、安裝WinSCP
二、撰寫Windows Batch檔，設定排程
三、將WinSCP指令檔放到WinSCP目錄下

實作如下：
1.WinSCP完成安裝

2.Windows Batch檔設定內容
----------------------------deleteOld&downloadNew.bat---------------------------------------
#刪除30天前的舊檔
forfiles /p "C:\Temp" /s /m *.* /d -30 /c "cmd /c del @path"

#設定今日日期，並創建資料夾
cd C:\Temp
SET _date=%date:~0,4%%date:~5,2%%date:~8,2%
md %_date%

c:
cd C:\Progra~1\WinSCP\
WinSCP.com /script=deleteOld&downloadNew.txt
------------------------------------------------------------------------

3.設定WinSCP檔案，將WinSCP指令檔放入WinSCP安裝目錄
-------------------deleteOld&downloadNew.txt--------------------------
#設定今日日期
SET _date=%date:~0,4%%date:~5,2%%date:~8,2%

#透過ftp協定抓取Linux檔案
option batch abort
option confirm off
open ftp://user:password@10.1.1.1
option transfer binary
get /temp C:\Temp\%_date%\
close
exit
---------------------------------------------------------------------------------

先紀錄，有空在整理。

2014年12月18日星期四

Oracle 11g使用exp匯出資料時，不會導出空表。

在轉資料的時候發生exp匯出資料時，不會導出空表。

使用exp/imp轉資料時，
從別的地方轉回來發現程式不能RUN，找了好久才知道原因。

Oracle 11g中，空的表格在exp指令中，為了節省空間並不會匯出表格。
為了解決這個問題，要將table的屬性改掉，才能順利匯出

從別的網站上找到的方法：
select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0;
以上的語句會匯出更改table屬性的語法，
執行那些語法就能夠順利將table匯出了！

另外，為了一勞永逸解決這些問題，記得更改系統參數。
alter system set deferred_segment_creation=false scope=both;

更改之後，新創立的table使用exp指令也可以輸出了。

參考網頁：
http://wanwentao.blog.51cto.com/2406488/545154
http://bloodsucker.pixnet.net/blog/post/40267539-oracle-11g-r2-%E9%82%A3-exp-imp-%E9%99%B7%E9%98%B1%E5%95%8F%E9%A1%8C

2014年10月31日星期五

PIVOT and UNPIVOT

來源：http://oracle-base.com/articles/11g/pivot-and-unpivot-operators-11gr1.php

工作上用到，現在忙，有空再回來翻譯。

PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1

This article shows how to use the new PIVOT and UNPIVOT operators in 11g, as well as giving a pre-11g solution to the same problems.

PIVOT

The PIVOT operator takes data in separate rows, aggregates it and converts it into columns. To see thePIVOT operator in action we need to create a test table.

CREATE TABLE pivot_test (
  id            NUMBER,
  customer_id   NUMBER,
  product_code  VARCHAR2(5),
  quantity      NUMBER
);

INSERT INTO pivot_test VALUES (1, 1, 'A', 10);
INSERT INTO pivot_test VALUES (2, 1, 'B', 20);
INSERT INTO pivot_test VALUES (3, 1, 'C', 30);
INSERT INTO pivot_test VALUES (4, 2, 'A', 40);
INSERT INTO pivot_test VALUES (5, 2, 'C', 50);
INSERT INTO pivot_test VALUES (6, 3, 'A', 60);
INSERT INTO pivot_test VALUES (7, 3, 'B', 70);
INSERT INTO pivot_test VALUES (8, 3, 'C', 80);
INSERT INTO pivot_test VALUES (9, 3, 'D', 90);
INSERT INTO pivot_test VALUES (10, 4, 'A', 100);
COMMIT;

So our test data starts off looking like this.

SELECT * FROM pivot_test;

        ID CUSTOMER_ID PRODU   QUANTITY
---------- ----------- ----- ----------
         1           1 A             10
         2           1 B             20
         3           1 C             30
         4           2 A             40
         5           2 C             50
         6           3 A             60
         7           3 B             70
         8           3 C             80
         9           3 D             90
        10           4 A            100

10 rows selected.

SQL>

In its basic form the PIVOT operator is quite limited. We are forced to list the required values to PIVOT using the IN clause.

SELECT *
FROM   (SELECT product_code, quantity
        FROM   pivot_test)
PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c));

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
           210             90            160

1 row selected.

SQL>

If we want to break it down by customer, we simply include the CUSTOMER_ID column in the initial select list.

SELECT *
FROM   (SELECT customer_id, product_code, quantity
        FROM   pivot_test)
PIVOT  (SUM(quantity) AS sum_quantity FOR (product_code) IN ('A' AS a, 'B' AS b, 'C' AS c))
ORDER BY customer_id;

CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
          1             10             20             30
          2             40                            50
          3             60             70             80
          4            100

4 rows selected.

SQL>

Prior to 11g we could accomplish a similar result using the DECODE function combined with aggregate functions.

SELECT SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity,
       SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity,
       SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity
FROM   pivot_test
ORDER BY customer_id;

A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
-------------- -------------- --------------
           210             90            160

1 row selected.

SQL>

SELECT customer_id,
       SUM(DECODE(product_code, 'A', quantity, 0)) AS a_sum_quantity,
       SUM(DECODE(product_code, 'B', quantity, 0)) AS b_sum_quantity,
       SUM(DECODE(product_code, 'C', quantity, 0)) AS c_sum_quantity
FROM   pivot_test
GROUP BY customer_id
ORDER BY customer_id;

CUSTOMER_ID A_SUM_QUANTITY B_SUM_QUANTITY C_SUM_QUANTITY
----------- -------------- -------------- --------------
          1             10             20             30
          2             40              0             50
          3             60             70             80
          4            100              0              0

4 rows selected.

SQL>

Adding the XML keyword to the PIVOT operator allows us to convert the generated pivot results to XML format. It also makes the PIVOT a little more flexible, allowing us to replace the hard coded IN clause with a subquery, or the ANY wildcard.

SET LONG 10000

SELECT *
FROM   (SELECT product_code, quantity
        FROM   pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code 
                                                                FROM   pivot_test
                                                                WHERE  id < 10));

product_code_XML
----------------------------------------------------------------------------------------------------
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">210</column></
item><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">90</column></item><
item><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">160</column></item><item>
<column name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY">90</column></item></PivotSet>

1 row selected.

SQL>

SELECT *
FROM   (SELECT product_code, quantity
        FROM   pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (ANY));

product_code_XML
----------------------------------------------------------------------------------------------------
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">210</column></
item><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">90</column></item><
item><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">160</column></item><item>
<column name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY">90</column></item></PivotSet>

1 row selected.

SQL>

Once again, the results can be broken down by customer, with each customers XML presented as a separate row.

SET LONG 10000

SELECT *
FROM   (SELECT customer_id, product_code, quantity
        FROM   pivot_test)
PIVOT XML (SUM(quantity) AS sum_quantity FOR (product_code) IN (SELECT DISTINCT product_code 
                                                                FROM   pivot_test));

CUSTOMER_ID
-----------
PRODUCT_CODE_XML
----------------------------------------------------------------------------------------------------
          1
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">10</column></i
tem><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">20</column></item><i
tem><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">30</column></item><item><c
olumn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY"></column></item></PivotSet>

          2
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">40</column></i
tem><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY"></column></item><ite

CUSTOMER_ID
-----------
PRODUCT_CODE_XML
----------------------------------------------------------------------------------------------------
m><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">50</column></item><item><col
umn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY"></column></item></PivotSet>

          3
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">60</column></i
tem><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY">70</column></item><i
tem><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY">80</column></item><item><c
olumn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY">90</column></item></PivotSet>


CUSTOMER_ID
-----------
PRODUCT_CODE_XML
----------------------------------------------------------------------------------------------------
          4
<PivotSet><item><column name = "PRODUCT_CODE">A</column><column name = "SUM_QUANTITY">100</column></
item><item><column name = "PRODUCT_CODE">B</column><column name = "SUM_QUANTITY"></column></item><it
em><column name = "PRODUCT_CODE">C</column><column name = "SUM_QUANTITY"></column></item><item><colu
mn name = "PRODUCT_CODE">D</column><column name = "SUM_QUANTITY"></column></item></PivotSet>


4 rows selected.

SQL>

UNPIVOT

The UNPIVOT operator converts column-based data into separate rows. To see the UNPIVOT operator in action we need to create a test table.

CREATE TABLE unpivot_test (
  id              NUMBER,
  customer_id     NUMBER,
  product_code_a  NUMBER,
  product_code_b  NUMBER,
  product_code_c  NUMBER,
  product_code_d  NUMBER
);

INSERT INTO unpivot_test VALUES (1, 101, 10, 20, 30, NULL);
INSERT INTO unpivot_test VALUES (2, 102, 40, NULL, 50, NULL);
INSERT INTO unpivot_test VALUES (3, 103, 60, 70, 80, 90);
INSERT INTO unpivot_test VALUES (4, 104, 100, NULL, NULL, NULL);
COMMIT;

So our test data starts off looking like this.

SELECT * FROM unpivot_test;

        ID CUSTOMER_ID PRODUCT_CODE_A PRODUCT_CODE_B PRODUCT_CODE_C PRODUCT_CODE_D
---------- ----------- -------------- -------------- -------------- --------------
         1         101             10             20             30
         2         102             40                            50
         3         103             60             70             80             90
         4         104            100

4 rows selected.

SQL>

The UNPIVOT operator converts this column-based data into individual rows.

SELECT *
FROM   unpivot_test
UNPIVOT (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D'));

        ID CUSTOMER_ID P   QUANTITY
---------- ----------- - ----------
         1         101 A         10
         1         101 B         20
         1         101 C         30
         2         102 A         40
         2         102 C         50
         3         103 A         60
         3         103 B         70
         3         103 C         80
         3         103 D         90
         4         104 A        100

10 rows selected.

SQL>

There are several things to note about the query:

The required column names, in this case QUANTITY and PRODUCT_CODE, are define in the UNPIVOT clause. These can be set to any name not currently in the driving table.
The columns to be unpivoted must be named in the IN clause.
The PRODUCT_CODE value will match the column name it is derived from, unless you alias it to another value.
By default the EXCLUDE NULLS clause is used. To override the default behaviour use the INCLUDE NULLS clause.

The following query shows the inclusion of the INCLUDE NULLS clause.

SELECT *
FROM   unpivot_test
UNPIVOT INCLUDE NULLS (quantity FOR product_code IN (product_code_a AS 'A', product_code_b AS 'B', product_code_c AS 'C', product_code_d AS 'D'));

        ID CUSTOMER_ID P   QUANTITY
---------- ----------- - ----------
         1         101 A         10
         1         101 B         20
         1         101 C         30
         1         101 D
         2         102 A         40
         2         102 B
         2         102 C         50
         2         102 D
         3         103 A         60
         3         103 B         70
         3         103 C         80

        ID CUSTOMER_ID P   QUANTITY
---------- ----------- - ----------
         3         103 D         90
         4         104 A        100
         4         104 B
         4         104 C
         4         104 D

16 rows selected.

SQL>

Prior to 11g, we can get the same result using the DECODE function and a pivot table with the correct number of rows. In the following example we use the CONNECT BY clause in a query from dual to generate the correct number of rows for the unpivot operation.

SELECT id,
       customer_id,
       DECODE(unpivot_row, 1, 'A',
                           2, 'B',
                           3, 'C',
                           4, 'D',
                           'N/A') AS product_code,
       DECODE(unpivot_row, 1, product_code_a,
                           2, product_code_b,
                           3, product_code_c,
                           4, product_code_d,
                           'N/A') AS quantity
FROM   unpivot_test,
       (SELECT level AS unpivot_row FROM dual CONNECT BY level <= 4)
ORDER BY 1,2,3;

        ID CUSTOMER_ID PRO   QUANTITY
---------- ----------- --- ----------
         1         101 A           10
         1         101 B           20
         1         101 C           30
         1         101 D
         2         102 A           40
         2         102 B
         2         102 C           50
         2         102 D
         3         103 A           60
         3         103 B           70
         3         103 C           80

        ID CUSTOMER_ID PRO   QUANTITY
---------- ----------- --- ----------
         3         103 D           90
         4         104 A          100
         4         104 B
         4         104 C
         4         104 D

16 rows selected.

SQL>

2014年10月30日星期四

[SYBASE]BackupServer不正常關閉，無法開啟backupserver

Sybase在進行backup的時候，
NFS硬碟網路斷線，導致Sybase卡住無法使用。

於是我先進入isql將backup server關閉，讓使用者可以正常使用，
>shutdown SYB_BACKUP

使用者可以正常使用了，但是接下來卻發生問題：
backup server沒有正常關閉。
導致backup server變成一種開不起來也關不掉的情形。

此時使用sybase使用者，進入Linux的Sybase環境，
root> su - sybase
sybase> showserver

檢查backupserver是否關閉，若是還沒關閉就直接關起來。
以這次的例子，backupserver沒有出現在showserver中，表示已關閉。
於是檢查是否有backup程式還在跑
sybase> su - root
root> ps -ef | grep back

檢查後發現有很多.lo的程式還在跑，
那些是backup用的檔案，把她們關閉。

root> kill -9 [pid]

接著就可以正常開啟backupserver了！

2014年9月11日星期四

[轉貼文章]SQL優化過程中常見Oracle HINT的用法

來源：http://oracled2k.pixnet.net/blog/post/21722059-sql%E5%84%AA%E5%8C%96%E9%81%8E%E7%A8%8B%E4%B8%AD%E5%B8%B8%E8%A6%8Boracle-hint%E7%9A%84%E7%94%A8%E6%B3%95-

在SQL語句優化過程中，我們經常會用到hint,現總結一下在SQL優化過程中常見Oracle HINT的用法：

1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

3. /*+CHOOSE*/
表明如果數據字典中有訪問表的統計信息,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計信息,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

4. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';

5. /*+FULL(TABLE)*/
表明對錶選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';

6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'
AND EMP_NO='SCOTT';

7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對像有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO='TEC304' AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX='M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO='SCOTT';

10. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 emp_no="SCOTT" sex="M" dpt_no="V.DPT_NO">V.AVG_SAL;

20. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

21. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

22. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

24. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過哈希連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

25. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

26. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.

27. /*+CACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

28. /*+NOCACHE(TABLE)*/
當進行全表掃描時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

29. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;

30. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;

2014年8月25日星期一

上PATCH時，遇到WORKER錯誤

到以下的位址去找出WORKER的LOG，檢查哪裡出錯。

cd $APPL_TOP/admin/<資料庫SID>/log

訂閱：文章 (Atom)

2016年12月19日 星期一

2015年2月2日 星期一

2014年12月18日 星期四

2014年10月31日 星期五