Posted by: piman007 | 29-11-2007

Reset or ALTER sequence บน Oracle

เมื่อวานคุยกับพี่พร(อดีต DBA ของบริษัท) เรื่องการ droup และ create SEQ บน Oracle เพื่อทำการ reset SEQ ทุกๆ วัน
เนื่องจาก Format จำนวน byte ของ Sequence ที่ใช้ใน Project มีแค่ 4 ดังนั้นสูงสุดก็แค่ 9999 ซึ่งไม่เพียงพอสำหรับข้อมูลเข้า
วันละหลายพัน record
 
เอาล่ะ ก่อนอื่นก็โดนพี่พรแกด่ามาก่อน(ตามสไตล์แก) ว่า ทำไมใช้วิธีเด็กๆ อย่างนี้ไม่มีใครเค้าทำกันหรอก droup และ create Sequence ทุกวัน
ถ้ายังยืนยันว่า droup และ create seq.  เนื่องจาก สิทธิ์ที่ผมได้ก็คือ ADM มิใช่ DBA ดังนั้นก็มีปัญหาพอสมควร
 
วิธีที่ 1 droup และ create seq.
1. grant สิทธิ์ droup และ create seq ให้กับ adm
2. grant select any sequence to xxxadm; <– ตัวนี้ล่ะที่ DBA ลืม grant ให้ เมื่อ droup และ create ใหม่แล้ว select ไม่ได้
 
วิธี droup และ create ให้ใส่ owner เข้าไปด้วย เช่น ในที่นี้ เป็น XXXDBA

DROP SEQUENCE XXXDBA.XXX_SEQ

CREATE SEQUENCE XXXDBA.XXX_SEQ  START WITH 1 INCREMENT BY 1 MINVALUE 0 CACHE 10

อธิบายแซะหน่อย
     START WITH 1 ค่าเริ่มต้นคือ 1
     INCREMENT BY 1  เพิ่มขึ้นทีละ 1
     MINVALUE 0 ค่าต่ำสุดเป็น 0 (เอ๊ะขัดแย้งกับ START WITH เน๊อะ ใครรู้บ้างบอกหน่อย)
     CACHE 10 หมายถึง สร้างเตรียมรอไว้ก่อน 10 ค่า แต่ค่านี้ล่ะบางทีเปลือง seq. ชะมัด แต่ก็แลกด้วยความเร็วที่ถือว่าคุ้ม
 
วิธีที่ 2  เป็นคำสั่ง Procedure เรียกใช้คำสังนี้เพื่อ ALTER sequence ไม่ต้อง droup และ creacte ใหม่อีกต่อไป (ยังไม่ได้ลอง อิอิ)
CREATE OR REPLACE PROCEDURE resetseq is
a  number;
b  number;
c  number;
i  number;
BEGIN
 a:=0;
 b:=0;
 c:=0;
 i:=0;
 select bndba.bn_serl.nextval into a from sys.dual;
 select bndba.bn_serl.currval into a from sys.dual;
 b:=a-1;
 for i in 1..b loop
  EXECUTE IMMEDIATE ‘alter sequence bn_serl increment by -1’;
  select bndba.bn_serl.nextval into c from sys.dual;
 end loop;
 EXECUTE IMMEDIATE ‘alter sequence bn_serl increment by 1’;
END;
 
ขอบคุณพี่พร (ศิริพร) ครับ

ใส่ความเห็น

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / เปลี่ยนแปลง )

Twitter picture

You are commenting using your Twitter account. Log Out / เปลี่ยนแปลง )

Facebook photo

You are commenting using your Facebook account. Log Out / เปลี่ยนแปลง )

Google+ photo

You are commenting using your Google+ account. Log Out / เปลี่ยนแปลง )

Connecting to %s

หมวดหมู่

%d bloggers like this: