Shrinking Temporary Tablespaces
When a large job that uses a temporary tablespace finishes executing, the database doesn’t immediately release the space used by the job in the temporary tablespace, even after the job completes. You can get the free space back faster sometimes by dropping the temporary tablespace and creating a smaller one instead, but then it may not be easy to do this on a live database because users may require the temporary tablespace for various operations. Oracle Database 11g lets you shrink a locally managed temporary tablespace online, thus enabling you to control the space allocated to temporary operations in the database. You can shrink both temporary tablespaces and individual tempfiles.
Use the shrink space clause within an alter tablespace command to shrink a temporary tablespace, as shown here:
SQL> alter tablespace temp shrink space;
To shrink a tempfile, use the shrink tempfile clause, as shown here:
SQL> alter tablespace temp shrink
tempfile '/u01/app/oracle/oradata/or11/temp01.dbf';
The shrink space command in the first example shrinks all tempfiles to a database-determined minimum size, 1 MB. The database takes into account the temporary tablespace storage requirements when determining the minimum size of the tempfiles. You can override this default behavior by specifying a minimum size for the temporary tablespace after a shrink operation, as shown here:
SQL> alter tablespace temp shrink space keep 100m;
The keep clause lets you specify the minimum value for the temporary tablespace named TEMP. The following examples illustrate how Oracle approaches a temporary tablespace shrinking operation. In this example, the temporary tablespace TEMP has two tempfiles, each sized at 1 GB, thus making the total size of the temporary tablespace 2 GB. You issue the following alter tablespace statement to shrink the temporary tablespace to 1 GB.
SQL> alter tablespace temp shrink space keep 1000m;
Tablespace altered.
Because the combined size of the two tempfiles in the TEMP tablespace is 2 GB, you’d assume that Oracle would shrink both tempfiles to about 500 MB each, to get a total of 1 GB, when you issue the alter tablespace statement shown here. However, this isn’t what happens, as you can see by issuing the following query:
SQL> select file#, name, bytes/1024/1024 MB from v$tempfile;
FILE# NAME MB
------ ------------------------------------ --------
1 /u01/app/oracle/temp/temp01.dbf 999.9375
2 /u01/app/oracle/temp/temp02.dbf
Oracle does shrink the TEMP tablespace from 2 GB to 1 GB, but not by shrinking both tempfiles by an equal amount. It shrinks the file temp01.dbf by less than 1 MB and the file temp02.dbf by over 999 MB. You can specify a minimum space that the database must retain in a specific tempfile, by specifying the keep clause in the alter tablespace . . . shrink statement, as follows:
SQL> alter tablespace temp shrink space
tempfile '/u01/app/oracle/temp02.dbf'
keep 500m;
Tablespace altered.
This statement will shrink just the datafile temp02 and leave the other tempfiles in the tablespace alone.
You can query the new DBA_TEMP_FREE_SPACE view to get information about temporary tablespace usage, as shown in this example:
SQL> select * from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
--------------- --------------- --------------- -----------
TEMP 41943040 41943040 40894464
The column definiton as following:
TABLESPACE_SIZE NUMBER: Total size of the tablespace, in bytes
ALLOCATED_SPACE NUMBER: Total allocated space, in bytes, including space that is currently allocated and used and space that is currently allocated and available for reuse
FREE_SPACE NUMBER: Total free space available, in bytes, including space that is currently allocated and available for reuse and space that is currently unallocated
Tablespace Option for Creating Temporary Tables
When you created a global temporary table in Oracle Database 10g, you didn’t have to specify a tablespace. In Oracle Database 11g, you can specify a tablespace clause when creating a temporary table. If you omit the tablespace clause, the database creates the global temporary table in the default temporary tablespace for the database. The database also stores the indexes you create on the global temporary table in the same tablespace as the temporary table, you can't designate other tablespace for tempoary index.
The ability to specify the tablespace when creating a global temporary table means that you can now assign a proper extent size for a temporary table to deal with its sort usage. Different tables might use the temporary space differently, and the capability to assign different extent sizes to them leads to better performance.
参 考至: 《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》
《Oracle® Database Reference 11g Release 1 (11.1)》
http://www.oracle-base.com/articles/11g/temporary-tablespace-enhancements-11gr1.php
本文原创,转载请注明出处、作者
如有错误,欢迎指正
邮箱:czmcj@163.com
相关推荐
解决Oracle 碎片整理 问题。比较Oracle中的alter table t move和alter table t shrink space。Oracle 10g Shrink Table 详解。
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 7、查看数据库库对象 select owner, object_type, status, count(*) count# from all_objects group by owner, object_...
High Performance Images Shrink Load and Deliver Images for Speed 英文epub
oracle数据表碎片整理 alter table <table_name> shrink space [ <null> | compact | cascade ];
High Performance Images Shrink Load and Deliver Images for Speed 英文mobi 本资源转载自网络,如有侵权,请联系上传者或csdn删除 本资源转载自网络,如有侵权,请联系上传者或csdn删除
Oracle Database 10g Release 2 introduced Transportable Database (TDB), which reduces the amount of time and effort required to migrate a database between platforms that share the same endian format ...
环境:OS:Red Hat Enterprise Linux AS release 4 (Nahant)DB:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production 一台Oracle10gR2数据库报出如下错误:ORA-1653: unable to extend table ...
Guided hybrid input-and-output (GHIO), HIO and shrink-wrap Matlab functions.zip
WinMP3Shrink 音乐压缩软件
1.领域:matlab,Shrink小波变换算法 2.内容:基于Shrink小波变换的数据去噪可以设置硬阈值和软阈值+代码操作视频 3.用处:用于Shrink小波变换算法编程学习 4.指向人群:本硕博等教研学习使用 5.运行注意事项:...
PDF Shrink是一个PDF文件体积压缩器,生成的文件大小适合作为电子邮件附件或在手机屏幕上阅读。PDF Shrink还可以进行文件压缩以减少生PDF文件的大小。
这个程序是为了实现图片(包括彩色图和灰度图)的放缩功能,能满足任意指定放缩倍数或任意目标尺寸,原理是依据双线性插值。
这是一个基于Matlab平台实现的图像放缩的代码,可以实现任意倍数,任意指定尺寸的放缩要求。原理是基于最近点近似。
因此oracle提供了shrink space碎片整理功能。对于索引,可以采取rebuild online的方式进行碎片整理,一般来说,经常进行DML操作的对象DBA要定期进行维护,同时注意要及时更新统计信息!一:准备测试数据,使用HR用户...
0.13um-shrink工艺的嵌入式闪存的耐久性特性研究_V2.0.zip
前端开源库-shrink-ray-current收缩光线流,node.js压缩中间件,支持brotli和zopfli
JavaScript
DVD影片备份工具(DVD Shrink) 3.20 绿色汉化版
可将PDF压缩。一般办公网络传输 压缩50%-80%
近期来,FreeList的重要作用逐渐为Oracle DBA所认识,网上也出现一些相关的讨论。本文以FreeList为线索对Oracle的存储管理的原理进行较深入的探讨,涉及Oracle段区块管理的原理,FreeList算法等。而与FreeList密切...