امکان استفاده از عبارت 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

(بیشتر…)

استفاده از Column Alias در قسمت Group by  و Having در اوراکل 23ai

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

در نسخه 23c می توان از Column Alias در قسمت GROUP BY و HAVING استفاده کرد. این امکان تا قبل از نسخه 23c وجود نداشت:

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
SQL> select lower(owner) as Malek, sum(bytes / 1024 / 1024) as SIZE_MB
  2    from dba_segments
  3   group by Malek
  4  having SIZE_MB>100;
ERROR at line 4:
ORA-00904: "SIZE_MB": invalid identifier

در نسخه 21c برای جلوگیری از خطای invalid identifier فوق، می بایست Alias ستون را حذف و کوئری را به صورت زیر بازنویسی کرد:

select lower(owner) as Malek, sum(bytes / 1024 / 1024) as SIZE_MB
  from dba_segments
 group by owner
having sum(bytes / 1024 / 1024) > 10;

(بیشتر…)

نوع داده Boolean در اوراکل 23ai

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

اوراکل در نسخه 23c دیتا تایپ Boolean را در SQL معرفی کرده است(البته این نوع داده قبلا در PLSQL وجود داشته است) و با این قابلیت، در هنگام ساخت جدول می توان نوع داده ستونها را به Boolean تنظیم کرد.

SQL> CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN);
Table created

از کلمه BOOL هم می توان به جای Boolean استفاده کرد.

SQL> CREATE TABLE jadval1 (id NUMBER, is_accept BOOL);
Table created

برای ستونهای از نوع Boolean میتوان مقدار پیش فرض تنظیم نمود:

SQL> CREATE TABLE jadval1 (id NUMBER, is_accept BOOL DEFAULT FALSE);
Table created

برای مقداردهی ستون is_accept می توانیم از سه کلمه کلیدی TRUE، FALSE و NULL استفاده کنیم:

SQL> insert into jadval1 values(1,true);
1 row inserted
SQL> insert into jadval1 values(2,null);
1 row inserted
SQL> insert into jadval1 values(3,false);
1 row inserted

SQL> select * from jadval1;
        ID    IS_ACCEPT
---------- ----------
         1          1
         2 
         3          0

(بیشتر…)

اوراکل 23ai- تنظیم اولویت برای PDB

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

سوال: اگر CDBای بیش از یک PDB داشته باشد، چگونه می توان در مورد ترتیب open شدن این PDBها اعمال نظر کرد؟

تا قبل از اوراکل 23c راهکاری در این زمینه وجود نداشت و با اجرای دستور startup، تضمینی در مورد ترتیب باز شدن این PDBها وجود نداشت(معمولا بر اساس شماره container این PDBها باز می شدند). اما در نسخه 23c قابلیت جدیدی ارائه شد و بر اساس آن می توان برای هر PDB یک PRIORITY تنظیم کرد تا از طریق آن، ترتیب انجام عملیاتهایی نظیر open کردن PDBء، upgrade و یا restoration را کنترل کرد.

ساختار کلی دستور را در قسمت زیر می بینید:

ALTER PLUGGABLE DATABASE <PDB name> PRIORITY <value>

در این دستور، نام PDB الزامی است و PRIORITY می تواند مقداری بین 1 تا 4096 بگیرد که هر چه این عدد کمتر باشد، PDB اولویت بیشتری دارد به طور مثال، PDB با اولویت 1 نسبت به PDB با اولویت 2 زودتر open خواهد شد.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEHRANPDB                      READ WRITE NO
         4 BABOLPDB                       READ WRITE NO
         5 VARAMINPDB                     READ WRITE NO
         6 ABADANPDB                      READ WRITE NO
SQL> ALTER PLUGGABLE DATABASE babolpdb PRIORITY 1;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE ABADANPDB PRIORITY 2;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE VARAMINPDB PRIORITY 3;
Pluggable database altered.
SQL> ALTER PLUGGABLE DATABASE TEHRANPDB PRIORITY 4;
Pluggable database altered.

با restart کردن دیتابیس خواهیم دید که دیتابیسها با ترتیب اعلام شده باز شده اند:

SQL> startup force;
SQL> select OPEN_TIME,NAME,PRIORITY from v$pdbs order by 1;
OPEN_TIME                                NAME         PRIORITY
---------------------------------------- ---------- ----------
10-MAY-23 05.52.43.190 PM +04:30         PDB$SEED            1
10-MAY-23 05.52.43.812 PM +04:30         BABOLPDB            1
10-MAY-23 05.52.44.889 PM +04:30         ABADANPDB           2
10-MAY-23 05.52.45.929 PM +04:30         VARAMINPDB          3
10-MAY-23 05.52.47.102 PM +04:30         TEHRANPDB           4

ترتیب باز شدن PDB در ALERT LOG هم ثبت شده است:

PDB$SEED(2):Opening pdb with Resource Manager plan: DEFAULT_PLAN
BABOLPDB(4):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
2023-05-10T17:52:44.739491+04:30
ABADANPDB(6):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
2023-05-10T17:52:45.784421+04:30
VARAMINPDB(5):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18
2023-05-10T17:52:46.828984+04:30
TEHRANPDB(3):SUPLOG: Initialize PDB SUPLOG SGA, old value 0x0, new value 0x18

*همراه با اوراکل 12cR2، اولویت PDBها در زمینه upgrade از طریق دستور زیر قابل تنظیم است:

SQL*Plus: Release 12.0.0.0.0 - Production on Wed May 10 16:56:59 2023
SQL> ALTER PLUGGABLE DATABASE babolpdb UPGRADE PRIORITY 5;
Pluggable database altered.

 

Schema Privilege در اوراکل 23ai

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

تا قبل از اوراکل23cء، privilegeها به سه نوع object، system و administrative قابل تقسیم بودند و برای آنکه کاربری صرفا به اشیاء یک اسکیما دسترسی داشته باشد به ناچار می بایست از object privilegeها استفاده کرد که این محدودیت را قبلا در مستندی شرح داده ایم.

رجوع شود به : اهدای مجوز در سطح اسکیما در اوراکل و پستگرس

در نسخه 23c قابلیت جدیدی در این زمینه ایجاد شد و اوراکل نوع دیگری از privilege به نام schema privilege را ارائه کرد که بر اساس آن می توان دسترسی به تمامی اشیاء{موجود و آینده} یک اسکیما را صرفا با اجرای یک دستور به یک کاربر(و یا role) اهدا کرد و یا به کاربر این امکان را داد تا برای اسکیما شی جدیدی بسازد.

schema privilege با کمک system privilegeها کار می کند مثلا می توانیم دسترسی select any table که یک system Privilege محسوب می شود را بر روی یک اسکیما به یک کاربر اهدا کنیم. بدیهی است که schema privilege به نسبت system privilege از دادن دسترسی های اضافه جلوگیری خواهد.

(بیشتر…)

کاهش زمان ساخت Primary/Foreign Key

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

استفاده از عبارت parallel در زمان ساخت primary key منجر به انجام عملیات به صورت همروند نخواهد شد. دستور زیر را مشاهده کنید:

SQL> alter table all_table add constraint pk_1 primary key (id) parallel 10;
Table altered.
Elapsed: 00:00:12.36

با فعال کردن trace خواهیم دید که اوراکل برای ساخت ایندکس متناظر با این PK، از عبارت noparallel استفاده کرده و عبارت parallel تاثیر مثبتی در زمان ایجاد این Primary Key نداشته است:

PARSING IN CURSOR #140396268419760 len=72 dep=1 uid=109 oct=9 lid=109 tim=29571676036680 hv=2994446983 ad='b9930010' sqlid='c1ddax6t7r8n7'
CREATE UNIQUE INDEX "USEF"."PK_1" on "USEF"."ALL_TABLE"("ID") NOPARALLEL

بنابرین زمان اجرای دستور فوق با دستور زیر برابر خواهد بود و اجرای هر دو دستور به 13 ثانیه زمان نیاز دارد:

SQL> alter table all_table add constraint pk_1 primary key (id) noparallel;
Table altered.
Elapsed: 00:00:12.95

(بیشتر…)

اهدای مجوز به فانکشن، پکیج و پروسیجر

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

زمانی که یک program unit(فانکشن، پکیج، پروسیجر) به صورت invoker right ایجاد می شود، کاربر صدا زننده برنامه، با مجوز خودش این برنامه را اجرا خواهد کرد بنابرین اگر جدولی در برنامه موجود باشد که این کاربر به آن دسترسی نداشته باشد، کارش با خطا متوقف خواهد شد(بررسی Invoker’s Rights و Definer’s Rights). برای مثال، تابع زیر را در نظر بگیرید:

SQL> CREATE FUNCTION sys.Tabe(name_malek in varchar2,name_jadval in varchar2)
   RETURN VARCHAR2
   AUTHID CURRENT_USER
AS
V_bytes varchar2(1000);
BEGIN
  select bytes into V_bytes from dba_segments where owner=name_malek and segment_name=name_jadval;
   RETURN V_bytes;
END;
/
Function created.

این تابع که با یوزر sys ایجاد شده، قرار است نام segment را دریافت و حجم آن را بر اساس بایت برگرداند. عبارت AUTHID CURRENT_USER بیانگر invoker right است.

کاربری با نام vahid را با حداقل دسترسی ایجاد کرده و بررسی می کنیم که آیا این کاربر می تواند تابع فوق را اجرا کند؟

SQL> create user vahid  identified by a;
User created.
SQL> grant create session to vahid; 
Grant succeeded.

(بیشتر…)

نکاتی در مورد Materialized View و NoLogging

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

بروزرسانی Materialized Viewهای حجیم آن هم به صورت complete می تواند DBA را در جنبه های مختلفی به چالش بکشاند به ویژه آنکه دیتابیس در مود آرشیو قرار داشته باشد چرا که در این صورت، بروزرسانی MV منجر به ایجاد حجم زیادی از آرشیولاگ خواهد شد. البته اثرات منفی این مسئله، صرفا به فضای مصرفی redoها خلاصه نمی شود و از لحاظ پرفورمنسی هم می تواند بر روی عملکرد دیتابیس اثر منفی بگذارد.

در این متن بررسی می کنیم که غیرفعال کردن Logging در سطوح object، tablespace و database چه اثراتی را بر روی عملیات ساخت و بروزرسانی Materialized Viewها به همراه خواهد داشت(مطالعه مطلب “تاثیر عملیات NOLOGGING در دیتاگارد”  پیشنهاد می شود).

(بیشتر…)

JSON Multivalue index در اوراکل 21c

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

در متن “JSON و دیتابیس اوراکل” توضیح دادیم که چگونه اوراکل در نسخه 12c امکان ذخیره و کنترل اطلاعات JSON را در قالب دیتاتایپ CLOB، varchar و … فراهم کرده است و همچنین مطالبی را در مورد نحوه ایندکس گذاری کلیدهای JSON ارائه کرده ایم(برای مطالعه). در نسخه 21c بهبودهای دیگری نظیر نوع داده JSON، تابع JSON_TRANSFORM و … هم به این مجموعه قابلیتها اضافه شد که قبلا بعضی از آنها را مستند کرده ایم و در این متن قصد داریم در مورد یکی دیگر از این قابلیتها که JSON Multi value index است، نکاتی را بنویسیم.

می دانیم که قرار نیست ما به ازای هر کلید در JSON، صرفا یک مقدار ذخیره شود و در مواردی ممکن است مقدار یک کلید، یک آرایه(یا همان لیست) باشد. در این شرایط استفاده از روشهای قبلی ایندکس گذاری برای جستجو چندان کارآمد نخواهند بود و باید به دنبال روش جدیدی برای این حالت باشیم. در ادامه با ارائه مثالی، بیشتر این موضوع را توضیح خواهیم داد.

(بیشتر…)

اوراکل 21c – بازگرداندن دیتابیس به هر زمانی در گذشته

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

اوراکل در نسخه 19c اجازه نمی دهد که یک pdb را به زمانی از یک ORPHAN incarnation برگردانیم:

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Apr 21 08:28:57 2022
Version 19.3.0.0.0
SQL>  SELECT con_id, status, pdb_incarnation# inc#, begin_resetlogs_scn, end_resetlogs_scn FROM v$pdb_incarnation ORDER BY 3;
    CON_ID STATUS        INC# BEGIN_RESETLOGS_SCN END_RESETLOGS_SCN
---------- ------- ---------- ------------------- -----------------
         3 PARENT           0             1920977           1920977
         3 ORPHAN           1             1963437           1963437
         3 CURRENT          2             1964176           1964176
SQL>  alter pluggable database pdb1401 close;
Pluggable database altered.
SQL> flashback pluggable database to scn 1962565;
ORA-39889: Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation.
SQL> flashback pluggable database PDB1401  to scn 1963437;
ORA-39889: Specified System Change Number (SCN) or timestamp is in the middle of a previous PDB RESETLOGS operation.
[oracle@stb ~]$ rman target sys/sys@192.168.1.20:1521/pdb1401
RMAN> reset pluggable database pdb1401  to incarnation 1;
'RMAN-07536: command not allowed when connected to a Pluggable Database'

اما در نسخه 21c این قابلیت به وجود آمد تا بتوان یک PDB را به هر زمانی در گذشته برگرداند(البته گذشته نزدیک). در ادامه با سناریوی زیر و با ایجاد یک ORPHAN incarnation بیشتر با این فیچر را آشنا خواهیم شد.

(بیشتر…)