"LISTENER", Reception คนเก่ง

โพสต์ครั้งแรก: 3 ธันวาคม 2011
บทความที่แล้วมีเพื่อนสมาชิกติงมาว่า SYSDATE แท้จริงแล้วคือฟังก์ชั่น ไม่ใช่ Pseudo Column ต้องขอขอบคุณด้วยนะครับสำหรับการท้วงติง ; ) สัปดาห์นี้เราลองมาดูเกี่ยวกับเรื่อง Listener กันดูบ้างนะครับ

ปกติเวลาเราไปติดต่อที่องค์กรใด ๆ เรามักจะพบกับพนักงานหนึ่งหรือหลายคนที่เคาน์เตอร์ที่เห็นได้ง่ายที่สุด พนักงานเหล่านี้มักจะมีหน้าตาน่ารักหรือไม่ก็ดูดีในระดับหนึ่ง และจะคอยแนะนำเราให้รู้ว่าเราจะต้องไปติดต่อที่ไหน ฯลฯ เราเรียกพนักงานเหล่านี้ว่า ประชาสัมพันธ์บ้าง รีเซฟชั่นบ้าง หรือพนักงานต้อนรับบ้าง

ระบบฐานข้อมูล Oracle หนึ่ง ๆ ก็มีประชาสัมพันธ์นี้เช่นกัน ต่างแต่เราเรียกประชาสัมพันธ์นี้ว่า Listener ซึ่งแปลตรงตัวว่า "ผู้คอยฟัง" หน้าที่ของเขาคือพาเอาผู้มาติดต่อได้เชื่อมต่อกับฐานข้อมูล และเมื่อเชื่อมต่อกันได้จนเกิดเป็น Session แล้วก็เป็นอันหมดหน้าที่ของ Listener

ผู้ที่ต้องการเชื่อมต่อจะติดต่อกับฐานข้อมูลผ่านระบบเครือข่ายเท่านั้นที่จำเป็นต้องได้ความช่วยเหลือจาก Listener เราสามารถเปิดฐานข้อมูลและทำงานกับมันได้โดยไม่ต้องเปิด Listener เลยก็ได้ ตราบใดที่เราเชื่อมต่อกับฐานข้อมูลผ่านโปรแกรมใด ๆ บนเครื่องที่ติดตั้งฐานข้อมูลนั้นไว้ ดังตัวอย่าง

บนเครื่อง Notebook ของผมมีฐานข้อมูล ORCL และมี Listener ON อยู่ และผมได้สร้าง Net Service ชื่อ ORCL เพื่อใช้ connect เข้าฐานข้อมูลในเครื่องเดียวกันผ่าน Listener
เราจะลองเช็คดูว่า Listener On อยู่

C:\> lsnrctl status
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-NOV-2011 18:26:32
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xp-orcl102)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENERVersion                   TNSLSNR for 32-bit Windows:
Version 10.2.0.1.0 - Production
Start Date                24-OCT-2011 13:30:21
Uptime                    32 days 4 hr. 56 min. 11 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\oracle\product.2.0\db_1\network\admin\listener.ora
Listener Log File         C:\oracle\product.2.0\db_1\network\log\listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=xp-orcl102)(PORT=1521)))
Services Summary...
Service "orcl.world" has 2 instance(s).
Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB.world" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orcl_XPT.world" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

ผมจะลองล็อกอินเข้าฐานข้อมูลโดยผ่าน Net Service (ซึ่งจะติดต่อกับ Listener) ซึ่งผมทำได้สำเร็จดังข้างล่าง

C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 25 19:04:15 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL>

คราวนี้ผมจะปิด Listener แล้วลองเชื่อมต่อโดยใช้ Net Service อีกครั้ง

C:\>lsnrctl stop
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 25-NOV-2011 19:07:57
Copyright (c) 1991, 2005, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xp-orcl102)(PORT=1521)))
The command completed successfully

C:\>sqlplus scott/tiger@orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 25 19:08:05 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
ERROR:ORA-12541: TNS:no listener

Enter user-name: scott/tiger
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining options
SQL>

จะเห็นว่าเมื่อเราปิด Listener เราจะไม่สามารถเชื่อมต่อกับฐานข้อมูลผ่านทาง Net Service (หรืออีกนัยหนึ่งผ่าน Listener) ซึ่งกรณีเดียวกันถ้าเราเชื่อมต่อจากเครื่อง Client เราก็จะพบปัญหาเดียวกัน อย่างไรก็ตามเรายังคงสามารถเชื่อมต่อกับระบบฐานข้อมูลได้ หากเราไม่ได้ระบุ Net Service ซึ่งเป็นการเชื่อมต่อแบบ Local 

ถ้าเราติดตั้งฐานข้อมูล Oracle ไว้หลาย Instance บนเครื่องของเรา เมื่อเราต้องการเชื่อมต่อกับฐานข้อมูลบนเครื่องโดยไม่ผ่าน Listener อย่าลืม Set Default SID ที่จะเข้าสู่ฐานข้อมูล ไม่เช่นนั้นเราอาจจะพบความประหลาดใจเมื่อมันเชื่อมต่อไปฐานข้อมูลอีกตัวหนึ่งแทน ซึ่งถ้าฐานข้อมูลตัวอื่นนั้นไม่ได้เปิดอยู่คุณก็จะเจอ Error คุณสามารถ Set Default SID ได้ดังนี้
บน Wndows (ตัวใหญ่เล็กไม่สำคัญ)
C:\> set oracle_sid=orcl
บน Unix (พิมพ์ตัวใหญ่เล็กให้ถูก)
-bash-3.00$ export ORACLE_SID=orcl

ด้วยข้อเท็จจริงดังกล่าวทำให้เราสามารถใช้เทคนิคนี้ในการปิดการให้บริการกับผู้ใช้ระบบซึ่งส่วนใหญ่จะติดต่อกับฐานข้อมูลผ่าน Listener ในขณะที่เราสามารถทำการ Maintain ฐานข้อมูลโดยเชื่อมต่อเข้าที่เครื่อง Server โดยตรง หรือผ่าน TELNET ก็ได้

เฮียไพฯ กับ Date Format

โพสต์ครั้งแรก: 21 พฤศจิกายน 2011

เฮียไพฯ ของเราเป็นผู้จัดการ IT มานานหลายปี วันหนึ่งขณะที่ผมกำลังเขียนคิวรีอยู่ เฮียก็มายืนข้างหลังผม แล้วก็ขอให้ผมช่วยคิวรีในช่วงของวันที่ที่แกอยากดูให้หน่อย ผมก็จัดให้ทันทีใส่ทูคงทูคา (TO_CHAR) ลงไป เฮียแกเห็นหยั่งงั้นแกก็แสดงภูมิรู้ของแกออกมาอีกเช่นเคย "เฮัย ทำไมมันยุ่งยากหยั่งงี้ ดาต้าเบสระดับนี้มันต้องมีวิธีที่ง่ายกว่านี้แน่ หรือว่าคุณไม่รู้วิธีกันแน่เนี่ย" อ๊ะจึ๋ย.. ไหงเป็นแบบนี้ (ฟะ)

แล้วจริงๆ มันยากจริง ๆ หรือที่จะคิวรีเอาค่าวันที่ออกมาน่ะ เรามาดูกันดีกว่า

เวลาคิวรีคอลัมน์ที่มีชนิดข้อมูลเป็นวันที่เราสามารถคิวรีโดยระบุชื่อคอลัมน์ตรง ๆ ในคิวรีได้เลย ซึ่งรูปแบบของวันที่ (เช่น DD/MM/YY หรือ DD-MON-YY) จะเป็นรูปแบบดีฟอลต์ที่กำหนดไว้ อาจจะโดยระบบหรือเราได้กำหนดค่าดีฟอลต์ก็ได้ ก่อนอื่นผมขอแนะนำให้รู้จักกับคอลัมน์ SYSDATE ซึ่งเราสามารถใช้ในลักษณะเป็น PSEUDO Column ซึ่งจริง ๆ แล้วไม่ได้เป็นคอลัมน์ของตารางใด ๆ จริง ๆ, เวลาจะเอาค่ามาดูจะต้องจัดให้อยู่ในรูปของ SELECT + (Pseudo) Column Name + FROM + Table Name เช่น

sys@PAO1> select sysdate from dual;

SYSDATE
---------
10-DEC-11

มีสิ่งที่น่าสนใจจากผลของคิวรีข้างบนคือ 1) รูปของคิวรีเป็นไปตามที่บอกข้างต้นโดยมี SYSDATE เป็น Pseudo Column และ DUAL เป็น Pseudo Table ซึ่ง ณ ที่นี้มีชื่อตารางนี้ไว้เพียงเพื่อให้รูปของคิวรีถูก Syntax จะได้ไม่ Error เท่านั้น ไม่ได้มีความหมายอะไรมากกว่านี้

SYSDATE มีชนิดข้อมูลเป็น Date เวลาเรา Select ตรง ๆ ออกมาโดยไม่ใส่ฟังก์ชันใด ก็จะแสดงผลเป็นฟอร์แมตที่เป็นดีฟอลต์คือ DD-MON-YY

ปกติแม้ว่าจะแสดงผลเฉพาะวันที่ แต่จริง ๆ แล้วชนิดข้อมูลที่เป็น DATE มีการเก็บเอาเวลาไว้ด้วย แต่เวลาแสดงผลหากไม่ได้ระบุก็จะไม่เห็นส่วนที่เป็นเวลา ถ้าเราต้องการให้แสดงผลเป็นเวลาก็จะยุ่งยากขึ้นอีกนิสสนึง

sys@PAO1> select to_char(sysdate,'DD-MON-YY HH24:MI:SSSSS') from dual;

TO_CHAR(SYSDATE,'DD-M
---------------------
10-DEC-11 13:01:46896

sys@PAO1> select to_char(sysdate,'dd-Mon-yy hh:mi AM') from dual

TO_CHAR(SYSDATE,'D
------------------
10-Dec-11 01:05 PM

เอาเป็นว่าถ้าเราต้องการให้แสดงผลเป็นวันที่และเวลาด้วยก็ให้ใช้ฟังก์ชั่น TO_CHAR จัดฟอร์แมทตามที่คุณต้องการ ส่วนที่อยู่ใน Single Quote สังเกตว่าเราสามารถใส่หน่วยของวินาทีได้ละเอียดทีเดียว แต่ในวันปกติเราคงไม่ได้ใช้กันละเอียดขนาดนั้น

เราสามารถให้แสดงผลเป็นเวลาแบบ 24 ชั่วโมง (HH24) หรือเป็นแบบ 12 ชั่วโมง (HH แล้วตามด้วย AM หรือ PM) สังเกตตัวเล็กตัวใหญ๋ด้วยนะครับว่ามีผลต่อฟอร์แมทยังไง ถ้าเราต้องการวันจันทร์,อังคาร,พุธก็ได้แบบนี้

sys@PAO1> select to_char(sysdate,'Day dd-mon-yy hh:mi AM') from dual

TO_CHAR(SYSDATE,'DAYDD-MON-Y
----------------------------
Saturday 10-dec-11 01:13 PM

ถ้าเราอยากรู้ว่าฟอร์แมทดีฟอลต์เป็นยังไง ก็ใช้คิวรีข้างล่างนี้

sys@PAO1> select parameter,value from nls_session_parameters
2 where parameter = 'NLS_DATE_FORMAT';

PARAMETER  VALUE
------------------ ------------------------------
NLS_DATE_FORMAT    DD-MON-RR

RR เป็นฟอร์แมตของการแสดงผลปีคล้าย ๆ กับ YY จะต่างกันตรงที่ถ้าเราใช้ TO_DATE กับ YY เราจะได้ผลที่สองหลักแรกของปีเป็นสองหลักแรกของปีปัจจุบันเท่านั้น แต่ถ้าเราใช้ RR, Oracle จะเลือกสองหลักแรกของปีให้ โดยมีเงื่อนไขดังนี้
- ถ้าปี RR ที่เราระบุเป็น 00 ถึง 49
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 00 ถึง 49 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 50 ถึง 99 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน บวกด้วย 1
- ถ้าปี RR ที่เราระบุเป็น 50 ถึง 99
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 00 ถึง 49 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน ลบด้วย 1
- ถ้าสองหลักหลังของปีจริง (ปีปัจจุบัน)อยู่ระหว่าง 50 ถึง 99 สองหลักแรกที่ได้จากการใช้ RR ก็จะเป็นเหมือนปีปัจจุบัน
ตัวอย่างเช่น ถ้าใช้คิวรีข้างล่างระหว่างปี 1950 และ 1999:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year" FROM DUAL;

Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"    FROM DUAL;

Year
----
2017

คิวรีเดียวกันแต่ใช้ในปี ระหว่างปี 2000 and 2049:

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR') ,'YYYY') "Year"    FROM DUAL;

Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR') ,'YYYY') "Year"    FROM DUAL;

Year
----
2017

เราสามารถเปลี่ยนฟอร์แมทที่เป็นดีฟอลต์ได้โดย ALTER SESSION แบบนี้

sys@PAO1> alter session set nls_date_format = 'Day DD Mon. YY HH:MI AM';
Session altered.

sys@PAO1> select parameter,value from nls_session_parameters
2 where parameter = 'NLS_DATE_FORMAT';

PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT Day DD Mon. YY HH:MI AM

sys@PAO1> select sysdate from dual;

SYSDATE
-----------------------------
Saturday 10 Dec. 11 01:21 PM

แต่ถ้าเราใช้ ALTER SESSION มันจะมีผลต่อค่าดีฟอลต์เฉพาะเมื่อยังไม่ได้ Logout ออกมาเท่านั้น ถ้าเรา Login เข้าไปใหม่ หรือว่าล็อกอินอยู่ใน Session อื่น ก็จะไม่ได้ใช้ดีฟอลต์ที่เรา ALTER SESSION นี้

sys@PAO1> disconn
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

sys@PAO1> conn sys@pao1 as sysdba
Enter password:
Connected.

sys@PAO1> select sysdate from dual;

SYSDATE
---------
10-DEC-11

ถ้าเราต้องการให้แสดงผลเป็นฟอร์แมทที่เราสร้างขึ้นนี้ไปตลอด ไม่ว่าจะล็อกอิน-เอาท์กันกี่ครั้ง หรือจะล็อกอินจาก Session ใดก็ตาม ในการนี้เราจะต้อง ALTER ในระดับที่ใหญ่กว่า SESSION นั่นคือ ALTER SYSTEM ซึ่งเราจะต้องใช้ scope=spfile เพื่อให้ไปบันทึกไว้ใน SPFILE รอการ Restart ครั้งต่อไป ก็จะได้ฟอร์แมทที่ต้องการ หรือจะใช้วิธีการไปแก้หรือเพิ่มในไฟล์ INIT.ORA ก็ได้ ซึ่งจะต้องทำการ Restart เช่นกัน

sys@PAO1> alter system set nls_date_format='Day DD/MM/YY hh24:mi' scope=spfile;
System altered.
sys@PAO1> select sysdate from dual;

SYSDATE
---------
10-DEC-11
sys@PAO1> startup force;
ORACLE instance started.
Total System Global Area 209715200 bytes
Fixed Size 1279336 bytes
Variable Size 125831832 bytes
Database Buffers 75497472 bytes
Redo Buffers 7106560 bytes
Database mounted.
Database opened.

sys@PAO1> select sysdate from dual;

SYSDATE
------------------------
Saturday 10/12/11 13:36

sys@PAO1> select parameter,value from nls_session_parameters
2 where parameter='NLS_DATE_FORMAT';

PARAMETER VALUE
------------------------------ ------------------------------
NLS_DATE_FORMAT Day DD/MM/YY hh24:mi

วันนี้พอแค่นี้ก่อนนะครับ เราอาจจะแก้ฟอร์แมทกลับเป็น DD-MON-RR นะครับ โดยส่วนตัวผมคิดว่าฟอร์แมทนี้ดูง่ายดีแม้ว่าจะไม่เห็นรายละเอียดอะไร แต่เราก็สามารถใส่ฟังก์ชั่น TO_CHAR เพื่อให้อยู่ในรูปแบบที่เราต้องการได้ครับ