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 10g版本中,`SHRINK SPACE`命令进一步完善,提供了更多的选项,如`COMPACT`和`CASCADE`。`COMPACT`选项用于压缩数据,`CASCADE`则会同时压缩关联的索引。 总结来说,Oracle中的碎片整理是数据库维护的重要...
《Oracle Database 11g:新特性为DBAs与开发者带来革新》一书,由Sam R. Alapati和Charles Kim合著,深入探讨了Oracle Database 11g的全新功能,旨在帮助数据库管理员(DBAs)和开发人员掌握最新的Oracle数据库管理...
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_...
Oracle 10g数据库管理员手册主要关注的是提升数据库性能、安全性和管理效率的新特性。以下是一些关键特性的详细介绍: 1. **透明数据加密 (Transparent Data Encryption, TDE)**: TDE 是一种增强的安全功能,允许...
SHRINK SPACE`命令可以回收表空间,但这个特性仅在Oracle 10g及以上版本支持。 - `ALTER TABLESPACE ... COALESCE`可以整合表空间碎片,提高空间利用率,但不会缩小文件大小。 - 若要查看表所占用的块数,可以...
在讨论Oracle 10g中的高水位标记(HWM)原理及性能优化策略之前,我们首先要了解HWM是什么以及它在Oracle数据库中的作用。HWM是Oracle数据库中用于区分数据段中已使用块和未使用块的界限。在全表扫描操作中,Oracle...
启用行移动后,当执行`ALTER TABLE SHRINK SPACE`时,Oracle会自动移动行到新的位置,从而重置高水位线。 - **收缩表空间**: ```sql ALTER TABLE table_name SHRINK SPACE; ``` 这条命令用于收缩表空间,即...
在Oracle 10g及后续版本中,Flashback技术主要包括以下几个方面: 1. **Flashback Database** - **定义**:类似于RMAN的不完全恢复,能够将整个数据库恢复到过去某个时间点的状态。 - **实现方式**:依赖于...
临时表 (Temporary Table) 临时表是在会话期间存储临时数据的特殊类型表。这部分可能涉及如何创建和使用临时表。 ### 18. 自动数据库诊断监控 (ADDM) ADDM是Oracle用来自动诊断数据库性能问题的一个工具。这一节...
- **使用SHRINK命令**:从10g开始,Oracle引入了更为简便的SHRINK命令,它允许用户通过简单的命令来整理表碎片,而且无需重建索引。具体操作如下: - **整理表,不影响DML操作**: ```sql alter table TABLE_...
Oracle数据库10g是Oracle公司推出的一个重要版本,它引入了许多自我管理的功能,极大地减轻了DBA(数据库管理员)的工作负担。本部分主要探讨Oracle 10g在自我管理数据库方面的核心特性,包括应用程序和SQL管理、...
6. **自动段空间管理(ASSM)**:Oracle从10g版本开始引入ASSM,它使用Bitmap Bitmap Free List(BBFL)来管理表空间中的空闲空间,有助于减少碎片的产生。 保持段的碎片低对数据库性能至关重要,因为它可以提高I/O...
High Performance Images Shrink Load and Deliver Images for Speed 英文epub
本文将深入探讨“shrink_zoom_bilinear.zip_bilinear shrink_zoom”这一程序,该程序专门用于实现图像的缩放操作,尤其是基于双线性插值的算法。 双线性插值是一种在二维空间中进行数据插值的方法,广泛应用于图像...
### 如何保持Oracle数据库优良性能 #### 一、理解Oracle数据库分区的重要性和作用 在大型Oracle数据库环境中,优化性能和管理效率是至关重要的任务之一。根据实践经验,大多数数据库空间往往被少数几个表占据。...
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 ...