مقدمه
چندین دلیل برای ایجاد فضای استفاده نشده در Datafileها وجود دارد که در این مقاله به ۲ روش می‌پردازیم:
• عدم housekeeping/maintenance به این معنی است که یک یا چند جدول بیش از حد رشد کرده‌اند. پس از حذف داده‌ها ، Datafileها حاوی فضای استفاده نشده‌ای هستند که باید دوباره بازیابی شود.
• یک یا چند segment (tables, partitions or indexes) به یک tablespace دیگر منتقل می‌شوند ولی فضای خالی در datafile قبلی باقی می‌ماند.
آزادسازی فضاهای استفاده نشده در oracle datafiles
در این مقاله به چند روش احیای مجدد این فضای بلااستفاده می‌پردازیم:
• Setup Test Environment
• Identify Tablespaces with Free Space
• Export/Import
• Manual Tablespace Reorganization
• Enterprise Manager Tablespace Reorganization
• Shrink
• Tablespaces with Multiple Datafiles
• Undo Tablespace
• Temp Tablespace
• SYSAUX Tablespace
هر بار که فضای خالی ایجاد می‌شود نیاز به انجام مجدد سازماندهی tablespace توصیه نمی‌شود. در بسیاری از موارد این فضا بدلیل نیاز به رشد tablespace مجدد استفاده خواهد شد.
لازم بذکر می‌باشد سازماندهی مجدد tablespace تغییر ساختاری بزرگی است و توصیه می‌شود قبل از انجام ، از دیتابیس بک‌آپ تهیه شود.
Setup Test Environment:
برای انجام سناریو ابتدا اقدام به ایجاد محیط تست می‌کنیم:
CONN / AS SYSDBA
— Create a tablespace and user for the test.
DROP USER reclaim_user CASCADE;
DROP TABLESPACE reclaim_ts INCLUDING CONTENTS AND DATAFILES;
CREATE TABLESPACE reclaim_ts DATAFILE ‘/u01/app/oracle/oradata/DB11G/reclaim01.dbf’ SIZE 1M AUTOEXTEND ON NEXT 1M;
CREATE USER reclaim_user IDENTIFIED BY reclaim_user DEFAULT TABLESPACE reclaim_ts QUOTA UNLIMITED ON reclaim_ts;
GRANT CREATE SESSION, CREATE TABLE TO reclaim_user;
— Create and populate two tables in the test schema.
CONN reclaim_user/reclaim_user
CREATE TABLE t1 (
id NUMBER,
description VARCHAR2(1000),
CONSTRAINT t1_pk PRIMARY KEY (id)
);
CREATE TABLE t2 (
id NUMBER,
description VARCHAR2(1000),
CONSTRAINT t2_pk PRIMARY KEY (id)
);
INSERT /+append/ INTO t1
SELECT rownum, RPAD(‘x’, 1000, ‘x’)
FROM dual
CONNECT BY level <= 10000;
COMMIT;
INSERT /+append/ INTO t2
SELECT rownum, RPAD(‘x’, 1000, ‘x’)
FROM dual
CONNECT BY level <= 10000; COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, ‘t1’); EXEC DBMS_STATS.gather_table_stats(USER, ‘t2’); می‌توانیم ببینیم که هر segment جدول از چندین extent و هر یک از آنها از چندین block oracle تشکیل شده است. CONN / AS SYSDBA COLUMN segment_name FORMAT A30 SELECT segment_type, segment_name, COUNT(*) FROM dba_extents WHERE owner = ‘RECLAIM_USER’ GROUP BY segment_type, segment_name ORDER BY segment_type, segment_name; SEGMENT_TYPE SEGMENT_NAME COUNT(*) INDEX T1_PK 3 INDEX T2_PK 3 TABLE T1 27 TABLE T2 27 SQL>
SELECT table_name, num_rows, blocks FROM dba_tables WHERE owner = ‘RECLAIM_USER’;
TABLE_NAME NUM_ROWS BLOCKS
T1 10000 1461
T2 10000 1461
می‌توان با استفاده از Enterprise Manager تصویری از محتویات tablespace مشاهده کرد:
• ابتدا زبانه server را انتخاب می‌کنیم.
• سپس گزینه tablespace را انتخاب می‌کنیم.
• بعد از آن tablespace موردنظر را انتخاب می‌کنیم.
• در قسمت actions گزینه “Show Tablespace Contents” را انتخاب و برروی کلید Go کلیک می‌کنیم.
• برای مشاهده از قسمت “Extent Map” استفاده می‌کنیم.
کلیک برروی یک Segment در لیست و یا کلیک برروی یک extent در extent map باعث می‌شود تمام extentهای مربوط به آن segment به رنگ زرد تغییر کند.
بمنظور شبیه سازی عملیات maintenance اقدام به truncate یکی از جداول می‌کنیم:
CONN reclaim_user/reclaim_user
TRUNCATE TABLE t1;
چیزی که سپس مشاهده می‌کنیم این است که جدول تنها دارای یک extent (به رنگ زرد) و مقدار زیادی فضای خالی (به رنگ سبز) می‌باشد.
درحقیقت اکثر فضای خالی قبل از برخی extentهای جدول T2 قرار دارند ، که به این معنی می‌باشد که نمی‌توان حجم Datafile را بمنظور آزاد سازی فضا کم کرد.
CONN / AS SYSDBA
COLUMN name FORMAT A50
SELECT name, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE name LIKE ‘%reclaim%’;
NAME SIZE_MB
/u01/app/oracle/oradata/DB11G/reclaim01.dbf 26
SQL>
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/DB11G/reclaim01.dbf’ RESIZE 24M;
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/DB11G/reclaim01.dbf’ RESIZE 24M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value
مشکل از اینجا آغاز می‌شود، زیرا ما فضای خالی داریم که باید از Datafile مرتبط با Tablespace آزاد کنیم.
قبل از کار با هر یک از روش های شرح داده شده ، این وضعیت را ایجاد کنید.
Identify Tablespaces with Free Space:
مشاهده وضعیت tablespaceها:
SET PAGESIZE 140 LINESIZE 200
COLUMN used_pct FORMAT A11
SELECT tablespace_name,
size_mb,
free_mb,
max_size_mb,
max_free_mb,
TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
RPAD(‘ ‘|| RPAD(‘X’,ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), ‘X’),11,’-‘) AS used_pct
FROM (
SELECT a.tablespace_name,
b.size_mb,
a.free_mb,
b.max_size_mb,
a.free_mb + (b.max_size_mb – b.size_mb) AS max_free_mb
FROM (SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS free_mb
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name,
TRUNC(SUM(bytes)/1024/1024) AS size_mb,
TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
)
ORDER BY tablespace_name;
SET PAGESIZE 14
با استفاده از اسکریپت بالا براحتی می توان tablespaceهایی که دارای فضای خالی هستند را شناسایی کنید:
SQL> @ts_free_space.sql
TABLESPACE_NAME SIZE_MB FREE_MB MAX_SIZE_MB MAX_FREE_MB FREE_PCT USED_PCT
EXAMPLE 345 35 345 35 10 XXXXXXXXX-
RECLAIM_TS 25 12 32767 32754 99 ———-
SYSAUX 580 33 580 33 5 XXXXXXXXX-
SYSTEM 720 7 720 7 0 XXXXXXXXXX
UNDOTBS1 70 41 70 41 58 XXXX——
USERS 20 15 20 15 75 XXX——-
6 rows selected.
زمانی که autoextend فعال باشد، اسکریپت بالا حداکثر اندازه و درصد را بر اساس حداکثر اندازه ای که Datafileها می‌توانند رشد کنند محاسبه می‌کند.
اگر autoextend را غیرفعال کنیم، ۴ ستون آخر دچار تغییر می‌شوند:
SQL> ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/DB11G/reclaim01.dbf’ AUTOEXTEND OFF;
Database altered.
SQL> @ts_free_space.sql
TABLESPACE_NAME SIZE_MB FREE_MB MAX_SIZE_MB MAX_FREE_MB FREE_PCT USED_PCT
EXAMPLE 345 35 345 35 10 XXXXXXXXX-
RECLAIM_TS 25 12 25 12 48 XXXXX—–
SYSAUX 580 33 580 33 5 XXXXXXXXX-
SYSTEM 720 7 720 7 0 XXXXXXXXXX
UNDOTBS1 70 40 70 40 57 XXXX——
USERS 20 15 20 15 75 XXX——-
6 rows selected.
Export/Import:
روند عملیات Export/Import به این شکل می‌باشد:
• تهیه اسکپورت از آبجکت‌های یک اسکیما، که در یک tablespace/datafile خاص که می‌خواهیم حجم آن‌را تغییر دهیم قرار دارند.
• حذف آبجکت‌های اکسپورت گرفته شده.
• تغییر سایز datafile.
• ایمپورت آبجکت‌ها.
• انجام تغییرات پ بررسی‌های مورد نیاز، مانند دسترسی‌ها …
در زیر روند مربوط به هر عملیات نشان داده شده است.
ابتدا برای عملیات اسکپورت/ایمپورت اقدام به ایجاد یک دایرکتوری می‌کنیم:
CONN / AS SYSDBA
CREATE DIRECTORY temp_dir AS ‘/tmp’;
GRANT READ, WRITE ON DIRECTORY temp_dir TO reclaim_user;
در این مورد از کل اسکیما اکسپورت می‌گیریم:
expdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=expdpRECLAIM_USER.log
پس از آن، آبجکت‌های اصلی را حذف و حجم datafileها را کاهش می‌دهیم:
CONN / AS SYSDBA
DROP TABLE reclaim_user.t1;
DROP TABLE reclaim_user.t2;
PURGE DBA_RECYCLEBIN;
ALTER TABLESPACE reclaim_ts COALESCE;
ALTER DATABASE DATAFILE ‘/u01/app/oracle/oradata/DB11G/reclaim01.dbf’ RESIZE 5M;
سپس عملیات ایمپورت را دراسکیمای موردنظر انجام می‌دهیم:
impdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=impdpRECLAIM_USER.log
نقشه tablespace نشان می دهد که ما قسمت وسیعی از فضای آزاد را در وسط datafile مرتبط با tablespace خود حذف کرده ایم.
همچنین می‌توان مشاهده کرد که اندازه datafile از26M به 14M کاهش یافته است:
CONN / AS SYSDBA
COLUMN name FORMAT A50
SELECT name, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE name LIKE ‘%reclaim%’;
NAME SIZE_MB
/u01/app/oracle/oradata/DB11G/reclaim01.dbf 14
در این مثال جدول اول را truncate کردیم، پس segmentهای جدول حاوی فضای خالی داخلی زیادی نیستند. اگر شما دارای segmentها با فضای خالی داخلی زیادی هستید، برای تمیز کردن علاوه بر فضای استفاده نشده در datafile، باید از پارامتر TRANFORM در ایمپورت استفاده کنید. استفاده از “TRANSFORM=SEGMENT_ATTRIBUTES:N” در دستور ایمپورت به معنی حذف ویژگی‌های فیزیکی جدول ( شامل storage clause ) در زمان ساخت جدول می‌باشد:
impdp reclaim_user/reclaim_user schemas=RECLAIM_USER directory=TEMP_DIR dumpfile=RECLAIM_USER.dmp logfile=impdpRECLAIM_USER.log \
transform=segment_attributes:n
Manual Tablespace Reorganization:
پشتیبانی از عملیات انتقال Segment بصورت آنلاین ، با هر نسخه بهبود می یابد. بسته به ورژن دیتابیس مورد استفاده و جنس Segmentهایی که می‌خواهیم جابجا کنیم ، شاید بتوانید این‌کار را بصورت آنلاین انجام دهید.
عملیات زیر می‌تواند یکی از دو روش برای این‌کار باشد:
• ساخت Tablespace جدید.
• انتقال Segmentها به Tablespace جدید.
• حذف Tablespace اصلی.
• تغییر نام Tablespace به نام Tablespace اصلی.
در برخی از ورژن‌های دیتابیس امکان بروز خطا در تغییر نام Tablespace ، به نامی که قبل‌تر مورد استفاده قرار گرفته وجود دارد ( طبق مستند MOS Doc ID 604648.1). اگر نیاز به استفاده از نام قبلی را دارید، بهتر است Tablespace را حذف و مجدد ایجاد کنید سپس همه اطلاعات را به آن منتقل کنید.
اگر نام Datafile باید ثابت بماند ، موارد زیر را انجام دهید:
• ساخت Tablespace جدید.
• انتقال Segmentها به Tablespace جدید.
• تغییر سایز Datafile اصلی.
• بازگردانی Segmentها به Tablespace اصلی.
• حذف Tablespace جدید.
بدیهی است که روش دوم به کار بسیار بیشتری نیاز دارد زیرا همه بخش‌ها دو بار جابجا می‌شوند.
روش انتقال Segmentها به جنس آنها بستگی دارد، در اینجا به مثال اشاره می‌کنیم:
— Move a table segment.
ALTER TABLE tab1 MOVE TABLESPACE new_ts;
— Online in 12.2 onward.
ALTER TABLE tab1 MOVE ONLINE TABLESPACE new_ts;
— Move an index segment.
ALTER INDEX ind1 REBUILD TABLESPACE new_ts;
ALTER INDEX ind1 REBUILD TABLESPACE new_ts ONLINE;
— Move a table partition segment. (Remember to check for unusable indexes)
ALTER TABLE tab1 MOVE PARTITION part_1 TABLESPACE new_ts NOLOGGING;
— Online in 12.1 onward.
ALTER TABLE tab1 MOVE PARTITION part_1 ONLINE TABLESPACE new_ts NOLOGGING;
— Move an index partition segment.
ALTER INDEX ind1 REBUILD PARTITION ind1_part1 TABLESPACE new_ts;
ALTER INDEX ind1 REBUILD PARTITION ind1_part1 TABLESPACE new_ts ONLINE;
— Move LOB segments if we had them.
— ALTER TABLE tab1 MOVE LOB(lob_column_name) STORE AS (TABLESPACE new_ts);
البته جداول و Indexهای مربوطه می‌توانند با استفاده از قابلیت Online Table Redefinition منتقل شوند.
در مثال زیر زمانی که نیاز به نگهداری نام قبلی Datafile نداریم، بصورت دستی اقدام به ساماندهی می‌کنیم:
ابتدا یک Tablespace جدید برای نگهداری Objectها ایجاد می‌کنیم:
CONN / AS SYSDBA
CREATE TABLESPACE reclaim_ts_temp DATAFILE ‘/u01/app/oracle/oradata/DB11G/reclaim02.dbf’ SIZE 1M AUTOEXTEND ON NEXT 1M;
ALTER USER reclaim_user QUOTA UNLIMITED ON reclaim_ts_temp;
سپس نسبت به انتقال Objectها به Tablespace جدید اقدام می‌کنیم:
ALTER TABLE reclaim_user.t1 MOVE TABLESPACE reclaim_ts_temp;
ALTER INDEX reclaim_user.t1_pk REBUILD TABLESPACE reclaim_ts_temp;
ALTER TABLE reclaim_user.t2 MOVE TABLESPACE reclaim_ts_temp;
ALTER INDEX reclaim_user.t2_pk REBUILD TABLESPACE reclaim_ts_temp;
پس از آن Tablespace اصلی را حذف و نام Tablespace جدید را به نام اصلی تغییر می‌دهیم:
DROP TABLESPACE reclaim_ts INCLUDING CONTENTS AND DATAFILES;
ALTER TABLESPACE reclaim_ts_temp RENAME TO reclaim_ts;
مجدد نقشه Tablespace نشان می دهد که ما قسمت وسیعی از فضای آزاد موجود در وسط Datafileهای مرتبط با Tablespace خود را حذف کرده‌ایم:
همچنین می‌توانیم ببینیم که اندازه Datafile از 26M به 13M کاهش یافته است.
CONN / AS SYSDBA
COLUMN name FORMAT A50
SELECT name, bytes/1024/1024 AS size_mb
FROM v$datafile
WHERE name LIKE ‘%reclaim%’;
NAME SIZE_MB
/u01/app/oracle/oradata/DB11G/reclaim01.dbf 13
در نسخه 12.1 امکان انتقال Partitionها و sub-partitionها بصورت آنلاین وجود دارد. همچنین در نسخه 12.2 امکان انتقال جداول عادی بصورت آنلاین میسر شده است.
Enterprise Manager Tablespace Reorganization:
ساماندهی Tablespaceها بصورت دستی بخوبی انجام می‌شود ولی زمانی که با تعداد زیادی Segments روبرو هستیم استفاده از اسکریپت ممکن است دشوار باشد ، خصوصا اگر از روش online table redefinition استفاده کنید. این جایی است که Enterprise Manager به کمک شما می‌آید زیرا می‌تواند تمام کارهای سخت را برای شما انجام دهد و به شما این امکان را می‌دهد که به راحتی یک job را برای انجام یک ساماندهی مجدد یک Tablespace تعریف کنید.
برای انجام این‌کار ابتدا Tablespace موردنظر را انتخاب و سپس در قسمت action گزینه “Reorganize” را انتخاب می‌کنیم:
در صفحه Objects بدون تغییرموارد پیش‌فرض برروی گزینه Next کلیک می‌کنیم:
صفحه Options به شما امکان تصمیم گیری در مورد نحوه ساماندهی مجدد را می‌دهد. قسمت Method دارای ۲ گزینه می‌باشد:
Speed: یک عملیات آفلاین است که از Move برای جداول و Rebuild برای Indexها استفاده می‌کند. اساسا همان ساماندهی مجدد Tablespace است که قبلا به آن پرداخته‌ایم.
Availability: این یک عملیات آنلاین است که از قابلیت online table redefinition استفاده می‌کند. online table redefinition می‌تواند براساس primary key جدول و یا ROWID سطرها انجام شود.
قسمت “Scratch Tablespace” دارای ۲ گزینه می‌باشد که مشخص می‌کند نام Datafile حفظ شود یا خیر:
• Use tablespace rename feature: همانظور که از نامش مشخص است، که از اولین روش شرح داده شده در بخش مربوط به ساماندهی دستی استفاده می‌کند.
• Use scratch tablespace: این عملیات از دومین روش شرح داده شده در بخش مربوط به ساماندهی دستی استفاده می‌کند. بمنظور نگهداری Objectها در طول ساماندهی باید نام یک tablespace موجود را معرفی کنید.
پس از انتخاب گزینه موردنظر برروی کلید Next کلیک می‌کنیم.
صفحه impact report حاصل گزارش را ارائه می دهد و اگر شامل مشکلات پیش‌بینی شده باشد ، ممکن است لازم باشد به عقب برگردید و گزینه های خود را تغییر دهید، در غیر اینصورت روی گزینه Next کلیک می‌کنیم:
در صفحه schedule زمانبندی دلخواه را مشخص و روی گزینه Next کلیک می‌کنیم:
صفحه review شامل اسکریپت است که توسط job اجرا می‌شود. در صورت تایید برروی گزینه “Submit Job” کلیک می‌کنیم:
بعد از انجام می‌توان مشاهده کرد که Segmentها در ایتدای نقشه Tablespace قرار دارند و این اجازه را به ما می‌دهد تا در صورت تمایل سایز Datafile را کاهش دهیم:
Shrink:
اگر تنها یک Object در Datafile وجود داشته باشد، ممکن است تنها یک عملیات آنلاین Shrink برای کوچک کردن دیتا و آزاد سازی بلاک‌های خالی کافی باشد.
لازم بذکر می‌باشد عملیات Shrink تنها اقدام به کوچک‌تر کردن دیتا می‌کند و تنضمینی برای قرارگیری بلاک‌ها در قسمت جلویی Datafile وجود ندارد و ممکن است بلاک‌ها در انتهای Datafile وجود داشته باشند، که به این معنی می‌باشد که امکان کوچک شدن Datafile وحود ندارد. در این صورت باید از یکی از روش‌های که قبل گفته شد استفاده کرد.
Tablespaces with Multiple Datafiles:
برای انجام این‌کار قبل از ایجاد جدول یک Datafile جدید نیز به Tablespace اضافه می‌کنیم.
ALTER TABLESPACE reclaim_ts ADD DATAFILE ‘/u01/app/oracle/oradata/DB11G/reclaim02.dbf’ SIZE 1M AUTOEXTEND ON NEXT 1M;
با ساخت و پر شدن جدول و جدول Truncate شده “T1” ، نقشه extent به این صورت نمایش داده می‌شود:
مسئله اینجاست که ما نمی توانیم بگوییم کدام فایل در بین فضاهای خالی قرار گرفته است خصوصا زمانی که دارای تعداد زیادی فایل ، Segment و فضای خالی هستیم. در این صورت باید با استفاده از اسکریپت اقدام به شناسایی فضاهای خالی در سطح Tablespace موردنظر بکنیم.
اسکریپت زیر یک لیست از تمام فضاهای خالی Tablespace موردنظر ایجاد می‌کند.
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF
DECLARE
l_tablespace_name VARCHAR2(30) := UPPER(‘&1’);
l_file_id VARCHAR2(30) := UPPER(‘&2’);
CURSOR c_extents IS
SELECT owner,
segment_name,
file_id,
block_id AS start_block,
block_id + blocks – 1 AS end_block
FROM dba_extents
WHERE tablespace_name = l_tablespace_name
AND file_id = DECODE(l_file_id, ‘ALL’, file_id, TO_NUMBER(l_file_id))
ORDER BY file_id, block_id;
l_block_size NUMBER := 0;
l_last_file_id NUMBER := 0;
l_last_block_id NUMBER := 0;
l_gaps_only BOOLEAN := TRUE;
l_total_blocks NUMBER := 0;
BEGIN
SELECT block_size
INTO l_block_size
FROM dba_tablespaces
WHERE tablespace_name = l_tablespace_name;
DBMS_OUTPUT.PUT_LINE(‘Tablespace Block Size (bytes): ‘ || l_block_size);
FOR cur_rec IN c_extents LOOP
IF cur_rec.file_id != l_last_file_id THEN
l_last_file_id := cur_rec.file_id;
l_last_block_id := cur_rec.start_block – 1;
END IF;
IF cur_rec.start_block > l_last_block_id + 1 THEN
DBMS_OUTPUT.PUT_LINE(‘*** GAP *** (‘ || l_last_block_id || ‘ -> ‘ || cur_rec.start_block || ‘)’ ||
‘ FileID=’ || cur_rec.file_id ||
‘ Blocks=’ || (cur_rec.start_block-l_last_block_id-1) ||
‘ Size(MB)=’ || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)
);
l_total_blocks := l_total_blocks + cur_rec.start_block – l_last_block_id-1;
END IF;
l_last_block_id := cur_rec.end_block;
IF NOT l_gaps_only THEN
DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || ‘.’ || cur_rec.segment_name, 40, ‘ ‘) ||
‘ (‘ || cur_rec.start_block || ‘ -> ‘ || cur_rec.end_block || ‘)’);
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total Gap Blocks: ‘ || l_total_blocks);
DBMS_OUTPUT.PUT_LINE(‘Total Gap Space (MB): ‘ || ROUND((l_total_blocks * l_block_size)/1024/1024,2));
END;
/
PROMPT
SET FEEDBACK ON
نتیجه شامل خروجی جمع کلی و جمع به ازای هر Datafile می‌باشد:
SQL> @ts_extent_map reclaim_ts all
Tablespace Block Size (bytes): 8192
*** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81
*** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5
*** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5
Total Gap Blocks: 1576
Total Gap Space (MB): 12.31
SQL> @ts_extent_map reclaim_ts 8
Tablespace Block Size (bytes): 8192
*** GAP *** (23 -> 896) FileID=8 Blocks=872 Size(MB)=6.81
*** GAP *** (959 -> 1024) FileID=8 Blocks=64 Size(MB)=.5
Total Gap Blocks: 936
Total Gap Space (MB): 7.31
SQL> @ts_extent_map reclaim_ts 9
Tablespace Block Size (bytes): 8192
*** GAP *** (127 -> 768) FileID=9 Blocks=640 Size(MB)=5
Total Gap Blocks: 640
Total Gap Space (MB): 5
این می‌تواند به شما برای تشخیص نیاز به ساماندهی Tablespaceها کمک کند.
Undo Tablespace:
ساده‌ترین راه برای بازپس گیری فضا از Undo tablespace ایجاد یک Undo tablespace جدید و حذف Undo tablespace قبلی می‌باشد.
CREATE UNDO TABLESPACE undotbs2 DATAFILE ‘/u01/app/oracle/oradata/DB11G/undotbs02.dbf’ SIZE 2G AUTOEXTEND ON NEXT 1M;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs2;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
لازم بذکر می‌باشد که عملیات Flashback امکان‌پذیر نخواهد بود زیرا Undo دخیره شده حذف می‌شود. بهتر است تنظیمات پارامتر UNDO_RETENTION را مدنظر داشته باشید.
Temp Tablespace:
در نسخه 11g امکان کاهش temporary tablespace با استفاده از دستور میسر می‌باشد.
Temporary Tablespace Enhancements in Oracle Database 11g Release 1:
در نسخه 11g با استفاده از view زیر می‌توان اطلاعات مربوط به فضای استفاده Temporary Tablespace را مشاهده کرد:
SQL> SELECT * FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP 56623104 56623104 55574528
1 row selected.
بااستفاده از این اطلاعات می‌توان بصورت آنلاین اقدام به کم کردن فضای Temporary Tablespace کرد:
SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 40M;
Tablespace altered.
SQL> SELECT * FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP 42991616 1048576 41943040
1 row selected.
با ساتفاده از عبارت TEMPFILE امکان کوچک کردن یک TEMPFILE خاص وجود دارد:
SQL> ALTER TABLESPACE temp SHRINK TEMPFILE ‘/u01/app/oracle/oradata/DB11G/temp01.dbf’ KEEP 30M;
Tablespace altered.
SQL> SELECT * FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP 31522816 65536 31457280
1 row selected.
عبارت KEEP حداقل مقدار tablespace یا Tempfile را مشخص می‌کند. در صورتی که از این عبارت استفاده نکنیم، دیتابیس کمترین مقدار ممکن را درنظر میگیرد:
SQL> ALTER TABLESPACE temp SHRINK SPACE;
Tablespace altered.
SQL> SELECT * FROM dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
TEMP 1114112 65536 1048576
1 row selected.
در صورتی که از نسخه قبل‌تر از 11G استفاده می‌کنید، کاهش سایز temporary tablespace مشابه undo tablespace می‌باشد. برای این‌کار ابتدا یک temporary tablespace جدید ایجاد می‌کنیم و پس از انتقال Userها به آن، temporary tablespace قبلی را حذف می‌کنیم:
CREATE TEMPORARY TABLESPACE temp2 TEMPFILE ‘/u01/app/oracle/oradata/DB11G/temp02.dbf’ SIZE 2G AUTOEXTEND ON NEXT 1M;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
— Switch all existing users to new temp tablespace.
BEGIN
FOR cur_user IN (SELECT username FROM dba_users WHERE temporary_tablespace = ‘TEMP’) LOOP
EXECUTE IMMEDIATE ‘ALTER USER ‘ || cur_user.username || ‘ TEMPORARY TABLESPACE temp2’;
END LOOP;
END;
/
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
SYSAUX Tablespace:
SYSAUX tablespace مشابه دیگر Tablespaceها می‌باشد. بنابراین روش‌های اعلام شده در این مقاله برروی SYSAUX tablespace نیز کار خواهد کرد. اگر برخی از موارد موجود را به tablespace جدید منتقل کرده‌اید، می‌توانید کاهش حجم را به میزان فضای اشغال شده انجام دهید. گزینه‌ها شامل موارد زیر است:
• کوچک شدن آنلاین Objectها به سمت انتهای Datafile می تواند به شما امکان دهد اندازه Datafile را کاهش دهید.
• انتقال Objectهای SYSAUX با Extentها به سمت انتهای Datafile در یک tablespace موقت ، سپس اندازه datafile را کاهش داده و اطلاعات را برمی‌گردانیم.
