管理表空间（四）--临时表空间

 

当执行排序操作时，服务器进程会将临时数据放到PGA工作区。当PGA工作区不足以存放临时数据时，服务器进程会建立临时段，并将这

些临时数据存放到临时段里。

可以建立多个临时表空间，但默认的临时表空间也只能有一个，默认临时表空间不能offline和drop。如果未指定默认的临时表空间

oracle将会使用system作为临时表空间，不建议使用system作为临时表空间。

1、指定默认的临时表空间

09:00:53 SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/lx02/temp01.dbf' size 100m reuse;

Tablespace altered.

09:01:14 SQL> select file_id,file_name,tablespace_name from dba_temp_files;

FILE_ID FILE_NAME                                          TABLESPACE_NAME

---------- -------------------------------------------------- ------------------------------

1 /u01/app/oracle/oradata/lx02/temp01.dbf            TEMP

09:01:17 SQL> select file#,name ,bytes/1024/1024 from v$tempfile;

FILE# NAME                                               BYTES/1024/1024

---------- -------------------------------------------------- ---------------

1 /u01/app/oracle/oradata/lx02/temp01.dbf                        100

09:01:22 SQL>

2、建立临时表空间

09:04:18 SQL> create temporary tablespace tmp01

09:05:42   2   tempfile '/u01/app/oracle/oradata/lx02/tmp01.dbf' size 10m

09:06:03   3     extent management local uniform size 128k;

Tablespace created.

09:06:17 SQL> select file#,name from v$tempfile;

FILE# NAME

---------- --------------------------------------------------

1 /u01/app/oracle/oradata/lx02/temp01.dbf

2 /u01/app/oracle/oradata/lx02/tmp01.dbf

09:06:32 SQL>

3、查看默认的临时表空间

09:06:52 SQL> col PROPERTY_VALUE for a30

09:06:59 SQL> col description for a50

09:07:04 SQL> r

1* 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

DBTIMEZONE                     -04:00                         DB time zone

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               ZHS16GBK                       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

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION

------------------------------ ------------------------------ --------------------------------------------------

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              10.2.0.1.0                     RDBMS version for NLS parameters

GLOBAL_DB_NAME                 LX02                           Global database name

EXPORT_VIEWS_VERSION           8                              Export views revision #

27 rows selected.

4、用户指定临时表空间

20:55:00 SQL> alter user scott  temporary tablespace tmp01;

User altered.

5、切换默认的临时表空间

09:07:05 SQL> alter database default temporary tablespace tmp01;

Database altered.

09:07:34 SQL> select * from database_properties

09:07:39   2  ;

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION

------------------------------ ------------------------------ --------------------------------------------------

DICT.BASE                      2                              dictionary base tables version #

DEFAULT_TEMP_TABLESPACE        TMP01                          Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

DBTIMEZONE                     -04:00                         DB time zone

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               ZHS16GBK                       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

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION

------------------------------ ------------------------------ --------------------------------------------------

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              10.2.0.1.0                     RDBMS version for NLS parameters

GLOBAL_DB_NAME                 LX02                           Global database name

EXPORT_VIEWS_VERSION           8                              Export views revision #

27 rows selected.

6、临时表空间租

（1）建立临时表空间组

09:07:41 SQL> alter tablespace temp tablespace group tmpgrp;

Tablespace altered.

09:09:33 SQL> alter tablespace tmp01 tablespace group tmpgrp;

Tablespace altered.

09:09:38 SQL> select * from dba_tablespace_groups;

GROUP_NAME                     TABLESPACE_NAME

------------------------------ ------------------------------

TMPGRP                         TEMP

TMPGRP                         TMP01

（2）将临时表空间组射程默认临时表空间，可以实现负载均衡

09:09:52 SQL> alter database default temporary tablespace tmpgrp;

Database altered.

09:10:10 SQL> select * from database_properties;

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION

------------------------------ ------------------------------ --------------------------------------------------

DICT.BASE                      2                              dictionary base tables version #

DEFAULT_TEMP_TABLESPACE        TMPGRP                         Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE   USERS                          Name of default permanent tablespace

DBTIMEZONE                     -04:00                         DB time zone

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               ZHS16GBK                       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

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION

------------------------------ ------------------------------ --------------------------------------------------

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              10.2.0.1.0                     RDBMS version for NLS parameters

GLOBAL_DB_NAME                 LX02                           Global database name

EXPORT_VIEWS_VERSION           8                              Export views revision #

27 rows selected.

09:10:17 SQL>

（3）查看临时表空间信息

21:03:08 SQL> col name for a50

21:03:10 SQL> r

1* select file#,name,bytes from v$tempfile

FILE# NAME                                                    BYTES

---------- -------------------------------------------------- ----------

1 /u01/app/oracle/oradata/prod/temp01.dbf              20971520

2 /u01/app/oracle/oradata/prod/tmp01.dbf               10485760

3 /u01/app/oracle/oradata/prod/tmp02.dbf               10485760

21:03:11 SQL> desc dba_temp_files;

Name                                                              Null?    Type

----------------------------------------------------------------- -------- -----------------------------------------

FILE_NAME                                                                  VARCHAR2(513)

FILE_ID                                                                    NUMBER

TABLESPACE_NAME                                                   NOT NULL VARCHAR2(30)

BYTES                                                                      NUMBER

BLOCKS                                                                     NUMBER

STATUS                                                                     CHAR(9)

RELATIVE_FNO                                                               NUMBER

AUTOEXTENSIBLE                                                             VARCHAR2(3)

MAXBYTES                                                                   NUMBER

MAXBLOCKS                                                                  NUMBER

INCREMENT_BY                                                               NUMBER

USER_BYTES                                                                 NUMBER

USER_BLOCKS                                                                NUMBER