Oracle：temporary tablespace

Oracle：temporary tablespace

temporary tablespace用途
用于排序，可以建立多个临时表空间，但默认的临时表空间只能有一个，default temporary tablespace不能offline和drop。如果未指定默认的临时表空间，oracle将会使用system作为临时表空间（非本地管理），只有temp表空间是nologing。

temporary tablespace原地重建
alter tablespace temp add tempfile ‘/opt/oracle/oradata/orcl/temp01.dbf’ size 100m reuse;

临时表空间什么时候需要原地重建
当查看dba_temp_files;没有检索出内容时，需要原地重建

idle>select file_id,file_name,tablespace_name,bytes/1024/1024 from dba_temp_files;FILE_ID FILE_NAME                                                    TABLESPACE_NAME                BYTES/1024/1024
---------- ------------------------------------------------------------ ------------------------------ ---------------1 /opt/oracle/oradata/orcl/temp01.dbf                          TEMP                                       1002 /opt/oracle/oradata/orcl/temp02.dbf                          TEMP2                                       10

查看临时数据文件size
dba_temp_files
v$tempfile

查看默认的临时表空间

idle>col property_value for a30;
idle>col description for a40;
idle>select * from database_properties;PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------
DICT.BASE                      2                              dictionary base tables version #
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace
DEFAULT_EDITION                ORA$BASE                       Name of the database default edition
Flashback Timestamp TimeZone   GMT                            Flashback timestamp created in GMT
TDE_MASTER_KEY_ID
DST_UPGRADE_STATE              NONE                           State of Day Light Saving Time Upgrade
DST_PRIMARY_TT_VERSION         11                             Version of primary timezone data file
DST_SECONDARY_TT_VERSION       0                              Version of secondary timezone data file
DEFAULT_TBS_TYPE               SMALLFILE                      Default tablespace type
NLS_LANGUAGE                   AMERICAN                       Language
NLS_TERRITORY                  AMERICA                        Territory
NLS_CURRENCY                   $                              Local currency
NLS_ISO_CURRENCY               AMERICA                        ISO currency
NLS_NUMERIC_CHARACTERS         .,                             Numeric characters
NLS_CHARACTERSET               WE8MSWIN1252                   Character set
NLS_CALENDAR                   GREGORIAN                      Calendar system
NLS_DATE_FORMAT                DD-MON-RR                      Date format
NLS_DATE_LANGUAGE              AMERICAN                       Date language
NLS_SORT                       BINARY                         Linguistic definition
NLS_TIME_FORMAT                HH.MI.SSXFF AM                 Time format
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM       Time stamp format
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR             Time with timezone format
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR   Timestamp with timezone format
NLS_DUAL_CURRENCY              $                              Dual currency symbol
NLS_COMP                       BINARY                         NLS comparison
NLS_LENGTH_SEMANTICS           BYTE                           NLS length semantics
NLS_NCHAR_CONV_EXCP            FALSE                          NLS conversion exception
NLS_NCHAR_CHARACTERSET         AL16UTF16                      NCHAR Character set
NLS_RDBMS_VERSION              11.2.0.1.0                     RDBMS version for NLS parameters
GLOBAL_DB_NAME                 ORCL                           Global database name
EXPORT_VIEWS_VERSION           8                              Export views revision #
WORKLOAD_CAPTURE_MODE                                         CAPTURE implies workload capture is in progressWORKLOAD_REPLAY_MODE                                          PREPARE implies external replay clientscan connect; REPLAY implies workload replay is in progressNO_USERID_VERIFIER_SALT        8BF56230FC49D6ECFE53EB96A5922DD5DBTIMEZONE                     00:00                          DB time zone36 rows selected.

切换默认临时表空间
alter database default temporary tablespace temp3;

指定用户使用临时表空间
alter user scott temporary tablespace temp2;

创建临时表空间 ,新增/删除tempfile

idle>create temporary tablespace temp3 tempfile '/opt/oracle/oradata/orcl/temp03.dbf' size 10m;Tablespace created.idle>alter tablespace temp2 add tempfile '/opt/oracle/oradata/orcl/temp04.dbf' size 5m;Tablespace altered.idle>select file_id,file_name,tablespace_name,bytes/1024/1024 from dba_temp_files;FILE_ID FILE_NAME                                TABLESPACE_NAME      BYTES/1024/1024
---------- ---------------------------------------- -------------------- ---------------1 /opt/oracle/oradata/orcl/temp01.dbf      TEMP                             1002 /opt/oracle/oradata/orcl/temp02.dbf      TEMP2                             103 /opt/oracle/oradata/orcl/temp03.dbf      TEMP3                             104 /opt/oracle/oradata/orcl/temp04.dbf      TEMP2                              5idle>alter tablespace temp2 drop tempfile '/opt/oracle/oradata/orcl/temp04.dbf';Tablespace altered.

创建临时表空间组

idle>alter tablespace temp tablespace group group1;Tablespace altered.idle>alter tablespace temp2 tablespace group group1;Tablespace altered.idle>select * from dba_tablespace_groups;GROUP_NAME                     TABLESPACE_NAME
------------------------------ --------------------
GROUP1                         TEMP
GROUP1                         TEMP2idle>

将临时表空间组设成默认临时表空间，实现负载均衡
alter database default temporary tablespace group1;

移除表空间组时，该组不能使缺省的临时表空间

idle>alter database default temporary tablespace group1;Database altered.idle>alter database default temporary tablespace temp;Database altered.idle>alter tablespace temp tablespace group '';Tablespace altered.idle>alter tablespace temp2 tablespace group '';Tablespace altered.idle>select * from dba_tablespace_groups;no rows selectedidle>drop tablespace temp2 including contents and datafiles;Tablespace dropped.

3-1