OracleDBA之路之Tabespace(二)

修改系统的默认临时表空间

查询系统使用的默认临时表空间
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