Tag: oracle database

Oracle: retrieve top n records from a query

Top-N queries เป็นวิธีการดึงข้อมูลสูงสุดหรือต่ำสุด N ลำดับแรกออกมาจากตาราง โดยวิธีการดึงข้อมูลแบบ Top-N นั้นมีได้หลายวิธี แต่ในบทความนี้จะนำเสนอวิธีการดึงข้อมูลแบบ Top-N records เพียง 3 วิธีการดังนี้

1. Inline View and ROWNUM
2. WITH Clause and ROWNUM
3. ROW_NUMBER

สมมติว่าเรามีข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่ซึ่งประกอบด้วย 5 ฟิลด์ข้อมูลดังตัวอย่างข้างล่าง
ข้อมูล: ตาราง TEST_NEW_STUDENT เป็นตัวอย่างข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการ

โจทย์: ต้องการดึงข้อมูลนักศึกษาที่ได้คะแนนภาษาอังกฤษสูงสุด 5 อันดันแรกจากข้อมูลคะแนนภาษาอังกฤษของนักศึกษาใหม่จำนวน 773 รายการนี้

เริ่มต้น Top-N query ตามลำดับเพื่อแก้โจทย์กันค่ะ

Inline View and ROWNUM
Classic Top-N style query

SELECT a.*,rownum
FROM (SELECT *
FROM test_new_student
ORDER BY eng_score desc) a
WHERE ROWNUM <= 5;

ผลลัพธ์:

• จากผลลัพธ์ที่ได้ข้อมูลจะถูกจัดเรียงจากคะแนนจากมากไปน้อยก่อนด้วย ORDER BY clause และหลังจากนั้นก็จะจำกัดจำนวนข้อมูลที่ต้องการด้วย ROWNUM
• Pseudocolumn ROWNUM เป็นค่าตัวเลขแสดงลำดับที่ของการดึงข้อมูลจากตาราง
• กรณีที่ต้องการข้อมูลคะแนนภาษาอังกฤษต่ำสุด ใส่ ASC แทน DESC ตรง ORDER BY clause

WITH Clause and ROWNUM
จากตัวอย่างข้างต้นเรายังสามารถเขียน query ด้วย WITH clause แทนที่ inline view ได้ดังนี้

WITH ordered_query AS
(SELECT *
FROM test_new_student
ORDER BY eng_score desc)
SELECT ordered_query.*,rownum
FROM ordered_query
WHERE rownum <= 5;

ROW_NUMBER
ฟังก์ชัน ROW_NUMBER เป็นฟังก์ชันที่กำหนดค่าลำดับของข้อมูลที่จัดเรียงตามข้อมูลที่กำหนดไว้ใน order_by_clause โดยจะมีค่าเริ่มต้นเท่ากับ 1 โดยเราจะมีวิธีการเขียน query ได้ดังนี้

SELECT *
FROM (SELECT a.*, row_number() OVER (ORDER BY eng_score DESC) AS val_row_number
FROM test_new_student a)
WHERE val_row_number <= 5;

ผลลัพธ์:

สังเกตที่ฟิลด์ VAL_ROW_NUMBER จะเห็นว่ามันแสดงตามอันดับของคะแนนภาษาอังกฤษแล้ว แค่นี้เราก็สามารถที่จะ select เอา Top ที่เท่าไหร่ได้แล้ว โดยเลือกเอา VAL_ROW_NUMBER ที่ต้องการ

July 14, 2017

Removing duplicate records by using Oracle’s ROWID

ถ้าคุณมีตารางข้อมูลอยู่ และรู้ว่ามีบางแถวที่มีข้อมูลซ้ำซ้อนกัน ทางไหนเป็นวิธีที่ดีที่จะสามารถหาและกำจัดแถวที่มีข้อมูลซ้ำนี้ออกไปจากตารางของฐานข้อมูล Oracle ?

การหาแถวที่มีข้อมูลซ้ำซ้อน

เราสามารถหาข้อมูลแถวที่มีข้อมูลซ้ำซ้อนกันได้โดยใช้คำสั่ง select ดังนี้

select a,b,count(*)

from test

group by a,b

having count(*) > 1;

ผลลัพธ์ที่ได้ :

A B COUNT(*)

———- ———- ———-

1 2 259

2 2 5

จากตัวอย่างในตาราง test นี้เราจะกำหนดว่าให้ค่าในคอลัมภ์ a และ b จะต้องมีค่าไม่ซ้ำ ซึ่งผลลัพธ์ที่ปรากฏคือ มีข้อมูลซ้ำ 258 แถว และ 4 แถว

การกำจัดแถวที่มีข้อมูลซ้ำซ้อน
เราสามารถกำจัดแถวที่มีข้อมูลซ้ำซ้อนกันได้โดยการใช้ rowid เข้ามาช่วย คราวนี้คุณต้องเลือกว่าจะเลือกเก็บข้อมูลแถวไหนไว้

เราลองมาดูข้อมูลที่ควรจะเป็นที่ไม่ซ้ำกันว่ามีข้อมูลอะไรบ้าง โดยสามารถใช้คำสั่งได้ดังนี้

select a,b,count(*) from test

group by a,b;

A B COUNT(*)

———- ———- ———-

1 2 259

2 2 5

3 0 1

กรณีที่ต้องการลบและคงเหลือไว้เฉพาะแถวแรกที่ซ้ำสามารถใช้คำสั่งได้ดังนี้

— เราต้องการกำจัด 258 แถวที่ซึ่ง A = 1 และ B = 2 บวกกับ

— 4 แถวที่ซึ่ง A = 2 และ B = 2

— ลองมา select แถวที่เราจะคงไว้ดูก่อน

select min(rowid),a,b from test

group by a,b;

MIN(ROWID) A B

——————————- ———- ———-

AAAAyvAAGAAAABYAAA 1 2

AAAAyvAAGAAAABYAED 2 2

AAAAyvAAGAAAABYAEI 3 0

— คราวนี้ก็ถึงเวลาลบข้อมูลกันแล้ว

— เริ่มกันเลย

delete from test where rowid not in (

select min(rowid) from test group by a,b);

262 rows deleted.

— คราวนี้มาตรวจสอบกันว่าข้อมูลที่คงเหลือถูกต้องหรือไม่

select rowid,a,b from test;

ROWID A B

——————————— ———- ———-

AAAAyvAAGAAAABYAAA 1 2

AAAAyvAAGAAAABYAED 2 2

AAAAyvAAGAAAABYAEI 3 0

กรณีที่ต้องการลบและคงเหลือไว้เฉพาะแถวสุดท้ายที่ซ้ำสามารถใช้คำสั่งได้ดังนี้

delete from test where rowid not in (

select max(rowid) from test group by a,b);

262 rows deleted.

แต่เพื่อป้องกันการเกิดการซ้ำซ้อนของข้อมูลเหล่านี้ เราสามารถที่จะใช้ unique constraints หรือ primary key ช่วยได้ ปัญหาข้อมูลซ้ำซ้อนแบบนี้ก็จะไม่มีทางเกิดขึ้นให้เกิดความปวดหัวได้อีก

January 12, 2017

อีกหนึ่งวิธีในการกำจัดข้อมูลที่ซ้ำซ้อนกันในตาราง

อีกวิธีหนึ่งที่เป็นไปได้ในการกำจัดแถวที่ซ้ำซ้อนกันคือการใช้คำสั่ง select distinct และใส่ข้อมูลที่ได้ลงในตารางใหม่

จากที่เราสามารถตรวจสอบหาข้อมูลแถวที่มีข้อมูลซ้ำซ้อนกันได้โดยใช้คำสั่ง select ดังนี้

SQL> select a,b,count(*) from test group by a,b;

ผลลัพธ์ที่ได้

A B COUNT(*)
———- ———- ———-
1 2 259
2 2 5
3 0 1

จากตัวอย่างในตาราง test นี้ที่กำหนดไว้ว่าค่าในคอลัมภ์ a และ b จะต้องมีค่าไม่ซ้ำ ซึ่งผลลัพธ์ที่ปรากฏคือ มีข้อมูลซ้ำ 258 แถว และ 4 แถว

เรามาเริ่มต้นกำจัดข้อมูลซ้ำซ้อนอีกวิธีกันเลย

การหาแถวข้อมูลที่ไม่ซ้ำซ้อนกัน

เราสามารถหาข้อมูลที่ไม่ซ้ำซ้อนกันได้โดยใช้คำสั่ง select distinct ดังนี้

SQL> select distinct * from test;

ผลลัพธ์ที่ได้

A B
———- ———-
1 2
2 2
3 0

สร้างตารางใหม่ชั่วคราวเพื่อเก็บผลลัพธ์ที่ได้

SQL> create table new_test as (select distinct * from test);

ตรวจสอบผลลัพธ์ที่ได้ในตารางชั่วคราวนี้

SQL> select * from new_test;

ผลลัพธ์ที่ได้

A B
———- ———-
1 2
2 2
3 0

ทำการลบข้อมูลในตาราง test ทั้งหมด

SQL> truncate table test;

Table truncated.

ทำการเพิ่มข้อมูลกลับไปยังตาราง test จากตารางชั่วคราว

SQL> insert into test (select * from new_test);

3 rows created.

ทำการลบตารางข้อมูลชั่วคราวทิ้ง

SQL> drop table new_test;

Table dropped.

ตรวจสอบกันอีกครั้งว่าข้อมูลยังซ้ำกันอีกหรือไม่โดยใช้คำสั่ง select ดังนี้

SQL> select a,b,count(*) from test group by a,b;

ผลลัพธ์ที่ได้

A B COUNT(*)
———- ———- ———-
1 2 1
2 2 1
3 0 1

นี่ก็เป็นอีกหนึ่งวิธีในการกำจัดแถวที่มีข้อมูลซ้ำซ้อนกันในตาราง แต่เพื่อป้องกันการเกิดการซ้ำซ้อนของข้อมูลเหล่านี้ ขอย้ำอย่าลืมใช้ unique constraints หรือ primary key ช่วยได้ ปัญหาข้อมูลซ้ำซ้อนแบบนี้ก็จะไม่มีทางเกิดขึ้นให้เกิดความปวดหัวได้อีก

January 12, 2017

last successful login time scripts

บน Oracle 12c ได้มีการเพิ่มข้อมูลสำหรับแสดงผลวันเวลาที่ผู้ใช้เชื่อมต่อกับระบบได้อย่างสมบูรณ์ไว้แล้ว สามารถเรียกดูได้โดยสั่งดังนี้

ใช้โปรแกรม SQL Developer เข้าระบบให้เรียบร้อยดังภาพ

พิมพ์คำสั่ง
alter session set NLS_TIMESTAMP_TZ_FORMAT='DD.MM.YYYY HH24:MI:SS';
col username   format a15
col last_login format a25
select username,last_login
from   dba_users
order by username;
ดังภาพ

ได้ผลลัพธ์ดังภาพ

จากภาพจะมี User ของระบบและ User ที่ใช้งานประจำ ปนมาด้วยต้องแก้ SQL ให้กรองออกดังนี้

col username   format a15
col last_login format a25
select username,last_login
from dba_users
where last_login is null
and username not like '%SYS%'
and username not like '%SPATIAL%'
and username not like '%SCHEMA%'
and username not like '%NULL'
and username not like 'XDB'
and username not like 'APEX%'
order by username;

ได้ผลลัพธ์ดังนี้

สำหรับ Oracle รุ่นเก่ากว่านี้จำเป็นจะต้องเปิด Audit Trail จึงจะสามารถตรวจสอบเวลาที่ user เชื่อมต่อกับระบบได้แต่สามารถตรวจสอบ ณ เวลาปัจจุบันได้ว่ามีใครเชื่อมต่ออยู่บ้างโดยพิมพ์คำสั่ง

select username,machine,terminal,logon_time from v$session;

จบขอให้สนุกครับ

January 11, 2017
Oracle Database 12CR1 monitoring with MRTG
- OS: Oracle Enterprise Linux 7.2 (CentOS 7.2)
- วิธีติดตั้ง MRTG สามารถติดตั้งได้โดยสามารถดูคู่มือที่ ติดตั้ง mrtg บน ubuntu อาจไม่เหมือนกันแต่สามารถทำได้ทำนองเดียวกัน
- กราฟสำหรับ Idle CPU and Load average, CPU Time spent waiting for IO, Traffic Analysis for eth0, TCP Current Establish สามารถใช้ script เดียวกับลิงค์ในข้อ ๒ ได้เลย
- สร้างแฟ้ม /etc/mrtg/get-memory.sh มีข้อความว่า
  #!/bin/bash
  FREE=$(free |grep "Mem:"|awk '{print $7}')
  SWAP=$(free |grep "Swap:"|awk '{print $3}')
  TIME=$(uptime)
  echo "${FREE}"
  echo "${SWAP}"
  echo "$TIME"
  hostname
  
  สร้างแฟ้ม /etc/mrtg/myhost-memory.cfg มีข้อความว่า
  WorkDir: /var/www/mrtg/myhost
  Target[myhost-mem]:`/etc/mrtg/get-memory.sh`
  MaxBytes[myhost-mem]: 20000000000
  Title[myhost-mem]: Free Memory and Swap Used
  PageTop[myhost-mem]: <H1>Free Memory and Swap Used</H1>
  ShortLegend[myhost-mem]: bytes
  YLegend[myhost-mem]: bytes
  LegendI[myhost-mem]: Free Memory:
  LegendO[myhost-mem]: Swap Used:
  Legend1[myhost-mem]: Free memory, in bytes
  Legend2[myhost-mem]: Swap Used, in bytes
  Options[myhost-mem]: gauge, nopercent, growrightทดสอบสร้างภาพต้นแบบด้วยคำสั่ง
  
  env LANG=C /usr/bin/mrtg/myhost-memory.cfgปรับปรุงแฟ้ม index.html ด้วยคำสั่ง
  indexmaker --column=2 --output=/var/www/mrtg/myhost/index.html /etc/mrtg/myhost-cpu.cfg /etc/mrtg/myhost-cpu-io.cfg /etc/mrtg/myhost-speed-eth0.cfg /etc/mrtg/myhost-tcpestab.cfg /etc/mrtg/myhost-memory.cfg
- โฟลเดอร์ที่ต้องเฝ้าระวังได้แก่ /u02/app/oracle/adump, /u02/app/oracle/diag/rdbms/regist/regist/alert, /u02/app/oracle/rdbms_trace ซึ่งเป็นโฟลเดอร์สำหรับเก็บ Log ไฟล์ต่างๆ ซึ่งอาจมีขนาดเพิ่มขึ้นจนระบบไม่สามารถให้บริการได้ และโฟลเดอร์ /u03 เป็นโฟลเดอร์ที่ใช้เก็บ archive log (ในกรณีที่ฐานข้อมูลเปิด archive log mode)
  - สร้างแฟ้ม /etc/mrtg/get-diskfree-misc1.sh มีข้อความว่า
    #!/bin/bash adump=$(du -sm /u02/app/oracle/adump|awk '{ print $1 }') free=$(df -m /u02|grep u02|awk '{ print $4 }') TEMP=$(uptime|grep -o "load average.*"|awk '{print $3}'|cut -d',' -f 1) LOAD=$(echo "${TEMP:-0} * 100"|bc|cut -d'.' -f 1) TIME=$(uptime) echo "${adump}" echo "${free}" echo "$TIME" hostname
  - สร้างแฟ้ม /etc/mrtg/myhost-diskfree-misc1.cfg มีข้อความว่า
    WorkDir: /var/www/mrtg/myhost Target[myhost-misc1]:`/etc/mrtg/get-diskfree-misc1.sh` MaxBytes[myhost-misc1]: 20000000000 Title[myhost-misc1]: Free disk space and disk Used of /u02/app/oracle/adump PageTop[myhost-misc1]: Free disk space and disk Used of /u02/app/oracle/adump ShortLegend[myhost-misc1]: bytes kMG[myhost-misc1]: M,G,T kilo[myhost-misc1]: 1024 YLegend[myhost-misc1]: bytes LegendI[myhost-misc1]: Disk Used: LegendO[myhost-misc1]: Free Disk: Legend1[myhost-misc1]: Disk usage, in Bytes Legend2[myhost-misc1]: Free Disk Space, in Bytes Options[myhost-misc1]: gauge, nopercent, growright Timezone[myhost-misc1]: Bangkok
    ทดสอบสร้างภาพต้นแบบด้วยคำสั่ง
    env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-diskfree-misc1.cfgปรับปรุงแฟ้ม index.html ด้วยคำสั่ง
    indexmaker --column=2 --output=/var/www/mrtg/myhost/index.html /etc/mrtg/myhost-cpu.cfg /etc/mrtg/myhost-cpu-io.cfg /etc/mrtg/myhost-speed-eth0.cfg /etc/mrtg/myhost-tcpestab.cfg /etc/mrtg/myhost-memory.cfg /etc/mrtg/myhost-diskfree-misc1.cfg
  - สร้างแฟ้มเลียนแบบข้อ 1 และ 2 สำหรับโฟลเดอร์ที่เหลือ
  - แก้ไขแฟ้ม /etc/mrtg/mymrtg.sh เพิ่มข้อความ env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-diskfree-misc1.cfg ต่อท้ายไฟล์และเพิ่มทุกไฟล์ของทุกโฟลเดอร์
  - สำหรับโฟลเดอร์ /u03 ให้สร้างแฟ้ม /etc/mrtg/get-diskfree-u03.sh มีข้อความว่า
    #!/bin/bash
    used=$(df -m /u03|grep u03|awk '{ print $3 }')
    free=$(df -m /u03|grep u03|awk '{ print $4 }')
    TEMP=$(uptime|grep -o "load average.*"|awk '{print $3}'|cut -d',' -f 1)
    LOAD=$(echo "${TEMP:-0} * 100"|bc|cut -d'.' -f 1)
    TIME=$(uptime)
    echo "${used}"
    echo "${free}"
    echo "$TIME"
    hostnamenof=$(ls -d1 /u03/app/oracle/fast_recovery_area/REGIST/archivelog/* |wc -l)
    max=3if [ "${nof}" == "${max}" ]
    then
            nod=$(expr ${max} - 1)
            f2d=$(ls -d1 /u03/app/oracle/fast_recovery_area/REGIST/archivelog/*|head -${nod})
            rm -rf ${f2d}
            su - oracle -c "/bin/sh /home/oracle/reclaim.sh"
    fiสร้างแฟ้ม /home/oracle/reclaim.sh มีข้อความว่า
    rman target / <<EOF
    crosscheck archivelog all;
    delete noprompt expired archivelog all;
    quit
    EOF
    สร้างแฟ้ม /etc/mrtg/myhost-diskfree-u03.cfg มีข้อความว่า
    WorkDir: /var/www/mrtg/myhost
    Target[myhost-u03]:`/etc/mrtg/get-diskfree-u03.sh`
    MaxBytes[myhost-u03]: 20000000000
    Title[myhost-u03]: Free disk space and disk Used of /u03
    PageTop[myhost-u03]: Free disk space and disk Used of /u03
    ShortLegend[myhost-u03]: bytes
    kMG[myhost-u03]: M,G
    kilo[myhost-u03]: 1024
    YLegend[myhost-u03]: bytes
    LegendI[myhost-u03]: Disk Used:
    LegendO[myhost-u03]: Free Disk:
    Legend1[myhost-u03]: Disk usage, in Bytes
    Legend2[myhost-u03]: Free Disk Space, in Bytes
    Options[myhost-u03]: gauge, nopercent, growrightทดสอบสร้างภาพต้นแบบด้วยคำสั่ง
    env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-diskfree-u03.cfg
    อย่าลืมปรับปรุงแฟ้ม index.html ด้วย
- เฝ้าระวังขนาดของ Tablespace SYSTEM และ USERS
  - สร้างแฟ้ม /etc/mrtg/get-tablespace-system.sh มีข้อความว่า
    #!/bin/bash
    used=$(su - oracle -c "sh /home/oracle/monitor/tablespacesize.sh"|grep SYSTEM|awk '{ print $2 }'|sed -e 's/,//g')
    used=$(expr ${used} \* 1024)
    free=$(su - oracle -c "sh /home/oracle/monitor/tablespacesize.sh"|grep SYSTEM|awk '{ print $3 }'|sed -e 's/,//g')
    free=$(expr ${free} \* 1024)
    TIME=$(uptime)
    echo "${used}"
    echo "${free}"
    echo "$TIME"
    hostname
    สร้างแฟ้ม /home/oracle/monitor/tablespacesize.sh มีข้อความว่า
    #!/bin/bash
    sqlplus / as sysdba << EOF
    col "Tablespace" for a22
    col "Used MB" for 99,999,999
    col "Free MB" for 99,999,999
    col "Total MB" for 99,999,999
    select df.tablespace_name "Tablespace",
    totalusedspace "Used MB",
    (df.totalspace - tu.totalusedspace) "Free MB",
    df.totalspace "Total MB",
    round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
    "Pct. Free"
    from
    (select tablespace_name,
    round(sum(bytes) / 1048576) TotalSpace
    from dba_data_files
    group by tablespace_name) df,
    (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
    from dba_segments
    group by tablespace_name) tu
    where df.tablespace_name = tu.tablespace_name ;
    quit
    EOF
    สร้างแฟ้ม /etc/mrtg/myhost-tablespace-system.cfg
    WorkDir: /var/www/mrtg/myhost
    Target[myhost-system]:`/etc/mrtg/get-tablespace-system.sh`
    MaxBytes[myhost-system]: 20000000000
    Title[myhost-system]: Tablespace SYSTEM disk usage
    PageTop[myhost-system]: Tablespace SYSTEM Disk Usage
    ShortLegend[myhost-system]: bytes
    kMG[myhost-system]: k,M,G
    kilo[myhost-system]: 1024
    YLegend[myhost-system]: bytes
    LegendI[myhost-system]: Disk Used:
    LegendO[myhost-system]: Free Disk:
    Legend1[myhost-system]: Disk usage, in Bytes
    Legend2[myhost-system]: Free Disk Space, in Bytes
    Options[myhost-system]: gauge, nopercent, growrightทดสอบสร้างภาพต้นแบบด้วยคำสั่ง
    env LANG=C /usr/bin/mrtg /etc/mrtg/myhost-tablespace-system.cfgอย่าลืมปรับปรุง index.html ด้วย
  - ทำแบบเดียวกันกับ tablespace users
- จบขอให้สนุก
May 10, 2016

Tag: oracle database

Oracle: retrieve top n records from a query

Removing duplicate records by using Oracle’s ROWID

อีกหนึ่งวิธีในการกำจัดข้อมูลที่ซ้ำซ้อนกันในตาราง

last successful login time scripts

Oracle Database 12CR1 monitoring with MRTG