اوراکل 23ai- قابلیت Dictionary Protection

زمانی که یکی از مجوزهای system “ANY” privilege نظیر select any table، drop any table و … را به کاربری می دهیم، آن کاربر امکان دسترسی به objectهای شمای sys و جداول Data Dictionary را ندارد:

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Jan 9 10:38:31 2024
SQL> show user
USER is "SYS"
SQL> create table sys.tb as select * from dual;
Table created.
SQL> create user usef identified by a;
User created.
SQL> grant create session,select any table to usef;
Grant succeeded.
SQL> conn usef/a
Connected.
SQL> select * from sys.tb;
ORA-00942: table or view does not exist
SQL> select * from v$datafile;
ORA-00942: table or view does not exist

اگر تصمیم داریم این محدودیت را حداقل در مورد مجوز select any table برای یک کاربر برداریم، می توانیم مجوز SELECT ANY DICTIONARY را به آن کاربر اعطا کنیم:

SQL> grant SELECT ANY DICTIONARY to usef;
Grant succeeded.
SQL> conn usef/a
Connected.
SQL> select * from sys.tb;
D
-
X
SQL> select file# from v$datafile where rownum=1;
     FILE#
----------
         1

البته از اوراکل 12c، دسترسی به بعضی از جداول Data Dictionary حتی با داشتن مجوز  SELECT ANY DICTIONARY  هم امکان پذیر نیست. لیست بعضی از این جداول را در قسمت زیر می بینید:

USER$, ENC$ , DEFAULT_PWD$, LINK$, USER_HISTORY$, CDB_LOCAL_ADMINAUTH$, XS$VERIFIERS

(بیشتر…)

اوراکل 23ai- تخمین صرفه جویی در فضا با DEDUPLICATION

DEDUPLICATION یکی از قابلیتهای جدید اوراکل در نسخه 11g است که همراه با SECUREFILE LOBها ارائه شد و می توان بعد از ایجاد جدول هم این قابلیت را برای SECUREFILE LOBها فعال کرد اما سوال مهمی که در این زمینه مطرح می شود آن است که DEDUPLICATION برای LOBهای موجود چقدر فضا آزاد می کند؟

در اوراکل نسخه 23c، تابعی در این زمینه ارائه شد که می تواند تا حدودی به این سوال پاسخ دهد. این تابع نام جدول(و یا نام پارتیشنی از جدول) را دریافت می کند و حداکثر 100 هزار رکورد از آن جدول را بررسی کرده و نهایتا تخمینی از میزان ذخیره سازی فضا را ارائه می کند.

در ادامه، سناریوی تستی را در این زمینه می بینید.

SQL> create table tbl (id number,describe clob);
Table created

در این جدول اطلاعاتی را درج می کنیم که بیشتر آن تکراری هستند:

SQL> begin
  2    for i in 1 .. 70000 loop
  3      insert into tbl
  4      values
  5        (1,
  6         lpad('www.usefzadeh.com',
  7              1000000000000000000000000000000000000000000000000000000000000000000000000000000000,
  8              'a'));
  9    end loop;
 10    insert into tbl values (2, 'Ramtollah Rabbani');
 11    insert into tbl values (3, 'Armin Bahamin');
 12    insert into tbl values (4, 'Jamshid Khan');
 13    commit;
 14  end;
 15  /

PL/SQL procedure successfully completed

(بیشتر…)

امکان تغییر رفتار پیش فرض kill session در اوراکل 23ai

زمانی که sessionای را در اوراکل kill می کنیم، به صورت پیش فرض آن session در حالت SOFTء، kill خواهد شد:

SQL> ALTER SYSTEM KILL SESSION '1025,44801';
System altered.

جزییات اجرای این دستور را می توانیم در Alert Log ببنیم:

2023-12-19T13:32:18.731476+03:30
(4):A user has terminated a session.
KILL SESSION for sid=(1025, 44801):
  Reason = alter system kill session
  Mode = KILL SOFT -/-/-/-
  Requestor = USER (orapid = 55, ospid = 3267554, inst = 1)
    User = oracle
    Program = sqlplus@OEL8 (TNS V1-V3)
  Owner = Process: USER (orapid = 75, ospid = 3268895)
    User = 462710969
    Program = plsqldev.exe
  Result = ORA-0

برای kill کردن session در حالت HARD می توانیم عبارت immediate را به انتهای دستور اضافه کنیم:

SQL> ALTER SYSTEM KILL SESSION '1025,43030' IMMEDIATE;
System altered.

مجددا با کمک Alert Log جزییات اجرای این دستور را هم خواهیم دید:

2023-12-19T13:33:07.749571+03:30
(4):A user has terminated a session.
KILL SESSION for sid=(1025, 43030):
  Reason = alter system kill session
  Mode = KILL HARD SAFE -/-/-/-
  Requestor = USER (orapid = 55, ospid = 3267554, inst = 1)
    User = oracle
    Program = sqlplus@OEL8 (TNS V1-V3)
  Owner = Process: USER (orapid = 75, ospid = 3268940)
    User = 462710969
    Program = plsqldev.exe
  Result = ORA-0

در نسخه 23c اوراکل Hidden Parameterای به نام kill_session_force_ را اضافه کرده است که می توان از طریق آن، این رفتار پیش فرض را تغییر داد:

(بیشتر…)

اوراکل 23ai– پارامتر error_message_details برای نمایش جزییات خطا

error_message_details یکی دیگر از پارامترهای جدید اوراکل در نسخه 23c هست که امکان نمایش جزییات خطاهای مربوط به Data Value را فراهم می کند با تنظیم این پارامتر به مقدار on، می توانیم برای خطاهایی نظیر ORA-00001: unique constraint violated، مقداری که سبب رخ دادن این دسته از خطاها شده است را در متن خطا ببینیم.

برای مثال، در سناریوی زیر، فعال بودن پارامتر error_message_details در پیدا کردن مقداری که باعث این خطا شده است کمک می کند:

SQL> create table tbl1(id number primary key);
Table created.
SQL> variable B number;
SQL> exec :B:=2547;
PL/SQL procedure successfully completed.
SQL> insert into tbl1 values(:B);
1 row created.
SQL> insert into tbl1 values(:B);
ERROR at line 1:
'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)'
'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists'
Help: https://docs.oracle.com/error-help/db/ora-00001/

در صورتی که پارامتر error_message_details غیر فعال شود، خطای فوق به شکل زیر نمایش داده می شود:

SQL> alter system set error_message_details=DISALLOWED ;
System altered.
SQL> insert into tbl1 values(:B);
'ORA-00001: unique constraint (USEF.SYS_C008329) violated on table USEF.TBL1 columns (ID)'
Help: https://docs.oracle.com/error-help/db/ora-00001/

 

اوراکل 23ai– بررسی وجود inconsistency در Data Dictionary

نازسازگاری در دیتادیکشنری امر متداولی نیست و به ندرت اتفاق می افتد اما در صورت رخ دادن می تواند بسیار چالش ساز باشد نمونه ای از این دست ناسازگاری که شاید شما هم با آن روبرو شده باشید، invalid reference است مثلا segmentای که اطلاعاتش در dba_objects موجود نیست.

 تا قبل از نسخه 23c، این قبیل ناسازگاری ها را می توانستیم با اسکریپت hcheck.sql شناسایی کنیم. اما در اوراکل نسخه 23c، پکیجی به نام DBMS_DICTIONARY_CHECK(البته در 23.3) ارائه شد که با کمک آن می توانیم از consistency در دیتادیکشنری اطمینان حاصل کنیم. این پکیج پروسیجرهای متعددی دارد:

SQL> desc DBMS_DICTIONARY_CHECK

PROCEDURE CRITICAL
PROCEDURE FULL
PROCEDURE DUPLICATEDATAOBJ
PROCEDURE IDNSEQOBJ
PROCEDURE IDNSEQSEQ
PROCEDURE INDEXPARTITIONSEG
PROCEDURE INDEXSEG
PROCEDURE INDINDPARMISMATCH
PROCEDURE INDPARTOBJ
PROCEDURE LOBSEG
PROCEDURE MVIEW
PROCEDURE NEXTOBJECT
PROCEDURE NOSEGMENTINDEX
PROCEDURE OBJECTNAMES
PROCEDURE OBJERROR
….

در این بین، پروسیجر FULL می تواند اطلاعات کاملی را در زمینه درستی Data Dictionary ارائه دهد که در ادامه نمونه ای از خروجی آن را مشاهده می کنید:

(بیشتر…)

اوراکل 23ai– بهبودهایی در زمینه DML RETURNING INTO

از نسخه های قدیمی اوراکل امکان استفاده از عبارت RETURNING INTO به همراه دستورات DMLای وجود داشت که در قسمت زیر نحوه استفاده از آن را می بینید:

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Aug 15 14:08:30 2023
SQL> select * from tbl1;
        ID NAME
---------- ----------
         1 Vahid
         2 Usef
SQL>declare
    var_id number;
    var_name varchar2(10);
  begin
  --Update
    update tbl1 set name='ALI' where id=2 returning name into var_name;
   	dbms_output.put_line('After_Update==>' || name='||var_name);
  --Delete
    delete tbl1 where id=1 returning name into var_name;
    dbms_output.put_line('Before_Delete==>' || name='||var_name);
  --Insert	
    insert into tbl1 values(3,'Reza') returning id,name into var_id,var_name;
    	 dbms_output.put_line('INSERT==> id='||var_id||' , name='||var_name);
    commit;
  end;
/        
After_Update==>name=ALI
Before_Delete==> name=Vahid
INSERT==> id=3 , name=Reza
SQL> select * from tbl1;
        ID NAME
---------- ----------
         2 ALI
         3 Reza

(بیشتر…)

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

از نسخه های قدیمی اوراکل امکان 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 امکان پذیر است که در ادامه نحوه استفاده از آن را شرح خواهیم داد.

(بیشتر…)

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

در نسخه 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).

(بیشتر…)

اوراکل 23ai- قابلیت 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.

(بیشتر…)

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

در نسخه 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.