任务描述：创建默认临时表空间组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