【儲存管理】建立臨時表空間組、建立臨時表空間組及使用

任務描述：建立預設臨時表空間組TMP_GRP，組成員temp1,temp2，資料檔案大小10M

實驗開始：

--檢視當前資料庫預設臨時表空間是什麼？

SQL> desc database_properties

 Name                                      Null?    Type

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

 PROPERTY_NAME                             NOT NULL VARCHAR2(30)

 PROPERTY_VALUE                                     VARCHAR2(4000)

 DESCRIPTION                                        VARCHAR2(4000)

 

SQL> select property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_VALUE

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

TEMP

 

--檢視當前資料庫有哪些型別的表空間

SQL>  desc dba_tablespaces

 Name                                      Null?    Type

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

 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)

 BLOCK_SIZE                                NOT NULL NUMBER

 INITIAL_EXTENT                                     NUMBER

 NEXT_EXTENT                                        NUMBER

 MIN_EXTENTS                               NOT NULL NUMBER

 MAX_EXTENTS                                        NUMBER

 MAX_SIZE                                           NUMBER

 PCT_INCREASE                                       NUMBER

 MIN_EXTLEN                                         NUMBER

 STATUS                                             VARCHAR2(9)

 CONTENTS                                           VARCHAR2(9)

 LOGGING                                            VARCHAR2(9)

 FORCE_LOGGING                                      VARCHAR2(3)

 EXTENT_MANAGEMENT                                  VARCHAR2(10)

 ALLOCATION_TYPE                                    VARCHAR2(9)

 PLUGGED_IN                                         VARCHAR2(3)

 SEGMENT_SPACE_MANAGEMENT                           VARCHAR2(6)

 DEF_TAB_COMPRESSION                                VARCHAR2(8)

 RETENTION                                          VARCHAR2(11)

 BIGFILE                                            VARCHAR2(3)

 PREDICATE_EVALUATION                               VARCHAR2(7)

 ENCRYPTED                                          VARCHAR2(3)

 COMPRESS_FOR                                       VARCHAR2(12)

 

SQL> select tablespace_name,contents from dba_tablespaces;

 

TABLESPACE_NAME                CONTENTS

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

SYSTEM                         PERMANENT

SYSAUX                         PERMANENT

UNDOTBS1                       UNDO

TEMP                           TEMPORARY

USERS                          PERMANENT

EXAMPLE                        PERMANENT

 

6 rows selected.  

 

——建立臨時表空間組TMP_GRP，檢視臨時表空間 （dba_temp_files檢視）或（v$tempfile檢視）

SQL> desc v$tempfile

 Name                                      Null?    Type

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

 FILE#                                              NUMBER

 CREATION_CHANGE#                                   NUMBER

 CREATION_TIME                                      DATE

 TS#                                                NUMBER

 RFILE#                                             NUMBER

 STATUS                                             VARCHAR2(7)

 ENABLED                                            VARCHAR2(10)

 BYTES                                              NUMBER

 BLOCKS                                             NUMBER

 CREATE_BYTES                                       NUMBER

 BLOCK_SIZE                                         NUMBER

 NAME  

                                            VARCHAR2(513)

——檢視預設臨時表空間：

SQL> select FILE#,STATUS,BYTES/1024/1024 m,NAME from v$tempfile;

 

   FILE# STATUS        M  NAME

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

   1    ONLINE        29  /u01/app/oracle/oradata/ORA11GR2/temp01.dbf

 

——建立臨時表空間組temp_grp,包含臨時表空間temp1、temp2：

SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/ORA11GR2/temp_01.dbf' size 10m tablespace group tem_grp;

 

Tablespace created.

 

SQL> create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ORA11GR2/temp_02.dbf' size 10m tablespace group tem_grp;

 

Tablespace created.

 

--檢視錶空間組情況

SQL> select tablespace_name,contents from dba_tablespaces;

 

TABLESPACE_NAME                CONTENTS

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

SYSTEM                         PERMANENT

SYSAUX                         PERMANENT

UNDOTBS1                       UNDO

TEMP                           TEMPORARY

USERS                          PERMANENT

EXAMPLE                        PERMANENT

TEMP1                          TEMPORARY

TEMP2                          TEMPORARY

 

8 rows selected.

 

SQL> select * from dba_tablespace_groups;

 

GROUP_NAME                     TABLESPACE_NAME

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

TEM_GRP                        TEMP1

TEM_GRP                        TEMP2

 

--修改臨時表空間組TMP_GRP為資料庫預設臨時表空間

SQL> alter database default temporary tablespace tem_grp;

 

Database altered.

 

——驗證檢視：

SQL> select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE';

 

PROPERTY_VALUE

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

TEM_GRP