جداول از نوع Immutable که اصطلاحا insert-only table هم نامیده می شوند، برای محافظت اطلاعات جدول از هرگونه دستکاری کاربرد دارند. در این نوع از جداول، درج اطلاعات جدید امکان پذیر است اما قابلیت بروزرسانی و اصلاح وجود ندارد و حداقل برای مدت زمان مشخصی، از حذف جدول و یا حذف اطلاعات آن ممانعت خواهند شد. این دسته از جداول در اوراکل 21.3 ارائه شدند و در نسخه 19.11 هم قابل استفاده هستند.

جداول Immutable را می توان از طریق دستور CREATE IMMUTABLE TABLE ایجاد کرد همچنین در زمان ایجاد این نوع از جداول، می توان در مورد حذف جدول(DROP) و حذف رکوردهای جدول(DELETE) سیاستهایی را اعمال کرد.

تنظیمات حذف جدول(DROP) از طریق عبارات زیر قابل اعمال است:

NO DROP [ UNTIL number DAYS IDLE ]

که عبارت NO DROP سبب خواهد شد تا جدول غیرقابل حذف شود. همچنین عبارت NO DROP UNTIL number DAYS IDLE مانع از حذف جدول خواهد شد مگر آنکه در مدت زمان تعیین شده برای DAYS IDLE، اطلاعات جدیدی در این جدول ثبت نشود که در این صورت جدول قابل حذف خواهد بود.

در کنار عبارت NO DROP می توان با استفاده از عبارت NO DELETE سیاستهایی را برای حذف رکوردهای جدول درنظر گرفت:

NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }

NO DELETE: استفاده از این عبارت مانع از حذف رکوردهای جدول برای همیشه خواهد شد. این تنظیم غیرقابل تغییر است.

NO DELETE LOCKED: این عبارت تفاوتی با NO DELETE ندارد.

NO DELETE UNTIL number DAYS AFTER INSERT: از طریق این عبارت می توان تعیین کرد که رکوردها حداقل چند روز بعد از زمان insert قابل حذف باشند(مقدار حداقل آن 16 روز می باشد). تعداد روزهای تنظیم شده، از طریق دستور alter table صرفا قابل افزایش هستند و امکان کاهش ان وجود ندارد.

NO DELETE UNTIL number DAYS AFTER INSERT LOCKED: مشابه عبارت قبلی می باشد با این تفاوت که مقدار تعیین شده(number DAYS) را نمی توان از طریق دستور ALTER TABLE افزایش داد.

 

ایجاد جدول immutable

برای استفاده از این ویژگی در اوراکل نسخه 19.11، باید پارامتر compatible را به 19.11 تنظیم کرد:

alter system set compatible=’19.11.0′ scope=spfile;

در صورت عدم تنظیم این پارامتر، با خطای زیر مواجه خواهیم شد:

ORA-00406: COMPATIBLE parameter needs to be 19.11.0.0.0 or greater

ORA-00722: Feature “Immutable table”

بعد از تنظیم پارامتر compatible، جدولی را ایجاد می کنیم که غیرقابل حذف باشد و رکوردهای آن را هم نتوان تحت هیچ شرایطی پاک کرد:

SQL> create immutable table immutbl(id  number)

         no drop

         no delete;

Table created

اطلاعاتی را در این جدول درج می کنیم:

SQL> insert into immutbl values(1);

1 row inserted

SQL> insert into immutbl values(2);

1 row inserted

SQL> commit;

Commit complete

حذف این جدول برای همیشه غیرممکن خواهد بود:

SQL> drop table immutbl;

ORA-05723: drop blockchain or immutable table IMMUTBL not allowed

در این شرایط حذف اسکیما هم با خطا مواجه خواهد شد:

SQL> drop user usef cascade;

ORA-05723: drop blockchain or immutable table IMMUTBL not allowed

البته حذف دیتابیس(pdb) امکان پذیر است:

SQL> drop pluggable database pdb1 including datafiles;

Pluggable database dropped.

با توجه به عبارت no delete، رکوردهای درج شده هم قابلیت حذف نخواهند داشت:

SQL> delete immutbl;

ORA-05715: operation not allowed on the blockchain or immutable table

SQL> truncate table immutbl;

ORA-05715: operation not allowed on the blockchain or immutable table

دستور update هم بر روی این جدول قابل اجرا نیست:

SQL> update immutbl2 set id=2;

ORA-05715: operation not allowed on the blockchain or immutable table

قصد داریم سیاستهای اعمال شده جدول را طوری تغییر دهیم که اگر برای مدت 20 روز در جدول اطلاعاتی درج نشد، امکان حذف آن به وجود آید و همچنین رکوردهای ثبت شده در این نوع از جدول، بعد از گذشت 17 روز از زمان insert قابل حذف باشند:

SQL> CREATE IMMUTABLE TABLE immutbl2 (id NUMBER)

  2  NO DROP UNTIL 20 DAYS IDLE

  3  NO DELETE UNTIL 17 DAYS AFTER INSERT;

Table created

SQL> select sysdate from dual;

SYSDATE

———–

06/17/2021

SQL> insert into immutbl2 values(1);

1 row inserted

SQL> insert into immutbl2 values(3);

1 row inserted

SQL> commit;

Commit complete

در این شرایط،جدول حداقل برای مدت زمان 20 روز غیرقابل حذف خواهد بود:

SQL> drop table immutbl2;

ORA-05723: drop blockchain or immutable table IMMUTBL2 not allowed

برای اثبات این مسئله، تاریخ سرور را 30 روز افزایش داده و دستور را مجددا اجرا می کنیم:

[root@oLinux7 ~]# date 07172021

Sat Jul 17 20:21:00 EDT 2021

SQL> select sysdate from dual;

SYSDATE

———–

07/17/2021

با این تغییر، جدول قابل حذف خواهد بود:

SQL> drop table immutbl2;

Table dropped

توجه: تغییر ساعت سرور در محیط عملیاتی به هیچ عنوان توصیه نمی شود.

نکته مهم دیگری که در مورد این جدول باید در نظر داشت، عدم امکان تغییر ساختار آن است. برای مثال، نمی توان به این نوع از جداول، ستونی را اضافه کرد:

SQL> alter table IMMUTBL2 add (test varchar2(100));

ORA-05715: operation not allowed on the blockchain or immutable table

SQL> alter table immutbl2 modify (id number(20));

ORA-05715: operation not allowed on the blockchain or immutable table

البته امکان ایندکس گذاری برای این نوع از جداول وجود دارد:

SQL> create index ind1 on immutbl2(id);

Index created

با ایجاد جدول IMMUTABLE، ستونهای نامرئی زیادی هم به آن اضافه خواهند شد که لیست این ستونها را می توان از طریق ویوی زیر مشاهده کرد:

select column_name,hidden_column from user_tab_cols v where table_name=upper(‘immutbl2’);

همچنین لیست جداول IMMUTABLE را می توان از طریق ویوی {CDB|DBA|ALL|USER}_IMMUTABLE_TABLES مشاهده کرد:

SQL> select * from  user_immutable_tables;

TABLE_NAME  ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION

———– ————- ——————– ————————–

IMMUTBL2               17 NO                                           20

همانطور که می بینید، جدول immutbl2 پالیسی حذف رکورد 17 روزه و پالیسی حذف جدول 20 روزه دارد که امکان افزایش دادن آن هم وجود دارد:

SQL> alter table immutbl2 no drop until 57 days idle;

Table altered

البته کاهش دادن این مقدار، امکان پذیر نیست:

SQL> alter table immutbl2 no drop until 56 days idle;

ORA-05732: retention value cannot be lowered

برای حذف رکوردهایی متجاوز از خصیصه ROW RETENTION(اصطلاحا expire شده)، می توانیم از پروسیجر delete_expired_rows در بسته dbms_immutable_table استفاده کنیم.

مثال زیر را ببینید.

SQL> CREATE IMMUTABLE TABLE immutbl3 (id NUMBER)

  2  NO DROP UNTIL 20 DAYS IDLE

  3  NO DELETE UNTIL 17 DAYS AFTER INSERT;

Table created

SQL> select sysdate from dual;

SYSDATE

———–

07/17/2021

SQL> insert into immutbl3 values(1);

1 row inserted

SQL> commit;

Commit complete

[root@oLinux7 ~]#  date 08172021

Tue Aug 17 20:21:00 EDT 2021

set serveroutput on

declare

  count_rows  number;

begin

  dbms_immutable_table.delete_expired_rows(

    schema_name            => ‘USEF’,

    table_name             => ‘immutbl3’,

    before_timestamp       => null,

    number_of_rows_deleted => count_rows);

  dbms_output.put_line(‘Expire Rows Deleted=’ || count_rows);

end;

/

Expire Rows Deleted=1

با نگاهی به تعداد رکوردهای جدول immutbl3 خواهیم دید که جدول immutbl3 رکوردی ندارد:

SQL> select count(*) from immutbl3;

  COUNT(*)

———-

         0