oracle中temp

-----------------------------------------------------------------------------------------------------------------------------------------------
如何创建一个用户
SQL> create user u1 identified by 123456; //创建一个用户（u1）密码为（123456）
User created.
SQL>
-----------------------------------------------------------------------------------------------------------------------------------------------
在/u01/app/oracle/oradata/orcl/temp02.dbf目录下创建一个100M的临时表空间
SQL> create temporary tablespace temp1 tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size  100m;
Tablespace created.
SQL>
-------------------------------------------------------------------------------------------------------------------------------------------------
创建大表临时表空间 temp2
SQL> create bigfile temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/orcl/temp03.dbf' size 100m;
Tablespace created.
SQL>
-------------------------------------------------------------------------------------------------------------------------------------------------
SQL> desc dba_temp_files; //用了查看临时表空间有哪些字段 然查看临时表空间的各种情况
SQL> desc v$tempfile;
-------------------------------------------------------------------------------------------------------------------------------------------------
查询临时表空间有哪些数据文件
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/temp01.dbf
/u01/app/oracle/oradata/orcl/temp02.dbf
/u01/app/oracle/oradata/orcl/temp03.dbf
SQL>
------------------------------------------------------------------------------------------------------------------------------------------------
查看创建了哪些临时表空间
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMP
TEMP1
TEMP2
SQL>
-------------------------------------------------------------------------------------------------------------------------------------------------
创建一个名为 tempzu的临时表空间组
SQL> create temporary tablespace temp3 tempfile '/u01/app/oracle/oradata/orcl/temp04.dbf' size 100m tablespace group tempzu;
Tablespace created.
SQL>
-------------------------------------------------------------------------------------------------------------------------------------------------
将名为temp2的临时表空间加入到名为tempzu的组中
SQL> alter tablespace temp2 tablespace group tempzu;
Tablespace altered.
SQL>
-------------------------------------------------------------------------------------------------------------------------------------------------
临时表空间组的视图 dba_tablespace_groups
SQL> desc dba_tablespace_groups;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 GROUP_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                           NOT NULL VARCHAR2(30)
SQL>
------------------------------------------------------------------------------------------------------------------------------------------------
查看临时表空间组和组中的成员
SQL> select * from dba_tablespace_groups;
GROUP_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TEMPZU                         TEMP2
TEMPZU                         TEMP3
SQL>
------------------------------------------------------------------------------------------------------------------------------------------------
指定默认的临时表空间
SQL> alter database default temporary tablespace temp2;
Database altered.
SQL>
------------------------------------------------------------------------------------------------------------------------------------------------
删除临时表空间和与其对应的数据文件
SQL> drop tablespace temp1 including contents and datafiles;
Tablespace dropped.
SQL>
------------------------------------------------------------------------------------------------------------------------------------------------
SQL> alter user u1 temporary tablespace temp2;
User altered.
SQL>