وبلاگ مسعود حاجی حسن پور

راهکار های عملی اوراکل

راهکار های عملی اوراکل

این وبلاگ جهت به اشتراک گذاری تجربیات و راهکار های عملی در زمینه پایگاه داده اوراکل می باشد.

دستاوردها
طبقه بندی موضوعی

ORA-00959 and ORA-14511

ora-error

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

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

توی یک پایگاه داده یکی از 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);

اولین قدم برای کوچک کردن datafile ها shrink کردن جداول و کم کردن حجم datafile هاست. اما همیشه این موضوع به خوبی جواب نمیدهد چون shrink کردن لزوما جدول را به اول datafile نمیبرد و از همان offset که block اول segment در datafile گرفته است شروع به نوشتن block های بعدی میکیند. به همین دلیل چون جدول در tablespace مورد نظر partition شده بود، shrink زیاد به ما کمک نکرد. به همین دلیل تصمیم گرفتیم که جدول را به tablespace دیگر جابجا کنیم و سپس tablespace را به نام قدیم خود تغییر نام دهیم.


اجازه بدید همین مورد را همراه با یک سناریو جلو ببریم:


در قدم اول یک جدول برای سناریو میسازیم:

As privileged user like sys:

SQL> create tablespace bc datafile '/u01/app/oracle/oradata/db12c/mas12c/bc01.dbf' size 8M autoextend on next 4M;

SQL> alter user masoud quota unlimited on bc;

As local user:

SQL> drop table part1 purge;

SQL> create table part1(
c1 number,
c2 date)
partition by range(c2) interval (numtodsinterval(1,'day'))
(PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2018-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')))
tablespace bc;

SQL> declare

begin 
    for i in 1..100 loop 
        for j in 1..50 loop 
            insert into part1 values (i, sysdate + i);
        end loop; 
    end loop; 
    commit; 
end;
/

SQL> begin
    dbms_stats.gather_table_stats(
        ownname     => 'masoud',
        tabname     => 'part1',
        granularity => 'all',
        cascade     => true
    );
end;
/

SQL> insert into part1 values (101, sysdate + 101);
SQL> commit;

بسیار خوب تا اینجا جدول ساختیم و مقداری داده در آن قرار داده ایم. حال جدوا را جابجا میکنیم به یک tablespace دیگر میبریم برای راحتی کار از یک تیکه PL/SQL استفاده میکنیم:

As privileged user like sys:

SQL> create tablespace bc_change datafile '/u01/app/oracle/oradata/db12c/mas12c/bc_change01.dbf' size 8M autoextend on next 4M;

SQL> alter user masoud quota unlimited on bc_change;

As local user:

SQL> declare
    current_tbs varchar2(10);

    cursor c_partitions is
    select *
    from user_tab_partitions
    where table_name ='PART1';
begin
    for thePartition in c_partitions loop
        execute immediate 'alter table '|| thePartition.table_name ||' move partition '|| thePartition.partition_name ||' tablespace bc_change';
        execute immediate 'select tablespace_name from user_tab_partitions where table_name=''PART1'' and partition_name='''||thePartition.partition_name ||'''' into current_tbs;
        dbms_output.put_line('Partition '|| thePartition.partition_name ||' has been moved to tablespace '|| current_tbs ||'.');
    end loop;
end;
/

SQL> col partition_name for a30;

SQL> select partition_name, tablespace_name from user_tab_partitions where table_name='PART1' and partition_name='P1';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P1                             BC_CHANGE

برای اینکه نشان دهیم partition ها جابجا شده اند برای مثال خروجی بالا را ایجاد کردیم. حالا بررسی میکنیم ببینیم tablespace bc به صورت کامل خالی از هرگونه segment هست یا نه سپس tablespace‌ به نام bc را جذف میکینیم و tablespace به نام bc_change را به bc تغییر نام میدهیم.

As privileged user like sys:

SQL> select count(1) as cnt from dba_segments where tablespace_name ='BC';

       CNT
----------
         0

SQL> drop tablespace bc including contents and datafiles;

SQL> alter tablespace bc_change rename to bc;

بسیار خوب، به نظر همه چیز درست پیش رفته است حالا دوباره بررسی میکنیم ببینیم که partition های ما در کدام tablespace‌ هستند. job را run میکنیم. job ما داده در جدول part1 قرار میدهد. به صورت دستی ما یک مقدار جدید در جدول وارد میکنیم

SQL> col partition_name for a30;

SQL> select partition_name, tablespace_name from user_tab_partitions where table_name='PART1' and partition_name='P1';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P1                             BC

SQL> insert into part1 values (103, sysdate + 103);

Error starting at line : 58 in command -
insert into part1 values (103, sysdate + 103)
Error report -
ORA-00959: tablespace '_$deleted$11$0' does not exist

خطای بالا نشان میدهد که جدول همچنان دارد به tablespace که وجود ندارد اشاره میکند. نکته موضوع در این است که جداول به اسم tablespace کاری ندارند آنها به شماره tablespace کار دارند. از آنجایی که partition ها از phisical structure جدول ارث بری میکنند، در اینجا داده ما میخواهد پارتیشن جدید ایجاد کند اما tablespace به شماره قبلی وجود نداد. حالا tablespace جدول را تغییر میدهیم:

SQL> alter table part1 move tablespace bc;

ORA-14511: cannot perform operation on a partitioned object

به درستی مشکل فوق پدیدار شد، چون عملیات فوق برروی جداول partition شده قابل اجرا نمیباشد. برای جداول partition‌ شده باید default attribute تغییر کرده تا مشکل حل گردد.

SQL> alter table part1 modify default attributes tablespace bc;

Table altered.

دوباره job یا همان قرار دادن داده در جدول را تکرار میکنیم:

insert into part1 values (103, sysdate + 103);

SQL> insert into part1 values (103, sysdate + 103);

1 row created.

SQL> commit;

Commit complete.

همانگون که در بالا میبینید مشکل حل شد و پایگاه داده به صورت صحیح به کار خود ادامه میدهد.


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

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

  • مسعود حاجی حسن پور

Error

VLDB

table partitioning

tablespace

نظرات  (۲)

  • حامد اسماعیلی
  • عالی بود مسعود جان . دم شما گرم
  • حامد آذرپیرا
  • مطلب بسیار مفید و عالی بود مسعود جان، دست شما درد نکنه، نکته جالبی بود.

    ارسال نظر

    ارسال نظر آزاد است، اما اگر قبلا در بیان ثبت نام کرده اید می توانید ابتدا وارد شوید.
    شما میتوانید از این تگهای html استفاده کنید:
    <b> یا <strong>، <em> یا <i>، <u>، <strike> یا <s>، <sup>، <sub>، <blockquote>، <code>، <pre>، <hr>، <br>، <p>، <a href="" title="">، <span style="">، <div align="">
    تجدید کد امنیتی