temporary tablespace

--1、查看临时表空间
SQL> Select tablespace_name,Contents From dba_tablespaces Where Contents='TEMPORARY';
TABLESPACE_NAME                CONTENTS
------------------------------ ---------
TEMP                           TEMPORARY
ZLTOOLSTMP                     TEMPORARY


SQL> Select ts#,Name From v$tablespace Where included_in_database_backup='NO';
       TS# NAME
---------- ------------------------------
         3 TEMP
         8 ZLTOOLSTMP


--2、查看临时表空间和数据文件
SQL> Select tablespace_name,file_name From dba_temp_files Order By tablespace_name ;
TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
ZLTOOLSTMP                     D:\APP\ADMINISTRATOR\ORADATA\ORCL\ZLTOOLSTMP.DBF


SQL> Select  ts#,Name From v$tempfile Order By ts#;
       TS# NAME
---------- --------------------------------------------------------------------------------
         3 D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
         8 D:\APP\ADMINISTRATOR\ORADATA\ORCL\ZLTOOLSTMP.DBF
--3、查看临时表空间组
SQL> Select * From dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------


SQL> 


--4、创建临时表空间
SQL> Create Temporary Tablespace temp2 Tempfile 'D:\app\Administrator\oradata\orcl\temp2a.dbf' Size 10m Autoextend On Next 10 Maxsize Unlimited;
Tablespace created
--5、为临时表空间添加数据文件
SQL> Alter Tablespace temp2 Add Tempfile 'D:\app\Administrator\oradata\orcl\temp2b.dbf' Size 10m Autoextend On Next 10 Maxsize Unlimited;

Tablespace altered

SQL> Alter Tablespace temp2 Add Tempfile 'D:\app\Administrator\oradata\orcl\temp2c.dbf' Size 10m Autoextend On Next 10 Maxsize Unlimited;
Tablespace altered

--6、为临时表空间删除一个数据文件
SQL> Alter Tablespace temp2 Drop Tempfile 'D:\app\Administrator\oradata\orcl\temp2b.dbf';
Tablespace altered
---7、连接查询临时表空间和数据文件
SQL> Select a.NAME,b.NAME From v$tablespace a,v$tempfile b Where a.TS#=b.TS# Order By 1,2;
NAME                           NAME
------------------------------ --------------------------------------------------------------------------------
TEMP                           D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP01.DBF
TEMP2                          D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP2A.DBF
TEMP2                          D:\APP\ADMINISTRATOR\ORADATA\ORCL\TEMP2C.DBF
ZLTOOLSTMP                     D:\APP\ADMINISTRATOR\ORADATA\ORCL\ZLTOOLSTMP.DBF


SQL> 
--8、创建一个临时表空间，并将其移入临时表空间组
SQL> Create Temporary Tablespace temp3 Tempfile 'D:\app\Administrator\oradata\orcl\temp3a.dbf' Size 10m Autoextend On Next 10 Maxsize Unlimited
  2  Tablespace Group tmp_grp;
Tablespace created
--9、将现有的一个临时表空间移入表空间组
SQL> Alter Tablespace temp2 Tablespace Group tmp_grp;
Tablespace altered
--10、查询临时表空间组
SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TMP_GRP                        TEMP2
TMP_GRP                        TEMP3


SQL> 
--11、将临时表空间移出临时表空间组
SQL> Alter Tablespace temp3 Tablespace Group '';
Tablespace altered


SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TMP_GRP                        TEMP2


SQL> 
--12、查询默认表空间
SQL> Select property_name,property_value From database_properties Where property_name In('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS


SQL> 
--13、将默认临时表空间从temp改为temp2
SQL> Alter Database Default Temporary Tablespace temp2;
Database altered


SQL> Select property_name,property_value From database_properties Where property_name In('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP2
DEFAULT_PERMANENT_TABLESPACE   USERS


SQL> 
--14、将默认临时表空间从temp改为临时表空间组
SQL> Alter Database Default Temporary Tablespace tmp_grp;
Database altered


SQL> Select property_name,property_value From database_properties Where property_name In('DEFAULT_PERMANENT_TABLESPACE','DEFAULT_TEMP_TABLESPACE');
PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ --------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE        TMP_GRP
DEFAULT_PERMANENT_TABLESPACE   USERS


SQL> 
--15、删除临时表空间和对应的数据文件
SQL> Drop Tablespace temp3 Including Contents And Datafiles;
Tablespace dropped