oracle表空间组

创建临时表空间mytemp1

SQL> create temporary tablespace mytemp1 tempfile '/u01/app/oraData/temp/mytemp1.dmp' size 10M;

Tablespace created.

把mytemp1表空间放入到表空间组hbk_group

SQL> alter tablespace mytemp1 tablespace group hbk_group;

Tablespace altered.

也可以一次性创建表空间并加入到表空间组

SQL> create temporary tablespace mytemp2 tempfile '/u01/app/oraData/temp/mytemp2.dbf' size 10M tablespace group hbk_group;

Tablespace created.

查看表空间组的信息

SQL> select * from dba_tablespace_groups;

GROUP_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
HBK_GROUP		       MYTEMP1
HBK_GROUP		       MYTEMP2

把默认临时表空间改成表空间组hbk_group
查看当前的默认表空间

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

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
TEMP

可以知道当前的默认临时表空间为TEMP
下面进行修改

SQL> alter database default temporary tablespace hbk_group;

Database altered.

再次查看，已经修改成了表空间组hbk_group

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

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
--------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
HBK_GROUP

把临时表空间mytemp2从表空间组hbk_group中删除，设置空即可

SQL> alter tablespace mytemp2 tablespace group '';

Tablespace altered.

查看表空间组的信息，只剩下mytemp1

SQL> select * from dba_tablespace_groups;

GROUP_NAME		       TABLESPACE_NAME
------------------------------ ------------------------------
HBK_GROUP		       MYTEMP1

查看数据库有哪些临时表空间，使用视图DBA_TEMP_FILES

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 					    VARCHAR2(7)
 RELATIVE_FNO					    NUMBER
 AUTOEXTENSIBLE 				    VARCHAR2(3)
 MAXBYTES					    NUMBER
 MAXBLOCKS					    NUMBER
 INCREMENT_BY					    NUMBER
 USER_BYTES					    NUMBER
 USER_BLOCKS					    NUMBER

SQL> col file_name format a60
SQL> col tablespace_name format a10
SQL> select tablespace_name,file_name,status from dba_temp_files order by tablespace_name;

TABLESPACE FILE_NAME							STATUS
---------- ------------------------------------------------------------ -------
HBK_TEMP   /u01/app/oraData/orcl/hbk_temp.dbf				ONLINE
MYTEMP1    /u01/app/oraData/temp/mytemp1.dmp				ONLINE
MYTEMP2    /u01/app/oraData/temp/mytemp2.dbf				ONLINE
TEMP	   /u01/app/oraData/orcl/temp01.dbf				ONLINE