قابلیت SQL domain در اوراکل 23ai

نویسنده مطلب: وحید یوسف زاده 4 Comments

دیتابیس اوراکل در نسخه 23c سعی کرده تا بسیاری از قابلیتهای موجود در دیتابیسهای رابطه ای دیگر را در این version ارائه کند قابلیتهای ساده ای نظیر «Schema level privilege» – «Boolean data type» – «Direct Joins for UPDATE and DELETE» -«SELECT without FROM» و …

یکی دیگر از این قابلیتها که موضوع بحث این مستند هم هست، SQL domain می باشد که می تواند شامل مجموعه ای از محدودیتها و خصوصیتها باشد و با تخصیص آن به یک ستون، می توان محدودیتهایی را برای آن ستون اعمال کرد به عبارت دیگر، SQL domain امکان توسعه Data type را متناسب با Business فراهم می کند.

یکی از کاربردهای مهم این قابلیت به زمانی برمی گردد که بخواهیم برای مقادیر ورودی یک ستون، شرطهای به خصوصی را اعمال کنیم. مثلا برای ستون Age با نوع داده number، با شرط Age>=18، از ثبت مقادیر کمتر از 18 جلوگیری کنیم و یا به عنوان مثالی کاربردی تر، برای ستونی که قرار است آدرس Email در آن ذخیره شود، شرطی را اعمال کنیم تا این ستون، صرفا اطلاعات ورودی با فرمت text@text.text را بپذیرد.

(بیشتر…)

معرفی Role جدید برای Developerها در اوراکل 23ai

نویسنده مطلب: وحید یوسف زاده Leave a comment

زمانی که ادمین دیتابیس، User جدیدی را برای Developerها ایجاد می کند شاید در تردید باشد که چه مجوزهایی را به این User اهدا کند تا Developer حداقل دسترسی لازم برای تولید Application را در نقطه شروع داشته باشد.

شاید بسیاری از ادمینها، Roleهای Connect و Resource را به این User اهدا می کنند تا Developer بتواند از طریق این User به دیتابیس وصل شده و در صورت لزوم اقداماتی را نظیر ساخت Table، View، Trigger و … انجام دهد.

این دو Role شامل system privilegeهای زیر هستند:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create user usef identified by a;
User created.
SQL> grant connect,resource to usef;
Grant succeeded.
SQL> conn usef/a@target:1521/PDBTARGET
Connected.

SQL> select * from session_privs;
CREATE SESSION
CREATE TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
SET CONTAINER
10 rows selected.

(بیشتر…)

اوراکل 23ai- قابلیت Read Only User

نویسنده مطلب: وحید یوسف زاده Leave a comment

Read Only User یکی از قابلیتهای جدید اوراکل در نسخه 23c است که اوراکل در مستندات مربوط به نسخه 23c حرفی در مورد آن نزده و ظاهرا اولین مستند آن مربوط به آقای Pete Finnigan است.

از طریق این قابلیت می توانیم امکان هرگونه تغییر داده را از یک کاربر بگیریم به طوری که کاربر با داشتن مجوز لازم برای insert، delete و update نتواند این دستورات را اجرا کند و یا با داشتن مجوز ساخت جدول، ویو، پروسیجر و … قابلیت ایجاد این اشیا را نداشته باشد چرا که ایجاد یک شی نیازمند تغییر داده در جداول Data Dictionary است.

بنابرین در صورتی که کاربر در حالت read only قرار بگیرد صرفا می تواند به دیتابیس وصل شده و اطلاعات جداول را ببیند و یا پروسیجر، فانکشن و پکیجی که تغییری را ایجاد نمی کنند اجرا کند.

در زمان ساخت یک user می توان آن را در حالت read only قرار داد:

SQL> create user USEF identified by abc read only;
User created.

(بیشتر…)

اوراکل 23ai- قابلیت Automatic SQL Transpiler

نویسنده مطلب: وحید یوسف زاده Leave a comment

همانطور که می دانید استفاده از تابع در یک دستور sqlای، context switch بین SQL engine و PL/SQL engine را در پی خواهد داشت و رخ دادن متعدد context switch می تواند اثرات منفی بر روی performance دیتابیس داشته باشد.

اوراکل در نسخه 21c برای افزایش سرعت اجرای این دسته از پرس و جوها، SQL Macros را معرفی کرده است که با استفاده از این قابلیت، متن پرس و جوی حاوی function به فرم ساده و بدون استفاده از function بازنویسی می شود.

در نسخه 23c هم اوراکل قابلیت Automatic SQL Transpiler را در این زمینه معرفی کرده است که در صورت تنظیم پارامتر sql_transpiler به مقدار ON، به صورت خودکار و بدون مداخله کاربر، تابع استفاده شده در متن دستور SQL «در صورت امکان» به SQL expression تبدیل خواهد شد تا از سربار ناشی از اجرای function در SQL کاسته شود.

(بیشتر…)

اوراکل 23ai – استفاده از Direct Join در دستورات Delete و Update

نویسنده مطلب: وحید یوسف زاده 2 Comments

برای حذف و یا بروزرسانی رکوردهای یک جدول «در بسیاری از مواقع» نیاز به join آن جدول با جداول دیگر داریم تا تعیین کنیم کدام یک از رکوردها باید delete و یا update شوند تا قبل از اوراکل 23c در این شرایط به ناچار می بایست از دستور select هم همزمان استفاده می کردیم و امکان استفاده از Direct Join را نداشتیم اما در نسخه 23c، اوراکل این قابلیت را اضافه کرده است.

برای مثال دستور زیر با join بین دو جدول employees و departments مشخص می کند کدام رکوردهای جدول employees باید بروزرسانی شوند:

SQL> update employees e
  2     set e.salary = e.salary * 2
  3     from departments d
  4   where d.department_id = e.department_id
  5     and d.department_name = 'IT';
5 rows updated

از این قابلیت برای حذف رکوردها هم می توانیم استفاده کنیم:

SQL> delete employees e
  2     from departments d
  3   where d.department_id = e.department_id
  4     and d.department_name = 'IT'
  5     and e.employee_id!=d.manager_id;
4 rows deleted

 

اوراکل 23ai – اجرای دستور SELECT بدون عبارت FROM

نویسنده مطلب: وحید یوسف زاده 1 Comment

قبلا در مطلبی  نکاتی را در مورد “جدول dual و فراخوانی توابع در اوراکل و پستگرس” ارائه کردیم و توضیح دادیم که در دیتابیس پستگرس، در زمان فراخوانی توابع و عبارتها به همراه دستور select الزامی به استفاده از کلمه کلیدی FROM نخواهد بود. در صورتی که در دیتابیس اوراکل به ناچار باید از عبارت FROM در دستور SELECT استفاده کرد همچنین ثابت کردیم که به لحاظ پرفورمنسی استفاده از جدول dual در این مواقع بسیار کاربردی است.

حالا در نسخه 23c اوراکل امکان اجرای دستور SELECT را بدون عبارت FROM فراهم کرده است:

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Jun 15 13:56:58 2023
SQL> select sysdate;
ORA-00923: FROM keyword not found where expected

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> SELECT sysdate;
SYSDATE
---------
15-JUN-23
SQL> select 15+85*98;
  15+85*98
----------
      8345

(بیشتر…)

اوراکل 23ai- قابلیت Traditional Auditing دیگر پشتیبانی نمی شود!

نویسنده مطلب: وحید یوسف زاده 1 Comment

از اوراکل 23c قابلیت Traditional Auditing(یا همان Standard Auditing) دیگر پشتیبانی نمی شود! و قابلیت Unified Auditing در این نسخه از اوراکل به صورت پیش فرض فعال است:

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed Jun 14 18:26:42 2023
SQL> SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
VALUE
-------------
TRUE

البته پارامترها و جداول مربوط به Traditional Auditing کماکان وجود دارند:

SQL> select count(*) from dba_audit_trail;
  COUNT(*)
----------
         0

SQL> show parameter audit_trail
NAME         TYPE        VALUE
------------ ----------- --------
audit_trail  string      DB

همچنین امکان تغییر مقدار پارامتر audit_trail هم وجود دارد هر چند که در حالت عادی تغییر آن اثری ندارد:

SQL> alter system set audit_trail='db,extended' scope=spfile;
System altered.

(بیشتر…)

جلوگیری از Blocking Session در زمان اجرای دستور Update(قابلیت Lock-free reservation)

نویسنده مطلب: وحید یوسف زاده 2 Comments

شرایطی را در نظر بگیرید که قرار است فیلدی به عنوان شمارنده، دائما توسط تعداد زیادی session بروزرسانی شود(با همزمانی بالا) به این شیوه که با هر بار انجام «اقدامی مشخص»، دستور updateای اجرا شده و یک عدد به این شمارنده اضافه کند.

در این حالت با توجه به زیاد بودن تعداد sessionهای همزمان، احتمال رخ دادن Blocking session هم بسیار افزایش می یابد چرا که در دیتابیس اوراکل اگر دو کاربر قصد ویرایش یک رکورد را داشته باشند و با فاصله زمانی کمی دستور Update را اجرا کنند، کاربری که دیرتر دستور update را صادر کرده Block خواهد شد و تا زمانی که کاربر اول(کاربری که زودتر رکورد را در اختیار گرفته) به تراکنش خاتمه ندهد، کاربر دوم در حالت Block باقی خواهد ماند.

--session 1:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
      2190
SQL> update tbl_counter set counttt=counttt+1;
1 row updated

--session 2:
SQL> select sid from v$mystat where rownum=1;
       SID
----------
       944

SQL> update tbl_counter set counttt=counttt+1;
Executing…

بلاک شدن session دوم را می توانیم از طریق دستور زیر ببینیم:

SQL>  select SID,ID1,ID2,LMODE,block,request from v$lock where type='TX';
       SID        ID1        ID2      LMODE      BLOCK    REQUEST
---------- ---------- ---------- ---------- ---------- ----------
       944     458766       2511          0          0          6
      2190     458766       2511          6          1          0

بنابرین زمانی که کاربران زیادی قصد کار بر روی یک رکورد را دارند، مکانیزم locking امکان ویرایش رکورد را در هر لحظه به یک نفر از آنها خواهد داد و بقیه sessionها Block می شوند. پیدا کردن ترفندی برای جلوگیری از Block شدن sessionها می تواند در بعضی از این شرایط نظیر «شرایط ذکر شده در ابتدای متن» بسیار راهگشا باشد.

(بیشتر…)

امکان استفاده از عبارت if [not] exists در هنگام ایجاد و یا حذف یک object

نویسنده مطلب: وحید یوسف زاده Leave a comment

در زمان ساخت یک object، اگر آن object از قبل موجود باشد، با خطای already exist مواجه خواهیم شد همچنین اگر قصد حذف کردن شی ای که موجود نیست را داشته باشیم، خطای does not exist رخ خواهد داد. برای جلوگیری از این دو خطا می توان از عبارت if [not] exists استفاده کرد.

برای مثال، جدول TB از قبل وجود دارد اگر از این مسئله مطمئن نیستیم، می توانیم دستور ساخت این جدول را با استفاده از عبارت if not exists اجرا کرده تا از خطای احتمالی ORA-00955 جلوگیری کنیم:

SQL> create table tb (c1 number(10));
ORA-00955: name is already used by an existing object
SQL> create table if not exists tb (c1 number(10));
Table created
SQL> create table if not exists tb (c1 number(10));
Table created

این عبارت برای دستورات DDLای دیگر هم کاربرد دارد:

SQL> drop sequence ss;
ORA-02289: sequence does not exist
SQL> drop sequence if exists ss;
Sequence dropped

(بیشتر…)

افزایش حداکثر تعداد ستونهای یک جدول به 4096

نویسنده مطلب: وحید یوسف زاده Leave a comment

تا قبل از اوراکل نسخه 23c، حداکثر تعداد ستونهای یک جدول به عدد 1000 محدود بود:

ORA-01792: maximum number of columns in a table or view is 1000

در نسخه 23c هر جدول می تواند تا 4096 ستون داشته باشد البته برای استفاده از این قابلیت باید پارامتر max_columns را به extended تنظیم کرد:

Connected to Oracle Database 23c Free, Release 23.0.0.0.0 
SQL> create table tb (c1 number(10));
Table created

SQL> alter system set max_columns=extended scope=spfile;
System altered

SQL> declare
  2  comm varchar2(100);
  3  begin
  4    for i in 2..4095 loop
  5     comm:='alter table  tb add c'||''||i||' number';
  6     execute immediate comm;
  7    end loop;
  8  end;
  9  /
  PL/SQL procedure successfully completed

SQL> select count(*) from dba_tab_columns p where p.table_name='TB';
  COUNT(*)
----------
      4095