تاریخ اخرین تغییر جدول و رکورد با کمک ستون ORA_ROWSCN

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

برای کاهش این سربار، دیتابیس اوراکل زمان آخرین تغییرات را به شکل scn و در سطح بلاک ذخیره خواهد کرد به این شکل که با هر تغییر در رکوردهای یک بلاک، scn مربوط به آن زمان، برای همه رکوردهای آن بلاک ثبت خواهد شد.

این اتفاق از اوراکل 10g و با اضافه کردن pseudocolumnای به نام ORA_ROWSCN(در زمان ایجاد جدول) انجام خواهد شد.

با کمک این قابلیت می توان آخرین زمان اجرای دستور DMLای را بر روی یک جدول و همچنین آخرین تغییر را در سطح بلاکهای متعلق به آن مشخص کرد اما در مورد یک رکورد مشخص، نمی توان با اطمینان خاطر نظر داد.

مثال: در این مثال خواهیم دید که با هر تغییر در هر کدام از رکوردهای یک بلاک، scn مربوط به تمامی رکوردها ی آن بلاک تغییر خواهد کرد و به صورت کلی، ثبت scn برای رکوردها، در سطح بلاک انحام خواهد شد:


SQL> create table usef.tb1 (id number,name varchar2(9));


SQL> insert into usef.tb1 values(1,’USEF’);


SQL> commit;


SQL> select dbms_rowid.rowid_block_number(rowid) as block#,a.id,a.name,ora_rowscn from usef.tb1 a;

#BLOCK ID NAME ORA_ROWSCN
392719 1 USEF 7777252


SQL> insert into usef.tb1 values(1,’VAHID’);


SQL> commit;


SQL> select dbms_rowid.rowid_block_number(rowid) as block#,a.id,a.name,ora_rowscn from usef.tb1 a;

#BLOCK ID NAME ORA_ROWSCN
392719 1 USEF 7777285
392719 2 VAHID 7777285

همانطور که می بینید، با انکه رکورد اول، در scn متفاوتی درج شده است، ستون ora_rowscn، برای هر دو رکورد عدد یکسانی را نشان می دهد.

برای ثبت و نگهداری scn در سطح رکورد، باید در هنگام ساخت جدول، عبارت rowdependencies را به دستور ساخت آن جدول اضافه کرد. مثال قبلی را با احتساب این عبارت، مجددا تکرار می کنیم:

مثال:


SQL> create table usef.tb2 (id number,name varchar2(9)) rowdependencies;


SQL> insert into usef.tb2 values(1,’USEF’);


SQL> commit;


SQL> select dbms_rowid.rowid_block_number(rowid) as block#,a.id,a.name,ora_rowscn from usef.tb2 a;


SQL> insert into usef.tb2 values(2,’VAHID’);


SQL> commit;


SQL> select dbms_rowid.rowid_block_number(rowid) as block#,a.id,a.name,ora_rowscn from usef.tb2 a;

#BLOCK ID NAME ORA_ROWSCN
392727 1 USEF 7781757
392727 2 VAHID 7781766

برگداندن timestamp متناظر با یک scn، با کمک تابع scn_to_timestamp قابل انجام است:


SQL> select a.id,a.name,ora_rowscn,to_char(scn_to_timestamp(ora_rowscn),’YYYY/MM/DD HH24:mi:ss’) time_date from usef.tb1 a;

ID NAME ORA_ROWSCN TIME_DATE
1 USEF 7777285 2018/12/06 12:16:40
2 VAHID 7777285 2018/12/06 12:16:40

بدیهی است که برای یافتن تاریخ اخرین تغییر یک جدول، می توان به سراغ (max(ora_rowscn رفت:

SQL> select max(ora_rowscn) max_scn, to_char(scn_to_timestamp(max(ora_rowscn)),’YYYY/MM/DD HH24:mi:ss’,’nls_calendar=PERSIAN’) date_time from usef.tb1;

MAX_SCN DATE_TIME
7777285 1397/09/15 12:16:40

البته با اجرای این دستور، full table scan رخ خواهد داد و ایجاد ایندکس هم بر روی ستون ora_rowscn، امکان پذیر نخواهد بود:


SQL> create index usef.ind on usef.tb1(ora_rowscn);

ORA-43807: indexes on ORA_ROWSCN not allowed

نکته 1: برای مشاهده لیست جداولی که خصیصه rowdependencies برای آنها فعال شده است، می توان از دستور زیر استفاده کرد:


SQL> SELECT table_name, dependencies FROM dba_tables WHERE dependencies=’ENABLED’;

TABLE_NAME DEPENDENCIES
TB2 ENABLED

نکته 2: تابع scn_to_timestamp بازه زمانی محدودی را پشتیبانی می کند(tuned_retention) و برای اطلاعات قدیمی، خطا بر می گرداند:

SQL> select scn_to_timestamp(min(ora_rowscn)) from sys.obj$;

ORA-08181: specified number is not a valid system change number

دیدگاهتان را بنویسید

نشانی ایمیل شما منتشر نخواهد شد.