修改系统的默认临时表空间
查询系统使用的默认临时表空间
SQL> desc database_properties
Name Null? Type
----------------------------------------- -------- ----------------------------
PROPERTY_NAME NOT NULL VARCHAR2(30)
PROPERTY_VALUE VARCHAR2(4000)
DESCRIPTION
SQL> col property_name format a30
SQL> col property_value format a40
SQL>
SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
创建一个临时表空间
SQL> CREATE TEMPORARY TABLESPACE myTemp TEMPFILE '/u01/oradata/houzhh/mytemp01.dbf' size 100M AUTOEXTEND ON NEXT 50M MAXSIZE 500M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
Tablespace created.
SQL> select * from v$tablespace;
TS# NAME INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
0 SYSTEM YES NO YES
1 SYSAUX YES NO YES
2 UNDOTBS1 YES NO YES
4 USERS YES NO YES
3 TEMP NO NO YES
6 EXAMPLE YES NO YES
7 MYTEMP NO NO YES
7 rows selected.
SQL>
修改默认临时表空间
SQL> alter database default temporary tablespace mytemp;
Database altered.
SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
DEFAULT_TEMP_TABLESPACE MYTEMP
恢复以前的默认临时表空间
SQL> alter database default temporary tablespace temp;
Database altered.
SQL> select property_name,property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ ----------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP