Shulou(Shulou.com)06/01 Report--

Today, I would like to talk to you about the temporary tablespace contraction of the new features of Oracle 11g. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

New feature of Oracle 11g-temporary tablespace contraction

Starting at 11g, you can use the ALTER TABLESPACE tablespace_name SHRINK SPACE command to shrink the temporary tablespace, or you can use the ALTER TABLESPACE tablespace_name SHRINK TEMPFILE command to shrink the temporary file, which uses the optional KEEP clause to specify the lower limit of the tablespace or temporary file.

11g New View: DBA_TEMP_FREE_SPACE

This view is used to view temporary tablespace usage information at the tablespace level.

Column name description

TABLESPACE_NAME tablespace name

Total size of TABLESPACE_SIZE tablespaces in bytes

ALLOCATED_SPACE has allocated tablespaces in bytes, including those currently allocated and in use and currently allocated and reusable.

FREE_SPACE total remaining free space in bytes, including currently allocated but reusable and unallocated space.

SQL > select * from dba_temp_free_space

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

--

TEMP 135266304 31457280 133169152

Based on the information provided by DBA_TEMP_FREE_SPACE, you can use the ALTER TABLESPACE command to shrink temporary tablespaces online.

SQL > alter tablespace temp shrink space keep 100m

Tablespace altered.

SQL > select * from dba_temp_free_space

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

TEMP 105906176 2097152 103809024

Or shrink a temporary file:

SQL > col file_name format A50

SQL > select file_name,bytes from dba_temp_files

FILE_NAME BYTES

/ u01/app/oracle/oradata/ora11g/temp01.dbf 2088960

/ u01/app/oracle/oradata/ora11g/temp02.dbf 103817216

SQL > alter tablespace temp shrink tempfile'/ u01 keep

Tablespace altered.

SQL > select * from dba_temp_free_space

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

TEMP 66043904 3129344 63963136

After reading the above, do you have any further understanding of the temporary tablespace contraction of Oracle 11g's new features? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.