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);
اولین قدم برای کوچک کردن 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.
همانگون که در بالا میبینید مشکل حل شد و پایگاه داده به صورت صحیح به کار خود ادامه میدهد.
دوستان عزیز امیدوارم مطلب کاربردی بوده باشد.
شاد و پیروز باشید.