การเรียกดูรายการ object ในฐานข้อมูล Oracle

by
Post Views: 4,910

คำสั่งที่ใช้ในการเรียกดู รายการ object ในฐานข้อมูล Oracle

  • การเรียกดูรายการ object ทั้งหมดในฐานข้อมูล Oracle (เช่น table, view ฯลฯ)  สามารถเรียกดูได้จาก view ที่ชื่อว่า ALL_OBJECTS  ตัวอย่างคำสั่งคือที่ใช้เรียกดูคือ

 

SELECT * FROM ALL_OBJECTS;

 

โดยคำสั่งนี้จะแสดงรายการ object ทั้งหมดที่มี

 

  • แต่ในการใช้งานส่วนใหญ่ อาจจะต้องการเจาะจงดูแค่บางเงื่อนไข เช่น ต้องการดูรายการ table ทั้งหมด โดยเจาะจงแค่ schema ใด schema  หนึ่งเท่านั้น  ซึ่งสามารถใช้เงื่อนไขจากฟีลด์ต่อไปนี้ คือ
    • OBJECT_TYPE เป็นการเรียกดูตามเงื่อนไขของประเภท object เช่น หากต้องการดูเฉพาะ table จะใช้เงื่อนไขเป็น  WHERE OBJECT_TYPE = ‘TABLE’
    • OWNER เป็นการเรียกดูเฉพาะเจาะจง schema ใด schema  หนึ่ง เช่น ต้องการเรียกดูข้อมูลจาก schema ชื่อ SCOTT จะใช้เงื่อนไขเป็น WHERE OWNER = ‘SCOTT’
    • ตัวอย่างคำสั่งเต็ม ๆ กรณีที่ต้องการเรียกดูเฉพาะ table ทั้งหมดที่อยู่ใน schema ที่ชื่อ SCOTT

SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE=’TABLE’ AND OWNER=’SCOTT’;

 

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

เพื่อให้เห็นภาพการนำไปใช้งานจริง จึงขอยกตัวอย่างที่เคยใช้งาน คือ การสร้างสคริปต์คำสั่ง sql ที่อ้างอิงชื่อ table ที่อยู่ใน schema ใด schema หนึ่ง โดยในที่นี้จะยกตัวอย่างการ grant สิทธิ์ในการเข้าถึงข้อมูลของ schema หนึ่งไปให้กับอีก schema หนึ่ง

  • ที่มาที่ไปคือ ในบางครั้ง เราต้องการ grant สิทธิ์ในการเข้าถึงข้อมูลของ schema หนึ่งไปให้กับอีก schema หนึ่ง เช่น ต้องการ grant สิทธิ์การเรียกดูข้อมูลใน table ชื่อ TABLE01 ของ schema ที่ชื่อ SCHEMA01 ให้ schema ที่ชื่อ SCHEMA02 สามารถเรียกดูข้อมูลได้ คำสั่งที่ใช้ในการ grant คือGRANT SELECT ON SCHEMA01.TABLE01 TO SCHEMA02;
  • แต่หากต้องการ grant หลาย ๆ  table ก็ต้องพิมพ์คำสั่งเหล่านี้ทีละคำสั่งซ้ำๆ ไปจนครบตามชื่อ talbe ที่ต้องการ grant ซึ่งถ้า table ที่ต้องการ grant มีเป็นจำนวนมากก็จะยิ่งใช้เวลามากยิ่งขึ้น และอีกปัญหาคือ มีโอกาสพิมพ์ชื่อ table ผิด
  • เทคนิคในการสร้างสคริปต์เพื่อช่วยให้การทำงานรวดเร็วขึ้นจะมีรูปแบบดังนี้คือSELECT ‘GRANT SELECT ON OWNER_SCHEMA.’ || OBJECT_NAME || ‘ TO OTHER_SCHEMA;’
    FROM ALL_OBJECTS
    WHERE OBJECT_TYPE = ‘TABLE’;

    เมื่อ

    • OWNER_SCHEMA คือชื่อของ schema ที่เป็นเจ้าของ table
    • OBJECT_NAME คือชื่อฟีลด์ที่อยู่ใน ALL_OBJECTS โดยเป็นข้อมูลของชื่อ object ซึ่งกรณีนี้คือชื่อของ table นั่นเอง
    • OTHER_SCHEMA คือชื่อของ schema ที่ได้รับการ grant สิทธิ์ให้เรียกดูข้อมูลใน table ของ OWNER_SCHEMA
    • WHERE OBJECT_TYPE = ‘TABLE’ เป็นการระบุเงื่อนไขว่าต้องการ object ที่เป็น table เท่านั้น

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

GRANT SELECT ON OWNER_SCHEMA.TABLE01 TO OTHER_SCHEMA;

GRANT SELECT ON OWNER_SCHEMA.TABLE02 TO OTHER_SCHEMA;

GRANT SELECT ON OWNER_SCHEMA.TABLE03 TO OTHER_SCHEMA;

GRANT SELECT ON OWNER_SCHEMA.TABLE04 TO OTHER_SCHEMA;

…….

…….

…….

GRANT SELECT ON OWNER_SCHEMA.TABLE99 TO OTHER_SCHEMA;

 

โดยคำสั่งทั้งหมดจะเท่ากับจำนวน table ของ OWNER_SCHEMA นั่นเอง ซึ่งหากไม่ต้องการ grant table ไหนก็ลบคำสั่ง grant เฉพาะ table นั้นออกไปได้

  • จะเห็นว่าการใช้เทคนิคลักษณะนี้ จะทำให้สามารถสร้างสคริปต์คำสั่งที่มีลักษณะแบบเดียวกันได้ภายในเวลาอันรวดเร็ว และลดความผิดพลาดของการอ้างอิงชื่อ object ลงไปได้ด้วย
  • อ้างอิงข้อมูล https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2005.htm#i1583352