About allocated_space and free_space of dba_temp_free_space

Each Oracle version has many new features and technologies. These technical features can greatly improve Oracle operation efficiency and application performance, or improve our daily work capabilities.

Temp tablespace is a special tablespace object in the Oracle tablespace system. Temporary tablespaces are mainly used to support the use of temporary tablespaces, and sort group action space overflow and temporary storage. Our use of Temp is mostly limited to space allocation and size settings. There are a series of new features for Temp tablespace at 11g, which can help us to a great extent.
 
View dba_temp_free_space is a new view introduced by 11 GB, which describes the usage of temporary tablespace.

-------------------------------------- Split line --------------------------------------

 

Oracle Undo image data exploration

Oracle ROLLBACK and Undo)

The Undo tablespace cannot be opened because it is damaged.

How to handle Undo tablespace failures

Oracle Undo tablespace reconstruction and restoration

-------------------------------------- Split line --------------------------------------

 

SQL> select * from dba_temp_free_space;

 

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 481296384 481296384 479199232

 

I recently talked to a friend about the meanings of allocated_space and free_space. I decided to prove the conclusion through some experiments.

 

1. Prepare the environment

 

I use the Oracle11gR2 environment for testing.

 

SQL> select * from v $ version;

 

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-Production

PL/SQL Release 11.2.0.3.0-Production

CORE 11.2.0.3.0 Production

TNS for Linux: Version 11.2.0.3.0-Production

NLSRTL Version 11.2.0.3.0-Production

 

Currently, only the default Temp temporary tablespace is included.

 

SQL> select file_name, file_id, tablespace_name from dba_temp_files;

 

FILE_NAME FILE_ID TABLESPACE_NAME

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

/U01/app/oradata/ORA11G/datafi 1 TEMP

Le/o1_mf_temp_92t73qm8 _. tmp

 

Note: to reduce the impact of the system on the experiment results, I have created a new temporary tablespace. Through a series of Typical operations, the field change form is monitored to prove the experiment conclusion.

 

2. Create a tablespace

 

First, create the temporary tablespace temptest. Previously, we checked the free disk space:

 

[Oracle @ SimpleLinux ~] $ Df-h

Filesystem Size Used Avail Use % Mounted on

/Dev/sda1 48G 24G 22G 52%/

Tmpfs 6.0G 256 M 5.8G 5%/dev/shm

/Dev/mapper/VolGrp01-lv1

194 M 5.6 M 179 M 4%/voltest01

 

The current/directory is 48 gb in size, with 24 GB space available. Create a tablespace as follows:

 

SQL> create temporary tablespace temptest tempfile size 5G

2 extent management local uniform size 1 m;

 

Tablespace created

 

Compared with creating a data table space of the same size, it is faster to execute a statement to create a temporary tablespace. Then, view the disk space.

 

[Oracle @ SimpleLinux ~] $ Df-h

Filesystem Size Used Avail Use % Mounted on

/Dev/sda1 48G 24G 22G 52%/

Tmpfs 6.0G 256 M 5.8G 5%/dev/shm

/Dev/mapper/VolGrp01-lv1

194 M 5.6 M 179 M 4%/voltest01

 

There is no change in capacity. If Oracle creates 5 GB space, it will at least be displayed on the disk size. However, from the Oracle internal registration system and file system, we can see the generation of temporary files.

 

SQL> select file_name, file_id, tablespace_name from dba_temp_files;

 

FILE_NAME FILE_ID TABLESPACE_NAME

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

/U01/app/oradata/ORA11G/datafi 1 TEMP

Le/o1_mf_temp_92t73qm8 _. tmp

 

/U01/app/oradata/ORA11G/datafi 2 TEMPTEST

Le/o1_mf_temptest_9j80859z _. tm

P

 

[Oracle @ SimpleLinux datafile] $ ls-l | grep temptest

-Rw-r ----- 1 oracle oinstall 5368717312 Feb 19 o1_mf_temptest_9j80859z _. tmp
 
 

This is a feature of temporary Oracle data files. Unlike temporary Oracle files, temporary Oracle files do not occupy sufficient space after being created. We all have this experience, creating a large data table space. The create/add process consumes a lot of time based on different system IO situations. However, the temporary file is not. A temporary file of dozens of GB can be created quickly.
 
However, this process is actually a "blind eye ". Although a temporary file is created in Oracle, the file system also allocates a display size file, but the space is not actually allocated. This is called a "sparse file" in some documents. The file architecture is in scope, but there is no actual writing process. Allocated in TempFile is also related to sparse files.
 
Note: This tells us that the system deployers should pay attention to this feature of temporary files and should not think that there is a lot of space on the disk.

In this case, observe the dba_temp_free_space view. The result is as follows:

 

SQL> select * from dba_temp_free_space;

 

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE

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

TEMP 481296384 481296384 480247808

Temtest 5368709120 1048576 5367660544

 

Tablespace_size is 5 bytes 709120bytes, equivalent to 5 GB. This is our definition of the temporary tablespace Temptest.

Allocated_space is 1048576 bytes, equivalent to 1 M. We have created a temporary file. Even in a sparse structure, some metadata in the file system occupies space. At this time, allocated_space = 1 m is also reasonable.
 
Free_Space is 5367660544 bytes, equivalent to 5119 M. If 1 M (allocated_size) is added, it is 5120 M, equivalent to 5 GB, which corresponds to Tablespace_size.
 
Currently, the database does not use temptest for operations. Therefore, for new temporary files, we can draw the following conclusions:

 

Ü the newly created data file is a "sparse file". Although it is successfully created, it is not completely written in the file system, and the space occupied is not allocated;

Ü when a new file is created: Allocated_space indicates the metadata information at the beginning;

Ü when a new file is created: Free_Space indicates no allocated space;

 

Now we use temporary tables for space usage.

For more details, please continue to read the highlights on the next page:

1 2 Next Page