修改临时表空间

来源：互联网发布：造价预算软件编辑：程序博客网时间：2024/05/10 18:33

查看当前默认的临时表空间

SQL> select PROPERTY_NAME,PROPERTY_VALUE  2  from database_properties  3  where property_name='DEFAULT_TEMP_TABLESPACE';PROPERTY_NAME                  PROPERTY_VALUE                                   ------------------------------ ------------------------------                   DEFAULT_TEMP_TABLESPACE        TEMP

创建新的临时表空间mytemp

SQL> create temporary tablespace mytemp  2  tempfile '/u01/app/oracle/oradata/aircy/mytemp01.dbf' size 100m;Tablespace created.

给mytemp临时表空间增加数据文件

SQL> alter tablespace mytemp  2  add tempfile  '/u01/app/oracle/oradata/aircy/mytemp02.dbf' size 50m;Tablespace altered.

修改默认临时表空间

SQL> alter database default temporary tablespace mytemp;Database altered.

验证一下是否生效

SQL> select property_name,property_value  2  from database_properties  3  where property_name='DEFAULT_TEMP_TABLESPACE';PROPERTY_NAME                  PROPERTY_VALUE                                   ------------------------------ ------------------------------                   DEFAULT_TEMP_TABLESPACE        MYTEMP

查看当前所有的临时表空间及表空间文件

SQL> select file_name,tablespace_name from dba_temp_files;FILE_NAME                                          TABLESPACE_NAME              -------------------------------------------------- --------------------         /u01/app/oracle/oradata/aircy/temp01.dbf           TEMP                         /u01/app/oracle/oradata/aircy/mytemp01.dbf         MYTEMP                       /u01/app/oracle/oradata/aircy/mytemp02.dbf         MYTEMP

删除临时表空间temp

SQL> drop tablespace temp including contents and datafiles;Tablespace dropped.

验证一下是否生效

SQL> select file_name,tablespace_name from dba_temp_files;FILE_NAME                                          TABLESPACE_NAME              -------------------------------------------------- --------------------         /u01/app/oracle/oradata/aircy/mytemp01.dbf         MYTEMP                       /u01/app/oracle/oradata/aircy/mytemp02.dbf         MYTEMP