از نسخه های قدیمی اوراکل امکان resize کردن دیتافایلها وجود دارد:

SQL*Plus: Release 10.1.0.4.2 - Production on Tue Nov 21 11:15:16 2023
SQL> select bytes/1024/1024 SIZE_MB from v$datafile where file#=29;
   SIZE_MB
----------
      1024
SQL> alter database datafile 29 resize 120m;
Database altered.
SQL> select bytes/1024/1024 SIZE_MB from v$datafile where file#=29;
   SIZE_MB
----------
       120

البته resize کردن دیتافایلها همیشه با موفقیت انجام نمی شود و بعضا با آزاد بودن درصد بالایی از فضای دیتافایل، به دلیل قرار گرفتن بعضی از بلاکهای segment بر سر High Water Mark، امکان کاهش سایز دیتافایل وجود ندارد و در این حالت به خطای زیر برخواهیم خورد:

SQL> alter database datafile 1 resize 10m;
ORA-03297: file contains used data beyond requested RESIZE value

برای جلوگیری از خطای ORA-03297، باید segmentای که بر سر راه High Water Mark قرار دارد را جابجا کنیم! که البته قبلا در مطلبی به طور مفصل این مسئله را بررسی کردیم و از تکرار آن خودداری می کنیم.

مسئله ای که در این متن به آن خواهیم پرداخت، موضوع shrink کردن در سطح tablespaceها است. در نسخه 11g امکان shrink کردن Temporary tablespaceها به وجود آمد:

SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 21 10:35:57 2023
SQL> select FILE#,BYTES/1024/1024 from v$tempfile where file#=5;
     FILE# BYTES/1024/1024
---------- ---------------
         5            5120
SQL> ALTER TABLESPACE Temp_TBS SHRINK SPACE KEEP 340m;
Tablespace altered.
SQL> select FILE#,BYTES/1024/1024 from v$tempfile where file#=5;
     FILE# BYTES/1024/1024
---------- ---------------
         5             340

و نهایتا در نسخه 23c اوراکل این امکان را برای Bigfile Tablespaceها ارائه کرده است این کار با استفاده از پروسیجر dbms_space.tablespace_shrink امکان پذیر است که در ادامه نحوه استفاده از آن را شرح خواهیم داد.

ابتدا محیط تست را با ایجاد یک tablespace و چند segment آماده می کنیم:

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release
SQL> create bigfile tablespace Barname_TBS datafile size 5g;
Tablespace created.

SQL> create table usef.Jadval1 tablespace Barname_TBS as select * from tbl1;
Table created.
SQL> create index usef.innn1 on usef. Jadval1(name) tablespace Barname_TBS;
Index created.
SQL> create index usef.innn2 on usef. Jadval1(LINE) tablespace Barname_TBS;
Index created.
SQL> create table usef.T1 tablespace Barname_TBS as select * from v$datafile;
Table created.
SQL> create table usef.T2 tablespace Barname_TBS as select * from v$datafile;
Table created.

اندازه هر کدام از این segmentها را می توان با دستور زیر مشاهده کرد:

SQL> select bytes/1024/1024 SEG_SIZE_MB,segment_name from dba_segments where tablespace_name='BARNAME_TBS' order by 1;
SEG_SIZE_MB SEGMENT_NA
----------- ----------
      .0625 T2
      .0625 T1
        172 INNN2
        280 INNN1
       1600 JADVAL1

با ایجاد segmentهای فوق، فضای اشغال شده در Barname_TBS به دو گیگابایت خواهد رسید:

SQL> select sum(bytes) TBS_SIZE from dba_segments where tablespace_name='BARNAME_TBS';
  TBS_SIZE
----------
2151809024

پارامترهای مربوط به پروسیجر dbms_space.tablespace_shrink را بازبینی می کنیم:

PROCEDURE TABLESPACE_SHRINK
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 TS_NAME                        VARCHAR2                IN
 SHRINK_MODE                    NUMBER                  IN     DEFAULT
 TARGET_SIZE                    NUMBER                  IN     DEFAULT
 SHRINK_RESULT                  CLOB                    OUT

قبل از shrink کردن tablespace می توانیم با کمک این پروسیجر، tablespace را آنالیز کنیم این کار با تنظیم پارامتر shrink_mode به DBMS_SPACE.TS_MODE_ANALYZE قابل انجام است:

SQL> set serveroutput on;
SQL> execute dbms_space.tablespace_shrink(TS_NAME=>'BARNAME_TBS', shrink_mode =>DBMS_SPACE.TS_MODE_ANALYZE);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 0
Total Movable Size(GB): 0
Orginal Datafile Size(GB): 5
Suggested Target Size(GB): 3.91
Process Time: +00 00:00:01.461820
PL/SQL procedure successfully completed.

همانطور که می بینید در صورت اجرای tablespace_shrink، حجم tablespace می تواند به 3.91GB برسد بدون آنکه objectای بخواهد جابجا شود. ایندکسی را حذف کرده و دستور فوق را دوباره اجرا می کنیم:

SQL> drop index INNN;
Index dropped. 

SQL> execute dbms_space.tablespace_shrink(TS_NAME=>'BARNAME_TBS', shrink_mode =>DBMS_SPACE.TS_MODE_ANALYZE);
-------------------ANALYZE RESULT-------------------
Total Movable Objects: 0
Total Movable Size(GB): 0
Orginal Datafile Size(GB): 5
Suggested Target Size(GB): 3.64
Process Time: +00 00:00:01.184724

در صورت اجرای tablespace_shrink، حجم tablespace می تواند به 3.64GB برسد و میزانی از فضای tablespace آزاد خواهد شد بنابرین عملیات shrink را اجرا خواهیم کرد. البته قبل از انجام، بهتر است سایز تنها datafile متعلق به این tablespace را هم ببنیم:

SQL> select bytes from v$datafile where file#=23;
     BYTES
----------
5368709120

بعد از مشاهده سایز دیتافایل، tablespace را shrink می کنیم:

SQL> execute dbms_space.tablespace_shrink(TS_NAME=>'BARNAME_TBS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 0
Total Moved Size(GB): 0
Orginal Datafile Size(GB): 5
New Datafile Size(GB): 2.07
Process Time: +00 00:00:02.230125
PL/SQL procedure successfully completed.

عملیات shrink در مدت زمان 2 ثانیه انجام شده است و سایز datafile از 5 گیگابایت به  2.07 گیگابایت رسیده است:

SQL> select bytes from v$datafile where file#=23;
     BYTES
----------
2223177728

حذف جدول می تواند در shrink کردن tablespace موثر واقع شود ولی حذف رکوردهای جدول از طریق دستور delete اثری بر روی shrink tablespace نخواهد داشت حتی اگر تمامی رکوردهای جدول را حذف کنیم چرا که بلاکی از segmentهای آن جدول کم نخواهد شد:

SQL> select bytes from user_segments where segment_name='JADVAL1';
     BYTES
----------
1677721600 
SQL> delete JADVAL1;
10216352 rows deleted.
SQL> commit;
Commit complete.
SQL> select bytes from user_segments where segment_name='JADVAL1';
     BYTES
----------
1677721600

SQL> execute dbms_space.tablespace_shrink(TS_NAME=>'BARNAME_TBS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 0
Total Moved Size(GB): 0
Orginal Datafile Size(GB): 2.07
New Datafile Size(GB): 2.07
Process Time: +00 00:00:02.680783
PL/SQL procedure successfully completed.

اما با truncate یا حذف جدول امکان shrink کردن tablespace به وجود خواهد آمد و فضای متعلق به جدول را می توان آزاد کرد:

SQL> drop table JADVAL1;
Table dropped.

SQL> execute dbms_space.tablespace_shrink(TS_NAME=>'BARNAME_TBS');
-------------------SHRINK RESULT-------------------
Total Moved Objects: 2
Total Moved Size(GB): 0
Orginal Datafile Size(GB): 2.07
New Datafile Size(GB): .07
Process Time: +00 00:00:14.774229
PL/SQL procedure successfully completed.

پس از حذف جدول JADVAL1، پروسیجر tablespace_shrink توانست فضای متعلق به این جدول را از tablespace بگیرد البته با توجه به آنکه دو جدول T1 و T2 بر سر راه HWM قرار داشتند، ابتدا این دو جدول را به ابتدای فایل جابجا کرد و سپس این فضا را آزاد کرد:

SQL> select bytes from v$datafile where file#=23;
     BYTES
----------
  71499776