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