ORA-00959 and ORA-14511

با سلام خدمت دوستان عزیز

امروز به یک مشکل برخوردم که بعد از رفع عیب دوست دارم تجربیات خود را با شما به اشتراک بذارم.

توی یک پایگاه داده یکی از job های ما با مشکل روبرو شده بود. دلیل مشکل پر شدن archivelog ها بود چون یک procedure اجرا شده بود که در آن پایگاه داده غیر رایج بود و تمام حجم fra به دلیل تغییرات بسیار زیاد بر روی block ها پر کرده بود. پس از رفع مشکل تصمیم گرفتیم کمی سرو سامان بدیم به پایگاه داده.

در حقیقت مقدار زیادی از فضای tablespace خالی بود و فقط فضا روی disk بی مورد اشغال شده بود. برای پیدا کردن مقدار حجم logical‌ و physical میتوانید از اسکریپت زیر که نوشته ام استفاده کنید.

SQL> col tname for a20;
SQL> col "Used space GB" for 99999;
SQL> col "Max space GB" for 99999;
SQL> col "Free space GB" for 99999;
SQL> col percent_free for a15;
SQL> select
  df.tname,
  round(us.used_space/1024/1024/1024) as "Used space GB",
  round(df.max_space/1024/1024/1024) as "Max space GB",
  round((df.max_space - us.used_space)/1024/1024/1024) as "Free space GB",
  round(((df.max_space - us.used_space) * 100) / df.max_space,2)||'%' as percent_free
from
  (
    select TABLESPACE_NAME  as tname,
    sum(case when autoextensible='YES' then maxbytes else bytes end) as max_space
    from DBA_DATA_FILES
    group by TABLESPACE_NAME
  ) df
inner join
  (
    select TABLESPACE_NAME  as tname,
    sum(BYTES) as used_space
    from DBA_segments
    group by  TABLESPACE_NAME
  ) us on (df.tname = us.tname)
--where df.tname in ('TXDATA','TXDATA_IX')
order by round(((df.max_space - us.used_space) * 100) / df.max_space,2);

پارتیشن بندی آنلاین یک جدول با dbms_redefinition

اگر شما بخواهید در سایت عملیاتی یک جدول که مدام زیر بار هست رو تغییر ساختار بدید، ۲ راه دارید یا downtime بدید یا از پکیج dbms_redefinition استفاده کنید. خوب پس با من هراه باشید تا با 100% availability تغییرات رو انجام بدیم.

تغییرات ساختار هر چیزی میتواند باشد، در اینجا قصد داریم یک heap table را به partitioned table بدون downtime همراه با dml کاربران تغییر ساختار بدیم.

• چون این کار را برای تست انجام میدهیم اول یک یوزر معمولی می سازیم و دسترسی های مورد نیاز رو به آن میدهیم:

create user masoud account unlock identified by masoud quota unlimited on users;
grant create table, create session to masoud;
grant create sequence to masoud;

• حال با آن کاربر متصل شده و یک جدول معمولی ایجاد میکنیم و آن را با ۵۰،۰۰۰ رکورد پر میکنیم:

create sequence masoud_seq;

create table mytab (
id  number(20),
cdate date
);

begin
  for i in 1..1000 loop
    for j in 1..50 loop
      insert into mytab values (masoud_seq.nextval, sysdate + i);
    end loop;
  end loop;
  commit;
end;
/

تا الان ما یک جدول داریم به نام mytab که پارتیشن شده نیست و از الان به بعد با دستورات dbms_redefinition میخواهمیم آن را در حالی که کاربران حال query زدن و اجرای DML هستند به یک جدول پارتیشن شده تیدیل کنیم.

• اول بررسی میکنیم ببینیم جدول جاری میتواند از تکنیک dbms_redefinition استفاده کند یا خیر :

 select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

begin
DBMS_REDEFINITION.CAN_REDEF_TABLE(
  UNAME => 'MASOUD',
  TNAME => 'MYTAB',
  OPTIONS_FLAG => DBMS_REDEFINITION.CONS_USE_ROWID
);
end;
/

PL/SQL procedure successfully completed.

همانگونه که میبینید خروجی با مشکلی رو به رو نشد و error هم نداشتیم پس جدول رو میتوانیم به صورت online تغییر ساختار بدیم. یک نکته در مورد CONS_USE_ROWID هست اون هم اینکه جدول یا باید دارای primary key باشد یا اگر نیست از تکنیک فوق استفاده کنید. درصورتی که این option رو انتخاب نکنید توی روال با مشکل رو به رو میشوید.

• در این مرحله یک جدول با ساختار partition شده میسازیم که میخواهیم جدول mytab به شکل آن باشد:

create table interim (
id  number(20),
cdate date
)
partition by range(cdate) interval (numtodsinterval(1,'day'))
(PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2017-05-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')));

جدول جاری من در حال حاضر ۵۰،۰۰۰ رکورد داد. پارتیشن های من در ساختار جدید هرکدام ۵۰ رکورد دارد، اگر query ها ای که به سمت پایگاه داده می آید table full scan باشد و اگر ما دنبال یک رکورد خاص با جستجو روی cdate باشیم احتمال partition pouring  بسیار زیاد است و سرعت اجرای query به مراتب بیشتر خواهد بود.

• خوب حال شروع به انجام redefinition میکنیم:

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE
 (uname=>'MASOUD',
 orig_table=>'MYTAB',
 int_table=>'interim',
 options_flag=>DBMS_REDEFINITION.CONS_USE_ROWID);
end;
/

PL/SQL procedure successfully completed.

اگر این مرحله بسیار زمان بر بود بهتره که SYNC_INTERIM_TABLE گهگاه اجرا کنیم تا در مرحله  FINISH_REDEF_TABLE زمان lock کمتری داشته باشیم.

• در این مرحله وابستگی ها و object های مرتبط را به جدول interim میدهیم:

vari p_errors number;

BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
 (uname=>'MASOUD',
 orig_table=>'MYTAB',
 int_table=>'INTERIM',
 num_errors=>:p_errors);
END;
/

PL/SQL procedure successfully completed.

SQL> print p_errors;
P_ERRORS
----------
0

در صورتی که روال بالا به مشکل رو به رو شده باشد p_errors مقدار خواهد داشد، که الان مشکلی را نشان نمیدهد.

• تا الان جدول mytab بصورت قلبی پابرجاست و حدول interim پارتیشن شده است و اطلاعات mytab در درون آن کپی شده است.

SQL> select table_name from user_part_tables;

TABLE_NAME
------------------------------
INTERIM

• درصورت مشکل در هرکدام از مراحل فوق  با دستور زیر فرایند redefinition را متوفق نمایید:

begin
  DBMS_REDEFINITION.ABORT_REDEF_TABLE
  (
    UNAME=>'MASOUD',
    ORIG_TABLE=>'MYTAB',
    INT_TABLE=>'interim');
end;
/

PL/SQL procedure successfully completed.

• و این هم آخرین مرحله از redefinition:

begin
dbms_redefinition.finish_redef_table
 (uname=>'MASOUD',
 orig_table=>'MYTAB',
 int_table=>'INTERIM');
end;
/

PL/SQL procedure successfully completed.

در این مرحله اسم جداول با یکدیگر جابجا شده و DML ها به صورت واقعی جدول با ساختار جدید را تحت تاثیر قرار میدهد.

• در نهایت بررسی میکنیم ببینیم جدول پارتیشن شده است یا خیر:

select table_name,partitioning_type from user_part_tables;

TABLE_NAME                     PARTITION
------------------------------ ---------
MYTAB                          RANGE

select count(1) as cnt from user_tab_partitions;

       CNT
----------
      1001

select index_name,table_name from user_indexes;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
MYTAB_ID_PX                    MYTAB
TMP$$_MYTAB_ID_PX0             INTERIM

دوستان عزیز امیدوارم مطلب کاربردی بوده باشد.

شاد و پیروز باشید.