همانطور که می دانید، در محیط RACء، Temporary Tablespaceها در سطح دیتابیس ایجاد شده و tempfileها هم باید در فضای shared بین همه instanceها به اشتراک گذاشته شوند.

از اوراکل 12cR2 نوع دیگری از Temporary Tablespace تحت عنوان local temporary tablespace اضافه شده که tempfileهای این نوع از temp TBSها را می توان در فضای local مربوط به instance ایجاد نمود.

در ادامه متن به نحوه ایجاد و مدیریت این نوع از temp tbsها و همچنین نحوه اولویت دهی اوراکل بین Shared Temporary Tablespace و Local temporary tablespace خواهیم پرداخت.

ایجاد و مدیریت Local temporary tablespaceها

برای ایجاد این نوع از Temp Tablespaceها، از دستور زیر استفاده می شود:

[oracle@RAC1 ~]$ mkdir /home/oracle/local_temp

[oracle@RAC2 ~]$ mkdir /home/oracle/local_temp

SQL> create local temporary tablespace FOR ALL Temp_Local_Tbs tempfile ‘/home/oracle/local_temp/Temp_Local_Tbs.dbf’ size 100M;

Tablespace created.

SQL> select inst_id,file_id,file_name from dba_temp_files where TABLESPACE_NAME=’TEMP_LOCAL_TBS’;

   INST_ID    FILE_ID FILE_NAME

———- ———- ———————————————

         1          4 /home/oracle/local_temp/Temp_Local_Tbs.dbf_1

         2          4 /home/oracle/local_temp/Temp_Local_Tbs.dbf_2

 

 [oracle@RAC1 local_temp]$ ls -l

-rw-r—– 1 oracle asmadmin 104865792 Apr  1 11:01 Temp_Local_Tbs.dbf_1

[oracle@RAC2 local_temp]$ ls -l

-rw-r—– 1 oracle asmadmin 104865792 Apr  1 11:01 Temp_Local_Tbs.dbf_2

عبارت FOR ALL در دستور فوق، به معنی ایجاد Local Temp Tablespace برای همه instanceها می باشد(صرف نظر از open_mode دیتابیس) و به جای عبارت FOR ALL می توان از عبارت FOR LEAF استفاده کرد که LEAF برگرفته از مبحث Flex Cluster می باشد که در اوراکل نسخه 19c منسوخ شده است(در Flex Cluster عبارت FOR LEAF مانع از ایجاد Local Temp Tablespace در instanceهای در حالت read write می شد).

Local Temp Tablespaceها همیشه بصورت Bigfile Tablespace ایجاد می شوند و امکان ساخت آنها بصورت smalfile وجود ندارد:

SQL> select SHARED,BIGFILE from dba_tablespaces p where p.tablespace_name=upper(‘Temp_Local_Tbs’);

SHARED        BIG

————- —

LOCAL_ON_ALL  YES

SQL> alter tablespace Temp_Local_Tbs add tempfile;

ORA-32771: cannot add file to bigfile tablespace

برای هر کاربر موجود در دیتابیس، در کنار Default shared Temp TBS، یک Default Local Temp TBS هم در نظر گرفته خواهد شد که در حالت پیش فرض، مقدار هر دو یکسان است و از Shared Temp Tablespace پیش فرض دیتابیس(یعنی temp) به عنوان Default Local Temp TBS هم استفاده خواهد شد:

SQL> SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME like ‘DEFAULT_%_TABLESPACE’;

PROPERTY_NAME                  PROPERTY_VALUE

—————————— ——————–

DEFAULT_LOCAL_TEMP_TABLESPACE  TEMP_LOCAL_TBS

DEFAULT_PERMANENT_TABLESPACE   USERS

DEFAULT_TEMP_TABLESPACE        TEMP

با دستور زیر، Default Local Temp TBS را در سطح Database تغییر خواهیم داد:

SQL> alter database default local temporary tablespace Temp_Local_Tbs;

Database altered.

SQL> select distinct LOCAL_TEMP_TABLESPACE from dba_users;

LOCAL_TEMP_TABLESPACE

———————-

TEMP_LOCAL_TBS

همچنین برای کاربران هم می توان LOCAL_TEMP_TABLESPACE مجزایی تعیین کرد:

SQL> select USERNAME,TEMPORARY_TABLESPACE,LOCAL_TEMP_TABLESPACE from dba_users where username=’USEF’;

USERN TEMPORARY_TABLESPACE    LOCAL_TEMP_TABLESPACE

—–         ——————–                  ———————-

USEF                    TEMP                                       TEMP

از طریق دستور زیر، Default Local Temp TBS کاربر usef را تغییر خواهیم داد:

SQL> alter user usef local temporary tablespace Temp_Local_Tbs;

User altered.

SQL> select USERNAME,TEMPORARY_TABLESPACE,LOCAL_TEMP_TABLESPACE from dba_users where username=’USEF’;

USERN TEMPORARY_TABLESPACE LOCAL_TEMP_TABLESPACE

—–       ——————–           ———————-

USEF          TEMP                               TEMP_LOCAL_TBS

 

کدام نوع از temp tablespace در حالت پیش فرض اولویت دارد؟

با ایجاد Local Temp tablespace، دو نوع Temp tablespace خواهیم داشت سوال؟! اوراکل بصورت پیش فرض، از کدام نوع از Temp TBSها برای انجام عملیات sort، join و … استفاده خواهد کرد؟

سعی کردیم با اجرای یک پرس و جو که برای انجام عملیاتش نیاز به مصرف فضای PGA و TEMP TBS زیادی هم دارد، به این سوال پاسخ دهیم.

توجه: یکی از فاکتورهای اثرگذار در این مسئله، open_mode مربوط instance یا همان وضعیت read/only ، read/write دیتابیس است.

SQL> select open_mode from v$database;

OPEN_MODE

——————–

READ WRITE

SQL> show user

User is “usef”

SQL> select USERNAME,TEMPORARY_TABLESPACE,LOCAL_TEMP_TABLESPACE from dba_users where username=’USEF’;

USERN TEMPORARY_TABLESPACE LOCAL_TEMP_TABLESPACE

—–     ——————–                  ———————-

USEF           TEMP                                 TEMP_LOCAL_TBS

SQL>  select a.* from sys.source$  a,sys.source$,sys.source$,sys.source$,sys.source$ order by 1,2,3;

Executing…

SQL> SELECT A.tablespace_name tablespace,

  2         D.mb_total,

  3         SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_used,

  4         D.mb_total – SUM(A.used_blocks * D.block_size) / 1024 / 1024 mb_free

  5    FROM v$sort_segment A,

  6         (SELECT B.name, C.block_size, SUM(C.bytes) / 1024 / 1024 mb_total

  7            FROM v$tablespace B, v$tempfile C

  8           WHERE B.ts# = C.ts#

  9           GROUP BY B.name, C.block_size) D

 10   WHERE A.tablespace_name = D.name

 11   GROUP by A.tablespace_name, D.mb_total;

TABLESPACE              MB_TOTAL    MB_USED    MB_FREE

——————–               ———- ———- ———-

TEMP                                     1803          0       1803

SQL> /              

TABLESPACE              MB_TOTAL    MB_USED    MB_FREE

——————–               ———- ———- ———-

TEMP                                     2297       2292          5

SQL> /              

TABLESPACE              MB_TOTAL    MB_USED    MB_FREE

——————–               ———- ———- ———-

TEMP                                      5305       5300          5

همانطور که می بینید، اوراکل در حالت read write، به سراغ Shared Temp Tablespace رفته است.

در همین حال، instance دوم را در حالت Read Only قرار داده و مجددا همین پرس و جو را اجرا می کنیم، خواهیم دید که تصمیم اوراکل متفاوت خواهد بود:

SQL> select open_mode from v$database;

OPEN_MODE

——————–

READ ONLY

SQL> @Temp_Usage

TABLESPACE                       MB_TOTAL    MB_USED    MB_FREE

—————————— ———- ———- ———-

TEMP_LOCAL_TBS                        100          7         93

SQL> /

TABLESPACE                       MB_TOTAL    MB_USED    MB_FREE

—————————— ———- ———- ———-

TEMP_LOCAL_TBS                        100         19         81

SQL> /

TABLESPACE                       MB_TOTAL    MB_USED    MB_FREE

—————————— ———- ———- ———-

TEMP_LOCAL_TBS                        100         96          4

SQL> /

TABLESPACE                       MB_TOTAL    MB_USED    MB_FREE

—————————— ———- ———- ———-

TEMP_LOCAL_TBS                        100          0        100

با توجه به آنکه auto extend مربوط به tempfile به on تنظیم نشده است، پرس و جوی فوق، بعد از استفاده کامل از 100 مگابایت TEMP_LOCAL_TBS، با خطا متوقف می شود:

SQL>  select a.* from sys.source$  a,sys.source$,sys.source$,sys.source$,sys.source$ order by 1,2,3 ;

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP_LOCAL_TBS                       

برای آنکه رفتار پیش فرض اوراکل را در این زمینه تغییر دهیم تا در همه حالتها از Local Temp tablespace استفاده کند، می توانیم از دو پارامتر مخفی prefer_local_temp_ و force_local_temp_ کمک بگیریم:

SQL> alter system set “_prefer_local_temp”=true;

System altered

SQL> alter system set “_force_local_temp”=true;

System altered

SQL> select open_mode from v$database;

OPEN_MODE

——————–

READ WRITE

SQL>  select a.* from sys.source$  a,sys.source$,sys.source$,sys.source$,sys.source$ order by 1,2,3;

Executing…

SQL> @Temp_Usage

TABLESPACE                                 MB_TOTAL    MB_USED    MB_FREE

———————-                      ———- ———- ———-

TEMP_LOCAL_TBS                       100         68         32

TEMP                                           5629          0       5629

SQL> /                

TABLESPACE                                    MB_TOTAL    MB_USED    MB_FREE

———————-                        ———- ———- ———-

TEMP_LOCAL_TBS                        100         89         11

TEMP                                            5629          0       5629