قابلیت Shrink کردن Bigfile tablespace در اوراکل 23c

از نسخه های قدیمی اوراکل امکان resize کردن دیتافایلها وجود دارد:

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Nov 21 11:15:16 2023
SQL> select bytes/1024/1024 SIZE_MB from v$datafile where file#=29;
   SIZE_MB
----------
      1024
SQL> alter database datafile 29 resize 120m;
Database altered.
SQL> select bytes/1024/1024 SIZE_MB from v$datafile where file#=29;
   SIZE_MB
----------
       120

البته resize کردن دیتافایلها همیشه با موفقیت انجام نمی شود و بعضا با آزاد بودن درصد بالایی از فضای دیتافایل، به دلیل قرار گرفتن بعضی از بلاکهای segment بر سر High Water Mark، امکان کاهش سایز دیتافایل وجود ندارد و در این حالت به خطای زیر برخواهیم خورد:

SQL> alter database datafile 1 resize 10m;
ORA-03297: file contains used data beyond requested RESIZE value

برای جلوگیری از خطای ORA-03297، باید segmentای که بر سر راه High Water Mark قرار دارد را جابجا کنیم! که البته قبلا در مطلبی به طور مفصل این مسئله را بررسی کردیم و از تکرار آن خودداری می کنیم.

مسئله ای که در این متن به آن خواهیم پرداخت، موضوع shrink کردن در سطح tablespaceها است. در نسخه 11g امکان shrink کردن Temporary tablespaceها به وجود آمد:

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 21 10:35:57 2023
SQL> select FILE#,BYTES/1024/1024 from v$tempfile where file#=5;
     FILE# BYTES/1024/1024
---------- ---------------
         5            5120
SQL> ALTER TABLESPACE Temp_TBS SHRINK SPACE KEEP 340m;
Tablespace altered.
SQL> select FILE#,BYTES/1024/1024 from v$tempfile where file#=5;
     FILE# BYTES/1024/1024
---------- ---------------
         5             340

و نهایتا در نسخه 23c اوراکل این امکان را برای Bigfile Tablespaceها ارائه کرده است این کار با استفاده از پروسیجر dbms_space.tablespace_shrink امکان پذیر است که در ادامه نحوه استفاده از آن را شرح خواهیم داد.

(بیشتر…)

اوراکل 23c – استفاده از پروسیجر JSON_TYPE_CONVERTIBLE_CHECK برای جابجایی دیتای JSON

اوراکل در نسخه 21c دیتاتایپ JSON را ارائه کرد و تا قبل از آن، دیتای JSON را می توانستیم در ستونهایی با نوع داده CLOB، BLOB و حتی VARCHAR ذخیره کنیم با این اوصاف اگر دیتابیس را به تازگی به نسخه 21c(و نسخ بالاتر) ارتقا دادیم ممکن است بخواهیم دیتای از نوع JSON را به ستونی که دیتاتایپ آن JSON است منتقل کنیم.

در نسخه 23c، پروسیجری اضافه شده است که می تواند در این فرایند مورد استفاده قرار بگیرد و بعضا بسیار راهگشا باشد. پروسیجر dbms_json.json_type_convertible_check ستونی را به عنوان ورودی می گیرد و بررسی می کند همه فیلدهای آن ستون حاوی دیتای معتبر با فرمت JSON هستند و اگر در این بررسی خطایی رخ دهد این خطا از طریق جدول json_data_precheck قابل مشاهده است.

(بیشتر…)

اوراکل 23c – شناسایی علت تغییر رفتار Optimizer با استفاده از ویوی DBA_HIST_OPTIMIZER_ENV_DETAILS

تغییر Execution Plan یک کوئری می تواند به دلایل ساده ای مثل حذف و اضافه کردن ایندکس، پارتیشن بندی جدول، پارتیشن بندی ایندکس اتفاق بیفتد اما شناسایی علت تغییر رفتار Optimizer همیشه ساده نیست چرا که در بعضی از موارد تغییر در Optimizer Environment منجر به ایجاد Execution Plan جدید می شود.

برای مثال در sessionای پارامتر OPTIMIZER_INDEX_COST_ADJ که میزان گرایش Optimizer به استفاده از ایندکس را تعیین می کند، به عدد 1 و در session دیگر این پارامتر به مقدار 1000! تنظیم شده است بدون تردید این تفاوت ها در Optimizer Environment، می تواند Execution Plan بعضی از کوئری ها را تغییر دهد.

موضوع این مستند در مورد آن است که چگونه می توانیم تشخیص دهیم تغییر Execution Plan یک کوئری به دلیل تغییر در Optimizer Environment است؟ و به طور دقیق تر، کدام پارامترها و عوامل محیطی منجر به ایجاد Execution Plan جدید شده اند. این کار را با قابلیت جدیدی که اوراکل در نسخه 23c ارائه کرده است، انجام خواهیم داد.

(بیشتر…)

امکان جستجو در سطح کل دیتابیس با DBMS_SEARCH – اوراکل 23c

در نسخه 23c، اوراکل پکیجی را به نام DBMS_SEARCH معرفی کرده است که می تواند با استفاده از زیرساخت ORACLE TEXT و حذف پیچیدگی های آن، امکان جستجو را بر روی Objectهای مختلف فراهم کند. پکیج DBMS_SEARCH با ایجاد ایندکسی از نوع JSON Search Index می تواند قابلیت جستجو را بر روی sourceهای مختلف اعم از Table و View فراهم کند.

برخلاف ایندکسهای متعارف نظیر Btree و Bitmap که بر روی ستونهای یک جدول قابل ایجاد هستند، ایندکسی که از طریق پکیج DBMS_SEARCH ایجاد می شود، می تواند چندین جدول و ویو را به عنوان source بپذیرد و جستجوی همزمان را بر روی این sourceها انجام دهد.

جداولی که به عنوان سورس تعیین می شوند می توانند حاوی ستونهایی با نوع داده number، varchar، CLOB، JSON و … باشند و محدودیتهای بسیاری کمی در این زمینه وجود دارد ضمنا اضافه کردن source به ایندکس به راحتی و صرفا با اجرای یک دستور امکان پذیر است.

توجه! ایندکسهایی که از طریق این پکیج ایجاد می شوند، بروزرسانی آنها به صورت خودکار انجام می شود(sync on commit).

(بیشتر…)

اوراکل 23c – قابلیت Table Values Constructor

Table Values Constructor قابلیتی است که در بیشتر دیتابیسهای رابطه ای وجود داشته و اوراکل امکان استفاده از این قابلیت را در نسخه 23c فراهم کرده است.

بر اساس این قابلیت، می توانیم با اجرای یک دستور insert ساده(Insert به همراه عبارت Values) چندین رکورد را در یک جدول درج کنیم البته استفاده از کلمه کلیدی Values به دستور insert محدود نمی شود و از این عبارت می توانیم برای دستورات DMLای دیگر نظیر Select و Merge هم استفاده کنیم.

ابتدا مثالی از نحوه استفاده از این قابلیت را به همراه دستور insert مشاهده می کنید.

SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Tue Aug 29 22:41:17 2023
SQL> insert into Irani values(1,'Vahid'),(2,'Usef');
2 rows created.
SQL> commit;
Commit complete.

(بیشتر…)

حذف محدودیتهای Parallel DML در اوراکل نسخه 23c

در نسخه 21c اگر در تراکنشی از Parallel DML استفاده کنیم، امکان گرفتن query و یا اجرای دستورات DML و یا Parallel DML بر روی همان جدول و در همان تراکنش از ما گرفته خواهد شد:

SQL> alter session enable parallel dml;
Session altered.

SQL> insert /*+parallel(10)*/ into tbl1 select * from v$datafile;
1536 rows created.

Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.8.0.0.0

SQL> select count(*) from tbl1;
'ORA-12838: cannot read/modify an object after modifying it in parallel'
SQL> delete tbl1;
'ORA-12838: cannot read/modify an object after modifying it in parallel'

با خاتمه دادن به تراکنش شاهد این خطا نخواهیم بود:

SQL> commit;
Commit complete.

SQL> select count(*) from tbl1;
  COUNT(*)
----------
      6144

SQL> delete tbl1;
6144 rows deleted.

در نسخه 23c این محدودیت برداشته شده و بدون بستن تراکنش می توانیم دستور فوق را اجرا کنیم:

Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL> alter session enable parallel dml;
Session altered.

SQL> insert /*+parallel(10)*/ into tbl1 select * from v$datafile;
14 rows created.

SQL> select count(*) from tbl1;
  COUNT(*)
----------
        28

SQL> delete tbl1;
28 rows deleted.

اوراکل 23c – پشتیبانی Fast Ingest از Partitioning، Compression و LOB

Memoptimized Rowstore Fast Ingest یکی از قابلیتهای جدید اوراکل در نسخه 19c است که می تواند در زمینه IOT بسیار کاربردی باشد قبلا در مطلبی تحت عنوان “Memoptimized Fast Ingest در اوراکل 19c“، سعی کردیم به طور مفصل این قابلیت را شرح دهیم. اوراکل در نسخه 23c بهبودهایی را در این زمینه ایجاد کرده است که موضوع این مستند خواهد بود.

پشتیبانی از Partitioning

در نسخه 19c و 21c امکان تنظیم MEMOPTIMIZE FOR WRITE برای جداول پارتیشن شده وجود نداشت:

Connected to Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 
SQL> create table TBL1
  2  (
  3    id   NUMBER not null,
  4    text VARCHAR2(3000),
  5    mydate date
  6  )
  7  SEGMENT CREATION IMMEDIATE
  8  partition by range ( mydate )
  9  interval ( numtoyminterval(1,'MONTH'))
 10  (
 11   PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2015', 'DD-MM-YYYY'))
 12  );
Table created
SQL> ALTER TABLE tbl1 MEMOPTIMIZE FOR WRITE;
ORA-62165: MEMOPTIMIZE FOR WRITE cannot be enabled on table with specified partitioning type.

این قابلیت در نسخه 23c اضافه شد:

(بیشتر…)

قابلیت Materialized View Concurrent Refresh در اوراکل 23c

بروز شدن همزمان Materialized Viewهای on-commit refresh از طریق sessionهای مختلف می تواند سبب رخ دادن eventای به نام enq: JI – contention شود چرا که این نوع از MVها به طور پیش فرض به صورت سریالی بروز می شوند و امکان بروزرسانی Concurrent را ندارند.

در قسمت زیر، از طریق دو session به طور همزمان دیتایی را در جدول مرجع MV درج کرده ایم که این  مسئله سبب شد تعداد زیادی از eventهای enq: JI – contention را شاهد باشیم:

SQL> create materialized view auther_contry_mv
refresh fast on commit
as
select country,count(*) from author_tbl group by country;
Materialized view created.
Session 1:
SQL>  begin
for i in  10000 ..19999 loop
insert into author_tbl values(i,'Rahmat Rabbani','m','Oracle DBA','IRAN');
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:03:00.18
Session 2:
begin
for i in 1 ..9999 loop
insert into author_tbl values(i,'Rahmat Rabbani','m','Oracle DBA','IRAN');
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:03:00.25

در نسخه 23c اوراکل از طریق قابلیت Materialized View Concurrent Refresh این محدودیت از بین برده است و با استفاده از این قابلیت می توان به صورت Concurrent این نوع از MVها را بروزرسانی کرد.

(بیشتر…)

تغییرات دستور OERR در اوراکل 23c

در نسخه 23c دستور OERR اطلاعات بیشتری را برای بسیاری از خطاها(نسبت به نسخه های قبلی) ارائه می کند که این مسئله می تواند در شناسایی علت رخ دادن خطا و همچنین رفع سریع تر آن کمک کند.

بعنوان مثال، برای خطای ORA-00001: unique constraint (constraint_name) violated این دستور را در دو نسخه 21c و 23c اجرا می کنیم:

'Oracle 21c':
[oracle@OEL7 ~]$ oerr ora 00001
00001, 00000, "unique constraint (%s.%s) violated"
// *Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
//         For Trusted Oracle configured in DBMS MAC mode, you may see
//         this message if a duplicate entry exists at a different level.
// *Action: Either remove the unique restriction or do not insert the key.
'oracle 23c'
[oracle@IranHost ~]$ oerr ora 00001
00001, 00000, "unique constraint (%s.%s) violated on table %s.%s columns (%s)"
// *Cause:  An UPDATE, INSERT or MERGE statement attempted to update or
//          create a record that duplicated values limited by a unique
//          constraint. A unique constraint can be implemented as an
//          explicit unique constraint, a unique index, or a primary key.
//
//          Consider the case where a table has a unique constraint on columns
//          FIRSTNAME and LASTNAME. Because of this constraint, it is not
//          possible to insert a row containing values of FIRSTNAME and
//          LASTNAME that are identical to the values of these columns in an
//          existing table row.
// *Action: Determine what type of unique constraint was violated (explicit
//          unique constraint, unique index, or primary key), and which table
//          columns are affected by this unique constraint.
//
//          Then choose whether to change the constraint to allow duplicate
//          values; modify the SQL statement to no longer create a duplicate
//          value; or drop the constraint, unique index, or primary key.
// *Params: 1) constraint_schema: The schema name where the constraint
//             resides.
//          2) constraint_name: The name of the constraint.
//          3) table_schema: The schema name for the table affected by this
//             constraint.
//          4) table_name: The name of the table affected by this
//             constraint.
//          5) column_names: The column names affected by this
//             constraint.

قابلیت دیگر اوراکل 23c در این زمینه، اضافه شدن دستور OERR به محیط sqlplus است:

[oracle@IranHost ~]$ sqlplus "/as sysdba"
Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL> OERR
Usage: OERR <facility name> <error number>
SQL> OERR ora 01452
Message: "cannot CREATE UNIQUE INDEX; duplicate keys found"
Help: https://docs.oracle.com/error-help/db/ora-01452/
Cause:     A CREATE UNIQUE INDEX statement specified one or more
           columns that currently contained duplicate values. All values in
           the indexed columns must be unique by row to create a UNIQUE
           INDEX.
Action:    If the entries need not be unique, remove the keyword
           UNIQUE from the CREATE INDEX statement, then re-execute the
           statement. If the entries must be unique, as in a primary key,
           then remove duplicate values before creating the UNIQUE index.

قابلیت Read Only Session در اوراکل 23c

یکی دیگر از پارامترهای جدید اوراکل در نسخه 23c، پارامتر read_only است:

SQL> show parameter READ_ONLY
NAME  TYPE        VALUE    
----- ----------- -------- 
read_only                            boolean     FALSE

این پارامتر صرفا در سطح session قابل تنظیم است و با تنظیم این پارامتر به مقدار true، امکان اجرای دستورات DML و DDL در سطح session از بین خواهد رفت:

SQL> alter system set read_only=true scope=spfile;
ORA-32017: failure in updating SPFILE
ORA-02065: illegal option for ALTER SYSTEM
 SQL> alter session set read_only=true;
Session altered.

SQL> delete tbl1;
ORA-28193: Can perform read operations only

SQL> drop table tbl1;
ORA-28193: Can perform read operations only

اگر تراکنشی در session در حال اجرا باشد، امکان فعال کردن این پارامتر وجود ندارد:

SQL> delete tbl1;
1 row deleted.

SQL> alter session set read_only=true;
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-65023: active transaction exists in container

این پارامتر برای هر دو نوع از userها یعنی local user و common user قابل استفاده است.