Category: Database

  • Grid infrastructure 18c ตอน 1

    grianggrai.n

    รอบนี้จะมาติดตั้ง Oracle grid infrastructure 18c กัน

    สิ่งที่ต้องเตรียม

    • Vmware 2 เครื่อง โดยแต่ละเครื่องมีคุณสมบัติดังนี้ (ปรับลดตามความเหมาะสมได้)
      • 2 CPU 64 core
      • 128 GB Memory
      • HDD 2 ลูก
        • 300GB
        • 1TB
      • Shared Storage ครั้งนี้เลือกใช้ iSCSI 6 ลูกลูกละ 1TB
      • Network Card 3 ใบ
      • IP address โดย 4 IP วงเดียวกัน 3 IP สำหรับชื่อเรียกใน DNS วงเดียวกันกับ 4 IP แรก 2 IP คนละวง
        • rac1 1 IP สำหรับการ์ดใบแรก เป็น IP ประจำเครื่อง หรือเรียก Public IP (ens192)
        • rac2 1 IP สำหรับการ์ดใบแรก เป็น IP ประจำเครื่อง หรือเรียก Public IP (ens192)
        • rac1-vip 1 IP เป็น virtual IP ไม่ต้องใส่ในการ์ดแลน วงเดียวกันกับ IP ประจำเครื่อง เรียก Virtual IP
        • rac2-vip 1 IP เป็น virtual IP ไม่ต้องใส่ในการ์ดแลน วงเดียวกันกับ IP ประจำเครื่อง เรียก Virtual IP
        • rac-scan 3 IP ต้องจดชื่อนี้ใน DNS ด้วยโดยจดเป็น round robin วงเดียวกันกับ IP ประจำเครื่อง เรียก Scan name
        • rac1-priv 1 IP สำหรับการ์ดใบที่สอง เป็น IP สำหรับคุยกันเองใน cluster คนละวงกับ IP ประจำเครื่อง เรียก Private IP (ens224) วงเดียวกับ rac2-priv
        • rac2-priv 1 IP สำหรับการ์ดใบที่สอง เป็น IP สำหรับคุยกันเองใน cluster คนละวงกับ IP ประจำเครื่อง เรียก Private IP (ens224) วงเดียวกับ rac1-priv
        • rac1-storage 1 IP สำหรับเชื่อมต่อ iSCSI ติดต่อผู้ดูแล iSCSI (ens256)***
        • rac2-storage 1 IP สำหรับเชื่อมต่อ iSCSI ติดต่อผู้ดูแล iSCSI (ens256)***
    • Download Software
    • Oracle Enterprise Linux 7.9
      • เลือกติดตั้งดังรูปได้แก่
        • Date & Time เลือก Timezone ที่ถูกต้อง Asia/Bangkok
        • Keyboard English (US), Thai
        • Software Selection เลิอกเป็น Server with GUI
        • KDUMP เลือก Disabled
        • Security Policy เลือก ปิด เป็น No profile selectd
        • ตั้งค่า network ตามที่เขียนข้างบน
    • ขั้นตอนแบ่งดิสก์
    • เริ่มการจัดการ iSCSI ในขั้นตอนนี้
    Device Selection
    • กด Add a disk
    Add iSCSI
    • กด Add iSCSI Target…
    • ใส่ IP Address ของ iSCSI และ iSCSI Initiator Name ที่ได้จาก iSCSI Admin
    ADD iSCSI STORAGE TARGET
    • กด Start Discovery
    • ทำเครื่องหมายถูกหน้าดิสก์ที่ขึ้นมาทั้งหมด
    ADD iSCSI STORAGE TARGET
    • กด Log In จะได้
    Installation Destination
    • กดเลือกทั้งหมดแล้วกด Done จะได้
    INSTALLATION DESTINATION
    • เลือกเฉพาะ disk ขนาด 300GB และเลือก I will configure partitioning กด Done
    INSTALLATION DESTINATION
    • ตอนแบ่ง Partition เลือกเป็น LVM
      • /boot 1GB file
      • /boot/efi 200MB (default) เนื่องจากเลือก boot แบบ efi
      • / 282.8 GB file system ext4
      • swap 16GB
    Manual Portioning
    • กด Done ได้ดังรูป
    • กด Accept Changes
    • กด Begin Installation
    • ขั้นตอนสร้าง User สร้างตามรูป
    Configuration 
    • คลิก Advanced…
    • คลิกถูกในช่องสี่เหลี่ยมทั้งสองช่อง พิมพ์ 54321 ทั้งสองช่อง
    • ในช่อง Add user to the following groups: เปลี่ยนเป็นดังนี้
    wheel,oinstall(54321),oper(54322),dba(54323),backupdba(54324),dgdba(54325)
,kmdba(54326),asmdba(54327), asmoper(54328),asmadmin(54329),racdba(54330)
    • รอจนติดตั้งเสร็จ reboot ทำแบบเดียวกันที่ rac2
    • เริ่มการตั้งค่าที่ rac1
    • ล็อคอินด้วยผู้ใช้ oracle เปิด terminal แล้วพิมพ์คำสั่งต่อไปนี้ จะขึ้นให้ใส่ password ให้ใส่ password ของ oracle
    sudo -i
    • แก้ไขไฟล์ host ด้วยคำสั่ง
    vi /etc/hosts
    • แก้ไขให้อยู่ดังรูป
    <IP-address>  <fully-qualified-machine-name>  <machine-name>
    • ปิด selinux แก้ไขแฟ้ม /etc/selinux/config
    vi /etc/selinux/config
    • เปลี่ยน SELINUX=enforcing เป็น SELINUX=permissive
    • บันทึกแล้วออกมารันคำสั่ง
     setenforce Permissive
    • ปิด firewall ด้วยคำสั่ง
    systemctl stop firewalld
systemctl disable firewalld
    • เปิดการทำงานของ NTP 
    systemctl enable chronyd
systemctl restart chronyd
chronyc -a 'burst 4/4'
chronyc -a makestep
    • ติดตั้งซอฟท์แวร์ที่จำเป็นสำหรับ Oracle 18c
    yum install oracle-database-preinstall-18c -y
yum install binutils -y
yum install compat-libstdc++-33 -y
yum install compat-libstdc++-33.i686 -y
yum install gcc -y
yum install gcc-c++ -y
yum install glibc -y
yum install glibc.i686 -y
yum install glibc-devel -y
yum install glibc-devel.i686 -y
yum install ksh -y
yum install libgcc -y
yum install libgcc.i686 -y
yum install libstdc++ -y
yum install libstdc++.i686 -y
yum install libstdc++-devel -y
yum install libstdc++-devel.i686 -y
yum install libaio -y
yum install libaio.i686 -y
yum install libaio-devel -y
yum install libaio-devel.i686 -y
yum install libXext -y
yum install libXext.i686 -y
yum install libXtst -y
yum install libXtst.i686 -y
yum install libX11 -y
yum install libX11.i686 -y
yum install libXau -y
yum install libXau.i686 -y
yum install libxcb -y
yum install libxcb.i686 -y
yum install libXi -y
yum install libXi.i686 -y
yum install make -y
yum install sysstat -y
yum install unixODBC -y
yum install unixODBC-devel -y
yum install zlib-devel -y
yum install zlib-devel.i686 -y
    • ปรับปรุงรุ่นของ software
    yum update -y
    • ยกเลิก virbr0 interface
    systemctl stop libvirtd.service
systemctl disable libvirtd.service
    • จัดการกับ disk ลูก 1TB 
    fdisk /dev/sdb
    • กด n แล้ว enter แล้วกด enter 4 ครั้ง
    • กด t แล้ว enter แล้วเลือก 31 กด enter
    • กด w enter
    • สร้าง sdb1 ให้เป็น LVM Physical volume 
    pvcreate /dev/sdb1
    • สร้าง LVM volume group ชื่อ vg_u01
    vgcreate vg_u01 /dev/sdb1
    • สร้าง LVM logical volume group ชื่อ lv_u01 ใน volume group ที่ชื่อ vg_u01 โดยให้มีขนาดทั้งหมดที่มีอยู่ใน vg_u01
    lvcreate -n lv_u01 -l 100%FREE vg_u01
    • format LVM logical volume group lv_u01 เป็นแบบ ext4 มี Label ว่า u01
    mkfs.ext4 -L u01 /dev/vg_u01/lv_u01
    • สร้าง /u01
    mkdir /u01
    • แก้ไขแฟ้ม /etc/fstab เพิ่มบรรทัดต่อไปนี้ต่อท้าย
    LABEL=u01               /u01                    ext4    defaults        1 1
    • สั่งให้ mount /u01 ด้วยคำสั่ง
    mount -a
    • ตรวจสอบว่า mount ได้หรือไม่
    df -h
    • ได้ผลประมาณว่า
    • จบการจัดการ disk 1TB
    • ต่อมาสร้างโฟลเดอร์ ที่จำเป็นสำหรับ grid infrastructure และ oracle database
    mkdir -p /u01/app/18.0.0.0/grid
mkdir -p /u01/app/oracle/product/18.0.0.0/db_1
chown -R oracle:oinstall /u01/app
chmod -R 775 /u01/app
    • พิมพ์ logout เพื่อออกจาก user root แล้วกลับเป็น user oracle
    • สร้างโฟลเดอร์ /home/oracle/scripts
    mkdir -p /home/oracle/scripts
    • สร้างแฟ้ม /home/oracle/scripts/setEnv.sh มีข้อความว่า โดย rac1.x.x.x คือชื่อเต็มของ rac1 หรือ rac2 ตามเครื่องที่ทำ ORACLE_SID ก็ต้องเป็น regist1 สำหรับ rac1 และ regist2 สำหรับ rac2
    cat > /home/oracle/scripts/setEnv.sh <<EOF
# Oracle Settings
export TMP=/tmp
export TMPDIR=\$TMP
export ORACLE_HOSTNAME=rac1.x.x.x  #เปลี่ยน   
export ORACLE_UNQNAME=regist   #เปลี่ยน
export ORACLE_BASE=/u01/app/oracle
export GRID_HOME=/u01/app/18.0.0.0/grid
export DB_HOME=\$ORACLE_BASE/product/18.0.0.0/db_1
export ORACLE_HOME=\$DB_HOME
export ORACLE_SID=regist1 #เปลี่ยน
export ORACLE_TERM=xterm
export BASE_PATH=/usr/sbin:\$PATH
export PATH=\$ORACLE_HOME/bin:\$BASE_PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
alias grid_env='. /home/oracle/scripts/grid_env'
alias db_env='. /home/oracle/scripts/db_env'
EOF
    • เพิ่ม script /home/oracle/scripts/setEnv.sh ให้ทำงานทุกครั้งที่ Login
    cat >> /home/oracle/.bashrc <<EOF
. /home/oracle/scripts/setEnv.sh
EOF
    • สร้างแฟ้ม /home/oracle/scripts/grid_env และแฟ้ม /home/oracle/scripts/db_env ด้วยคำสั่ง
    cat > /home/oracle/scripts/grid_env <<EOF
export ORACLE_SID=+ASM1
export ORACLE_HOME=\$GRID_HOME
export PATH=\$ORACLE_HOME/bin:\$BASE_PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
EOF

cat > /home/oracle/scripts/db_env <<EOF
export ORACLE_SID=regist1
export ORACLE_HOME=\$DB_HOME
export PATH=\$ORACLE_HOME/bin:\$BASE_PATH
export LD_LIBRARY_PATH=\$ORACLE_HOME/lib:/lib:/usr/lib
export CLASSPATH=\$ORACLE_HOME/JRE:\$ORACLE_HOME/jlib:\$ORACLE_HOME/rdbms/jlib
EOF
    • reboot ด้วยคำสั่งต่อไปนี้ หากขึ้นให้ใส่ password ให้กรอก password ของ oracle
    sudo reboot
    • ทำซ้ำที่ rac2 อีกเครื่อง
    https://sysadmin.psu.ac.th/2021/01/18/grid-infrastructure-18c-2/

  • การสร้าง Flow ใน Microsoft SharePoint

    wiboon.w

    ผมใช้งาน SharePoint สำหรับจัดเก็บ data เพราะว่าเป็นวิธีที่สะดวก เนื่องจากหน่วยงานมีให้ใช้งานอยู่แล้ว ผมได้สร้าง List เพื่อบันทึกรายการ (item) เกี่ยวกับ การจัดการความเสี่ยง เอาไว้ใน subsite ชื่อ RISK เมื่อบันทึกไปสักระยะ พบว่า เมื่อถึงรอบถัดไปในการประเมินเพื่อการจัดการความเสี่ยง หากต้องคีย์ข้อมูลใหม่ทีละรายการ ทั้ง ๆ ที่ ข้อมูลส่วนมากจะเหมือนกับรอบก่อนหน้านี้ จึงคิดว่า น่าจะมีวิธีการ คัดลอกรายการบรรทัดที่ต้องการ ทุกคอลัมน์ มาเป็นรายการใหม่

    ค้นหาดูใน SharePoint จะมีเครื่องมือที่เรียกว่า Flow ซึ่งจะเป็นส่วนที่ SharePoint เรียกว่า Power Automate ให้ใช้งาน แต่เราต้องสร้าง Flow ขึ้นมาก่อน โดยต้องระบุเองว่า เราจะเอาคอลัมน์ไหนบ้าง และ ต้องการใช้สูตรคำนวณ (formula) ด้วยมั้ย เช่น มีคอลัมน์ที่แสดงค่า รอบที่ เมื่อประเมินรอบใหม่จะต้องเพิ่มรอบที่ เช่น เดิมเป็นรอบที่ 8 เมื่อคัดลอกเป็นรายการใหม่ คอลัมน์ที่เก็บ รอบที่ ก็จะกลายเป็น 9 ให้เอง อย่างนี้เป็นต้น หลังจากศึกษาหาทางจาก Google search ก็พบว่า Microsoft SharePoint มีคำแนะนำเยอะพอสมควร จึงคิดว่า อยากจะบันทึกไว้กันลืม วิธีสร้างก็ค่อนข้างยาว มีดังนี้

    การสร้าง Flow

    ขั้นตอน

    1. ที่เมนูด้านบนของ List ที่กำลังใช้งาน ให้คลิกที่ Automate และเลือก Power Automate เลือก See your flows
    2. หากเป็นครั้งแรก ในหน้าต่างก็จะไม่มีรายการ Flow
    3. ที่เมนูด้านซ้าย ให้คลิกที่ Create และเลือก Instant flow
    4. จะได้หน้าต่าง Build an instant flow จะมีช่องให้ตั้งชื่อ flow จะตั้งชื่อว่า Clone Selected Item
    5. ต่อไปก็เลื่อนหารายการ tigger ที่ให้เลือกใช้ ก็ให้เลือกอันที่เขียนว่า For a selected item SharePoint
    6. ใส่ข้อมูลในช่อง Site Address โดยเลือกจาก drop down list
    7. ใส่ข้อมูลในช่อง List name โดยเลือกจาก drop down list
    8. คลิก New step
    9. หน้าต่าง Choose an action ในช่อง search ใส่คำว่า sharepoint เพื่อบีบ scope ให้แคบลง และเลื่อนหารายการจนเจอคำว่า Get item
    10. จะมีหน้าต่างโผล่มาทางขวา ใต้แท็บ Dynamic content ให้เลือก ID จะได้ดังรูปข้างล่างนี้
    11. ต่อไปคลิก New Step
    12. จะได้หน้าต่าง Choose an action อีกครั้ง ที่นี้ในช่อง search ใส่คำว่า sharepoint create เพื่อบีบ scope ให้แคบลง และเลื่อนหารายการจนเจอคำว่า Create item
    13. ทยอยใส่ทีละคอลัมน์ โดยเลือกจาก Dynamic content แต่หากต้องการการคำนวณ ให้คลิกแท็บ Expression จนครบทุกคอลัมน์ที่ต้องการคัดลอก

    การใช้สูตรคำนวณ

    ขั้นตอน

    ในขั้นตอน Create item เมื่อมีคอลัมน์ที่จะคำนวณ เช่น คอลัมน์ “รอบที่” บวกเพิ่ม 1 ให้เลือก Expression แล้วใส่สูตรคำนวณ ตัวอย่าง add(outputs(‘Get_item’),1) เป็นต้น

    การใช้งาน Flow

    เมื่อต้องการคัดลอกรายการที่มีอยู่ สร้างเป็นรายการใหม่อีกอัน พร้อมข้อมูลคอลัมน์ทีจะถูกคัดลอกมาด้วย Flow ชื่อ Clone Selected Item

    ขั้นตอน

    1. เปิด List ที่ใช้งาน คลิกที่รูปวงกลม (เลือก) หน้ารายการ เพื่อเลือกว่าจะคัดลอกรายการนั้น
    2. คลิกที่เมนู (ด้านบน) ชื่อ Automate
    3. คลิกเลือก Clone Selected Item
    4. จะได้หน้าต่าง Popup ทางขวา ชื่อว่า Run flow แจ้งสิทธิการใช้งาน ถ้าได้รับสิทธิ ก็คลิก Continue
    5. แล้วคลิก Run flow
    6. จะได้รายการใหม่ต่อท้าย
    7. คลิกที่รูปวงกลม (เลือก) หน้ารายการ ซ้ำ เพื่อ จบขั้นตอน

  • ORA-31623: a job is not attached to this session via the specified handle

    grianggrai.n

    • ปัญหา
      • พยายามใช้ expdp แล้วเกิด error

    UDE-31623: operation generated ORACLE error 31623
    ORA-31623: a job is not attached to this session via the specified handle
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 3905
    ORA-06512: at “SYS.DBMS_DATAPUMP”, line 5203
    ORA-06512: at line 1

    งานมา…
    • เมื่อค้นข้อมูลไปเรื่อย ๆ จะพบว่าอาจเกี่ยวของกับการปรับแต่งค่าต่างๆ เกี่ยวกับ memory ซึ่งก็ได้ทำไปจริงนั่นคือการปรับ shared_pool_size ให้มีขนาดมากขึ้นทำให้หน่วยความจำสำหรับทำเรื่อง expdp ไม่พอ
    • แก้ไขได้ด้วยการลดค่า shared_pool_size ลง ด้วยคำสั่ง
    SQL> alter system set shared_pool_size=5G scope=both;
    • เพื่อปรับขนาด shared_pool_size ให้เป็น 5GB
    • และอาจต้องเพิ่ม streams_pool_size เพราะไม่ได้ลด shared_pool_size ให้เท่าเดิม เนื่องจากมีการเพิ่มหน่วยความจำของระบบให้มากขึ้นแล้ว ด้วยคำสั่ง
    SQL> alter system set streams_pool_size=128M scope=both;
    • เพื่อปรับ streams_pool_size ให้เป็น 128MB
    • และยังมี parameter บางตัวที่อาจเกี่ยวข้อง เช่น db_cache_size เป็นต้น
    • แต่ในครั้งนี้ปรับแค่สองอย่าง คือ shared_pool_size และ streams_pool_size ก็สามารถแก้ปัญหานี้ได้
    • จบ
    • ขอให้สนุก

    อ้างอิง

    ORA-31623: a job is not attached to this session via the specified handle
    UDI-31623: operation generated ORACLE error 31623 / ORA-31623

  • Oracle User expire โดยไม่ทราบสาเหตุ

    worapot.k

    นักพัฒนาบางท่านที่เพิ่งเริ่มพัฒนาระบบบนฐานข้อมูล Oracle อาจจะเคยเจอปัญหาเมื่อใช้ Oracle ตั้งแต่เวอร์ชัน 11 เป็นต้นไป นั่นคือ เมื่อสร้าง user บน Oracle และใช้งานไปได้ซักระยะอยู่ ๆ user นี้ก็เกิดก็ expire โดยที่ไม่ได้มีใครเข้าแก้ไขการตั้งค่าของ user นี้ ซึ่งเหตุการณ์นี้อาจจะมีสาเหตุแบบเดียวกับที่จะนำเสนอในบทควาามนี้ก็เป็นได้

    หมายเหตุ ในบทความนี้จะใช้ Oracle SQL Developer ซึ่งเป็นเครื่องมือในการเข้าถึงและจัดการฐานข้อมูล Oracle มาช่วยในการอธิบายเพื่อให้เห็นภาพได้ง่ายขึ้น

    ขั้นตอนการตรวจสอบที่มาของปัญหาและการแก้ไข

    1. login ด้วย sys
    2. ตรวจสอบการตั้งค่า Default Profile โดยเลือกเมนู View –> DBA

    3. ในหน้าต่าง DBA เลือกเพิ่มการเชื่อมต่อโดย SYS

    4. ไปที่การตั้งค่า Default Profile

    5. คลิกขวาที่ Default แล้วเลือก Edit

    6. จะปรากฏหน้าต่าง Default Profile

    ซึ่งใน Oracle 11 เป็นต้นไป หลังจากติดตั้งและสร้าง Database แล้ว ใน Default Profile จะมีการตั้งค่าเริ่มต้นไว้ดังรูป โดย Default Profile จะมีผลกับ user ที่ถูกสร้างใหม่ และค่าที่ทำให้ user หมดอายุโดยอัตโนมัติก็คือ “Expire in (days)” นั่นเอง จึงเป็นที่มาของการที่ user expire เองโดยที่ไม่ได้มีการเข้าไปแก้ไขข้อมูลใด ๆ ของ user นี้

    7. การแก้ไขไม่ให้ user ที่สร้างใหม่ expire อัตโนมัติ ทำได้โดยการแก้ค่า “Expire in (days)” ให้เป็น UNLIMITED

    เมื่อแก้ไขแล้ว user ใหม่ที่ถูกสร้างหลังจากนี้ก็จะใช้งานได้ตลอดไม่มีวัน expire แต่ส่วนของ user ที่เคยสร้างไปก่อนหน้านี้ ผู้พัฒนาจะต้องเข้าไปปรับแก้วันที่ expire เป็นราย user ไป

  • คำสั่ง Update โดยใช้ข้อมูลจากอีกตาราง [Oracle]

    worapot.k

    ในการสั่ง update ข้อมูล ในบางครั้งเราอาจจะเจอโจทย์ว่า ต้องการ update ข้อมูลในตารางหนึ่งโดยใช้ข้อมูลจากอีกตารางหนึ่ง ซึ่งลักษณะของข้อมูลทั้งสองตารางนี้ จะต้องมีฟีลด์ข้อมูลที่สามารถเชื่อมโยงความสัมพันธ์ของข้อมูลแต่ละเร็คคอร์ดได้

    ตัวอย่างข้อมูล

    1. มีข้อมูลสองตาราง

    ตารางที่ 1 เก็บข้อมูลวันในหนึ่งสัปดาห์ภาษาไทย [ชื่อตาราง : DAY_OF_WEEK_THAI]

    โครงสร้างตารางประกอบด้วย

    • ฟีลด์ ID เก็บ ID ของแต่ละเร็คคอร์ด
    • ฟีลด์ DAY_FULL เก็บข้อมูลวันแบบเต็มภาษาไทย
    • ฟีดล์ DAY_SHORT เก็บข้อมูลวันแบบย่อภาษาไทย

    ข้อมูลในตารางเป็นดังนี้

    ตารางที่ 2 เก็บข้อมูลวันในหนึ่งสัปดาห์ภาษาอังกฤษ [ชื่อตาราง : DAY_OF_WEEK]

    โครงสร้างตารางประกอบด้วย

    • ฟีลด์ ID เก็บ ID ของแต่ละเร็คคอร์ด
    • ฟีลด์ DAY_ENG เก็บข้อมูลวันแบบเต็มภาษาอังกฤษ
    • ฟีดล์ DAY_ENG_SHORT เก็บข้อมูลวันแบบย่อภาษาอังกฤษ

    ข้อมูลในตารางเป็นดังนี้

    2. ฟีลด์ที่เชื่อมโยงความสัมพันธ์ของทั้งสองตารางคือฟีลด์ ID

    3. ต่อมามีการปรับแก้โครงสร้างตาราง DAY_OF_WEEK โดยเพิ่มฟีลด์ 2 ฟีลด์ ดังนี้

    ฟีลด์ DAY_THA สำหรับเก็บข้อมูลวันแบบเต็มภาษาไทย

    ฟีลด์ DAY_THA_SHORT สำหรับเก็บข้อมูลวันแบบย่อภาษาไทย

    4. เนื่องจากมีข้อมูลวันภาษาไทยในตาราง DAY_OF_WEEK_THAI อยู่แล้ว และสามารถเชื่อมโยงข้อมูลกันได้ด้วยฟีลด์ ID ดังนั้นความต้องการคือ การใช้ข้อมูลจากตาราง DAY_OF_WEEK_THAI มา update ให้กับตาราง DAY_OF_WEEK

    5. รายละเอียดการ update

    นำข้อมูลวันแบบเต็มภาษาไทยในฟีลด์ DAY_FULL ของตาราง DAY_OF_WEEK_THAI มาใส่ในฟีลด์ DAY_THA ของตาราง DAY_OF_WEEK

    นำข้อมูลวันแบบย่อภาษาไทยในฟีลด์ DAY_SHORT ของตาราง DAY_OF_WEEK_THAI มาใส่ในฟีลด์ DAY_THA_SHORT ของตาราง DAY_OF_WEEK

    รูปแบบคำสั่ง SQL

    แบบที่ 1

    update table1 t1 set

    table1.data1= (select table2.data1 from table2 where table2.key = t1.key),

    table1.data2 = (select table2.data2 from table2 where table2.key = t1.key);

    แบบที่ 2

    update table1 t1 set

    (table1.data1, table1.data2) =

    (select table2.data1, table2.data2 from table2 where table2.key = t1.key);

    อธิบายคำสั่ง

    table1 คือ ตารางที่ต้องการ update

    t1 คือ ชื่ออ้างอิงแทนตาราง table1 ซึ่ง t1 จะถูกใช้ในคำสั่ง select

    table1.data1 และ table1.data2 คือ ฟีลด์ข้อมูลที่ต้องการ update

    table2 คือ ตารางที่จะนำข้อมูลมาใช้เพื่อ update ให้กับ table1

    table2.data1 และ table2.data2 คือ ฟีลด์ข้อมูลที่จะนำมาใช้เพื่อ update ให้กับ table1.data1 และ table1.data2 ตามลำดับ

    ตัวอย่างการนำไปใช้งาน

    แบบที่ 1

    update day_of_week t1 set
    day_tha = (select day_full from day_of_week_thai where day_of_week_thai.id = t1.id),
    day_tha_short = (select day_short from day_of_week_thai where day_of_week_thai.id = t1.id);

    แบบที่ 2

    update day_of_week t1 set
    (day_tha, day_tha_short) =
    (select day_full, day_short from day_of_week_thai where day_of_week_thai.id = t1.id);

    ผลลัพธ์การรันคำสั่งทั้งสองรูปแบบ จะได้ผลลัพธ์เช่นเดียวกัน ดังรูป

    ข้อมูลก่อน update

    ข้อมูลหลัง update

    สรุป

    1. สามารถใช้คำสั่ง update ได้ทั้งสองรูปแบบซึ่งจะได้ผลลัพธ์เหมือนกัน
    2. แบบที่ 2 จะเป็นการใช้คำสั่งที่สั้นและกระชับกว่าแบบที่ 1 มาก กรณีที่ข้อมูลที่ต้องการ update มีมากกว่า 1 ฟีลด์
    3. ข้อควรระวังคือ ความสัมพันธ์ระหว่างสองตารางต้องเป็น 1 ต่อ 1 จึงจะสามารถใช้คำสั่งรูปแบบนี้ได้

  • [กันลืม] ปรับแต่ง MySql/Mariadb ให้โหลดข้อมูลได้เร็วขึ้น

    niti.c

    เพื่อให้ดาต้าเบส (Database) ทำงานได้อย่างเต็มประสิทธิภาพและสามารถใช้ทรัพยาการของเครื่องเซิร์ฟเวอร์ (Server) ได้เต็มศักยภาพของเครื่องเมื่อติดตั้งเสร็จก็จะควรมีการปรับแต่งค่าให้เหมาะสมกับการใช้งาน

    • สำหรับ Linux ให้แก้ไขไฟล์ /etc/mysql/my.cnf
    • สำหรับ Windows (XAMPP) ให้แก้ไขไฟล์ C:\xampp\mysql\bin\my.ini

    มาเริ่มกันเลย! ปัจจุบัน MySql/Mariadb เวอร์ชั่นใหม่จะใช้เอนจินแบบ InnoDB เป็นค่าเริ่มต้น ฉะนั้นเราจะโฟกัสความสำคัญเฉพาะการตั้งค่า InnoDB โดยเฉพาะค่า innodb_buffer_pool_size ควรกำหนดให้ไม่เกิน 70-80% ของขนาด RAM มีตัวย่างการค่าคอนเฟิกดังนี้

    (สมมุติสเปกเครื่องเซิร์ฟเวอร์ CPU 4 core  RAM 4 GB)

    # InnoDB Settings
default_storage_engine          = InnoDB
innodb_buffer_pool_instances    = 1     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 1G    # Use up to 70-80% of RAM
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_log_buffer_size          = 8M
innodb_log_file_size            = 256M
innodb_stats_on_metadata        = 0

    คำสั่งคิวรีที่ใช้ตรวจสอบค่าที่เหมาะสมของ innodb_buffer_pool_size

    SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

    เมื่อใช้ดาต้าเบสไปได้ 1-2 สัปดาห์ ให้ใช้คำสั่งคิวรีนี้เพื่อใช้ตรวจสอบว่าหน่วยความจำ GB จริงที่ถูกใช้โดย InnoDB Data ใน InnoDB Buffer Pool ในขณะนี้ถูกใช้งานไปเท่าไร

    SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

    และมีค่าอื่นๆ อีกเช่น

    join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
read_rnd_buffer_size = 1M

    เสร็จแล้วให้บักทึก และ Restart การทำงาน MySql/Mariadb ใหม่อีกครั้ง

    แนะนำบทความเพื่อศึกษาเพิ่มเติม : Tuning MySQL : สำรวจตัวเองและเข้าใจตัวแปร

    อ้างอิง

  • Change BYTE to CHAR for all columns, all tables

    grianggrai.n

    เมื่อมีการสร้างฟิลด์ให้มีชนิดเป็น char หรือ varchar2 จริงๆ แล้ว มันมีหน่วยย่อยไปอีกว่าจะให้เป็น Byte หรือ Char ซึ่งโดยปกติจะเป็น Byte สามารถเปลี่ยนค่า default นี้ได้ด้วยการเปลี่ยนตัวแปรของระบบตัวแปรชื่อ nls_length_semantics

    https://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams127.htm#REFRN10124

    ดูรายละเอียดเพิ่มเติม

    ทีนี้ Byte กับ Char มันสำคัญอย่างไร…สำคัญเรื่องการแปลง Character Set เช่น จาก TH8ASCII เป็น AL32UTF8 จากข้อมูลเดิม 1 ตัวอักษรเท่ากับ 1Byte สำหรับ TH8ASCII และ 3Byte สำหรับ AL32UTF8 ทำให้เมื่อเราสร้างฟิลด์ ตอนมี Character set บน TH8ASCII ชนิดของข้อมูลเป็น varchar2 มีขนาด 3Byte ถ้าจะนำข้อมูลเข้าอีกฐานข้อมูลที่มี Character Set เป็น AL32UTF8 จะนำเข้าไม่ได้เพราะขนาดเกินจำนวน Byte ที่สร้างคือกลายเป็น 9Byte ต้องกลับไปเปลี่ยนต้นทางจากหน่วย Byte เป็น Char คือเก็บเป็นตัวอักษรแทน

    ทีนี้เราจากสร้างสคริปต์เพื่อ สร้างสคริปต์ อีกที… ด้วยคำสั่ง (สคริปต์ในตัวอย่างนี้จะเป็นการสร้างจาก Username SCOTT)

    SELECT 'alter table '||owner||'.'|| table_name ||
       ' modify '||column_name||' varchar2('|| 
       data_length ||' CHAR); '
  FROM all_tab_columns
 WHERE data_type = 'VARCHAR2'
   AND char_used = 'B'
   and owner in ('SCOTT')
;

    เมื่อสั่งสคริปต์ทำงานจะได้ผลลัพธ์ประมาณว่า

    alter table

    ทีนี้จากสคริปต์ข้างต้น จะดึงข้อมูลของ Table และ View มาทั้งหมดทำให้เมื่อได้สคริปต์ไปแล้วสั่งทำงานจะมี error ต้องกรองเอา view ออก

    error

    เปลี่ยนสคริปต์ใหม่เป็น

    select 'alter table '||all_objects.OWNER||'.'||'"'||all_objects.OBJECT_NAME ||'"'||' modify '||'("'||column_name||'"'||' varchar2('|| ALL_TAB_COLUMNS.DATA_LENGTH ||' CHAR)); '
from   all_objects,all_tab_columns
where  all_objects.object_type in ('TABLE')
and all_tab_columns.data_type = 'VARCHAR2'
AND all_tab_columns.char_used = 'B'
and object_name not in (select all_objects.object_name from all_objects where all_objects.object_type = 'VIEW')
and all_objects.object_name=all_tab_columns.table_name
and all_objects.owner in ('SCOTT');

    เมื่อสั่งรันสคริปต์จะได้ผลลัพธ์

    alter script

    ก็จะสามารถนำสคริปต์ที่ได้ไปสั่งรันได้ปกติ

    Complete

    หลังจากเปลี่ยนข้อมูล TH8ASCII จาก Byte เป็น Char แล้วสามารถนำเข้าใน AL32UTF8 ได้เลย

    จบขอให้สนุก

    ต้นฉบับ

    http://webgeest.blogspot.com/2014/12/change-byte-to-char-for-all-columns-all.html

  • การ Connect Navicat ผ่าน xampp-linux 7.3.12 บน Ubuntu 18.04

    syed.s

    1. Download ไฟล์ ntunnel_mysqli.php
      https://qrgo.page.link/6G6uX
    2. โยน File ชื่อว่า ntunnel_mysqli.php เข้าไปใน Server ที่ wwwroot
    3. ทดสอบโดยการเข้าเว็บบราว์เซอร์ ด้วย : 192.168.xxx.xxx/ntunnel_mysqli.php แล้วลอง login ดูว่าผ่านไหม ?
    4. เข้าไปที่โปรแกรม Navicat
    5. ไปที่เมนู Connection เลือกเอาเลยว่าต้องการ Connection แบบไหน

    6. ไปที่ General
    – Connection Name = ตั้งชื่ออะไรก็ได้
    – Host Name / IP Address = localhost
    – Port = 3306
    – User Name = root หรือ User ที่เรา Add ใน phpmyadmin
    – Password = Password ที่เรา Add ใน phpmyadmin

    7. ไปที่ HTTP
    – ติ๊กเลือก Use HTTP Tunnel
    – Tunnel URL : https://192.168.xxx.xxx/ntunnel_mysqli.php หรือ https://xxxx.com/ntunnel_mysqli.php

    8. Test Connection

    หาก Connect ไม่ผ่าน ให้เข้าไปแก้ไฟล์ my.cnf บรรทัดที่เขียนว่า skip-networking ให้ไป # ปิดใว้ แล้ว Restart Service ใหม่อีกครั้ง

    หมายเหตุ : ผมได้ทดสอบกับ xampp-linux 7.3.12 บน Ubuntu 18.04
    php 5.4 up มันจะใช้ mysqli เป็นส่วนใหญ่ เลยต้องใช้ ntunnel_mysqli

  • How to safely grant ALTER SYSTEM KILL SESSION “privilege” to non-DBA users

    grianggrai.n

    อยากฆ่าคน เอ้ย session บน oracle แต่ไม่มีสิทธิ์ (Privilege) ไปขอสิทธิ์ DBA แล้ว DBA บอกว่าไม่มีสิทธิ์ kill session จะแจกให้!!! ทำไงดี

    เข้าระบบฐานข้อมูล Oracle ด้วยผู้ใช้ system หรือ sys แล้วสร้าง Procedure ชื่อว่า sp_kill_dev_session ด้วยคำสั่ง

    CREATE OR REPLACE PROCEDURE sys.sp_kill_dev_session(p_sid NUMBER, p_serial NUMBER)
AS
    v_user VARCHAR2(30);
BEGIN
    SELECT MAX(username)
    INTO v_user
    FROM v$session
    WHERE sid = p_sid
      AND serial# = p_serial;

    IF v_user IN ('SCOTT') THEN --the list can be extended
         EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION ''' || p_sid || ',' || p_serial || '''';
    ELSIF v_user IS NULL THEN
         RAISE_APPLICATION_ERROR(-20001,'Session has Expired or Invalid sid/serial Arguments Passed');
    ELSE
         RAISE_APPLICATION_ERROR(-20002,'Unauthorized Attempt to Kill a Non-Dev Session has been Blocked.');
    END IF;
END sp_kill_dev_session;
/

    จาก code ข้างต้น ให้เปลี่ยน SCOTT เป็น username ที่เป็นเจ้าของ session เช่น 

    IF v_user IN ('HIRXPXT','PHU_XDMIHHION') THEN --the list can be extended

    เป็นต้น จากตัวอย่าง จะมี 2 user คือ HIRXPXT และ PHU_XDMIHHION

    แล้ว grant สิทธิ์ ให้สามารถ exec กับ user ที่ต้องการฆ่า session เหล่านั้นด้วยคำสั่ง ตัวอย่างจะให้ user ชื่อว่า HIRXPXT มีสิทธิ์ exec

    GRANT EXECUTE ON sp_kill_dev_session TO HIRXPXT;

    แล้วจะทำให้ user ที่ชื่อ HIRXPXT สามารถฆ่า (kill) session ของ user ได้ 2 คนคือ HIRXPXT และ PHU_XDMIHHION

    วิธีใช้งาน เข้าระบบด้วย user ที่ชื่อ HIRXPXT แล้วเรียกใช้คำสั่ง

    exec sys.sp_kill_dev_session(sid,serial num);

    เช่น

    Session Properties 
    exec sys.sp_kill_dev_session(154,42802);

    โดย 14 คือ sid และ 26043 คือ serial ซึ่งสองค่านี้สามารถเรียกดูได้จากคำสั่ง

    SELECT SID, SERIAL#, STATUS, SERVER
FROM V$SESSION
WHERE USERNAME = upper('<username>');

    เปลี่ยน <username> เป็น username ที่ต้องการ แต่การที่จะดูค่าเหล่านี้ได้ต้องมีสิทธิ์ จากตัวอย่าง user ที่ชื่อ HIRXPXT สามารถ select view และ table เหล่านี้ได้

    SYS.V_$PROCESS
SYS.V_$SESSION
SYS.V_$TRANSACTION
SYS.V_$SESSION_LONGOPS
SYS.V_$LOCK
SYS.V_$SESSTAT
SYS.V_$STATNAME
SYS.V_$ACCESS
SYS.V_$ROLLNAME
SYS.V_$SQLTEXT
SYS.V_$SQLTEXT_WITH_NEWLINES
SYS.V_$SQL
SYS.V_$OPEN_CURSOR
SYS.V_$SESSION_WAIT
SYS.V_$SESSION_EVENT
SYS.V_$SESSION_CONNECT_INFO
SYS.V_$EVENT_NAME
SYS.V_$SESS_IO
SYS.V_$EVENTMETRIC
SYS.DBA_ROLLBACK_SEGS

    เท่านี้ก็เพียงพอที่จะให้ทีมพัฒนาสามารถฆ่า (kill) session ของตัวเองที่ค้างอยู่บน server ได้โดยไม่ต้องมาจิกหัว DBA กรั่กๆ

    ต้นฉบับ

    https://sqlpatterns.wordpress.com/2015/03/02/how-to-safely-grant-alter-system-kill-session-privilege-to-non-dba-users/

    จบ…. ขอให้สนุก