`
tianshibaijia
  • 浏览: 1118791 次
文章分类
社区版块
存档分类
最新评论

Oracle Temp 临时表空间

 
阅读更多

一.Temporary Tablespacs说明

A temporary tablespace contains transient data that persists only for the duration of a session. No permanent schema objects can reside in a temporary tablespace. The database stores temporary tablespace data in temp files.

Temporary tablespaces can improve the concurrency of multiple sort operations that do not fit in memory. These tablespaces also improve the efficiency of space management operations during sorts.

When the SYSTEM tablespace is locally managed, a default temporary tablespace is included in the database by default during database creation.A locally managed SYSTEM tablespace cannot serve as default temporary storage.

--本地管理的system表空间,不能作为默认的临时表空间。

You cannot make a default temporary tablespace permanent.

You can specify a user-named default temporary tablespace when you create a database by using the DEFAULT TEMPORARY TABLESPACE extension to the CREATE DATABASE statement.If SYSTEM is dictionary managed, and if a default temporary tablespace is not defined at database creation, then SYSTEM is the default temporary storage.However, the database writes a warning in thealert logsaying that a default temporary tablespace is recommended.

--当SYSTEM表空间是数据字典管理时,并且没有定义默认的临时表空间,那么SYSTEM表空间会作为默认的temporary storage。

关于表空间的两种类型:locally managed和dictionary managed的区别,参考我的Blog:

Oracle自动段空间管理(ASSM:auto segment space management)

http://blog.csdn.net/xujinyang/article/details/6829924

A temporary tablespace contains schema objects only for the duration of a session.Locally managed temporary tablespaces have temporary files (temp files),which are special files designed to store data in hash, sort, and other operations.Temp files also store result set data when insufficient space exists in memory.

Temp files are similar to permanent data files, with the following exceptions:

(1)Permanent database objects such as tables are never stored in temp files.

(2)Temp files are always set to NOLOGGING mode,which means that they never have redo generated for them. Media recovery does not recognize temp files.

(3)You cannot make a temp file read-only.

(4)You cannot create a temp file with the ALTER DATABASE statement.

(5)When you create or resize temp files, they are not always guaranteed allocation of disk space for the file size specified.On file systems such as Linux and UNIX, temp files are created as sparse files. In this case, disk blocks are allocated not at file creation or resizing, but as the blocks are accessed for the first time.

Caution:

Sparse files enable fast temp file creation and resizing; however, the disk could run out of space later when the temp files are accessed.

(6)Temp file information is shown in the data dictionary view DBA_TEMP_FILES and the dynamic performance view V$TEMPFILE,but not in DBA_DATA_FILES or the V$DATAFILE view.

临时表空间主要用途是在数据库进行排序运算、管理索引、访问视图等操作时提供临时的运算空间,当运算完成之后系统会自动清理。当oracle里需要用到sort的时候,PGA中sort_area_size大小不够时,将会把数据放入临时表空间里进行排序,同时如果有异常情况的话,也会被放入临时表空间,正常来说,在完成Select语句、create index等一些使用TEMP表空间的排序操作后,Oracle是会自动释放掉临时段的。注意这里的释放,仅仅是将这些空间标记为空闲,并可重用,真正占用的磁盘空间并没有释放。所以Temp表空间可能会越来越大。

排序是很耗资源的,Temp表空间满了,关键是优化你的语句,尽量使排序减少才是上策.

二.Temp表空间的操作

You cannot use the ALTER TABLESPACE statement, with the TEMPORARY keyword, to change a locally managed permanent tablespace into a locally managed temporary tablespace. You must use the CREATE TEMPORARY TABLESPACE statement to create a locally managed temporary tablespace.

You can use ALTER TABLESPACE to add a tempfile, take a tempfile offline, or bring a tempfile online,as illustrated in the following examples:

ALTER TABLESPACE lmtemp

ADD TEMPFILE '/u02/oracle/data/lmtemp02.dbf' SIZE 18M REUSE;

SQL>ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;

SQL>ALTER TABLESPACE lmtemp TEMPFILE ONLINE;

Note:

You cannot take a temporary tablespace offline. Instead, you take its tempfile offline. The viewV$TEMPFILE displays online status for a tempfile.

--不可以将Temp表空间offline,但是可以将tempfile offline。V$TEMPFILE显示了tempfile的状态。

The ALTER DATABASE statement can be used to alter tempfiles.

The following statements take offline and bring online tempfiles. They behave identically to the last two ALTER TABLESPACE statements in the previous example.

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' OFFLINE;

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' ONLINE;

The following statement resizes a tempfile: -- resize表空间

SQL>ALTER DATABASETEMPFILE'/u02/oracle/data/lmtemp02.dbf' RESIZE 18M;

The following statement drops a tempfile and deletes its operating system file:

SQL>ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP

INCLUDING DATAFILES;

-- drop tempfile和它的物理文件。

The tablespace to which this tempfile belonged remains.A message is written to the alert log for the tempfile that was deleted. If an operating system error prevents the deletion of the file, the statement still succeeds, but a message describing the error is written to the alert log.

--当我们把temp表空间的数据文件文件删除之后,表空间的信息还会存在,但是在alert log里会有错误信息。

It is also possible to use the ALTER DATABASE statement to enable or disable the automatic extension of an existing tempfile, and to rename a tempfile. SeeOracle Database SQL Language Referencefor the required syntax.

Note:

To rename a tempfile, you take the tempfile offline, use operating system commands to rename or relocate the tempfile, and then use the ALTER DATABASE RENAME FILE command to update the database controlfiles.

tempfile数据文件重命名的步骤:

(1)将tempfile offline

(2)在操作系统上重命名tempfile

(3)使用alter database rename file更新控制文件。

三.临时表空间满时的处理方法

3.1添加数据文件

如果Temporary tablespace还不大,那么我们可以增加一些数据文件。SQL语句如下:

SQL>ALTERTABLESPACETEMPADDTEMPFILE'D:/ORADATA/NEWCCS/TEMP02.DBF'SIZE100MAUTOEXTENDOFF;

一般来说,Temp tablespace和Undo Tablespace是不建议设置为自增长,设置自增长可能会把磁盘给撑满。

3.2修改数据文件大小

可以将原来的数据文件改大一点,如:

SQL>ALTERDATABASETEMPFILE'D:/ ORADATA/NEWCCS/TEMP02.DBF'

RESIZE100M;

四. Temp表空间过大的处理方法

Temp表空间过大,会占用很多的磁盘空间,这时候,我们可以用一下2中方法来缩小temp表空间的大小。

4.1替换Temp表空间

4.1.1查看目前Temp表空间的信息

SQL> select name from v$tempfile;

NAME

———————————————————————

D:/ORACLE/ORADATA/TEST/TEMP01.DBF

SQL> select username,temporary_tablespace from dba_users;

USERNAMETEMPORARY_TABLESPACE

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

MGMT_VIEWTEMP

SYSTEMP

SYSTEMTEMP

DBSNMPTEMP

SYSMANTEMP

关于用户这块是要特别注意的,如果我们将默认的Temp表空间指向其他的名称,那么这些用户的信息就会失效,所以,我们特换时,要么创建一个临时的Temp表空间中转一下,这样切换之后,我们的temp表空间名称不变,要么改变名称,同时更新相关用户的default temp表空间。

这里用中转的方法来测试。

4.1.2创建中转临时表空间

UNIFORM specifiesthat the tablespace is managed with uniform extents of SIZE bytes.The default SIZE is 1 megabyte.All extents of temporary tablespaces are of uniform size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM in order to specify SIZE.You cannot specify UNIFORM for an undo tablespace.

-- Temp表空间必须是uniform的,undo必须是autoallocate的。默认情况下uniform是1M。

If you do not specify AUTOALLOCATE or UNIFORM,then the default is UNIFORM for temporary tablespacesandAUTOALLOCATE for all other types of tablespaces.

更多内容参考:

Oracle表空间创建参数说明

http://blog.csdn.net/xujinyang/article/details/6829309

创建SQL如下:

SQL>CREATETEMPORARYTABLESPACETEMP2TEMPFILE

'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/TEMP03.DBF'SIZE10MAUTOEXTENDOFF;

EXTENTMANAGEMENTLOCALUNIFORMSIZE1M;

上面是默认情况,等于一下SQL:

SQL>CREATETEMPORARYTABLESPACETEMP2TEMPFILE

'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/TEMP03.DBF'SIZE10MAUTOEXTENDOFF

EXTENTMANAGEMENTLOCALUNIFORMSIZE1M;

4.1.3修改Temp2为默认临时表空间

SQL>alter database default temporary tablespace temp2;

4.1.4.删除原来临时表空间

SQL>drop tablespace temp including contents and datafiles;

4.1.5.重新创建临时表空间

SQL>CREATETEMPORARYTABLESPACETEMPTEMPFILE

'D:/APP/ADMINISTRATOR/ORADATA/NEWCCS/TEMP03.DBF'SIZE10MAUTOEXTENDOFF;

EXTENTMANAGEMENTLOCALUNIFORMSIZE1M;

4.1.6.重置缺省临时表空间为新建的temp表空间

SQL>alter database default temporary tablespace temp;

4.1.7.删除中转用临时表空间

SQL>drop tablespace temp2 including contents and datafiles;

4.1.8如果有必要,重新指定用户表空间为重建的临时表空间

SQL>alter user dave temporary tablespace temp;

4.2Shrinking aLocally Managed Temporary Tablespace

Large sort operations performed by the database may result in a temporary tablespace growing and occupying a considerable amount of disk space.After the sort operation completes, the extra space is not released; it is just marked as free and available for reuse.

--当排序操作完成,占用的空间并没有释放,仅仅是将它标记为空闲,并可重用。

Therefore, a single large sort operation might result in a large amount of allocated temporary space that remains unused after the sort operation is complete. For this reason,the database enables you to shrink locally managed temporary tablespaces and release unused space.

--可以使用shrink来释放没有使用的空间。

(1)You use the SHRINK SPACE clause of the ALTER TABLESPACE statement to shrink a temporary tablespace, or(2)the SHRINK TEMPFILE clause of the ALTER TABLESPACE statement to shrink a specific tempfile of a temporary tablespace.Shrinking frees as much space as possible while maintaining the other attributes of the tablespace or tempfile.The optional KEEP clause defines a minimum size for the tablespace or tempfile.

Shrinking is an online operation,which means that user sessions can continue to allocate sort extents if needed, and already-running queries are not affected.

-- shrink是一个online的操作,不影响其他的查询。

示例一:

The following example shrinks the locally managed temporary tablespace lmtmp1 to a size of 20M.

SQL>ALTER TABLESPACE lmtemp1 SHRINK SPACE KEEP 20M;

示例二:

The following example shrinks the tempfile lmtemp02.dbf of the locally managed temporary tablespace lmtmp2. Because the KEEP clause is omitted, the database attempts to shrink the tempfile to the minimum possible size.

SQL>ALTER TABLESPACE lmtemp2 SHRINK TEMPFILE '/u02/oracle/data/lmtemp02.dbf';

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

分享到:
评论

相关推荐

    一个释放临时表空间实例

    重启数据库可以释放临时表空间,如果不能重启实例,而一直保持问题sql语句的执行,temp表空间会一直增长。直到耗尽硬盘空间。 网上有人猜测在磁盘空间的分配上,oracle使用的是贪心算法,如果上次磁盘空间消耗达到...

    如何解决Oracle8i数据库临时表空间满的问题.pdf

    如何解决Oracle8i数据库临时表空间满的问题.pdf

    Oracle特性临时表

    在oracle 中,临时表与普通数据表一样只需要一次...相对于其它类型的表,临时表只有在用户实际向表中添加数据时,才会为其为配存储空间,并且分配的空间来自临时表空间(temp),这就避免了与永久的数据争用存储空间。

    创建Oracle表空间

    /*第1步:创建临时表空间 */ create temporary tablespace user_temp tempfile 'D:\oracle\oradata\Oracle9i\user_temp.dbf' size 50m autoextend on next 50m maxsize 20480m extent management local; ...

    4.oracle_管理表空间

    oracle_管理表空间 一个数据库有: SYSTEM、SYSAUX、TEMP三个默认表空间 一个或多个临时表空间 一个撤销表空间 几个应用程序专用的应用表空间

    Oracle数据库创建和授权

    - 删除临时表空间及文件 DROP TABLESPACE ELWY_TEMP INCLUDING CONTENTS AND DATAFILES; -- 创建临时表空间 CREATE TEMPORARY TABLESPACE ELWY_TEMP TEMPFILE 'E:\DataBase\Oracle\ELWY_TEMP.DBF' SIZE 100m ...

    Oracle常用的命令如何查看表的结构

    10 temporary tablespace temp --临时表空间为temp 11 profile default --受profile文件的限制 12 quota unlimited on 表空间名; --在表空间下面建表不受限制 4.创建角色 create role 角色名 identified by 密码;...

    Oracle表空间的增删改

    2.临时性表空间,如temp。只用于保存系统中短期活动的数据 3.撤销表空间,如undo。用来帮助回退未提交的事务数据 创建表空间 通过create tablespace 命令创建表空间,如: create tablespace test datafile ‘d:\...

    Oracle数据库操作

    3、创建角色用户,点击【安全性】---【用户】节点右击创建,输入用户名称,口令和确认口令,表空间的默认值上一步创建的表空间,临时表空间为temp, 点击【角色】选项卡,为当前用户添加DBA角色。 备份数据库: ...

    shell脚本操作oracle删除表空间、创建表空间、删除用户

    oracle下表空间的导出,用户的删除,表空间.../bin/shoraclehome=$ORACLE_HOMEecho $oraclehomelocaldir=”/oracle/data”echo $localdir#删除已经存在的临时dmp文件rm -rf $localdir/$2temp.dmprmresult=$?echo “rm

    Temp-Table-In-Oracle.rar_TEMP TABLE orac_Table_oracle ppt

    Oracle 临时表的使用技巧说明,挺好的资料。

    数据库表空间介绍

    什么是表空间? Oracle数据库包含逻辑结构和物理文件。数据库的物理结构是指构成数据库的一组操作系统文件数据库的逻辑结构是指...临时表空间是通用的,所的用户都使用TEMP作为临时表空间。一般只有tmp一个临时表空间

    2010年oracle命令176页完整版型

    临时表空间: create temporay tablespae tmp_tablespace_name tempfile '/XXX/xxx/datafile_name1.ora' size 4095M; 回滚表空间: create undo tablespace undotbs_tablespace datafile '/XXX/xxx/datafile_name1....

    oracle 创建表空间步骤代码

    /*第1步:创建临时表空间 */create temporary tablespace user_temp tempfile ‘D:\oracle\oradata\Oracle9i\user_temp.dbf’ size 50m autoextend on next 50m maxsize 20480m extent management local;...

    Oracle最常用的语句

     temporary tablespace temp --临时表空间为temp  profile default --受profile文件的限制  quota unlimited on 表空间名; --在表空间下面建表不受限制 4.创建角色  create role 角色名 identified by 密码; 5....

    Oracle常用SQL

    本人多年来的常用并实用的所有Oracle Sql,其中包含大数据异常处理的sql,如当表超过1亿行时,就要使用“17临时表空间【Temp】操作.sql”来增加临时表空间,因为默认只有32G(注:临时表空间与表空间是不一样的东东...

    Oracle9i数据库系统概述.pdf

    索引段一般存储在INDX表空间下 临时段:在执行查询、排序、等操作时, Oracle自动在TEMP表空间上创建一个临时段。 撤消段(回退段):记录数据库中所有事务 修改前的数据值,这些数据用于读一致性、回 退事务、恢复...

    Oracle数据库管理员技术指南

    7.8.7 恢复丢失的临时表空间 7.8.8 只读表空间丢失 7.8.9 索引表空间丢失 7.9 恢复联机重做日志 7.9.1 联机重做日志组某个成员丢失 7.9.2 非活动的重做日志组丢失 7.9.3 活动重做日志组丢失 7.10 恢复丢失的...

    软件学院ORACLE数据库课程习题

    软件学院数据库课程习题 51 下列哪项后台服务进程不是ORACLE例程必须要启动的( ) (A) DBWn (B) LGWR (C) SMON ...(A) 临时表空间TEMP (B) 用户表空间USER (C) 索引表空间INDEX (D) 系统表空间SYSTEM

Global site tag (gtag.js) - Google Analytics