oracle删除临时表空间_删除临时表空间组

把在临时表空间组中所有的临时表空间剔除后，临时表空间组会自动删除：

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_GROUP01 TEMP

TEMP_GROUP01 TEMP02

SQL> alter tablespace temp02 tablespace group '';

Tablespace altered.

SQL> alte tablespace temp tablespace group '';

SP2-0734: unknown command beginning "alte table..." - rest of line ignored.

SQL> alter tablespace temp tablespace group '';

Tablespace altered.

SQL> select * from dba_tablespace_groups;

no rows selected

SQL>

5)把临时表空间组设置为数据库默认的临时 表空间 组；

SQL> SELECT * FROM DBA_TABLESPACE_GROUPS;

GROUP_NAME TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_GROUP01 TEMP

TEMP_GROUP01 TEMP02

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_GROUP01;

Database altered.

SQL>

SQL> select * from database_properties where rownum<5;

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION

------------------------------ -------------------------------------------------- ----------------------------------------------------------------------

DICT.BASE 2 dictionary base tables version #

DEFAULT_TEMP_TABLESPACE TEMP_GROUP01 Name of default temporary tablespace

DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace

DEFAULT_EDITION ORA$BASE Name of the database default edition

SQL>

6)把一个临时表空间移动到 其他临时表空间中；

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_GROUP01 TEMP

TEMP_GROUP01 TEMP02

SQL> alter tablespace temp02 tablespace group temp_group02;

Tablespace altered.

SQL> select * from dba_tablespace_groups;

GROUP_NAME TABLESPACE_NAME

------------------------------ ------------------------------

TEMP_GROUP01 TEMP

TEMP_GROUP02 TEMP02

SQL>