Creating a Default Temporary Tablespace (75)

最新推荐文章于 2025-12-18 10:59:28 发布

转载最新推荐文章于 2025-12-18 10:59:28 发布 · 149 阅读

文章标签：

本文介绍在Oracle数据库中，默认临时表空间的创建与管理方法。如果不指定用户的临时表空间，则默认使用SYSTEM表空间；若SYSTEM为本地管理，则不可作为临时表空间。推荐使用CREATEDATABASE语句的DEFAULTTEMPORARYTABLESPACE子句来设置。可通过ALTERDATABASE语句更改默认临时表空间。

The DEFAULT TEMPORARY TABLESPACE clause of the CREATE DATABASE statement
creates a default temporary tablespace for the database. Oracle Database assigns this
tablespace as the temporary tablespace for users who are not explicitly assigned a
temporary tablespace.

You can explicitly assign a temporary tablespace or tablespace group to a user in the
CREATE USER statement. However, if you do not do so, and if no default temporary
tablespace has been specified for the database, then by default these users are assigned
the SYSTEM tablespace as their temporary tablespace. It is not good practice to store
temporary data in the SYSTEM tablespace, and it is cumbersome to assign every user a
temporary tablespace individually. Therefore, Oracle recommends that you use the
DEFAULT TEMPORARY TABLESPACE clause of CREATE DATABASE.

Note: When you specify a locally managed SYSTEM tablespace,
the SYSTEM tablespace cannot be used as a temporary tablespace.

You can add or change the default temporary tablespace after database creation. You
do this by creating a new temporary tablespace or tablespace group with a CREATE
TEMPORARY TABLESPACE statement, and then assign it as the temporary tablespace
using the ALTER DATABASE DEFAULT TEMPORARY TABLESPACE statement. Users
will automatically be switched (or assigned) to the new default temporary tablespace.
The following statement assigns a new default temporary tablespace:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;

The new default temporary tablespace must already exist. When using a locally
managed SYSTEM tablespace, the new default temporary tablespace must also be
locally managed.

You cannot drop or take offline a default temporary tablespace, but you can assign a
new default temporary tablespace and then drop or take offline the former one. You
cannot change a default temporary tablespace to a permanent tablespace.

Users can obtain the name of the current default temporary tablespace by querying the
PROPERTY_NAME and PROPERTY_VALUE columns of the DATABASE_PROPERTIES
view. These columns contain the values "DEFAULT_TEMP_TABLESPACE" and the
default temporary tablespace name, respectively.

创建默认临时表空家
1. 如果不为用户显示的指定默认临时表空间则缺省为system表空间
2. 如果system表空间为本地管理 , system表空间不能当作临时表空间使用
3. 使用ALTER DATABASE DEFAULT TEMPORARY TABLESPACE 语句 ,可改变默认的临时表空间
4. 不能删除 , 脱机默认临时表空间 , 不能改变为永久性表空间
5. 从DATABASE_PROPERTIES视图中的PROPERTY_NAME , PROPERTY_VALUE字段可以查询到当前默认
的临时表空间

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-995636/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10599713/viewspace-995636/